一：使用動態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>
<br/> <br/><br/>
studentDao dao = MyBatis.getSessionTwo().getMapper(studentDao.= "z"List<student> list="----------"+
----------zy
----------zhang<br/>
b：choose when 分類
這種方式和java中choose循環結構原理是一樣的，判斷多種情況，只要修改一下映射檔即可
#
public List<student> getAllStudentByLike(Map<String, Object> userMap); //使用map作为参数
# 映射檔案
<span style="color: #0000ff"><</span><span style="color: #800000">select </span><span style="color: #ff0000">id</span><span style="color: #0000ff">="getAllStudentByLike"</span><span style="color: #ff0000"> parameterType</span><span style="color: #0000ff">="Map"</span><span style="color: #ff0000"> resultType</span><span style="color: #0000ff">="stu"</span><span style="color: #0000ff">></span><span style="color: #000000">select * from student</span><span style="color: #0000ff"><</span><span style="color: #800000">where</span><span style="color: #0000ff">></span><span style="color: #0000ff"><</span><span style="color: #800000">choose</span><span style="color: #0000ff">></span><span style="color: #0000ff"><</span><span style="color: #800000">when </span><span style="color: #ff0000">test</span><span style="color: #0000ff">="stuName!=null"</span><span style="color: #0000ff">></span><span style="color: #000000"> stuName like CONCAT('%',#{stuName},'%')</span><span style="color: #0000ff"></</span><span style="color: #800000">when</span><span style="color: #0000ff">></span><span style="color: #0000ff"><</span><span style="color: #800000">when </span><span style="color: #ff0000">test</span><span style="color: #0000ff">="stuAge!=0"</span><span style="color: #0000ff">></span><span style="color: #000000"> stuAge> #{stuAge}</span><span style="color: #0000ff"></</span><span style="color: #800000">when</span><span style="color: #0000ff">><br/></span><otherwise>
1=1
</otherwise><span style="color: #0000ff"><br/></span><span style="color: #0000ff"></</span><span style="color: #800000">choose</span><span style="color: #0000ff">></span><span style="color: #0000ff"></</span><span style="color: #800000">where</span><span style="color: #0000ff">></span><span style="color: #0000ff"></</span><span style="color: #800000">select</span><span style="color: #0000ff">></span>
結果
#zhangyu zy zy zhang
//传一组 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());
}##
#public List<student> getStudentBystuId_foreach_list(List<Integer> list);
## 二種：傳入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());
}測驗：
public List<student> getStudentBystuId_foreach_map(Map<String, Object> stuMap);
三種：依據Map集合
<!--跟据学生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><span style="color: #008000"> 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());
}</span><span style="color: #008000"><br/></span>public grade getGradeById(int gradeId);
<span style="color: #008000"><!--</span><span style="color: #008000">实现一 对多的第一中实现</span><span style="color: #008000">--></span><span style="color: #0000ff"><</span><span style="color: #800000">resultMap </span><span style="color: #ff0000">id</span><span style="color: #0000ff">="gradeMapOne"</span><span style="color: #ff0000"> type</span><span style="color: #0000ff">="grade"</span><span style="color: #0000ff">></span><span style="color: #0000ff"><</span><span style="color: #800000">id </span><span style="color: #ff0000">column</span><span style="color: #0000ff">="gradeId"</span><span style="color: #ff0000"> property</span><span style="color: #0000ff">="gradeId"</span><span style="color: #0000ff">></</span><span style="color: #800000">id</span><span style="color: #0000ff">></span><span style="color: #0000ff"><</span><span style="color: #800000">result </span><span style="color: #ff0000">column</span><span style="color: #0000ff">="gradeName"</span><span style="color: #ff0000"> property</span><span style="color: #0000ff">="gradeName"</span><span style="color: #0000ff">></</span><span style="color: #800000">result</span><span style="color: #0000ff">></span><span style="color: #0000ff"><</span><span style="color: #800000">collection </span><span style="color: #ff0000">property</span><span style="color: #0000ff">="gatStudent"</span><span style="color: #ff0000"> ofType</span><span style="color: #0000ff">="stu"</span><span style="color: #0000ff">></span><span style="color: #0000ff"><</span><span style="color: #800000">id </span><span style="color: #ff0000">column</span><span style="color: #0000ff">="stuUd"</span><span style="color: #ff0000"> property</span><span style="color: #0000ff">="stuId"</span><span style="color: #0000ff">></</span><span style="color: #800000">id</span><span style="color: #0000ff">></span><span style="color: #0000ff"><</span><span style="color: #800000">result </span><span style="color: #ff0000">column</span><span style="color: #0000ff">="stuName"</span><span style="color: #ff0000"> property</span><span style="color: #0000ff">="stuName"</span><span style="color: #0000ff">></</span><span style="color: #800000">result</span><span style="color: #0000ff">></span><span style="color: #0000ff"><</span><span style="color: #800000">result </span><span style="color: #ff0000">column</span><span style="color: #0000ff">="stuAge"</span><span style="color: #ff0000"> property</span><span style="color: #0000ff">="stuAge"</span><span style="color: #0000ff">></</span><span style="color: #800000">result</span><span style="color: #0000ff">></span><span style="color: #0000ff"></</span><span style="color: #800000">collection</span><span style="color: #0000ff">></span><span style="color: #0000ff"></</span><span style="color: #800000">resultMap</span><span style="color: #0000ff">></span><span style="color: #008000"><!--</span><span style="color: #008000">实现一 对多的第二中实现</span><span style="color: #008000">--></span><span style="color: #0000ff"><</span><span style="color: #800000">resultMap </span><span style="color: #ff0000">id</span><span style="color: #0000ff">="gradeMap"</span><span style="color: #ff0000"> type</span><span style="color: #0000ff">="entity.grade"</span><span style="color: #0000ff">></span><span style="color: #0000ff"><</span><span style="color: #800000">id </span><span style="color: #ff0000">column</span><span style="color: #0000ff">="gradeId"</span><span style="color: #ff0000"> property</span><span style="color: #0000ff">="gradeId"</span><span style="color: #0000ff">></</span><span style="color: #800000">id</span><span style="color: #0000ff">></span><span style="color: #0000ff"><</span><span style="color: #800000">result </span><span style="color: #ff0000">column</span><span style="color: #0000ff">="gradeName"</span><span style="color: #ff0000"> property</span><span style="color: #0000ff">="gradeName"</span><span style="color: #0000ff">></</span><span style="color: #800000">result</span><span style="color: #0000ff">></span><span style="color: #0000ff"><</span><span style="color: #800000">collection </span><span style="color: #ff0000">property</span><span style="color: #0000ff">="gatStudent"</span><span style="color: #ff0000"> ofType</span><span style="color: #0000ff">="student"</span><span style="color: #ff0000"> select</span><span style="color: #0000ff">="getStudentById"</span><span style="color: #ff0000"> column</span><span style="color: #0000ff">="gradeId"</span><span style="color: #0000ff">></</span><span style="color: #800000">collection</span><span style="color: #0000ff">> <!--column的值主要作为下次查询的条件，既查询学生的条件--></span><span style="color: #0000ff"></</span><span style="color: #800000">resultMap</span><span style="color: #0000ff">><br/></span>
<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><br/>
<br/>
@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)
<== Columns: gradeId, gradeName, stuId, stuName, stuAge, stuGrade <br/><== Row: 1, S1297, 2, zhangyu, 19, 1<br/>< ;== Row: 1, S1297, 3, zy, 20, 1
zy
zyProcess finished with exit code 0<br/>方案二列印效果<br/><br/>==> Preparing: select * from grade where gradeId=? ==========第一sql
==> Parameters: 1(Integer)
<== Columns: gradeId, gradeName<br/><== Row: 1, S1297<br/>====> Preparing: select * from student where stuGrade = ? ==========第二條sql
====> Parameters: 1(Long)
<==== Columns: stuId, stuName, stuAge, stuGrade
<==== Row: 2, zhangyu, 19, 1
<==== Row 20, 1
<==== Row: 4, zy, 21, 1
<==== Total: 3
<== Total: 1. zy
以上是MyBatis中的多條件查詢解說的詳細內容。更多資訊請關注PHP中文網其他相關文章！
