动态SQL

不知道现在学习 MyBatis的朋友有没有使用过原生 JDBC的经验，如果有那么你应该清楚使用 StringBuffer来拼接 sql语句是多么的痛苦且低效

StringBuffer sql = new StringBuffer("select * from smbms_provider");
if (!StringUtils.isNullOrEmpty(providerCode)) {
    sql.append(" where proCode like ?");
    list.add("%"+providerCode+"%");
}
if (!StringUtils.isNullOrEmpty(providerName)) {
    sql.append(" and proName like ?");
    list.add("%"+providerName+"%");
}
if(...) {
    ......
}
复制代码

MyBatis的动态 SQL功能实际上可以一定程度上的简化这一过程，且让原本复杂的拼接逻辑变得简单

如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器，你对动态 SQL 元素可能会感觉似曾相识。

在 MyBatis 之前的版本中，需要花时间了解大量的元素。

借助功能强大的基于 OGNL 的表达式，MyBatis 3 替换了之前的大部分元素，大大精简了元素种类，现在要学习的元素种类比原来的一半还要少。

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

我们一个一个来进行讲解

if

if元素是整个动态 SQL的核心也是最容易理解的元素，它可以让我们在 sql层面进行一些简单的 if判断，根据传入的条件来决定 sql 的拼接。

在上述 JDBC原生代码中使用的是 Java的 if判断和 StringBuffer来完成这一操作，那我们来看一下如何通过MyBatis简化这一操作。

首先是环境的搭建 :

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for blog
-- ----------------------------
DROP TABLE IF EXISTS `blog`;
CREATE TABLE `blog`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '博客id',
  `title` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '博客标题',
  `author` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '博客作者',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `views` bigint(20) NULL DEFAULT NULL COMMENT '浏览量',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of blog
-- ----------------------------
INSERT INTO `blog` VALUES (1, 'MyBatis配置文件详解', 'Moluu', '2021-06-12 17:50:18', 100);
INSERT INTO `blog` VALUES (2, 'MyBatis初始化流程详解', 'Moluu', '2021-06-12 17:51:16', 100);
INSERT INTO `blog` VALUES (3, 'MyBatis动态SQL', 'Moluu', '2021-06-12 17:51:33', 50);
INSERT INTO `blog` VALUES (4, 'MyBatis缓存详解', 'moluu', '2021-06-12 17:52:05', 0);

SET FOREIGN_KEY_CHECKS = 1;
复制代码

完成了表的创建我们可以基于该表写一些测试 Demo

if元素的使用十分简单，一般内嵌在操作数据库的 DML语句元素 sql片段之中，其拥有一个必填的 **”test”**属性，我们的逻辑判断就写在这个属性 value处。

以下为演示：

<select id="selectBlogByIf" resultType="blog" parameterType="blog">
    select * from `blog` where 1=1
    <if test="title != null">
        and title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
    <if test="views != null">
        and views = #{views}
    </if>
</select>
复制代码

我们在必定会返回全部博客数据的 sql语句中内嵌了三个 if元素，当 if元素 test属性中的条件成立时

我们写在 if元素内的 sql语句就会被追加到查询 sql中，比如说当 title有值时，我们的sql会是这个样子：

select * from `blog` where 1=1 and title=#{tiitle}
复制代码

这里在 sql语句中写了一个多余的 where 1=1，是为了防止 sql执行时报错

因为不确定可能会传入的第一个条件是什么，所以 where写在哪个 if元素内都不合适，但如果让它一开始就存在就不会有这种困扰了。

# 如果去掉这个 where 1=1，执行的 sql很可能会变成这个样子
select * from `blog` and title=#{title}
# 这明显是没办法通过的 sql语句，除非你限定死哪个查询条件必须先传入，否则不好解决该问题
复制代码

我们通过测试类来进行测试：

@Test
public void queryTestByIf(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    // 当我们创建的 blog对象走的是无参构造时
    List<Blog> blogs = mapper.selectBlogByIf(new Blog());
    blogs.forEach(System.out::println);
    /* 查询到的数据为：
    Blog(id=1, title=MyBatis配置文件详解, author=Moluu, createTime=Sat Jun 12 17:50:18 CST 2021, views=100)
	Blog(id=2, title=MyBatis初始化流程详解, author=Moluu, createTime=Sat Jun 12 17:51:16 CST 2021, views=100)
	Blog(id=3, title=MyBatis动态SQL, author=Moluu, createTime=Sat Jun 12 17:51:33 CST 2021, views=50)
	Blog(id=4, title=MyBatis缓存详解, author=moluu, createTime=Sat Jun 12 17:52:05 CST 2021, views=0)
    */
    sqlSession.close();
}
复制代码

当我们创建一个空 Blog对象，并将其作为查询条件传入方法中时，很明显我们写在 if元素 test属性内的条件一个都不会被满足

故执行的 sql为：

select * from `blog` where 1=1
复制代码

当某一个条件被满足时：

@Test
public void queryTestByIf(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    Blog blog = new Blog();
    // 为我们的空 blog对象设置属性 title
    blog.setTitle("MyBatis缓存详解");
    List<Blog> blogs = mapper.selectBlogByIf(blog);
    blogs.forEach(System.out::println);
    // 查询的结果为
    // Blog(id=4, title=MyBatis缓存详解, author=moluu, createTime=Sat Jun 12 17:52:05 CST 2021, views=0)
    sqlSession.close();
}
复制代码

当我们为创建的空 Blog对象设置了一些属性值后，if元素 test属性处的条件被满足

<if test="title != null">
    and title = #{title}
</if>
复制代码

里面的 sql也会被拼接到原本的 sql语句中，那么此时执行的完整 sql语句应该为：

select * from `blog` where 1=1 and title = ?
复制代码

这样我们就可以实现查询出指定标题博客的功能了

这就是所谓的动态SQL，但这也只是冰山一角，也是最简单的使用而已；如果你对动态 SQL感兴趣不妨继续向下看

trim

在 if元素的演示 Demo中，我们写了一个多余的 where 1=1来保证拼接的 sql可以成功的执行；但如果这么写又显得不太优雅。

我们又不可以将这个 where子句拿掉，那样的话查询的自由性会下降，为了解决这一尴尬的问题我们可以使用 trim元素。

where

where元素属于是内置的一种 trim元素（可以简单的理解为 where是定制的 trim，用于一些常用的固定场合）

where元素的使用也十分的简单，我们只需要使用该元素包裹住拼接 sql时可能会出现错误的动态 SQL即可

比如 if元素中示例 Demo那一段动态 SQL

# 之前的
<select id="selectBlogByIf" resultType="blog" parameterType="blog">
    select * from `blog` where 1=1
    <if test="title != null">
        and title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
    <if test="views != null">
        and views = #{views}
    </if>
</select>

# 使用 where元素后的
<select id="selectBlogByIf" resultType="blog" parameterType="blog">
    select * from `blog`
    <where>
        <if test="title != null">
            and title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
        <if test="views != null">
            and views = #{views}
        </if>
    </where>
</select>
复制代码

我们去掉了默认查询 sql后面的 where 1=1，并使用 where元素包裹住了之前的 if元素

为什么这么做呢，我们来了解一下 where元素的作用就明白了

where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。

如何理解这段话呢，我们直接在配置文件中加上日志，运行之前的示例 Demo查看输出的 sql语句就清楚了

<settings>
    <!--开启 MyBatis内置的默认日志实现-->
    <setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
复制代码

运行后控制台中打印输出的 sql为：

这就是 where元素在拼接 sql时进行处理后的 sql语句；

原本我们写在 if元素中的 sql语句应该是 and title = #{title}，也没有在默认执行的 sql语句中加上 where关键字

但经过 where元素的处理后，自动的加上了 where关键字，但这样还不够如果仅仅是加上 where关键字的话 sql还是不能够正常的执行

# 会变成这个样子
select * from `blog` where and title = ?
复制代码

很明显的语法错误，要保证 sql的顺利执行 where元素还要去除掉子句开头的 and关键字才是，这正好也是 where元素的功能之一

当然，并不是每一个 and关键字都会被去掉，where元素只会去除掉子句开头的 and，第二或第三个 and还是会保留下来的

// 满足全部 if元素判断条件
blog.setTitle("MyBatis缓存详解");
blog.setAuthor("moluu");
blog.setViews(10);

// 拼接后的 sql为   select * from `blog` WHERE title = ? and author = ? and views = ?
// 可以看到 其他的 and还是被保留了下来
复制代码

set

set元素的功能和 where元素类似，也是保证我们拼接后 sql语句的正确性，它会在我们执行 update操作时自动的在首行添加 set关键字，且去除尾部多余的 ” , “（因为在写 updateSQL语句时每一个需要修改的元素之间我们使用 ” , “分隔）

我们直接通过示例 Demo来了解该元素的具体作用：

<update id="updateBlogBySet" parameterType="blog">
    update `blog`
    <set>
        <if test=" title != null">
            title = #{title},
        </if>
        <if test=" author != null">
            author = #{author},
        </if>
        <if test=" createTime != null">
            create_time = #{createTime},
        </if>
        <if test=" views != null">
            views = #{views},
        </if>
    </set>
        where id = #{id}
</update>
复制代码

@Test
public void queryTestByIf(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    Blog blog = new Blog();
    blog.setTitle("Set元素测试");
    blog.setId(1);
    mapper.updateBlogBySet(blog);
    sqlSession.close();
}
复制代码

我们为空 Blog对象设置属性值 title使其满足 if元素中的判断条件，如果我们不加上 set元素那么拼接后的 sql应该是这样子的

update `blog` title = ?, where id = ?
复制代码

这种 sql怎么看都有问题，没有 set关键字，即使加上 set关键字多出的逗号也会导致 sql 的编译时就无法通过

但我们如果把把 if元素写在 set元素内，那么这些问题就都迎刃而解了

set元素会将没加上的 set关键字加上，还会去掉使编译报错的 ” , ” 使得拼接后的 sql变成这样

trim

实际上 trim元素的使用我们可能会用得比较少，因为 where和 set已经可以满足我们大部分的使用需求了

trim元素的存在可以让我们完成一些定制化的操作，不仅仅局限于使用 where和 set；当然它的使用也更加复杂

它有以下四个属性让我们完成定制化这一操作

prefix
prefixOverriders
suffix
suffixOverriders

prefix

先简单的认识一下这些属性吧，prefix为可以理解为是前缀的意思，当我们在 trim元素中添加该属性并给定其属性值后

那么当需要动态拼接 SQL时，prefix中的内容会首当其冲的被拼接上去，以下对之前示例 Demo进行了简单的修改

<update id="updateBlogBySet" parameterType="blog">
    update `blog`
    <trim prefix="SET">
        <if test=" title != null">
            title = #{title}
        </if>
    </trim>
        where id = #{id}
</update>
复制代码

可以看到，我们拿掉了多余的 if元素只留下了一个，并将 set元素替换为了 trim，删掉了 ” } “后面的 ” , “

这是为了保证 sql能够通过运行，因为有 trim元素的处理（即加上前缀 “SET”），这样写是没问题的。

执行的 sql语句为

update `blog` SET title = ? where id = ?
复制代码

suffix

suffix和 prefix相反，它属性值中的字符串内容会被拼接在需要拼接的动态 SQL尾部

我们可以尝试将之前写在 trim元素外的 where id = #{id}写在 suffix元素属性值中

<update id="updateBlogBySet" parameterType="blog">
    update `blog`
    <trim prefix="SET" suffix="where id = #{id}">
        <if test=" title != null">
            title = #{title}
        </if>
    </trim>
</update>
复制代码

这样写也是可以的当我们启动测试类时，suffix元素属性值中的内容会被拼接到尾部

执行的 sql语句为

update `blog` SET title = ? where id = ?
复制代码

prefixOverriders

prefixOverrides属性值中的字符串内容，如果出现在所需要拼接 sql的句首，那么它会将这个出现在句首的字符串内容去除

听起来可能会有点绕，实际上 where元素就是 prefixOverrides属性和 prefix属性二者配合使用的产物

如果要使用 trim元素来做到和 where元素相同的功能

那么trim元素的 prefixOverriders属性值就应该为 "and |or "，prefix属性值为 "where" 也就是下面这个样子：

<trim prefix="where" prefixOverrides="and |or ">
    
</trim>
复制代码

这样也就实现了如果 and或 or关键字出现在所需拼接的句首就将其去掉，并在句首添加 where关键字的功能

我们来对该属性值进行测试：

<update id="updateBlogBySet" parameterType="blog">
    update `blog`
    <trim prefix="set" prefixOverrides="test">
        <if test=" title != null">
            test title = #{title}
        </if>
    </trim>
</update>
复制代码

如上，我们对所需拼接的 sql进行了改动，在句首写了一个 test，正常来说你这么写运行的话一定会报错

但如何我们将 prefixOverrides的属性值字符串设置为 "test"，那么这个 test字符串内容就会被去除

也就可以通过运行了，执行的 sql语句如下：

update `blog` set title = ?
复制代码

suffixOverriders

和 prefixOverriders属性类似，suffixOverrides属性值中的字符串内容如果出现在所需拼接 sql的末尾，suffixOverrides属性会将其去除

也就是说 set元素是 prefix和 suffixOverriders属性配合使用的产物

写成 trim元素的话就是下面这个样子：

<trim prefix="SET" suffixOverrides=",">

</trim>
复制代码

这样也就实现了去除 set元素中多余 ” , ” 并在句首添加 SET关键字的功能了

我们同样对其进行测试：

<update id="updateBlogBySet" parameterType="blog">
    update `blog`
    <trim prefix="set" suffixOverrides=",">
        <if test=" title != null">
            title = #{title},
        </if>
    </trim>
    where id = #{id}
</update>
复制代码

可以看到，我们在所需拼接的 sql末尾加上了一个 ” , ” ，此时我们如果不加上 suffixOverrides="," 那么这个 sql拼接上后就无法通过编译；它会是这样：

update `blog` SET title = ?, where id = ?
复制代码

但我们加上suffixOverrides=","就不会出现这个问题了，末尾的 ” , “会被 suffixOverrides属性去除

choose

MyBatis还提供了一个类似 Switch语句的动态SQL元素 choose，该元素中我们还可以书写二级元素 when、otherwise；

这两个元素对应的是 switch中的 case（带有 break的 case）、default，所以理解起来也相当容易

when

一般来说我们会在 choose元素内书写多个 when元素，就像 switch中书写 case一般

<select id="selectBlogByWhen" parameterType="blog" resultType="blog">
    select * from `blog`
    <where>
        <choose>
            <when test="title != null">
                and title = #{title}
            </when>
            <when test="author != null">
                and author = #{author}
            </when>
            <when test="views != null">
                and views = #{views}
            </when>
        </choose>
    </where>
</select>
复制代码

当 when元素 test属性值的中条件成立时，when元素内的 sql会被处理并拼接至默认执行的 sql语句之后

但与 if不同的是，一旦有一个 when元素中条件成立，那么其他的 when元素都会失效，也就是已经 break了

@Test
public void queryTestByWhen(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    Blog blog = new Blog();
    blog.setTitle("MyBatis初始化流程详解");
    blog.setAuthor("moluu");
    mapper.selectBlogByWhen(blog);
    sqlSession.close();
}
复制代码

虽然我们使得两个 when元素中的条件满足了（且这两个条件是矛盾的），但实际上只会有一个 when元素被执行；即先被满足的那一个

/*
Preparing: select * from `blog` WHERE title = ?
Row: 2, MyBatis初始化流程详解, Moluu, 2021-06-12 17:51:16, 100
*/
复制代码

otherwise

除了在 choose元素中书写大量的 when外，我们还可以写一个 otherwise元素来保证我们的查询 sql必定会选择一个条件来执行一次

<where>
    <choose>
        <when test="title != null">
            and title = #{title}
        </when>
        <when test="author != null">
            and author = #{author}
        </when>
        <when test="views != null">
            and views = #{views}
        </when>

        <otherwise>
            views = 100
        </otherwise>
    </choose>
</where>
复制代码

如上，我们在众多 when元素之后添加了一个 otherwise元素，其中的 sql为 views = 100

写下这几行代码后，我们的查询 sql在传入查询参数都为空时，仍会执行一次查询全部浏览量为 100的博客

@Test
public void queryTestByWhen(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    Blog blog = new Blog();

    /*blog.setTitle("MyBatis初始化流程详解");
    blog.setAuthor("moluu");*/

    // 将空 blog对象作为查询参数传入，任何 when元素都不会被满足
    mapper.selectBlogByWhen(blog);
    sqlSession.close();
}

// 执行的查询 sql为：select * from `blog` WHERE views = 100

// 返回的结果为 ：2, MyBatis初始化流程详解, Moluu, 2021-06-12 17:51:16, 100
复制代码

foreach

在某些情况下，我们书写的 sql语句中需要传入的参数是不固定的，碰到这种情况我们要写死 sql就变成了工作量很大的事情

但如果通过 MyBatis动态 sql这一特性来做这件事就显得即优雅又高效了

foreach元素允许你指定一个集合，它会对该集合中的元素进行遍历，你可以为每一个集合项（item）和索引项（index）指定别名

除此之外我们还可以使用特定的属性来指定这些集合项引用处的前缀（open）分隔符（separator）及后缀（close）

如何理解呢？光通过文字来理解这一高级特性是很困难的，我们依然通过示例来了解该元素的使用

假如有一种情况，我们需要返回用户指定 id（或者其他属性）的全部博客，在 sql层面你要如何实现呢

我们可以将接收到的 id都封装在集合中，最后遍历并将其作为参数传入到 sql中，但这样你就需要写一大堆的冗余 sql了

select * from `blog` where (id = ? or id = ? or id = ? or id = ... )
复制代码

除非你指定一次最多可传入的 id数量，这样你的 sql起码还能写到头，如果你不指定的话天知道用户会一次性查询多少博客

所以写死 sql是一件很不现实的事情，我们迫切的需要一种更优雅的方式来解决该问题即 foreach元素

我们可以将封装好的集合作为参数传给 foreach元素，它来将其遍历并直接将每一个集合项作为参数引用（如有必要还可以指定前后缀和分隔符）

<select id="selectBlogByForeach" resultType="blog" parameterType="arraylist">
    select * from `blog`
    <where>
        <foreach collection="ids" item="foreach_id" open="(" separator="or" close=")">
            id = #{foreach_id}
        </foreach>
    </where>
</select>
复制代码

如上，我们为 foreach元素指定了一个集合 ids，从 ids中遍历出的集合项别名为 foreach_id；

在集合项被引用时，foreach还会在引用处做一些前后缀分隔符的处理

我们在测试类中进行简单的测试：

@Test
public void queryTestByWhen(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap<Object, Object> map = new HashMap<>();
    ArrayList<Integer> ids = new ArrayList<>();
    /* 在此处模拟用户指定 id */
    ids.add(1);
    ids.add(2);
    ids.add(3);
    // 将需传入的集合参数 ids封装到 map中
    map.put("ids",ids);
    mapper.selectBlogByForeach(map);
    sqlSession.close();
}

// 执行的查询 sql为：select * from `blog` WHERE ( id = ? or id = ? or id = ? )
/* 返回的结果为	1, MyBatis配置文件详解, Moluu, 2021-06-12 17:50:18, 100
				2, MyBatis初始化流程详解, Moluu, 2021-06-12 17:51:16, 100
				3, MyBatis动态SQL, Moluu, 2021-06-12 17:51:33, 50  */
               
               
复制代码