MyBatis 动态 SQL

动态 SQL 是 MyBatis 一个强大的特性，它可以帮助程序员减轻根据不同条件拼接 SQL 语句的痛苦。MyBatis 动态 SQL 元素与 JSTL 或其他类 XML 文件处理器相似。MyBatis 采用功能强大的基于 OGNL 的表达式来消除其他元素。

MyBatis 常用的动态 SQL 元素包括：

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

1.准备

假设有一个 user 表，建表语句如下：

    mysql> create table user(
        -> id int primary key auto_increment,
        -> username varchar(20),
        -> password varchar(20),
        -> sex varchar(10),
        -> age int,
        -> phone varchar(20),
        -> address varchar(20));

插入一些数据(这里展示一条),如：

insert into user(username,password,sex,age,phone,address) values('Tom','123456','male',18,'18200123456','chengdu');

与 user 表映射的实体类 User.java 如下：

public class User {

    private Integer id; // id，主键
    private String username; // 用户名 
    private String password; // 密码
    private String sex; // 性别
    private Integer age; // 年龄
    private String phone; // 电话
    private String address; // 地址

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }
}

2.if

动态 SQL 通常要做的事情是有条件地包含 where 子句的一部分，if 在 where 子句中做简单的条件判断。

<select id="dynamicIfTest" resultType="User">
    select * from user where sex = 'male'
    <if test="address != null">
        and address = #{address}
    </if>
</select>

上述语句的意思是，如果没有提供参数 address，语句相当于 select * from user where sex = 'male'， 会查询所有性别为 male 的用户的所有信息，如果提供了参数 address，那么就会查询性别为 male，且地址为传入内容的用户的所有信息。

如果想可选两个条件进行查询只需加入另一条件即可，如：

<select id="dynamicIfTest" resultType="User">
    select * from user where sex = 'male'
    <if test="address != null">
        and address = #{address}
    </if>
    <if test="phone != null">
        and phone like #{phone}
    </if>
</select>

3.choose

如果只想从所有条件中择其一二，可采用 choose 元素。 choose 相当于 java 语言中的 switch，与 jstl 中的 choose 很类似。

<select id="dynamicChooseTest" resultType="User">
    select * from user where sex = 'male'
    <choose>
        <when test="username != null">
            and username like #{username}
        </when>
        <when test="phone != null">
            and phone like #{phone}
        </when>
        <otherwise>
            and address = 'chengdu'
        </otherwise>
    </choose>
</select>

choose 的用法和 java 的 switch 类似，按照顺序执行，当 when 中有条件满足时，则跳出 choose，所以在 when 和 otherwise 中只会输出一个，当所有 when 的条件都不满足就输出 otherwise 的内容。

在上述的语句中，如果第一个 when 满足，则查询性别为 male，用户名包含所传入内容的用户所有信息，如果第一个不满足，第二个 when 满足，则查询性别为 male，电话包含所传入内容的用户所有信息，如果所有的 when 都不满足，则查询性别为 male，地址为 chengdu 的用户所有信息。

4.trim (where,set)

来看下面的例子：

<select id="dynamicSqlTest" resultType="User">
    select * from user where
    <if test="address != null">
        address = #{address}
    </if>
    <if test="phone != null">
        and phone like #{phone}
    </if>
</select>

可以看到，如果两个条件都不满足的话，SQL 语句变成：select * from user where ，如果只满足后一个条件呢，SQL 语句变成：select * from user where and phone like #{phone}。这两种情况均会导致查询失败。

因此，动态 SQL 引入了 trim、where 和 set 元素。

（1） trim

trim 元素可以给自己包含的内容加上前缀（prefix）或加上后缀（suffix），也可以把包含内容的首部（prefixOverrides）或尾部（suffixOverrides）某些内容移除。

<select id="dynamicTrimTest" resultType="User">
    select * from user
    <trim prefix="where" prefixOverrides="and |or ">
        <if test="address != null">
            address = #{address}
        </if>
        <if test="phone != null">
            and phone like #{phone}
        </if>
    </trim>
</select>

（2） where

where 元素知道只有在一个以上的 if 条件满足的情况下才去插入 where 子句，而且能够智能地处理 and 和 or 条件。

<select id="dynamicWhereTest" resultType="User">
    select * from user
    <where>
        <if test="address != null">
            address = #{address}
        </if>
        <if test="phone != null">
            and phone like #{phone}
        </if>
    </where>
</select>

（3） set

set 元素可以被用于动态包含需要更新的列，而舍去其他的。

<update id="dynamicSetTest">
    update User
    <set>
        <if test="phone != null">phone=#{phone},</if>
        <if test="address != null">address=#{address}</if>
    </set>
    where id=#{id}
</update>

set 元素会动态前置 set 关键字，同时也会消除无关的逗号。与其等价的 trim 语句如下：

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

5.foreach

foreach 元素常用到需要对一个集合进行遍历时，在 in 语句查询时特别有用。

foreach 元素的主要属性：

• item：本次迭代获取的元素，当使用字典或者 Map 时，index 是键，item 是值
• index：当前迭代的次数，当使用字典或者 Map 时，index 是键，item 是值
• open：开始标志
• separator：每次迭代之间的分隔符
• close：结束标志
• collection：该属性是必须指定的，在不同情况下，该属性的值是不一样的，主要有一下3种情况：
  单参数且为 List 时，值为 list
  单参数且为 array 数组时，值为 array
  多参数需封装成一个 Map，map 的 key 就是参数名，collection 属性值是传入的 List 或 array 对象在自己封装的 map 里面的 key

<select id="dynamicForeachTest" resultType="User">
    select * from user where id in
    <foreach item="item" index="index" collection="list"
        open="(" separator="," close=")">
        #{item}
    </foreach>
</select>

在上述语句中，collection 值为 list，因此其对应的 Mapper 中：

public List<User> dynamicForeachTest(List<Integer> ids);  

6.bind

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

<select id="dynamicBindTest" resultType="User">
    <bind name="pattern" value="'%' + _parameter.getPhone() + '%'" />
    select * from user where phone like #{pattern}
</select>

参考链接

• mybatis中文文档
• mybatis 动态sql语句
• 《Spring+MyBatis 企业应用实战》