一：使用动态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.= "z"list<student> list="----------"+
----------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>
@testpublic 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)
310972c59990f340cb058bc11fe5bf2f preparing: select * from grade where gradeid=? ==========第一条sql
==> parameters: 1(integer)
816ef50cd7bc472de795aa370f68ea84 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
以上就是mybatis中的多条件查询讲解的详细内容。