Explanation of multi-condition query in MyBatis

One: Use dynamic SQL to complete multi-condition query

a:Use if+where to implement multi-condition query

First of all, the scenario requirements, there is a grade and class table, the first requirement is Conditional query based on fuzzy query name and age, interface layer method

 public  List<student>  getStudentByIf(student stu);

The second is the configuration of the mapping file

 <select>select * from student       <where>   <if>   and stuAge>#{stuAge}       </if> <if> and stuName LIKE '%' #{stuName} '%'             </if>   </where></select>

Test

 studentDao dao = MyBatis.getSessionTwo().getMapper(studentDao.= "z"List<student> list="----------"+</student>

------------zhangyu<br>---- ------zy<br>----------zy<br>----------zhang

 <br>

b: choose when classification

This method is the same as the choose loop structure principle in java. To judge multiple situations, just modify the mapping file

Interface class

  public List<student> getAllStudentByLike(Map<string> userMap);  //使用map作为参数</string></student>

Mapping file

 <span style="color: #0000ff"><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 style="color: #800000">where</span><span style="color: #0000ff">></span><span style="color: #0000ff"><span style="color: #800000">choose</span><span style="color: #0000ff">></span><span style="color: #0000ff"><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 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></span></span></span></span></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>

Result

zhangyu
zy
zy
zhang

c: There are three ways to use foreach to complete complex queries,

The first one: the parameters passed in are array types

   

//传一组 xueshengID public List<student> getStudentBystuId_foreach_array(Integer[] ints);




映射文件配置 <!--跟据学生id查询学生Interger-->
    <select>select * from student<if>0">where stuId IN/*数组形式传入学生Id*/<foreach>  #{stu}</foreach>
        </if>
    </select></student>

 

Test Class

 

  Integer[] ints = {2,3,4};
        List<student> list = dao.getStudentBystuId_foreach_array(ints);for (student item:list) {
            System.out.println(item.getStuName());
        }</student>

 

 Two: Pass in the list collection

   public List<student> getStudentBystuId_foreach_list(List<integer> list);</integer></student>

  <!--跟据学生id查询学生list方式--><select>select * from student<if>0">where stuId IN
        /*集合形式传入学生Id*/<foreach>#{stu}</foreach></if></select>

Test:

 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());
        }</student></integer></integer>

Third type: Based on Map collection

 public List<student> getStudentBystuId_foreach_map(Map<string> stuMap);</string></student>

 <!--跟据学生id查询学生map方式--><select>select * from student where stuId IN
        /*集合形式传入学生Id*/<foreach>    <!--collection是自己定义的，就是map的key值-->#{stu}</foreach></select>

<span style="color: #008000">  Map<string> 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());
        }</student></integer></integer></string></string></span><span style="color: #008000"><br></span>

Print results You can do the following.

d;Two implementation methods of one-to-many

Mainly due to different configurations in resultMapper

Interface method

 public grade getGradeById(int gradeId);

Mapping file configuration

 <span style="color: #008000"><!--</span><span style="color: #008000">实现一 对多的第一中实现</span><span style="color: #008000">--></span><span style="color: #0000ff"><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 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 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 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 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 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 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 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 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 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 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></span></span></span></span></span></span></span></span></span></span></span>

    <select>select * from grade,student where grade.gradeId = student.stuGrade and gradeId = #{gradeId}</select><!--ddddddddddddddddddd--><select>select * from grade where gradeId=#{gradeId}</select><select>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());
       }

    }

Both methods can be achieved, the printing effect

Option 1 printing effect

==> Preparing: select * from grade,student where grade.gradeId = student.stuGrade and gradeId = ? ============A sql<br>==> Parameters: 1(Integer)<br>< ;== Row: 1, S1297, 3, zy, 20, 1<br>zhangyu <br>zy<br>zy

Process finished with exit code 0

Option 2 printing effect

==> Preparing: select * from grade where gradeId=? ==========First sql<br>==> Parameters: 1(Integer)<br>====> Preparing: select * from student where stuGrade = ? ==========The second sql<br>====> Parameters: 1(Long)<br>zhangyu<br> zy<br>zy

Process finished with exit code 0

The above is the detailed content of Explanation of multi-condition query in MyBatis. For more information, please follow other related articles on the PHP Chinese website!