MyBatis【六】动态 SQL

十二、动态 SQL

动态 SQL ：指根据不同的条件，生成不同的 SQL 语句。

复杂的 SQL 语句，往往需要拼接，而拼接 SQL ，稍微不注意，由于引号、空格等缺失可能都会导致错误。利用 MyBatis 动态 SQL 可以摆脱拼接 SQL 语句的痛苦，通过 if, choose, when, otherwise, trim, where, set, foreach 等标签，可组合成非常灵活的 SQL 语句，从而在提高 SQL 语句的准确性的同时，也大大提高了开发人员的效率。

如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器，你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中，需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式，MyBatis 3 替换了之前的大部分元素，大大精简了元素种类，现在要学习的元素种类比原来的一半还要少。

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

12.1 搭建环境

0. 数据库表准备

CREATE TABLE `blog` (
    `id` VARCHAR(50) NOT NULL COMMENT '博客id',
    `title` VARCHAR(100) NOT NULL COMMENT '博客标题',
    `author` VARCHAR(30) NOT NULL COMMENT '博客作者',
    `create_time` DATETIME NOT NULL COMMENT '创建时间',
    `views` INT(30) NOT NULL COMMENT '浏览量'
) ENGINE=INNODB DEFAULT CHARSET=utf8

1. 创建一个基础工程

2. 编写 IDutils 工具类

@SuppressWarnings("all") //抑制警告
public class IDutils {
    public static String getId(){
        return UUID.randomUUID().toString().replaceAll("-", " ");
    }
}

3. 实体类

@Data
public class Blog {
    private String id;
    private String title;
    private String author;
    //属性名和字段名不一致,mapUnderscoreToCamelCase 在 setting 中配置，开启驼峰命名
    private Date createTime;
    private int views;
}

4. 编写接口及 xml 文件（插入初始数据）

public interface BlogMapper {
    // 插入数据
    int addBlog(Blog blog);
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.song.dao.BlogMapper">
    <insert id="addBlog" parameterType="blog">
        insert into mybatis.blog(id, title, author, create_time, views)
        values (#{id}, #{title}, #{author}, #{createTime}, #{views});
    </insert>
</mapper>  

5. mybatis核心配置文件，下划线驼峰自动转换

<settings>
    <setting name="logImpl" value="STDOUT_LOGGING"/>
    <!--下划线驼峰自动转换-->
    <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<mappers>
    <mapper class="com.song.dao.BlogMapper"/>
</mappers>

6. 插入初始数据

@Test
public void addInitBlog(){
    SqlSession session = MybatisUtils.getSqlSession();
    BlogMapper mapper = session.getMapper(BlogMapper.class);

    Blog blog = new Blog();
    blog.setId(IDutils.getId());
    blog.setTitle("Mybatis如此简单");
    blog.setAuthor("狂神说");
    blog.setCreateTime(new Date());
    blog.setViews(9999);

    mapper.addBlog(blog);

    blog.setId(IDutils.getId());
    blog.setTitle("Java如此简单");
    mapper.addBlog(blog);

    blog.setId(IDutils.getId());
    blog.setTitle("Spring如此简单");
    mapper.addBlog(blog);

    blog.setId(IDutils.getId());
    blog.setTitle("微服务如此简单");
    mapper.addBlog(blog);

    session.close();
}

12.2 if 标签

1. 编写接口类

List<Blog> queryBlogIF(Map map);

2. 编写 SQL 语句

<select id="queryBlogIF" parameterType="map" resultType="blog">
    select * from mybatis.blog where 1=1
    <if test="title != null">
        and  title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</select>

3. 测试

@Test
public void queryBlogIF(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap map = new HashMap();
//	map.put("title", "java如此简单");
    map.put("author", "狂神说");
    List<Blog> blogs = mapper.queryBlogIF(map);
    for (Blog b : blogs) {
        System.out.println(b);
    }
    sqlSession.close();
}

• 如果 title 和 author 都等于 null，查询语句为 select * from blog where 1=1；

• 如果 title 不等于 null，查询语句为 select * from blog where 1=1 and title= #{title}；

• 如果 author 不等于 null，查询语句为 select * from blog where 1=1 and author= #{author}；

• 如果都不为 null，查询语句为select * from blog where 1=1 and title= #{title} and author= #{author}

12.3trim (where, set)

where 标签

对于上面使用 if 的 SQL，如果省去 SQL 语句 where 后面的 1=1，由于第二个 if 中的 SQL 语句前面 and 的存在，（即：title 等于 null，而 author 不等于 null 时，查询语句为 select * from blog where and author= #{author}）执行会出现错误，这时需要使用 where 标签。

where 标签会知道如果它包含的标签中有返回值的话，它就插入一个 where；此外，如果标签返回的内容是以 AND 或 OR 开头的，则它会剔除掉。

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

<select id="queryBlogIF" parameterType="map" resultType="blog">
    select * from mybatis.blog
    <where>
        <if test="title != null">
            title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </where>
</select>

执行时会自动转换：

• 只有第二个条件满足时，查询语句为 select * from blog where author= #{author}，即 where 标签会帮我们自动去掉 and

• 两个条件都不满足时，查询语句为 select * from blog，即 where 标签会帮我们自动省略掉 where

set 标签

在进行更新操作，含有 set 关键词时，每个 if 子句后面的 "，" 有可能会使得执行出错，这时会使用一个 set 标签。

set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号（这些逗号是在使用条件语句给列赋值时引入的）。

int updateBlog(Map map);

<update id="updateBlog" parameterType="map">
    update mybatis.blog
    <set>
        <if test="title != null">
            title = #{title},
        </if>
        <if test="author != null">
            author = #{author}
        </if>
    </set>
    where id = #{id}
</update>

trim 标签

where 和 set 的功能都可以通过 trim 元素来实现。即可以通过自定义 trim 元素来定制 where 和 set 元素。

• 与 where 元素等价的自定义 trim 元素为：

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>

• 与 set 元素等价的自定义 trim 元素：

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

12.4 choose (when, otherwise)

有时候，我们不想用到所有的查询条件，只想选择其中的一个，查询条件有一个满足即可，使用 choose 标签可以解决此类问题，类似于 Java 中的 switch 语句，只会执行其中一个条件。

List<Blog> queryBlogChoose(Map map);

<select id="queryBlogChoose" parameterType="map" resultType="blog">
    select * from mybatis.blog
    <where>
        <choose>
            <when test="title != null">
                title = #{title}
            </when>
            <when test="author != null">
                and author = #{author}
            </when>
            <otherwise>
                and views = #{views}
            </otherwise>
        </choose>
    </where>
</select>

12.5 SQL 片段

有时候可能某个 sql 语句我们用的特别多，为了增加代码的重用性，简化代码，我们需要将这些代码抽取出来，然后使用时直接调用。

1. 使用 SQL 标签抽取公共部分

<sql id="if-title-author">
    <if test="title != null">
        title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</sql>

2. 在需要使用的时候使用 include 标签引用即可

<select id="queryBlogIF" parameterType="map" resultType="blog">
    select * from mybatis.blog
    <where>
       <include refid="if-title-author"/>
    </where>
</select>

注意：

• 最好基于单表来定义 SQL 片段，提高片段的可重用性
• 在 SQL 片段中不要包括 where

12.6 Foreach

动态 SQL 的另一个常见使用场景是对集合进行遍历（尤其是在构建 IN 条件语句的时候）。

foreach 元素的功能非常强大，它允许你指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符。这个元素也不会错误地添加多余的分隔符。

<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>
collection:指定输入对象中的集合属性
item:每次遍历生成的对象
open:开始遍历时的拼接字符串
close:结束时拼接的字符串
separator:遍历对象之间需要拼接的字符串

1. 编写接口

// 查询第1,2,3号id 的元素
List<Blog> queryBlogForeach(Map map);

2. 编写 SQL 语句

<!--
    select * from mybatis.blog where 1=1 and (id=1 or id=2 or id=3);
    现在传递了一个万能 map，这个 map 中可以存在一个集合
-->
<select id="queryBlogForeach" parameterType="map" resultType="blog">
    select * from mybatis.blog
    <where>
        <foreach collection="ids" item="id" open="(" separator="or" close=")">
            id = #{id}
        </foreach>
    </where>
</select>

3. 测试

@Test
public void queryBlogForeach(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap map = new HashMap();
    ArrayList<Integer> ids = new ArrayList<Integer>();
    ids.add(1);
    ids.add(2);
    ids.add(3);
    map.put("ids", ids);

    List<Blog> blogs = mapper.queryBlogForeach(map);
    for (Blog b : blogs) {
        System.out.println(b);
    }
    sqlSession.close();
}

• ids 中没有元素时，查询语句为 `select * from mybatis.blog

• ids 中添加一个元素时，查询语句为 select * from mybatis.blog WHERE ( id = ? )

• ids 中添加两个元素时，查询语句为 select * from mybatis.blog WHERE ( id = ? or id = ? )

总结：

所谓的动态 SQL ，本质还是 SQL语句，只是我们可以在 SQL 层面，去执行一个逻辑代码。

动态 SQL 就是在拼接 SQL 语句，我们只要保证 SQL 的正确性，按照 SQL 的格式，去排列组合就可以了。

建议：

动态 sql 语句的编写往往就是一个拼接的问题，为了保证拼接准确，首先在 MySql 中写出完整的 SQL，再对应的通过 MyBatis 去修改为动态 SQL，防止出错， 实现通用。