MyBatis06：动态SQL

2022-05-02 18:07:51

1、介绍动态SQL

什么是动态SQL：动态SQL指的是根据不同的查询条件 , 生成不同的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

我们之前写的 SQL 语句都比较简单，如果有比较复杂的业务，我们需要写复杂的 SQL 语句，往往需要拼接，而拼接 SQL ，稍微不注意，由于引号，空格等缺失可能都会导致错误。

那么怎么去解决这个问题呢？这就要使用 mybatis 动态SQL，通过 if, choose, when, otherwise, trim, where, set, foreach等标签，可组合成非常灵活的SQL语句，从而在提高 SQL 语句的准确性的同时，也大大提高了开发人员的效率。

2、搭建环境

1、创建数据库

CREATE TABLE `blog` (
  `id` varchar(50) NOT NULL COMMENT ‘博客id‘,
  `title` varchar(30) NOT NULL COMMENT ‘文章标题‘,
  `author` varchar(20) NOT NULL COMMENT ‘作者‘,
  `create_time` datetime NOT NULL COMMENT ‘创建时间‘,
  `views` int(11) NOT NULL COMMENT ‘浏览量‘,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2、创建一个新的工程

2、编写一个返回随机唯一ID的工具类

public class UUIDUtil {

    public static String getId(){
        return UUID.randomUUID().toString().replaceAll("-", "");
    }
    
}

3、编写实体类

@Data //导入Lombok的jar包
@AllArgsConstructor
@NoArgsConstructor
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;
    private Integer views;

}

4、创建mapper接口

public interface BlogMapper {
    
    // 添加一篇博客
    int addBlog(Blog blog);

}

5、编写相应的xml文件

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

</mapper>

6、注册Mapper，并且由于表中的create_time与实体类中的createTime不同，需要修改下配置

  <settings>
      <!-- 日志 -->
      <setting name="logImpl" value="STDOUT_LOGGING"/>
      <!-- 下划线与驼峰命名自动转换 -->
      <setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>

<mappers>
    <mapper class="org.com.mapper.BlogMapper"/>
</mappers>

7、初始化博客数据库

@Test
public void test1(){
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
    Blog blog1 = new Blog(UUIDUtil.getId(), "java如此简单", "张三", new Date(), 100);
    blogMapper.addBlog(blog1);

    Blog blog2 = new Blog(UUIDUtil.getId(), "数据结构", "小飞", new Date(), 1000);
    blogMapper.addBlog(blog2);

    Blog blog3 = new Blog(UUIDUtil.getId(), "这就是操作系统", "王晓", new Date(), 500);
    blogMapper.addBlog(blog3);

    Blog blog4 = new Blog(UUIDUtil.getId(), "编程世界", "jetty", new Date(), 800);
    blogMapper.addBlog(blog4);

    MyBatisUtils.closeSqlSession();
}

3、if 语句

需求：根据作者名字和博客名字来查询博客！如果作者名字为空，那么只根据博客名字查询，反之，则根据作者名来查询

1、在BlogMapper接口中新增一个方法

List<Blog> selectBlogByIf(Map<String, Object> map);

2、在映射文件中编写SQL语句

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

3、测试

@Test
public void test2(){
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
    Map<String, Object> map = new HashMap<>();
    map.put("author", "张三");
    map.put("title", "java如此简单");
    List<Blog> blogList = blogMapper.selectBlogByIf(map);
    for (Blog blog : blogList) {
        System.out.println(blog);
    }
    MyBatisUtils.closeSqlSession();
}

这样写我们可以看到，如果 title 等于 null，那么查询语句为 select * from blog where author=#{author},但是如果author为空呢？那么查询语句为 select * from blog where and title =#{title }，这是错误的 SQL 语句，如何解决呢？请看下面的 where 语句！

4、trim(where、set）语句

4.1、where

修改上面的SQL语句：

 <select id="selectBlogByIf" parameterType="map" resultType="blog">
     select * from blog
     <where>
         <if test="title != null">
             title = #{title}
         </if>

         <if test="author != null">
             and author = #{author}  // 这个and不能省
         </if>
     </where>
</select>

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

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

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

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

4.2、set

同理，上面的对于查询 SQL 语句包含 where 关键字，如果在进行更新操作的时候，含有 set 关键词，我们怎么处理呢？

1、在BlogMapper接口中新增一个方法

int updateBlogBySet(Map<String, Object> map);

2、在映射文件中编写SQL语句

<update id="updateBlogBySet" parameterType="map">
    update blog
    <set>
        <if test="title != null">
            title = #{title}, // 这个,不能省
        </if>
        <if test="author != null">
            author = #{author}
        </if>
    </set>
    where id=#{id}

</update>

3、测试

@Test
public void test3(){
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
    Map<String, Object> map = new HashMap<>();
    map.put("id", "78b3969c063a4a0aaede1ccc9adc8dc8");
    map.put("title", "如何提问？");
    blogMapper.updateBlogBySet(map);
    MyBatisUtils.closeSqlSession();
}

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

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

6、choose 语句

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

1、编写一个接口方法

List<Blog> selectBlogByChoose(Map<String, Object> map);

2、在映射文件中编写SQL语句

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

    </where>


</select>

3、测试

@Test
public void test4(){
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
    Map<String, Object> map = new HashMap<>();
    map.put("title", "编程世界");
    map.put("author", "张三");
    map.put("views", "500");
    List<Blog> blogList = blogMapper.selectBlogByChoose(map);
    for (Blog blog : blogList) {
        System.out.println(blog);
    }
    MyBatisUtils.closeSqlSession();
}

7、SQL片段

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

1、提取的SQL片段

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

2、调用SQL片段

<select id="selectBlogByIf" parameterType="map" resultType="blog">
    select * from blog
    <where>
        <!-- 引用 sql 片段，如果refid 指定的不在本文件中，那么需要在前面加上 namespace -->
        <include refid="selectByTitle-Author"></include>
         <!-- 在这里还可以引用其他的 sql 片段 -->
    </where>
</select>

注意：

①、最好基于单表来定义 sql 片段，提高片段的可重用性

②、在 sql 片段中不要包括 where

8、foreach 语句

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

1、编写一个接口方法

List<Blog> selectBlogByForeach(Map<String, Object> map);

2、在映射文件中编写SQL语句

<select id="selectBlogByForeach" parameterType="map" resultType="blog">
    select * from blog where author in
        <!--
       collection:指定输入对象中的集合属性
       item:每次遍历生成的对象
       open:开始遍历时的拼接字符串
       close:结束时拼接的字符串
       separator:遍历对象之间需要拼接的字符串
     -->
    <foreach collection="list" item="item" open="(" separator="," close=")">
        #{item}
    </foreach>

</select>

3、测试

@Test
public void test5(){
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
    List<String> list = new ArrayList<>();
    list.add("张三");
    list.add("王晓");
    Map<String, Object> map = new HashMap<>();
    map.put("list", list);
    List<Blog> blogList = blogMapper.selectBlogByForeach(map);
    for (Blog blog : blogList) {
        System.out.println(blog);
    }
    MyBatisUtils.closeSqlSession();
}

小结：

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

提示 你可以将任何可迭代对象（如 List、Set 等）、Map 对象或者数组对象作为集合参数传递给 foreach。当使用可迭代对象或者数组时，index 是当前迭代的序号，item 的值是本次迭代获取到的元素。当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值。

9、总结

其实动态 sql 语句的编写往往就是一个拼接的问题，为了保证拼接准确，我们最好首先要写原生的 sql 语句出来，然后在通过 mybatis 动态sql 对照着改，防止出错。多在实践中使用才是熟练掌握它的技巧。

MyBatis06：动态SQL

码农公寓

1、介绍动态SQL

2、搭建环境

3、if 语句

4、trim(where、set）语句

4.1、where

4.2、set

6、choose 语句

7、SQL片段

8、foreach 语句

9、总结

相关文章