mybatis 动态 SQL

案例引入：

点击学生管理和查询按钮查询的都是学生信息，那么这两个功能用的是同一条查询语句还是不同的(或者说是后端调用的是一个方法还是两个方法)？

• 答案当然是调用同一个方法，只不过点击学生管理时没有姓名或学号约束，在点击查询的时候有可能是通过姓名查询，有可能是通过学号查询，还有可能通过姓名和学号两个条件查询，但是两个功能的核心sql其实只有一条(select * from student)，在点击学生管理时只不过姓名和学号传入的是NULL值，在点击查询通过学号或者姓名查询时，学号和姓名哪个不为空就将哪个值拼接到where子句后，这就要用到动态 sql 来实现了，用不同的方法起码需要三条sql(姓名为空，学号为空，两个都为空)，非常麻烦，所以最好的办法就是使用mybatis提供的动态sql，在sql中加入判断。

什么是动态sql

• 可以根据具体的参数条件，来对 sql 语句进行动态拼接

• 动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。

比如在以前的开发中，由于不确定查询参数是否存在，大多程序员会使用类似于where 1 = 1 来作为前缀，然后后面用and拼接要查询的参数，这样就算要查询的参数为空，也能够正确执行查询，如果不加1 = 1，则参数为空的话， SQL 语句就会变成SELECT * FROM student WHERE ，SQL不合法。

<select id="findStudents" resultType="Student">
    select * from student
    where 1 = 1
        <if test="num!=null">
            and num = #{num}
        </if>
        <if test="name!=null">
            and name = #{name}
        </if>
        <if test="gender!=null">
            and gender = #{gender}
        </if>
    </where>
</select>

MyBatis 中用于实现动态 SQL 的元素主要有：

• If

  • 可以对传入的条件进行判断

if test= "条件"

//方法定义
Student findStudent(Student student);

<!-- 示例 -->
<select id="findStudent" resultType="Student">
     SELECT * FROM student WHERE gender = #{gender}
     <if test="name != null">
         AND name like #{name}
     </if>
</select>

测试：

 @Test                                                                 
 public void findStu() {                                               
     SqlSession sqlSession1 = MybatisUtil.getSqlSession();             
     StudentDao studentDao1 = sqlSession1.getMapper(StudentDao.class); 
                                                                       
     Student student = new Student();                                  
     student.setName("张三");                                            
     student.setGender("男");                                           
     studentDao1.findStudent1(student);                                
                                                                       
     sqlSession1.close();                                              
 }                                                                     

• where

  • 对于查询条件个数不确定的情况，可使用元素。

  • 当 where 标签内的 if 有成立的，就会动态添加一个 where 关键字

  • 如果 where 后面有 and、or 这种关键字，也会动态删除

<!-- 示例 -->
<select id="findStudents" resultType="Student">
    select * from student
    <where>
        <if test="num!=null">
            and num = #{num}
        </if>
        <if test="name!=null">
            and name = #{name}
        </if>
        <if test="gender!=null">
            and gender = #{gender}
        </if>
    </where>
</select>

测试：

• trim
  • where 标签其实用 trim 也可以表示，当 WHERE 后紧随 AND 或则 OR 的时候，就去除 AND 或者 OR。
  • prefix 前缀，prefixOverrides 覆盖首部指定内容

<!--
   <trim prefix="where" prefixOverrides="and|or">
    trim 可以自定义指定关键字，覆盖掉指定开头的关键字
-->
<select id="findStudents" resultType="Student">
    select * from student
    <trim prefix="where" prefixOverrides="and|or">
        <if test="num!=null">
            and num = #{num}
        </if>
        <if test="name!=null">
            and name = #{name}
        </if>
        <if test="gender!=null">
            and gender = #{gender}
        </if>
    </trim>
</select>

• set
  • set 元素可以用于动态包含需要更新的列，忽略其它不更新的列

  • set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号

  • 为什么要修改的值会是空的？这里我们来说一个使用场景：

    • 在疫情期间，我们如果要回家，需要向学院办理请假手续，在学院审批同意后再回家，在填写请假条时，我们只填写我们需要填写的部分(请假原因、开始时间、结束时间...)，还有一部分是需要导员填写的(意见、时间、审批人...)，这时我们在提交时导员需要填写的部分就是空值，导员在提交时，也就是做一次修改操作，我们需要填写的部分就是空值，通过动态 sql，我们只需要写一个方法就可以实现，哪些不为空就修改哪些。

<update id="updateStudent">
        update student
        <set>
            <if test="num!=null">
                num = #{num},
            </if>
            <if test="name!=null">
                name = #{name},
            </if>
            <if test="gender!=null">
                gender = #{gender},
            </if>
        </set>
        where id = #{id}
</update>

或者，你可以通过使用*trim*元素来达到同样的效果：

<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

• choose (when, otherwise)

  • 有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。

  • 传入了 “title” 就按 “title” 查找，传入了 “author” 就按 “author” 查找的情形。若两者都没有传入，就返回标记为 featured 的 BLOG（这可能是管理员认为，与其返回大量的无意义随机 Blog，还不如返回一些由管理员精选的 Blog）。

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
    <choose>
        <when test="title != null">
          AND title like #{title}
        </when>
        <when test="author != null and author.name != null">
          AND author_name like #{author.name}
        </when>
        <otherwise>
          AND featured = 1
        </otherwise>
    </choose>
</select>

• foreach

  • 主要用在构建 in 条件中，它可以在 SQL 语句中进行迭代一个集合。

  • foreach 元素的属性主要有 item，index，collection，open，separator，close。

    • item 表示集合中每一个元素进行迭代时的别名

    • index 指定一个名字，用于表示在迭代过程中，每次迭代到的位置

    • open 表示该语句以什么开始

    • separator 表示在每次进行迭代之间以什么符号作为分隔符

    • close 表示以什么结束

    • 在使用 foreach 的时候最关键的也是最容易出错的就是 collection 属性，该属性是必须指定的，但是在不同情况下，该属性的值是不一样的。

      • 如果传入的是单参数且参数类型是一个 List 的时候，collection 属性值为 list

      • 如果传入的是单参数且参数类型是一个 array 数组的时候，collection 的属性值为 array

案例：模拟批量删除

void deleteStudent(List<Integer> list);

<!--
   从list集合中每次拿出一个元素，赋给item变量
-->
<delete id="deleteStudent">
    delete from student where id in
    <foreach collection="list" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</delete>

测试：

@Test
public void delete(){
    SqlSession sqlSession = MybatisUtil.getSqlSession();
    StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
​
    List<Integer> list = new ArrayList<>();
                  list.add(5);
                  list.add(6);
                  list.add(7);
    studentDao.deleteStudent(list);
​
    sqlSession.commit();
    sqlSession.close();
}