05、动态SQL

Mybatis框架的动态SQL技术是一种根据特定条件动态拼装SQL语句的功能，它存在的意义是为了解决拼接SQL语句字符串时的痛点问题。

1、if

if标签可通过test属性的表达式进行判断，若表达式的结果为true，则标签中的内容会执行;反之标签中 的内容不会执行

1
2
3
4
5
6
7
8
9
10
11
12
13
<!--List<Emp> getEmpListByMoreTJ(Emp emp);-->
<select id="getEmpListByMoreTJ" resultType="Emp">
    select * from t_emp where 1=1
    <if test="ename != '' and ename != null">
        and ename = #{ename}
    </if>
    <if test="age != '' and age != null">
        and age = #{age}
    </if>
    <if test="sex != '' and sex != null">
        and sex = #{sex}
    </if>
</select>

2、where

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<select id="getEmpListByMoreTJ2" resultType="Emp">
    select * from t_emp
    <where>
        <if test="ename != '' and ename != null">
            ename = #{ename}
        </if>
        <if test="age != '' and age != null">
            and age = #{age}
        </if>
        <if test="sex != '' and sex != null">
            and sex = #{sex}
        </if>
    </where>
</select>

where和if一般结合使用:

a>若where标签中的if条件都不满足，则where标签没有任何功能，即不会添加where关键字

b>若where标签中的if条件满足，则where标签会自动添加where关键字，并将条件最前方多余的 and去掉

注意:where标签不能去掉条件最后多余的and

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<select id="selectSelective" resultType="com.secbro.entity.User">
  select * from t_user
  <where>
    <if test="username != null and username != ''">
      username = #{username}
    </if>
    <if test="idNo != null and idNo != ''">
      and id_no = #{idNo}
    </if>
  </where>
</select>


<select id="selectSelective" resultType="com.secbro.entity.User">
  select * from t_user
  <where>
    <if test="username != null and username != ''">
      and username = #{username}
    </if>
    <if test="idNo != null and idNo != ''">
      and id_no = #{idNo}
    </if>
  </where>
</select>

仔细观察会发现，这两种方式的区别在于第一if条件中的SQL语句是否有and。

这里就涉及到where标签的两个特性：

  • 第一，只有if标签有内容的情况下才会插入where子句；
  • 第二，若子句的开通为 “AND” 或 “OR”，where标签会将它替换去除；

所以说，上面的两种写法都是可以了，Mybatis的where标签会替我们做一些事情。

但需要注意的是：where标签只会 智能的去除（忽略）首个满足条件语句的前缀。所以建议在使用where标签时，每个语句都最好写上 and 前缀或者 or 前缀，否则像以下写法就会出现问题：

1
2
3
4
5
6
7
8
9
10
11
<select id="selectSelective" resultType="com.secbro.entity.User">
  select * from t_user
  <where>
    <if test="username != null and username != ''">
      username = #{username}
    </if>
    <if test="idNo != null and idNo != ''">
      id_no = #{idNo}
    </if>
  </where>
</select>
1
select * from t_user      WHERE username = ?  id_no = ?

因此，在使用where标签时，建议将所有条件都添加上and或or；

3、trim

上面使用where标签可以达到拼接条件语句时，自动去掉首个条件的and或or，那么如果是其他自定义的关键字是否也能去掉呢？

此时，where标签就无能为力了，该trim标签上场了，它也可以实现where标签的功能。

1
2
3
4
5
6
7
8
9
10
11
<select id="selectSelective" resultType="com.secbro.entity.User">
  select * from t_user
  <trim prefix="where" prefixOverrides="and | or ">
    <if test="username != null and username != ''">
      and username = #{username}
    </if>
    <if test="idNo != null and idNo != ''">
      and id_no = #{idNo}
    </if>
  </trim>
</select>

将上面基于where标签的写改写为trim标签，发现执行效果完全一样。而且trim标签具有了更加灵活的自定义性。

#where语句的坑

另外，在使用where语句或其他语句时一定要注意一个地方，那就是：注释的使用。

先来看例子：

1
2
3
4
5
6
7
8
9
10
11
12
<select id="selectSelective" resultType="com.secbro.entity.User">
  select * from t_user
  <where>
    <if test="username != null and username != ''">
      and username = #{username}
    </if>
    <if test="idNo != null and idNo != ''">
      /* and id_no = #{idNo}*/
      and id_no = #{idNo}
    </if>
  </where>
</select>

上述SQL语句中添加了 /**/的注释，生成的SQL语句为：

1
select * from t_user WHERE username = ? /* and id_no = ?*/ and id_no = ?

执行时，直接报错。

还有一个示例：

1
2
3
4
5
6
7
8
9
10
11
12
<select id="selectSelective" resultType="com.secbro.entity.User">
  select * from t_user
  <where>
    <if test="username != null and username != ''">
      -- and username = #{username}
      and username = #{username}
    </if>
    <if test="idNo != null and idNo != ''">
      and id_no = #{idNo}
    </if>
  </where>
</select>

生成的SQL语句为：

1
select * from t_user WHERE -- and username = ? and username = ? and id_no = ?

同样会导致报错。

这是因为我们使用 XML 方式配置 SQL 时，如果在 where 标签之后添加了注释，那么当有子元素满足条件时，除了 < !– –> 注释会被 where 忽略解析以外，其它注释例如 // 或 /**/ 或 – 等都会被 where 当成首个子句元素处理，导致后续真正的首个 AND 子句元素或 OR 子句元素没能被成功替换掉前缀，从而引起语法错误。

同时，个人在实践中也经常发现因为在XML中使用注释不当导致SQL语法错误或执行出错误的结果。强烈建议，非必要，不要在XML中注释掉SQL，可以通过版本管理工具来追溯历史记录和修改。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
<select id="getEmpListByMoreTJ" resultType="Emp">
    select * from t_emp
    <trim prefix="where" suffixOverrides="and">
        <if test="ename != '' and ename != null">
            ename = #{ename} and
        </if>
        <if test="age != '' and age != null">
            age = #{age} and
        </if>
        <if test="sex != '' and sex != null">
          sex = #{sex}
        </if>
    </trim>
</select>

trim用于去掉或添加标签中的内容 常用属性:

prefix:在trim标签中的内容的前面添加某些内容

prefixOverrides:在trim标签中的内容的前面去掉某些内容

suffix:在trim标签中的内容的后面添加某些内容

suffixOverrides:在trim标签中的内容的后面去掉某些内容

4、choose、when、otherwise

choose、when、otherwise相当于if…else if..else

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<!--List<Emp> getEmpListByChoose(Emp emp);-->
<select id="getEmpListByChoose" resultType="Emp">
    select <include refid="empColumns"></include> from t_emp
    <where>
        <choose>
            <when test="ename != '' and ename != null">
                ename = #{ename}
            </when>
            <when test="age != '' and age != null">
age = #{age}
            </when>
            <when test="sex != '' and sex != null">
sex = #{sex}
            </when>
            <when test="email != '' and email != null">
                email = #{email}
            </when>
        </choose>
    </where>
</select>

5、foreach

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<!--int insertMoreEmp(List<Emp> emps);-->
<insert id="insertMoreEmp">
    insert into t_emp values
    <foreach collection="emps" item="emp" separator=",">
        (null,#{emp.ename},#{emp.age},#{emp.sex},#{emp.email},null)
    </foreach>
</insert>
<!--int deleteMoreByArray(int[] eids);-->
<delete id="deleteMoreByArray">
    delete from t_emp where
    <foreach collection="eids" item="eid" separator="or">
eid = #{eid}
</foreach>
  </delete>
<!--int deleteMoreByArray(int[] eids);-->
<delete id="deleteMoreByArray">
    delete from t_emp where eid in
    <foreach collection="eids" item="eid" separator="," open="(" close=")">
#{eid}
    </foreach>
</delete>

属性:

collection:设置要循环的数组或集合

item:表示集合或数组中的每一个数据

separator:设置循环体之间的分隔符

open:设置foreach标签中的内容的开始符

close:设置foreach标签中的内容的结束符

6、SQL片段

sql片段，可以记录一段公共sql片段，在使用的地方通过include标签进行引入

1
2
3
4
<sql id="empColumns">
    eid,ename,age,sex,did
</sql>
select <include refid="empColumns"></include> from t_emp

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

8、script

要在带注解的映射器接口类中使用动态 SQL，可以使用 script 元素。比如:

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

9、 多数据库支持

如果配置了 databaseIdProvider，你就可以在动态代码中使用名为 “_databaseId” 的变量来为不同的数据库构建特定的语句。比如下面的例子：

1
2
3
4
5
6
7
8
9
10
11
<insert id="insert">
  <selectKey keyProperty="id" resultType="int" order="BEFORE">
    <if test="_databaseId == 'oracle'">
      select seq_users.nextval from dual
    </if>
    <if test="_databaseId == 'db2'">
      select nextval for seq_users from sysibm.sysdummy1"
    </if>
  </selectKey>
  insert into users values (#{id}, #{name})
</insert>