MyBatis 的动态SQL使用

动态 SQL 是 MyBatis 一个很强大的特性,我们经常会因为不同的条件去拼接 sql,一不小心可能就会少了个空格,或是列名后面多了个逗号。利用动态 sql 可以很灵活的去为我们拼接sql,且相对简洁清晰。

动态 SQL 元素

这里我们主要来了解下面几个元素:

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

if

我们先看一下语法

1
2
3
4
5
6
7
<select id="selectUser" resultMap="upmsUser">
select * from upms_user
where is_locked = 1
<if test="loginname != null">
and loginname like #{loginname}
</if>
</select>

test 里面是对条件的判断,若里面为 true 的话,便会加上 if 元素里的内容。如上面的loginname 不为空的话,sql语句就会是

select * from upms_user where is_locked = 1 and loginname like #{loginname}

test 里面的 与 和 或 不能用 &&||,而是要使用 andor

test 属性值是不能包含 ‘<’ 字符的, 所以判断大小不能用 <<=,用 ltlte 代替。>>= 虽然可以用,但规范起见,最好也用 gtgte 代替

1
2
3
4
5
6
7
8
9
<select id="selectUser" resultMap="upmsUser">
select * from upms_user
where is_locked = 1
<if test="upmsUser.age gte 10 and upmsUser.age lt 20">
<if test="loginname != null and loginname != '' ">
and loginname like #{loginname}
</if>
</if>
</select>

choose (when, otherwise)

有时候我们只想应用多个条件语句中的一个,我们就要用到 choose (when, otherwise) 了。它与 java 中的 if...else if...elseswitch 很像。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<select id="selectUser" resultMap="upmsUser">
select * from upms_user
where is_locked = 1
<choose>
<when test="loginname != null and loginname != '' ">
and loginname like #{loginname}
</when>
<when test="upmsUser != null and upmsUser.realname != '' ">
and realname like #{upmsUser.realname}
</when>
<otherwise>
and phone is not null
</otherwise>
</choose>
</select>

test 属性值的语法和 if 元素的是一样的

trim (where, set)

where

这里我们先回到 if 的第一个例子,假如把 is_locked 字段也设为动态的

1
2
3
4
5
6
7
8
9
10
<select id="selectUser" resultMap="upmsUser">
select * from upms_user
where
<if test="locked != null">
is_locked = #{locked}
</if>
<if test="loginname != null and loginname != '' ">
and loginname like #{loginname}
</if>
</select>

如果两个 if 的条件都成立,这时是没有问题的,但有两个情况下会出问题。

1、如果两个 if 都不成立,sql 语句就会变成

select * from upms_user where
(多了一个where)

2、如果 locked 为 null,而后面的 if 成立,sql 语句就会变成

select * from upms_user where and loginname like #{loginname}
(多了一个and)

这里其实我们可以在 where 后面加上一个 1=1,但 mabatis 给了我们一个更好的解决方法

1
2
3
4
5
6
7
8
9
10
11
<select id="selectUser" resultMap="upmsUser">
select * from upms_user
<where>
<if test="locked != null">
is_locked = #{locked}
</if>
<if test="loginname != null and loginname != '' ">
and loginname like #{loginname}
</if>
</where>
</select>

使用 where 元素,它会在没有条件成立时去掉where,在有条件成立的时候检查语句的开头,并去掉多余的 and 或是 or

set

同样的道理,我们在更新数据时使用 if 动态更新语句也可能会出现问题。这里可以使用 set 元素

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

它会删除set语句后面可能会多出来的逗号

trim

有时候我们在其他地方可能也需要类似的功能,去除某些语句开头或结尾多出来的符号或是连接词。这时候我们可以使用 trim 去定制某些功能

1
2
3
4
<!-- prefix:前缀词  prefixOverrides:语句前多余要去除的内容   suffixOverrides:语句后多余要去除的内容 -->
<trim prefix="" prefixOverrides="" suffixOverrides="">
...
</trim>

这里我们可以自定义与 where 和 set 元素等价的 trim 元素

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

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

foreach

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

1
2
3
4
5
6
7
8
<select id="selectUser" resultMap="upmsUser">
select * from upms_user
where user_id in
<foreach item="item" index="index" collection="userIdList"
open="(" separator="," close=")">
#{item}
</foreach>
</select>

collection 表示要遍历的集合或数组,item 表示当前遍历的对象,index 表示当前遍历的序号或键,在元素体内使用的集合项(item)和索引(index)变量。

open、close、separator 可以指定开头与结尾的字符串以及在迭代结果之间放置分隔符

你可以将任何可迭代对象(如 List、Set 等)、Map 对象或者数组对象传递给 foreach 作为集合参数。当使用可迭代对象或者数组时,index 是当前迭代的次数,item 的值是本次迭代获取的元素。当使用 Map 对象(或者 Map.Entry 对象的集合)时,index 是键,item 是值

bind

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

1
2
3
4
5
<select id="selectUser" resultMap="User">
<bind name="pattern" value="'%' + user.getLoginname() + '%'" />
SELECT * FROM upms_user
WHERE loginname LIKE #{pattern}
</select>