12、动态SQL

使用动态SQL省去在 JDBC中的以下麻烦：

• 需要根据不同条件手动拼接 SQL 语句；
• 拼接时要注意添加空格，去掉最后一个列名的逗号；
• ......

环境搭建

注意：开启日志功能，查看SQL语句！

1、数据库表

blog

2、实体类

Blog

/**
 * ID
 */
private String id;
/**
 * 标题
 */
private String title;
/**
 * 作者
 */
private String author;
/**
 * 创建时间
 */
private Date createTime;
/**
 * 浏览量
 */
private int views;

3、工具类

编写一个IdUtils工具类，用于为博客生成随机的UUID。

public class IdUtils {

    /**
     * 生成随机UUID，作为Blog的ID
     *
     * @return 去掉-的随机UUID
     */
    public static String getUUid() {
        return UUID.randomUUID().toString().replaceAll("-", "");
    }
}

4、测试

Mapper

/**
 * 添加博客
 *
 * @param blog 待添加博客
 * @return 受影响行数
 */
int insertBlog(Blog blog);

Mapper.xml

<insert id="insertBlog">
    insert into mybatis.blog(id, title, author, create_time, views)
    values (#{id}, #{title}, #{author}, #{createTime}, #{views})
</insert>

JUnit

@Test
public void insertBlog() {
    // 获取SqlSession
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    // 获取Mapper
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

    Blog blog = new Blog(IdUtils.getUUid(), "我的博客", "Jaywee", new Date(System.currentTimeMillis()), 7);

    // 执行方法
    int i = mapper.insertBlog(blog);

    if (i > 0) {
        System.out.println("插入" + i + "行数据");
    }

    // 提交事务！
    sqlSession.commit();

    // 关闭连接
    sqlSession.close();
}

if

通过if元素判断条件：

• 匹配到任意一个条件：追加相应子句；
• 匹配到多个条件：继续追加；
• 一个条件都没有匹配到：不会追加子句。

需求：查询所有BLOG。

• 如果有传入title，则在此基础上对title进行模糊查找；
• 如果有传入author，则在此基础上对author进行模糊查找。

注意：传入null，即视作没有传入参数；

1、Mapper

/**
 * 查询所有博客
 *
 * @param title  标题
 * @param author 作者
 * @return 博客列表
 */
List<Blog> listBlogsLike(@Param("title") String title, @Param("author") String author);

<select id="listBlogsLike" resultType="blog">
    select *
    from mybatis.blog
    where 1=1
    <if test="title != null">
        and title like concat(‘%‘,#{title},‘%‘)
    </if>
    <if test="author != null">
        and author like concat(‘%‘,#{author},‘%‘)
    </if>
</select>

2、测试

@Test
public void testListBlogsLike() {
    // 获取SqlSession
    SqlSession sqlSession = MyBatisUtils.getSqlSession();
    // 获取Mapper
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);

    String str = ?;
    // 执行方法
    List<Blog> blogs = mapper.listBlogsLike(str);

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

    // 关闭连接
    sqlSession.close();
}

测试不同传参的SQL语句：

1. 没传参（两个参数都为null）：不会追加任何条件。

   

2. 传入参数（即使是空串、值为null的引用类型）：追加匹配到的子句

   • 传入title

     

   • 传入author

     

   • 都有传参

     

choose (when、otherwise)

• 使用if元素，只要符合条件的所有语句都会被追加；

• 如果只是想使用其中的某一个条件，而不是所有，则选择choose元素，类似Java中的switch-case语句；

• 通过when元素判断条件：

  • 匹配到任意一个条件：追加对应子句；
  • 匹配到多个条件：看Mapper中的条件先后顺序，追加第一个匹配到的。
  • 一个条件都没有匹配到：
    • 有otherwise元素：追加otherwise元素中的子句；
    • 没有otherwise元素：不追加任何子句。

需求：查询所有BLOG。

• 如果有传入title和author中任意一个参数，根据参数匹配条件并追加子句。

1、Mapper

<select id="listBlogsLike" resultType="blog">
    select *
    from mybatis.blog
    where
    <choose>
        <!-- author写在title前面，比title优先接收参数 -->
        <when test="author != null">
            author like concat(‘%‘,#{author},‘%‘)
        </when>
        <when test="title != null">
            title like concat(‘%‘,#{title},‘%‘)
        </when>
        <otherwise>
            author = ‘Jaywee‘
        </otherwise>
    </choose>
</select>

2、测试

测试不同传参的SQL语句：

1. 没传参（两个参数都为null）：因为有otherwise元素，追加其子句。

   

2. 传入参数（即使是空串、值为null的引用类型）：追加匹配到的子句

   • 传入title

     

   • 传入author

     

   • 都有传参：实际只传递了第一个匹配到的条件所接收的元素。Mapper中author写在title的前面，所以author接收到了参数。

     

trim(where、set)

1、问题引入

在以上例子中，我们在原本的SQL语句中事先加入了where 1=1的条件，方便匹配条件后追加and语句。但如果不添加这个条件，会出现以下情况：

去掉where 1=1

• 如果只匹配了第二个语句，SQL语句为select * from blog and author like concat(‘%‘,#{author},‘%‘) ，显然不合法。

<select id="listBlogsLike" resultType="blog">
    select *
    from mybatis.blog
    <if test="title != null">
        where title like concat(‘%‘,#{title},‘%‘)
    </if>
    <if test="author != null">
        and author like concat(‘%‘,#{author},‘%‘)
    </if>
</select>

去掉1=1，保留where

• 如果没有匹配的条件，SQL语句为select * from blog where；
• 如果只匹配了第二条，SQL语句为select * from blog where and author like concat(‘%‘,#{author},‘%‘)，显然不合法。

<select id="listBlogsLike" resultType="blog">
    select *
    from mybatis.blog
    where 
    <if test="title != null">
        title like concat(‘%‘,#{title},‘%‘)
    </if>
    <if test="author != null">
        and author like concat(‘%‘,#{author},‘%‘)
    </if>
</select>

2、where

使用where元素，解决以上问题。

• 匹配到至少一个条件时，才会在添加where子句；
• 没有匹配到任何条件时，不会添加where子句；
• 会将子句的开头的 “AND” 或 “OR”去除，不会出现where and ...的情况；

注意：不会为子句开头的添加 “AND” 或 “OR”，需要自己加上。

<select id="listBlogsLike" resultType="blog">
    select *
    from mybatis.blog
    <where>
        <if test="title != null">
            and title like concat(‘%‘,#{title},‘%‘)
        </if>
        <if test="author != null">
            and author like concat(‘%‘,#{author},‘%‘)
        </if>
    </where>
</select>

3、set

使用set元素：

• 自动在行首添加SET关键字；
• 删除列赋值时的多余逗号。

/**
 * 更新博客
 *
 * @param map 参数列表
 * @return 受影响行数
 */
int updateBlog(Map<String, Object> map);

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

4、自定义trim

trim元素有4个属性：

• prefix（前缀）：在行首自动添加prefix中的文本值；
• prefixOverrides（前缀覆盖）：如果行首为prefixOverrides中的文本值，自动去除；
• suffix（后缀）：在行尾自动添加suffix中的文本值；
• suffixOverrides（后缀覆盖）：如果行尾为suffixOverrides中的文本值，自动去除。

例：自定义trim元素，实现与set元素定价的功能。

• 在行首自动添加SET；
• 自动去除行尾的逗号。

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

【MyBatis】七、动态SQL