动态 SQL

动态SQL

MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其他类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句有多么痛苦。拼接的时候要确保不能忘了必要的空格，还要注意省掉列名列表最后的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。

通常使用动态 SQL 不可能是独立的一部分,MyBatis 当然使用一种强大的动态 SQL 语言来改进这种情形,这种语言可以被用在任意的 SQL 映射语句中。

动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似。在 MyBatis 之前的版本中,有很多的元素需要来了解。MyBatis 3 大大提升了它们,现在用不到原先一半的元素就可以了。MyBatis 采用功能强大的基于 OGNL 的表达式来消除其他元素。

  • if
  • choose (when, otherwise)
  • trim (where, set)
  • foreach

if

动态SQL通常要做的事情就是有条件的包含where子句的一部分。比如：

1
2
3
4
5
6
7
8
<select id="findUser"
     resultType="User">
  SELECT * FROM User 
  WHERE power = #{power}
  <if test="name != null">
    AND name like #{title}
  </if>
</select>

如果没有传入name。那么所有power = power的User都会返回，否则就会返回根据那么模糊查询的值。（就这个例子而言，细心的读者会发现其中的参数值是可以包含一些掩码或通配符的）。

如果多加个属性adress，只需要多加一个条件：

1
2
3
4
5
6
7
8
9
10
11
<select id="findUser"
     resultType="User">
  SELECT * FROM User 
  WHERE power = #{power}
  <if test="name != null">
    AND name like #{title}
  </if>
  <if test="adress!=null">
  	AND adress = #{adress}
  </if>
</select>

choose, when, otherwise

Mybatis提供的choose元素，就像是JAVA中的switch语句。还是上面的例子，改一下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<select id="findUser"
     resultType="User">
  SELECT * FROM User WHERE power = #{power}
  <choose>
     <when test="name != null">
       AND name like #{title}
     </when>
     <when  test="adress!=null">
       AND adress = #{adress}
     </when>
     <otherwise>
       AND age >= 18
     <otherwise>
  <choose>
</select>

这样一来，sql语句就变成了提供了什么就查什么。

trim, where

前面的已经解决了SQL动态问题。现在再来考虑，这里将 power = 'power'也设置成动态条件。

1
2
3
4
5
6
7
8
9
10
11
12
13
<select id="findUser"
     resultType="User">
  SELECT * FROM User WHERE
  <if test="power != null">
  	power = #{power}
  </if>
  <if test="name != null">
    AND name like #{title}
  </if>
  <if test="adress!=null">
  	AND adress = #{adress}
  </if>
</select>

那么会发现一个问题，如果所有条件都没有匹配上，sql语句就会变成这样：

1
SELECT * FROM User WHERE

如果只有第二个条件匹配上，sql语句就会变成这样：

1
2
SELECT * FROM User WHERE
AND name like #{title}

这两种情况都会导致查询失败。但是Mybatis有方法解决，把where语句换成<where>标签：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<select id="findUser"
     resultType="User">
  SELECT * FROM User 
  <WHERE>
    <if test="power != null">
      power = #{power}
    </if>
    <if test="name != null">
      AND name like #{title}
    </if>
    <if test="adress!=null">
      AND adress = #{adress}
    </if>
  </WHERE>
</select>

<where>会自动去除AND或OR，如果没有借据号这个问题，那就可以用自定义元素trim

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

prefixOverrides属性会忽略通过管道分隔的文本序列（注意此例中的空格也是必要的）。它带来的结果就是所有在 prefixOverrides属性中指定的内容将被移除，并且插入 prefix属性中指定的内容。

动态更新

类似的用于动态更新语句的解决方案叫做 set。set 元素可以被用于动态包含需要更新的列，而舍去其他的。比如：

1
2
3
4
5
6
7
8
9
10
<update id="updateUser">
  update User
    <set>
      <if test="name != null">name=#{username},</if>
      <if test="password != null">password=#{password},</if>
      <if test="email != null">email=#{email},</if>
      <if test="sex != null">sex=#{sex}</if>
    </set>
  where id=#{id}
</update>

这里，set 元素会动态前置 SET 关键字，同时也会消除无关的逗号，因为用了条件语句之后很可能就会在生成的赋值语句的后面留下这些逗号。

foreach

动态 SQL 的另外一个常用的必要操作是需要对一个集合进行遍历，通常是在构建 IN 条件语句的时候。比如

1
2
3
4
5
6
7
8
9
<select id="selectPostIn" resultType="entity.Post">
  SELECT *
  FROM POST P
  WHERE ID in
  <foreach item="item" index="index" collection="list"
      open="(" separator="," close=")">
        #{item}
  </foreach>
</select>

collection中是传入的要遍历的数组变量，index是索引，item是把遍历结果赋值

bind

bind 元素可以从 OGNL 表达式中创建一个变量并将其绑定到上下文。比如：

1
2
3
4
5
<select id="selectBlogsLike" resultType="Blog">
  <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
  SELECT * FROM BLOG
  WHERE title LIKE #{pattern}
</select>