Mybatis的特性详解——动态SQL

---

前言

动态 SQL 是 MyBatis 的强大特性之一。在 JDBC 或其它类似的框架中，开发人员通常需要手动拼接 SQL 语句。根据不同的条件拼接 SQL 语句是一件极其痛苦的工作。例如，拼接时要确保添加了必要的空格，还要注意去掉列表最后一个列名的逗号。而动态 SQL 恰好解决了这一问题，可以根据场景动态的构建查询。

动态 SQL 只有几个基本元素，与 JSTL 或 XML 文本处理器相似，十分简单明了，大量的判断都可以在 MyBatis 的映射 XML 文件里配置，以达到许多需要大量代码才能实现的功能。

好处：：动态 SQL 大大减少了编写代码的工作量，更体现了 MyBatis 的灵活性、高度可配置性和可维护性。

---

一、动态sql的元素

MyBatis 的动态 SQL 包括以下几种元素，如下表所示

1.MyBatis if标签：条件判断

MyBatis if 类似于 Java 中的 if 语句，是 MyBatis 中最常用的判断语句。使用 if 标签可以节省许多拼接 SQL 的工作，把精力集中在 XML 的维护上。if 语句使用方法简单，常常与 test 属性联合使用。
语法如下:

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

当判断条件为 true 时，才会执行所包含的 SQL 语句。
最常见的场景是在 if 语句中包含 where 子句，例如。

<select id="selectAllBaseData" resultMap="myResult">
    select id,name from baseData
    <if test="name != null">
        where name like #{name}
    </if>
</select>
1
2
3
4
5
6

以上代表表示根据name去查找相应的信息，但是name是一个可填可不填的条件，不填写的时候不作为查询条件。

可多个 if 语句同时使用。以下语句表示为可以按照 name 或者 id 进行模糊查询。如果您不输入，则返回所有的记录。但是，如果你传递了任意一个参数，它就会返回与给定参数相匹配的记录：

<select id="selectAllBaseData" resultMap="myResult">
    select id,name from baseData
    <if test="name != null">
        where name like #{name}
    </if>
    <if test="id != id">
        AND id like #{id}
    </if>
</select>
1
2
3
4
5
6
7
8
9

2.MyBatis choose、when和otherwise标签

MyBatis 中动态语句 choose-when-otherwise 类似于 Java 中的 switch-case-default 语句。由于 MyBatis 并没有为 if 提供对应的 else 标签，如果想要达到...... 的效果，可以借助 、、 来实现。
动态语句 choose-when-otherwise 语法如下。

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

choose 标签按顺序判断其内部 when 标签中的判断条件是否成立，如果有一个成立，则执行相应的SQL 语句，choose 执行结束；
如果都不成立，则执行 otherwise 中的 SQL 语句。
这类似于 Java 的 switch 语句；

• choose 为 switch，
• when 为 case，
• otherwise 则为 default。

示例

以下示例要求：
当网站名称不为空时，只用网站名称作为条件进行模糊查询；
当网站名称为空，而网址不为空时，则用网址作为条件进行模糊查询；
当网站名称和网址都为空时，则要求网站年龄不为空.

<mapper namespace="net.biancheng.mapper.WebsiteMapper">
    <select id="selectWebsite">
        SELECT id,name,url,age,country
        FROM website WHERE 1=1
        <choose>
            <when test="name != null and name !=''">
                AND name LIKE CONCAT('%',#{name},'%')
            </when>
            <when test="url != null and url !=''">
                AND url LIKE CONCAT('%',#{url},'%')
            </when>
            <otherwise>
                AND age is not null
            </otherwise>
        </choose>
    </select>
</mapper>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

这样 MyBatis 就会根据参数的设置进行判断来动态组装 SQL，以满足不同业务的要求。远比 Hibernate 和 JDBC 中大量判断 Java 代码要清晰和明确。

3.MyBatis where标签

where 标签

where 标签主要用来简化 SQL 语句中的条件判断，可以自动处理 AND/OR 条件，语法如下

<where>
    <if test="判断条件">
        AND/OR ...
    </if>
</where>
1
2
3
4
5

if 语句中判断条件为 true 时，where 关键字才会加入到组装的 SQL 里面，否则就不加入。where 会检索语句，它会将 where 后的第一个 SQL 条件语句的 AND 或者 OR 关键词去掉。

示例

<select id="selectWebsite" resultType="net.biancheng.po.Website">
    select id,name,url from website
    <where>
        <if test="name != null">
            AND name like #{name}
        </if>
        <if test="url!= null">
            AND url like #{url}
        </if>
    </where>
</select
1
2
3
4
5
6
7
8
9
10
11

4.MyBatis set标签

在 Mybatis 中，update 语句可以使用 set 标签动态更新列。set 标签可以为 SQL 语句动态的添加 set 关键字，剔除追加到条件末尾多余的逗号。 代码如下。

    <!--使用set元素动态修改一个网站记录 -->
    <update id="updateWebsite">
        UPDATE website
        <set>
            <if test="name!=null">name=#{name}，
            <if test="url!=null">url=#{url}，
        </set>
        WHERE id=#{id}
    </update>
1
2
3
4
5
6
7
8
9

5.MyBatis foreach标签

对于一些 SQL 语句中含有 in 条件，需要迭代条件集合来生成的情况，可以使用 foreach 来实现 SQL 条件的迭代。
Mybatis foreach 标签用于循环语句，它很好的支持了数据和 List、set 接口的集合，并对此提供遍历的功能。语法格式如下。

<foreach item="item" index="index" collection="list|array|map key" open="(" separator="," close=")">
   #{item}
</foreach>
1
2
3

foreach 标签主要有以下属性，说明如下:

• item：表示集合中每一个元素进行迭代时的别名。
• index：指定一个名字，表示在迭代过程中每次迭代到的位置。
• open：表示该语句以什么开始（既然是 in 条件语句，所以必然以(开始）。
• separator：表示在每次进行迭代之间以什么符号作为分隔符（既然是 in 条件语句，所以必然以,作为分隔符）。
• close：表示该语句以什么结束（既然是 in 条件语句，所以必然以)开始）。

使用 foreach 标签时，最关键、最容易出错的是 collection 属性，该属性是必选的，但在不同情况下该属性的值是不一样的，主要有以下 3 种情况：

• 如果传入的是单参数且参数类型是一个 List，collection 属性值为 list。
• 如果传入的是单参数且参数类型是一个 array 数组，collection 的属性值为 array。
• 如果传入的参数是多个，需要把它们封装成一个 Map，当然单参数也可以封装成 Map。Map 的 key 是参数名，collection 属性值是传入的 List 或 array 对象在自己封装的 Map 中的 key。

示例

修改array里面id为这些的数据status改为0

  update project set status = 0 where id in
  <foreach item="id" collection="array" open="(" separator="," close=")">
      #{id}
  </foreach>
1
2
3
4

注意事项：在使用 foreach 标签时，应提前预估一下 collection 对象的长度。因为大量数据的 in 语句会影响性能，且还有一些数据库会限制执行的 SQL 语句长度。

6.MyBatis bind标签

每个数据库的拼接函数或连接符号都不同，例如 MySQL 的 concat 函数、Oracle 的连接符号“||”等。这样 SQL 映射文件就需要根据不同的数据库提供不同的实现，显然比较麻烦，且不利于代码的移植。MyBatis 提供了 bind 标签来解决这一问题。
bind 标签可以通过 OGNL 表达式自定义一个上下文变量。
比如，按照网站名称进行模糊查询，SQL 映射文件如下。

<select id="selectWebsite" >
    <bind name="pattern" value="'%'+_parameter+'%'" />
    SELECT id,name,url,age,country
    FROM website
    WHERE name like #{pattern}
</select>
1
2
3
4
5
6

bind 元素属性如下:

• value：对应传入实体类的某个字段，可以进行字符串拼接等特殊处理。
• name：给对应参数取的别名。

以上代码中的“_parameter”代表传递进来的参数，它和通配符连接后，赋给了 pattern，然后就可以在 select 语句中使用这个变量进行模糊查询，不管是 MySQL 数据库还是 Oracle 数据库都可以使用这样的语句，提高了可移植性。
大部分情况下需要传递多个参数，下面为传递多个参数时 bind 的用法示例。

示例

//mapper文件中入参
public List<User> selectUser(User user);
-- xml中
<select id="selectUser" resultType="User">
    <bind name="pattern_name" value="'%'+name+'%'" />
    <bind name="pattern_url" value="'%'+url+'%'" />
    SELECT id,name,url,age,country
    FROM User
    WHERE name like #{pattern_name}
    AND url like #{pattern_url}
</select>
1
2
3
4
5
6
7
8
9
10
11

7.MyBatis trim标签

在 MyBatis 中除了使用 if+where 实现多条件查询，还有一个更为灵活的元素 trim 能够替代之前的做法。
trim 一般用于去除 SQL 语句中多余的 AND 关键字、逗号，或者给 SQL 语句前拼接 where、set 等后缀，可用于选择性插入、更新、删除或者条件查询等操作。trim 语法格式如下。

<trim prefix="前缀" suffix="后缀" prefixOverrides="忽略前缀字符" suffixOverrides="忽略后缀字符">
    SQL语句
</trim>
1
2
3

trim 中属性说明如下
示例

<select id="selectUser" resultType="User">
    SELECT id,name,url,age,country
    FROM User
    <trim prefix="where" prefixOverrides="and">
        <if test="name != null and name !=''">
            AND name LIKE CONCAT ('%',#{name},'%')
        </if>
        <if test="url!= null">
            AND url like concat ('%',#{url},'%')
        </if>
    </trim>
</select>
1
2
3
4
5
6
7
8
9
10
11
12

---

仰天大笑出门去，我辈岂是蓬蒿人