二十二、动态SQL常用标签

狂神说Java：https://www.bilibili.com/video/BV1NE411Q7Nx

1、choose、when、otherwise

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

测试

mapper文件

<!--choose标签-->
<select id="queryBlogByChoose" parameterType="map" resultType="Blog">
    select * from blog
    <where>
        <choose>
            <when test="title != null">
                title = #{title}
            </when>
            <when test="author != null">
                author = #{author}
            </when>
            <otherwise>
                views = #{views}
            </otherwise>
        </choose>
    </where>
</select>

测试代码

@Test
public void test03(){

    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
    HashMap<Object, Object> map = new HashMap<>();
    map.put("views",99999);
    map.put("title","Java如此简单");
    map.put("author","狂神说");
    List<Blog> blogs = blogMapper.queryBlogByChoose(map);
    for (Blog blog : blogs) {
        System.out.println(blog);
    }

    sqlSession.close();

}

结果

// 不传任何条件
Opening JDBC Connection
Created connection 1747352992.
==>  Preparing: select * from blog WHERE views = ? 
==> Parameters: null
<==      Total: 0

// 只传views
Opening JDBC Connection
Created connection 1747352992.
==>  Preparing: select * from blog WHERE views = ? 

// 只传title
Opening JDBC Connection
Created connection 1748876332.
==>  Preparing: select * from blog WHERE title = ? 
    
// 只传author
Opening JDBC Connection
Created connection 1747352992.
==>  Preparing: select * from blog WHERE author = ?    
 
// 传title和author和views
Opening JDBC Connection
Created connection 1748876332.
==>  Preparing: select * from blog WHERE title = ? 

2、trim、where、set

where

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

测试

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

结果

// 不传任何条件
Opening JDBC Connection
Created connection 681094281.
==>  Preparing: select * from blog 

// 传title
Opening JDBC Connection
Created connection 681094281.
==>  Preparing: select * from blog WHERE title = ? 
    
// 传author
Opening JDBC Connection
Created connection 681094281.
==>  Preparing: select * from blog WHERE author = ?    
 
// 传title和author
Opening JDBC Connection
Created connection 1850777594.
==>  Preparing: select * from blog WHERE title = ? and author = ? 

set

用于动态更新语句的类似解决方案叫做 set。set 元素可以用于动态包含需要更新的列，忽略其它不更新的列。set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号（这些逗号是在使用条件语句给列赋值时引入的）。

测试

mapper文件

<!--更新信息-->
<update id="updateBlog" parameterType="map">
    update blog
    <set>
        <if test="title != null">
            title = #{title},
        </if>
        <if test="author != null">
            author = #{author}
        </if>
    </set>
    where id = #{id}
</update>

测试代码

@Test
public void test04(){

    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
    HashMap<Object, Object> map = new HashMap<>();
    map.put("title","Mybatis快学完了");
    map.put("author","张三");
    map.put("id","a4a36cb8722b492e96919a64dae72ff5");
    int i = blogMapper.updateBlog(map);

    sqlSession.close();
}

结果

// 只修改title
Opening JDBC Connection
Created connection 365590665.
==>  Preparing: update blog SET title = ? where id = ? 
    
// 只修改author
Opening JDBC Connection
Created connection 365590665.
==>  Preparing: update blog SET author = ? where id = ?
    
// 修改title和author
Opening JDBC Connection
Created connection 1007412025.
==>  Preparing: update blog SET title = ?, author = ? where id = ? 

trim

和 where 元素等价的自定义 trim 元素

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

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

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

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

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

3、总结

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