MyBatis 动态SQL

在 MyBatis 中的动态 SQL，就是根据不同的条件生成不同的 SQL 语句。

一、if

1. 语法

1
2
3
<if test="条件">
	SQL语句
</if>

当条件成立时，if 标签内包裹的 SQL 语句生效。

2. 示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<select id="queryBlog"  resultType="pojo.Blog">
    select
           *
    from
         blog
    where
    <if test="author != null">
    	author like #{author}
    </if>
    <if test="author != null and title != null">
    	and title like #{title}
    </if>
    <if test="author == null and title != null">
    	title like #{title}
    </if>
</select>

当仅传入 author 时，SQL 语句为：

1
2
3
4
5
6
select
	*
from
	blog
where
	author like #{author}

当传入 author 和 title 时，SQL 语句为：

1
2
3
4
5
6
7
select
	*
from
	blog
where
	author like #{author}
	and title like #{title}

当仅传入 title 时，SQL 语句为：

1
2
3
4
5
6
select
	*
from
	blog
where
	title like #{title}

二、choose、when 和 otherwise

1. 说明

choose、when 和 otherwise 配合使用，它们类似于 switch 语句。

2. 语法

1
2
3
4
5
6
7
8
9
10
11
<choose>
	<when test="条件1">
		SQL语句1
	</when>
	<when test="条件2">
		SQL语句2
	</when>
	<otherwise>
		SQL语句3
	</otherwise>
</choose>

when 之间和 when 与 otherwise 是互斥的

  • 当条件 1 成立时，仅有 SQL 语句 1 生效
  • 当条件 2 成立时，仅有 SQL 语句 2 生效
  • 当条件 1 和条件 2 均不成立时，仅有 SQL 语句 3 生效

3. 示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<select id="queryBlog"  resultType="pojo.Blog">
    select
           *
    from
         blog
    <choose>
        <when test="author != null">
            where author like #{author}
        </when>
        <when test="title != null">
            where title like #{title}
        </when>
        <otherwise>
            where 1 = 1
        </otherwise>
    </choose>
</select>

当传入 author 时，SQL 语句为：

1
2
3
4
5
6
select
	*
from
	blog
where
	author like #{author}

当不传入 author 且传入 title 时，SQL 语句为：

1
2
3
4
5
6
select
	*
from
	blog
where
	title like #{title}

当 author 和 title 均没有传入时，SQL 语句为：

1
2
3
4
select
	*
from
	blog

三、where、set 和 trim

1. 问题

MyBatis 的动态 SQL 最终要拼接成 SQL 语句，所以应该遵循 SQL 的规范。

注意到在 SQL 语句中：

(1) where

1
2
3
4
where
	条件1
	and 条件2
	or 条件3

SQL 中的 where 子句，有以下要求：

  • 有条件时，应该有 where，

    无条件时，不应该有 where

  • 第一个条件前不加 and/or

    其它条件之前应该加 and/or，以连接条件

(2) set

1
2
3
4
5
6
7
8
update 
	表名
set
   	列名1 = 值1,
    列名2 = 值2,
    ···
where
	条件

SQL 中的 set 语句，有以下要求：

  • 不同的赋值语句之间用 , 进行分隔
  • 最后一个赋值语句之后不应加 ,

(3) 复杂的动态拼接

因此，MyBatis 中的动态拼接也不得不写得臃肿繁杂，例如：

1
2
3
4
5
6
7
8
9
10
where
	<if test="author != null">
		author like #{author}
	</if>
	<if test="author != null and title != null">
		and title like #{title}
	</if>
	<if test="author == null and title != null">
		title like #{title}
	</if>

MyBatis 引入了 where、set 和 trim 用于解决这一问题。

2. where

1
2
3
4
5
6
7
8
<where>
    <if test="条件1">
        SQL语句1
    </if>
    <if test="条件2">
        SQL语句2
    </if>
</where>

where 的作用是：

  • 当有条件成立时，添加 where
  • 删除第一个 SQL 语句中开头的 and/or

因此，上述案例可以重写为：

1
2
3
4
5
6
7
8
9
10
11
12
select
       *
from
     blog
<where>
    <if test="author != null">
        and author like #{author}
    </if>
    <if test="title != null">
        and title like #{title}
    </if>
</where>

3. set

1
2
3
4
5
6
7
8
<set>
	<if test="条件1">
		列名1=#{属性1},
	</if>
	<if test="条件2">
		列名2=#{属性2},
	</if>
</set>

set 的作用是：

  • 当有条件成立时，添加 set
  • 删除最后一个赋值语句之后的 ,

4. trim

(1) 说明

where 和 set 本质上是 trim 。

trim 标签的作用的：

  • 可以通过配置 prefix 或 suffix，当标签包裹语句时，增加前缀或增加后缀
  • 可以通过配置 prefixOverrides 或 suffixOverrides，删除语句开头或结尾的关键字

(2) 属性

属性 描述
prefix 增加前缀
suffix 增加后缀
prefixOverrides 删除语句开头的关键字
suffixOverrides 删除语句末尾的关键字

(3) 实现 where

1
2
3
<trim prefix="WHERE" prefixOverrides="AND |OR ">
	...
</trim>
  • 当标签包裹语句时，增加 WHERE 前缀
  • 删除语句前的 and 或 or

(4) 实现 set

1
2
3
<trim prefix="SET" suffixOverrides=",">
	...
</trim>
  • 当标签包裹语句时，增加 SET 前缀
  • 删除语句后的 ,

四、foreach

1. 语法

1
2
3
<foreach item="item" index="index" collection="集合" open="开头" separator="分隔符" close="结尾">
	SQL片段
</foreach>
  • 在 SQL 片段中，
    • 使用 #{item} 或 ${item} 获取每个元素
    • 使用 #{index} 或 ${index} 获取每个元素的下标
  • collection 指定遍历的集合
  • open 指定整个 foreach 开头要拼接的字符串
  • separator 指定每个 SQL 片段之间的分隔符
  • close 指定整个 foreach 结尾要拼接的字符串

2. 示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<select id="query" resultType="pojo.Blog">
    select
        <foreach item="item" collection="list" separator=",">
            ${item}
        </foreach>
    from
         blog
</select>

···

ArrayList<String> arrayList = new ArrayList<>();
arrayList.add("id");
arrayList.add("name");
System.out.println(mapper.query(arrayList));

五、bind

bind 元素用于创建变量。

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

六、script

在使用注解定义 SQL 映射时，可以通过 script 标签来使用动态 SQL。

1
2
3
4
5
6
7
8
9
10
11
@Update({"<script>",
    "update Author",
    "  <set>",
    "    <if test='username != null'>username=#{username},</if>",
    "    <if test='password != null'>password=#{password},</if>",
    "    <if test='email != null'>email=#{email},</if>",
    "    <if test='bio != null'>bio=#{bio}</if>",
    "  </set>",
    "where id=#{id}",
    "</script>"})
void updateAuthorValues(Author author);

参考