MyBatis 映射文件之动态 SQL 总结

1. if

通过 title 和 author 两个参数进行可选搜索。

<select id="findActiveBlogLike" resultType="Blog">
  SELECT * FROM BLOG WHERE state = 'ACTIVE'
  <if test="title != null and title != '' ">
    AND title like #{title}
  </if>
  <if test="author != null and author.name != null ">
    AND author_name like #{author.name}
  </if>
</select>

条件判断注意事项：

非 String 类型使用obj != null即可，String 通常要加一个空串的判断obj != null and obj != ''。

如果入参是一个数字类型，如 Long，但是在判空时使用obj != null and obj != ''，你会发现，当 Long 值传 0 的时候，obj != null and obj != ''条件是不成立的，这就导致了错误的结果。MyBatis 的处理方式：当发现你用一个数字类型的对象和字符串比较的时候，它会尝试将字符串转换成数字类型，MyBatis 会将空串转换成数字 0。最后的结论就是，数字类型的对象不要使用空串判断表达式obj != ''。

条件中==和!=调用的是org.apache.ibatis.ognl.ASTEq和org.apache.ibatis.ognl.ASTNotEq中的getValueBody方法。都调用了org.apache.ibatis.ognl.OgnlOps的equal方法，源码太多，就不贴出来了，感兴趣的可以从下面的方法开始去跟踪一下：

public static boolean equal(Object v1, Object v2) {
    if (v1 == null) {
        return v2 == null;
    } else if (v1 != v2 && !isEqual(v1, v2)) { //逻辑在这里面
        if (v1 instanceof Number && v2 instanceof Number) {
            return ((Number)v1).doubleValue() == ((Number)v2).doubleValue();
        } else {
            return false;
        }
    } else {
        return true;
    }
}

2. choose

有时我们不想应用到所有的条件语句，而只想选择其中一项。针对这种情况，MyBatis 提供了choose元素，它有点像 Java 中的switch语句。搭配when和otherwise使用。（我觉得更类似于Java中的if...else if...else语句，而switch语句会有switch穿透的情况。）

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

提供了 title 就按 title 查找，提供了 author 就按 author 查找，若两者都没有提供，就返回所有符合条件的 BLOG（实际情况可能是由管理员按一定策略选出 BLOG 列表，而不是返回大量无意义的随机结果）。

3. where

where元素只会在至少有一个子元素的条件返回SQL子句的情况下才去插入WHERE子句。而且，若紧跟WHERE后面的语句开头为AND或OR，where元素会将它们自动去除。

<select id="findActiveBlogLike" resultType="Blog">
  SELECT * FROM BLOG
  <where>
    <if test="state != null">
         AND state = #{state}
    </if>
    <if test="title != null">
        AND title like #{title}
    </if>
    <if test="author != null and author.name != null">
        AND author_name like #{author.name}
    </if>
  </where>
</select>

这里注意如果所有条件都不满足的话会返回所有的记录，实际使用中应该进行相应处理。上面SQL如果所有条件都不满足会变成：

SELECT * FROM BLOG

如果 Mapper 接口使用的是一个对象进行查询结果接收，如果查询到的记录条数超过一条，则会报出org.apache.ibatis.exceptions.TooManyResultsException异常。如果使用的是List进行接收，则没有问题。

如果where元素没有按正常套路出牌，我们可以通过自定义trim元素来定制where元素的功能。比如，和where元素等价的自定义trim元素为：

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>

prefixOverrides属性会忽略通过管道分隔的文本序列（注意示例中AND和OR后面的空格也是必要的）。它的作用是移除所有在prefixOverrides属性中指定的内容（出现在语句开头的），并且插入prefix属性中指定的内容。

4. set

set元素可以用于动态包含需要更新的列。set元素只会在至少有一个子元素的条件返回SQL子句的情况下才去插入SET子句。而且，若紧临WHERE前面的语句结尾为逗号，set元素会将多余的逗号自动去除。

<update id="updateAuthorIfNecessary">
  UPDATE Author
    <set>
      <if test="username != null">
          username=#{username},
      </if>
      <if test="password != null">
          password=#{password},
      </if>
    </set>
  WHERE id=#{id}
</update>

这里注意如果所有条件都不满足的话会返回错误的SQL（没有更新字段的UPDATE语句），实际使用中应该进行相应处理。上面SQL如果所有条件都不满足会变成：

UPDATE Author WHERE id=1

这很明显是错误的SQL语句，会报java.sql.SQLSyntaxErrorException异常。

和set元素等价的自定义trim元素：

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

suffixOverrides的作用是移除所有在suffixOverrides属性中指定的内容（出现在语句结尾的），并且插入prefix属性中指定的内容。

5. foreach

5.1. 集合遍历

动态SQL的另外一个常见的操作是对集合进行遍历，通常是在构建IN条件语句和批量插入的时候。比如：

<select id="selectPostIn" resultType="Post">
  SELECT * FROM POST P WHERE id IN
  <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
     #{item}
  </foreach>
</select>

<insert id="insertAuthor" useGeneratedKeys="true" keyProperty="id">
  INSERT INTO Author (username, password, email, bio) VALUES
  <foreach item="item" collection="list" separator=",">
    (#{item.username}, #{item.password}, #{item.email}, #{item.bio})
  </foreach>
</insert>

foreach元素的功能非常强大，它允许你指定一个集合（collection），声明可以在元素体内使用的集合中元素（item）和索引（index）。它也允许你指定开头（open）与结尾（close）的字符串以及在迭代结果之间放置分隔符（separator）。这个元素是很智能的，因此它不会附加多余的分隔符。

可以将任何可迭代对象（如 List、Set 等）、数组、Map 对象传递给foreach作为collection参数。当使用可迭代对象或者数组时，index是当前迭代的次数，item是本次迭代获取的元素。当使用Map对象（或者Map.Entry对象的集合）时，index是key，item是value。

对于collection的值，直接写上@Param注解指定的参数名即可。如果没有注解的话，List类型参数就写list；Set类型参数就写collection；数组类型参数就写array；Map类型参数就写_parameter。

List<Author> getListByList(List<Long> ids);

<select id="getListByList" resultType="com.example.demo.model.Author">
    select * from Author where id IN
    <foreach collection="list" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

List<Author> getListBySet(Set<Long> ids);

<select id="getListBySet" resultType="com.example.demo.model.Author">
    select * from Author where id IN
    <foreach collection="collection" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

List<Author> getListByArray(Long[] ids);

<select id="getListByArray" resultType="com.example.demo.model.Author">
    select * from Author where id IN
    <foreach collection="array" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

Map 的迭代，如果参数注解为@Param("map")，map等价于没注解时的_parameter，有以下三种遍历场景：

• 如果collection为map，则index是key，item是value；
• 如果collection为map.keys，则index是当前迭代的次数，item是本次迭代获取的key。主要用于迭代所有的key。此时可通过#{map[${key}]}获取到对应的value。
• 如果collection为map.values，则index是当前迭代的次数，item是本次迭代获取的value。主要用于迭代所有的value。

List<Author> getListByMap(@Param("map") Map<String, Long> ids);

<select id="getListByMap" resultType="com.example.demo.model.Author">
    select * from Author where id IN
    <foreach collection="map" index="key" item="value" open="(" separator="," close=")">
        #{value}
    </foreach>
</select>

<select id="getListByMap" resultType="com.example.demo.model.Author">
    select * from Author where id IN
    <foreach collection="map.keys" item="key" open="(" separator="," close=")">
        #{map[${key}]}
    </foreach>
</select>

<select id="getListByMap" resultType="com.example.demo.model.Author">
    select * from Author where id IN
    <foreach collection="map.values" item="value" open="(" separator="," close=")">
        #{value}
    </foreach>
</select>

5.2. 批量执行

这个也属于集合遍历。特殊在批量 SQL 操作。

MySQL

MySQL 的批量操作是要我们主动去设置的， 在数据库的连接url上加上&allowMultiQueries=true即可。如果不进行设置，将不能进行批量 SQL 操作，会报语法错误。数据库连接示例：

jdbc.jdbcUrl=jdbc:mysql://127.0.0.1:3306/database?useUnicode=true&characterEncoding=utf8&allowMultiQueries=true

<update id="updateAllAvailable">
    <foreach collection="skuOptionList" item="item" index="index" open="" close="" separator=";">
      UPDATE t_xxx SET old_id = #{item.oldId} WHERE id = #{item.id}
    </foreach>
</update>

allowMultiQueries=true和rewriteBatchedStatements=true是 MySQL JDBC 连接参数，用于优化批量 SQL 操作，具体作用如下：

allowMultiQueries=true：允许在单个 JDBC 语句中执行多条 SQL（用分号分隔），常用于 MyBatis 批量操作，比如：

   UPDATE table1 SET col1=val1; UPDATE table2 SET col2=val2;

rewriteBatchedStatements=true：将批量插入/更新语句重写为单条多值 SQL，提升性能，需配合JDBC的addBatch()方法使用。

INSERT INTO table VALUES (1),(2),(3)

Oracle

实际的业务系统里面Oracle数据库也用的非常的多，当然，Oracle数据库不需要做特殊的配置，但是相应的 SQL 写法要做些变化。

<update id="updateAllAvailable">
    <foreach collection="skuOptionList" item="item" index="index" open="begin" close="end;" separator=";">
        UPDATE t_xxx SET old_id = #{item.oldId} WHERE id = #{item.id}
    </foreach>
</update>

6. 特殊符号

使用 MyBatis 在 XML 中编写 SQL 时如果需要输入大于小于等特殊符号时，需要写成如下形式，否则会报错，原因是 XML 中不允许出现一些特殊字符。以下是 MyBatis 中特殊符号的两种写法。

6.1. 转义字符替换

lt即英文less then小于的意思，gt即英文great then大于的意思。实体符号必须以符号&开头，以符号;结尾。虽然只有<字符和&字符对于 XML 来说是严格禁止使用的，剩下的都是合法的，但是为了减少出错，使用实体符号是一个好习惯。

常用符号：

• 大于号>，MyBatis SQL 中需要写为>。
• 大于等于号>=，MyBatis SQL 中需要写为>=。
• 小于号<，MyBatis SQL 中需要写为<。
• 小于等于号<=，MyBatis SQL 中需要写为<=。

6.2. CDATA 部件包含（不建议）

在CDATA部件内部的所有内容都会被解析器忽略。如果文本包含了很多的"<"字符和"&"字符，那么最好把他们都放到CDATA部件中。一个CDATA部件以<![CDATA[标记开始，以]]>标记结束。

大于等于    <![CDATA[ >= ]]>
小于等于    <![CDATA[ <= ]]>

注意事项：

• CDATA部件之间不能再包含CDATA部件（不能嵌套）。如果CDATA部件包含了字符]]> 或者<![CDATA[，将很有可能出错。
• 同样要注意在字符串<![CDATA[和]]>中不能有空格或者换行符。