一：使用動(dòng)態(tài)SQL完成多條件查詢
a:使用if+where實(shí)現(xiàn)多條件查詢
首先場(chǎng)景需求躲查，有 個(gè)年級(jí)和班級(jí)表术吝，第一個(gè)要求是根據(jù)模糊查詢姓名转捕，和年齡大小進(jìn)行條件查詢园细，接口層方法
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>
測(cè)試
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循環(huán)結(jié)構(gòu)原理是一樣的，判斷多種情況昔馋，只要修改一下映射文件即可
接口 類
public List<student> getAllStudentByLike(Map<String, Object> userMap); //使用map作為參數(shù)
映射文件
<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>
結(jié)果
zhangyu
zy
zy
zhang
c:使用foreach完成復(fù)雜 查詢筹吐，有三種方式，
第一種：傳入的參數(shù)為數(shù)組類型
//傳一組 xueshengID
public List<student> getStudentBystuId_foreach_array(Integer[] ints);
映射文件配置
<!--跟據(jù)學(xué)生id查詢學(xué)生Interger-->
<select id="getStudentBystuId_foreach_array" resultMap="studentList">
select * from student
<if test="array.length>0">
where stuId IN
/*數(shù)組形式傳入學(xué)生Id*/
<foreach collection="array" item="stu" open="(" separator="," close=")">
#{stu}
</foreach>
</if>
</select>
測(cè)試類
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);
<!--跟據(jù)學(xué)生id查詢學(xué)生list方式-->
<select id="getStudentBystuId_foreach_list" resultMap="studentList">
select * from student
<if test="list.size>0">
where stuId IN
/*集合形式傳入學(xué)生Id*/
<foreach collection="list" item="stu" open="(" separator="," close=")">
#{stu}
</foreach>
</if>
</select>
測(cè)試：
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());
}
第三種：根據(jù)Map集合
public List<student> getStudentBystuId_foreach_map(Map<String, Object> stuMap);
<!--跟據(jù)學(xué)生id查詢學(xué)生map方式-->
<select id="getStudentBystuId_foreach_map" resultMap="studentList">
select * from student where stuId IN
/*集合形式傳入學(xué)生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());
}
打印結(jié)果可以執(zhí)行以下丘薛。
d;一對(duì)多的兩種實(shí)現(xiàn)方式
主要是resultMapper里的配置不同
接口方法
public grade getGradeById(int gradeId);
映射文件配置
<!--實(shí)現(xiàn)一 對(duì)多的第一中實(shí)現(xiàn)-->
<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>
<!--實(shí)現(xiàn)一 對(duì)多的第二中實(shí)現(xiàn)-->
<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的值主要作為下次查詢的條件，既查詢學(xué)生的條件-->
</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>
<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());
}
}
兩種方式都能實(shí)現(xiàn)邦危，打印效果
方案一打印效果
==> 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
