MyBatis中的多条件查询讲解

巴扎黑

发布时间：2017-07-23 13:45:48

2672人浏览过

来源于php中文网

原创

一：使用动态sql完成多条件查询

a:使用if+where实现多条件查询

首先场景需求，有个年级和班级表，第一个要求是根据模糊查询姓名，和年龄大小进行条件查询，接口层方法

 public  List<student>  getStudentByIf(student stu);

　　其次是映射文件的配置

衣购网站项目(三层开发)源码

商品查询功能提供了一个快速查看商品的途径。商品查询分为基本查询和高级查询。基本查询：提供关键字和商品大类两种条件的查询，用户可以只填写关键字或者选择商品大类或者关键字和商品大类都填写来查询商品。高级查询：提供关键字，商品大类，商品小类，商品价格范围四种条件的查询，用户可以任意填写其中一种或几种的查询条件来查询想要了解的商品信息。商品查询功能大大的方便了用户，提高了网站的用户体验。（5）帮助系统模块

下载

 <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

 <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

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>

<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>

打印结果可以执行以下。

d;一对多的两种实现方式

主要是resultMapper里的配置不同

接口方法

 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)
zhangyu
zy
zy

Process finished with exit code 0

方案二打印效果

==> Preparing: select * from grade where gradeId=? ==========第一条sql
==> Parameters: 1(Integer)
====> Preparing: select * from student where stuGrade = ? ==========第二条sql
====> Parameters: 1(Long)
zhangyu
zy
zy

Process finished with exit code 0

在Java中如何搭建一个基础的在线教育平台_JavaWeb应用解析

在Java中如何使用Spring Boot构建项目_Java快速开发框架说明

在Java里为什么需要反射_Java反射机制原理解析

在Java里如何设计一个简单的学生成绩查询系统_Java数据库操作解析

在Java中反射机制的核心价值是什么_Java反射原理解析

本站声明：本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn

上一篇：Extjs如何动态修改gridPanel列头信息和数据的解决方法下一篇：对比比较@RequestParam与@PathVariable的用法区别

作者最新文章

HTML中用正则表达式实现表格验证

2018-05-29 15:14

Swiper轮播图源码分享解析