Mybatis动态SQL

发表于 | 分类于 | 本文总阅读量 次
字数统计: 2.1k | 阅读时长 ≈ 9

动态SQL

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

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

搭建环境

  1. 创建新表

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    CREATE TABLE `mybatis`.`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
      CHARSET = utf8
      COLLATE = utf8_general_ci;

  2. 创建一个基础工程

    1. 编写实体类

      1
      2
      3
      4
      5
      6
      public class Blog {
          private int id;
          private String title;
          private String author;
          private Date createTime;
          private int views;

    2. 属性名和字段不一致的问题

      一个是createTime, 一个是create_time

      方法是使用官方文档中的设置选项:

    3. 往数据库中添加数据

      1
      2
      3
      4
      <insert id="addBlog" parameterType="blog">
          insert into mybatis.blog (id, title, author, create_time, views)
          values (#{id}, #{title}, #{author}, #{createTime}, #{views});
      </insert>

IF

使用动态 SQL 最常见情景是根据条件包含 where 子句的一部分。比如：

1
2
3
4
5
6
7
8
<select id="findActiveBlogWithTitleLike"
     resultType="Blog">
  SELECT * FROM BLOG
  WHERE state = ‘ACTIVE’
  <if test="title != null">
    AND title like #{title}
  </if>
</select>

这条语句提供了可选的查找文本功能。如果不传入 “title”，那么所有处于 “ACTIVE” 状态的 BLOG 都会返回；如果传入了 “title” 参数，那么就会对 “title” 一列进行模糊查找并返回对应的 BLOG 结果（细心的读者可能会发现，“title” 的参数值需要包含查找掩码或通配符字符）。

如果希望通过 “title” 和 “author” 两个参数进行可选搜索该怎么办呢？首先，我想先将语句名称修改成更名副其实的名称；接下来，只需要加入另一个条件即可。

1
2
3
4
5
6
7
8
9
10
<select id="findActiveBlogLike"
     resultType="Blog">
  SELECT * FROM BLOG WHERE state = ‘ACTIVE’
  <if test="title != null">
    AND title like #{title}
  </if>
  <if test="author != null and author.name != null">
    AND author_name like #{author.name}
  </if>
</select>

应用到我们的项目中就是:

1
2
3
4
5
6
7
8
9
<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>

测试:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
@Test
public void test2(){
    SqlSession sqlSession = MybtisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    HashMap map = new HashMap<>();
    map.put("title", "Mybatis教程");
    List<Blog> blogs = mapper.queryBlogIF(map);

    for (Blog blog : blogs) {
        System.out.println(blog);
    }

    sqlSession.close();
}

输出为:

Mapper语句中的where 1=1 太牵强, 改换为where标签, 如下:

1
2
3
4
5
6
7
8
9
10
11
<select id="queryBlogIF" parameterType="map" resultType="blog">
    select * from mybatis.blog
    <where>
        <if test="title!=null">
            and title=#{title}
        </if>
        <if test="author != null">
            and author=#{author}
        </if>
    </where>
</select>

查看日志内容可以看到这个标签自动把and去掉了, 所以可以得到正确的查询结果:

choose、when、otherwise

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

还是上面的例子，但是策略变为：传入了 “title” 就按 “title” 查找，传入了 “author” 就按 “author” 查找的情形。若两者都没有传入，就返回标记为 featured 的 BLOG（这可能是管理员认为，与其返回大量的无意义随机 Blog，还不如返回一些由管理员挑选的 Blog）。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<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>

套用在我们的项目中, 如下代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<select id="queryBlogChoose" parameterType="map" resultType="blog">
    select * from mybatis.blog
    <where>
        <choose>
            <when test="title!=null">
                and title=#{title}
            </when>

            <when test="author!=null">
                and author=#{author}
            </when>

            <otherwise>
                and views = #{views}
            </otherwise>
        </choose>
    </where>
</select>

后面的otherwise 根据具体的逻辑是可以不写的.

SET

用于动态更新语句的类似解决方案叫做 set。set 元素可以用于动态包含需要更新的列，忽略其它不更新的列。比如：

1
2
3
4
5
6
7
8
9
10
<update id="updateAuthorIfNecessary">
  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}
</update>

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

因为update语句修改多个字段时, where之前的字段不需要逗号, 而其他的字段都要以逗号结尾, 否则会出现格式错误

在我们的项目中测试:

1
2
3
4
5
6
7
8
9
10
11
12
13
<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>

需要注意的是, set标签只能做到删除逗号, 不能添加逗号, 所以写的时候不能省

trim

如果 where 元素与你期望的不太一样，你也可以通过自定义 trim 元素来定制 where 元素的功能。比如，和 where 元素等价的自定义 trim 元素为：

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

prefixOverrides 属性会忽略通过管道符分隔的文本序列（注意此例中的空格是必要的）。上述例子会移除所有 prefixOverrides 属性中指定的内容，并且插入 prefix 属性中指定的内容。

来看看与 set 元素等价的自定义 trim 元素吧：

1
2
3
<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

注意，我们覆盖了后缀值设置，并且自定义了前缀值。

SQL片段

有的时候, 我们可能会将一些功能的部分抽取出来, 方便复用

  1. 使用标签抽取公共的部分
  2. 在使用的时候使用标签引用即可

foreach

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

1
2
3
4
5
6
7
8
9
<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 元素的功能非常强大，它允许你指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符。这个元素也不会错误地添加多余的分隔符

想办法拼出这样的SQL语句:

1
select * from mybatis.blog where 1=1 and (id=1 or id=2 or id=3);

  1. Mapper映射:

    1
    2
    3
    4
    5
    6
    7
    8
    <select id="queryBlogForeach" parameterType="map" resultType="blog">
        select * from mybatis.blog
        <where>
            <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                id=#{id}
            </foreach>
        </where>
    </select>

  2. 测试:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    @Test
    public void tes5(){
        SqlSession sqlSession = MybtisUtils.getSqlSession();

        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

        HashMap map = new HashMap();

        ArrayList<Integer> ids = new ArrayList<>();
        ids.add(1);
        map.put("ids", ids);

        List<Blog> blogList = mapper.queryBlogForeach(map);
        for (Blog blog : blogList) {
            System.out.println(blog);
        }

        sqlSession.close();
    }

  3. 结果

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    Opening JDBC Connection
    Created connection 1806431167.
    Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@6babf3bf]
    ==>  Preparing: select * from mybatis.blog WHERE ( id=? ) 
    ==> Parameters: 1(Integer)
    <==    Columns: id, title, author, create_time, views
    <==        Row: 1, Spring教程, 刘潇博, 2020-07-24 09:58:00.0, 7412831
    <==      Total: 1
    Blog{id=1, title='Spring教程', author='刘潇博', createTime=Fri Jul 24 09:58:00 CST 2020, views=7412831}
    Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@6babf3bf]
    Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@6babf3bf]
    Returned connection 1806431167 to pool.

总结

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

建议:

  • 先在Mysql中写出完整的SQL, 再对应地去修改称为我们的动态SQL实现
-------------本文结束感谢您的阅读-------------
可以请我喝杯奶茶吗