动态SQL

MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其它类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句的痛苦。例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。

虽然在以前使用动态 SQL 并非一件易事，但正是 MyBatis 提供了可以被用在任意 SQL 映射语句中的强大的动态 SQL 语言得以改进这种情形。

动态 SQL 元素和 JSTL 或基于类似 XML 的文本处理器相似。在 MyBatis 之前的版本中，有很多元素需要花时间了解。MyBatis 3 大大精简了元素种类，现在只需学习原来一半的元素便可。MyBatis 采用功能强大的基于 OGNL 的表达式来淘汰其它大部分元素。

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

问题引入

以上一篇文章的数据为例：
数据库：

drop database if exists mybatisdemo;
create  database mybatisdemo;
use mybatisdemo;
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id`         int(11) NOT NULL AUTO_INCREMENT,
  `username`  varchar(32) NOT NULL COMMENT '用户名称',
  `birthday`  date DEFAULT NULL COMMENT '生日',
  `sex`       char(1) DEFAULT NULL COMMENT '性别',
  `address`   varchar(256) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;
INSERT INTO `user` VALUES ('1', '张三', '2018-07-10', '1', '北京');
INSERT INTO `user` VALUES ('2', '李四', '2018-07-10', '0', '上海');
INSERT INTO `user` VALUES ('3', '王五', '2018-07-10', '1', '广州');
INSERT INTO `user` VALUES ('4', '王六', '2018-07-10', '1', '深圳');
INSERT INTO `user` VALUES ('5', '王八', '2018-07-10', '1', '上海');
 
create table employee(
    id varchar(255) primary key,
    name varchar(255)
)

案例：三个条件并存的查询

  • 背景：查询符合姓名，性别，地区条件的人

  • Mapper.java

    List<User> selectUserByExample(@Param("user") User user);

  • Mapper.xml

    <select id="selectUserByExample" resultMap="baseResultMap">
    select  id, username ,birthday,sex , address from user where username=#{user.username} and sex=#{user.sex} and address=#{user.address}
    </select>

  • Test.java

    @Test
    public void testSelectUserByExample(){
        User user = new User();
        user.setUsername("王五");
        user.setSex("1");
        user.setAddress("广州");
        List<User> users = userMapper.selectUserByExample(user);
        for (User u :
                users) {
            System.out.println(u);
        }
    }

  • 结果正常

    DEBUG [main] - ==>  Preparing: select id, username ,birthday,sex , address from user where username=? and sex=? and address=? 
DEBUG [main] - ==> Parameters: 王五(String), 1(String), 广州(String)
TRACE [main] - <==    Columns: id, username, birthday, sex, address
TRACE [main] - <==        Row: 3, 王五, 2018-07-10, 1, 广州
DEBUG [main] - <==      Total: 1
User{id=3, username='王五', birthday=Tue Jul 10 00:00:00 CST 2018, sex='1', address='广州'}

  • 提出问题
    若让sex为空值，那么查出的结果将为空，但是符合条件的却有一条
    因此现在想让sql语句实现这样的功能：如果传来的参数不为空，那么就执行响应的判断，如果传来的参数为空，则干脆不执行传来的参数部分的语句。因此我们需要在xml文件中的sql语句中添加动态SQL语句

在sql语句中使用if标签

Mapper.xml

<select id="selectUserByExample" resultMap="baseResultMap">
   select  id, username ,birthday,sex , address from user where
   <if test="user.username!=null">
       username=#{user.username}
   </if>
   <if test="user.sex!=null">
       and sex=#{user.sex}
   </if>
   <if test="user.address!=null">
       and address=#{user.address}
   </if>
   </select>

这样当sex或者address为空时，sql语句正常运转

  • 执行结果(sex为空时)
    DEBUG [main] - ==>  Preparing: select id, username ,birthday,sex , address from user where username=? and address=? 
DEBUG [main] - ==> Parameters: 王五(String), 广州(String)
TRACE [main] - <==    Columns: id, username, birthday, sex, address
TRACE [main] - <==        Row: 3, 王五, 2018-07-10, 1, 广州
DEBUG [main] - <==      Total: 1
User{id=3, username='王五', birthday=Tue Jul 10 00:00:00 CST 2018, sex='1', address='广州'}
  • 问题：如果username为空，且sex为空，那么语句依然会报错：
    DEBUG [main] - ==>  Preparing: select id, username ,birthday,sex , address from user where and address=? 
DEBUG [main] - ==> Parameters: 广州(String)
    怎么解决呢？

在动态SQL语句中使用trim

  • where 元素只会在至少有一个子元素的条件返回 SQL 子句的情况下才去插入“WHERE”子句。而且，若语句的开头为“AND”或“OR”，where 元素也会将它们去除。
  • 如果 where 元素没有按正常套路出牌，我们可以通过自定义 trim 元素来定制 where 元素的功能。比如，和 where 元素等价的自定义 trim 元素为：
    Mapper.xml
    <select id="selectUserByExample" resultMap="baseResultMap">
    select  id, username ,birthday,sex , address from user
        <trim prefix="where" prefixOverrides="and | or ">
    
            <if test="user.username!=null">
                username=#{user.username}
            </if>
            <if test="user.sex!=null">
                and sex=#{user.sex}
            </if>
            <if test="user.address!=null">
                and address=#{user.address}
            </if>
        </trim>
    </select>
  • 但是trim这个标签用的不是很多 因为有另一个标签更常用而且还包含了trim的所有功能，这个标签就是where标签

在动态SQL语句中使用where标签

<select id="selectUserByExample" resultMap="baseResultMap">
        select id, username ,birthday,sex , address from user
        <where>
            <if test="user.username!=null">
                username=#{user.username}
            </if>
            <if test="user.sex!=null">
                and sex=#{user.sex}
            </if>
            <if test="user.address!=null">
                and address=#{user.address}
            </if>
        </where>
    </select>
<!--where标签在这里使用与<trim prefix="where" prefixOverrides="and | or "></trim>标签的功能等价-->
  1. where标签在此时，如果三个语句都为空，就不会在最终的sql语句中出现where，只要有一个不为空，那么where就会显示
  2. where标签还拥有和trim等价的功能，可以在适当的时间屏蔽and或者or

因此trim不常用，不是trim功能不够强大，而是where的功能包含了它

foreach

  • 动态 SQL 的另外一个常用的操作需求是对一个集合进行遍历，通常是在构建 IN 条件语句的时候。比如：
    <select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  WHERE ID in
  <foreach item="item" index="index" collection="list"
      open="(" separator="," close=")">
        #{item}
  </foreach>
</select>
    *foreach的使用，请看Mybatis案例（查询）的案例2：
    <!--演示List做参数的使用-->
<select id="selectByAddrs" resultMap="userMap">
    select * from user where address in
    <foreach collection="addresses" separator="," open="(" close=")" item="addr">
        #{addr}
</foreach>
</select>

<!--演示数组做参数的使用-->
<select id="selectByAddrsA" resultMap="userMap">
    select * from user where address in
    <foreach collection="addressses" separator="," open="(" close=")" item="addr">
        #{addr}
</foreach>
</select>
    foreach 元素的功能非常强大，它允许你指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。它也允许你指定开头与结尾的字符串以及在迭代结果之间放置分隔符。这个元素是很智能的，因此它不会偶然地附加多余的分隔符。
    注意:你可以将任何可迭代对象（如 List、Set 等）、Map 对象或者数组对象传递给 foreach 作为集合参数。当使用可迭代对象或者数组时，index 是当前迭代的次数，item 的值是本次迭代获取的元素。当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值。

choose, when, otherwise

有些时候，我们不想用到所有的条件语句，而只想从中择其一二。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。

还是上面的例子，但是这次变为提供了”title”就按”title”查找，提供了”author”就按”author”查找，若两者都没有提供，就返回所有符合条件的BLOG（实际情况可能是由管理员按一定策略选出BLOG列表，而不是返回大量无意义的随机结果）。

<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <choose>
    <when test="title != null">
      AND title like #{title}
    </when>
    <when test="author != null and author.name != null">
      AND author_name like #{author.name}
    </when>
    <otherwise>
      AND featured = 1
    </otherwise>
  </choose>
</select>

Mybatis关于动态SQL的官方文档