一:使用动态SQL完成多条件查询
a:使用if+where实现多条件查询
首先场景需求,有 个年级和班级表,第一个要求是根据模糊查询姓名,和年龄大小进行条件查询,接口层方法
public List<student> getStudentByIf(student stu);
其次是映射文件的配置
<select id="getStudentByIf" parameterType="stu" resultType="stu"> select * from student <where> <if test="stuAge!=0"> and stuAge>#{stuAge} </if> <if test="stuName!=null"> and stuName LIKE '%' #{stuName} '%' </if> </where> </select>
测试
studentDao dao = MyBatis.getSessionTwo().getMapper(studentDao.class); student stu = new student(); stu.setStuName("z"); // stu.setStuAge(19); List<student> list= dao.getStudentByIf(stu); for (student item:list) { System.out.println("----------"+item.getStuName()); }
打印效果
----------zhangyu
----------zy
----------zy
----------zhang
b:choose when 分类
这种方式和java中choose循环结构原理是一样的,判断多种情况,只要修改一下映射文件即可
接口 类
public List<student> getAllStudentByLike(Map<String, Object> userMap); //使用map作为参数
映射文件
<select id="getAllStudentByLike" parameterType="Map" resultType="stu"> select * from student <where> <choose> <when test="stuName!=null"> stuName like CONCAT('%',#{stuName},'%') </when> <when test="stuAge!=0"> stuAge> #{stuAge} </when>
<otherwise>
1=1
</otherwise>
</choose> </where> </select>
结果
zhangyu
zy
zy
zhang
c:使用foreach完成复杂 查询,有三种方式,
第一种:传入的参数为数组类型
//传一组 xueshengID public List<student> getStudentBystuId_foreach_array(Integer[] ints); 映射文件配置 <!--跟据学生id查询学生Interger--> <select id="getStudentBystuId_foreach_array" resultMap="studentList"> select * from student <if test="array.length>0"> where stuId IN /*数组形式传入学生Id*/ <foreach collection="array" item="stu" open="(" separator="," close=")"> #{stu} </foreach> </if> </select>
测试类
Integer[] ints = {2,3,4}; List<student> list = dao.getStudentBystuId_foreach_array(ints); for (student item:list) { System.out.println(item.getStuName()); }
第二种:传入list集合
public List<student> getStudentBystuId_foreach_list(List<Integer> list);
<!--跟据学生id查询学生list方式--> <select id="getStudentBystuId_foreach_list" resultMap="studentList"> select * from student <if test="list.size>0"> where stuId IN /*集合形式传入学生Id*/ <foreach collection="list" item="stu" open="(" separator="," close=")"> #{stu} </foreach> </if> </select>
测试:
studentDao dao = MyBatis.getSessionTwo().getMapper(studentDao.class); Integer ints = 2; List<Integer> list = new ArrayList<Integer>(); list.add(ints); List<student> stulist = dao.getStudentBystuId_foreach_list(list); for (student item:stulist) { System.out.println(item.getStuName()); }
第三种:根据Map集合
public List<student> getStudentBystuId_foreach_map(Map<String, Object> stuMap);
<!--跟据学生id查询学生map方式--> <select id="getStudentBystuId_foreach_map" resultMap="studentList"> select * from student where stuId IN /*集合形式传入学生Id*/ <foreach collection="stuId" item="stu" open="(" separator="," close=")"> <!--collection是自己定义的,就是map的key值--> #{stu} </foreach> </select>
Map<String ,Object> stumap = new HashMap<String, Object>(); List<Integer> listStuId = new ArrayList<Integer>(); listStuId.add(2); listStuId.add(3); listStuId.add(4); stumap.put("stuId",listStuId); List<student> list = dao.getStudentBystuId_foreach_map(stumap); for (student item:list ) { System.out.println(item.getStuName()); }
打印结果可以执行以下。
d;一对多的两种实现方式
主要是resultMapper里的配置不同
接口方法
public grade getGradeById(int gradeId);
映射文件配置
<!--实现一 对多的第一中实现--> <resultMap id="gradeMapOne" type="grade"> <id column="gradeId" property="gradeId"></id> <result column="gradeName" property="gradeName"></result> <collection property="gatStudent" ofType="stu"> <id column="stuUd" property="stuId"></id> <result column="stuName" property="stuName"></result> <result column="stuAge" property="stuAge"></result> </collection> </resultMap> <!--实现一 对多的第二中实现--> <resultMap id="gradeMap" type="entity.grade"> <id column="gradeId" property="gradeId"></id> <result column="gradeName" property="gradeName"></result> <collection property="gatStudent" ofType="student" select="getStudentById" column="gradeId"></collection> <!--column的值主要作为下次查询的条件,既查询学生的条件--> </resultMap>
<select id="getGradeById" resultMap="gradeMapOne"> select * from grade,student where grade.gradeId = student.stuGrade and gradeId = #{gradeId} </select> <!--ddddddddddddddddddd--> <select id="getGradeById" resultMap="gradeMap"> select * from grade where gradeId=#{gradeId} </select> <select id="getStudentById" resultType="entity.student"> select * from student where stuGrade = #{stuGrade} </select>
@Test public void TestConn(){ gradeDao dao = MyBatis.getSessionTwo().getMapper(gradeDao.class); grade grade = dao.getGradeById(1); for (student item:grade.getGatStudent() ) { System.out.println(item.getStuName()); } }
两种方式都能实现,打印效果
方案一打印效果
==> Preparing: select * from grade,student where grade.gradeId = student.stuGrade and gradeId = ? ============一条sql
==> Parameters: 1(Integer)
<== Columns: gradeId, gradeName, stuId, stuName, stuAge, stuGrade
<== Row: 1, S1297, 2, zhangyu, 19, 1
<== Row: 1, S1297, 3, zy, 20, 1
<== Row: 1, S1297, 4, zy, 21, 1
<== Total: 3
zhangyu
zy
zy
Process finished with exit code 0
方案二打印效果
==> Preparing: select * from grade where gradeId=? ==========第一条sql
==> Parameters: 1(Integer)
<== Columns: gradeId, gradeName
<== Row: 1, S1297
====> Preparing: select * from student where stuGrade = ? ==========第二条sql
====> Parameters: 1(Long)
<==== Columns: stuId, stuName, stuAge, stuGrade
<==== Row: 2, zhangyu, 19, 1
<==== Row: 3, zy, 20, 1
<==== Row: 4, zy, 21, 1
<==== Total: 3
<== Total: 1
zhangyu
zy
zy
Process finished with exit code 0