空~

12. 动态 SQL

空~2022年8月30日
  • mybatis
大约 3 分钟

12. 动态 SQL

根据环境自动拼接 SQL

环境搭建:

  1. 实体类

    @Data
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;
    private int views;
}

  2. id 工具类

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

  3. 接口

    public interface BlogMapper {
/**
* 添加博客
*
* @param blog 博客
* @return 受影响的行
*/
int addBlog(Blog blog);

/**
* 动态sql
*
* @param map map
* @return 博客
*/
List<Blog> getBlog(Map map);
}

if

Mapper 文件

<?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="dao.BlogMapper">
    <insert id="addBlog" parameterType="blog">
        insert into mybatis.blog(id, title, author, create_time, views)
        values (#{id}, #{title}, #{author}, #{createTime}, #{views});
    </insert>
    <select id="getBlog" parameterType="map" resultType="blog">
        select *
        from blog
        where 1 = 1
        <if test="title != null">
            and title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </select>
</mapper>

测试类

public class BlogTest {
    @Test
    public void getBlogTest() {
        SqlSession session = MybatisUtils.getSession();
        BlogMapper mapper = session.getMapper(BlogMapper.class);
        Map<String, String> map = new HashMap<>();
        map.put("author", "admin");
        map.put("title", "spring");
        List<Blog> blogList = mapper.getBlog(map);
        for (Blog blog : blogList) {
            System.out.println(blog);
        }
        session.close();
    }

    @Test
    public void addBlog() {
        SqlSession session = MybatisUtils.getSession();
        BlogMapper mapper = session.getMapper(BlogMapper.class);
        Blog blog = new Blog();
        blog.setId(IdUtils.getId());
        blog.setAuthor("admin");
        blog.setTitle("mybatis");
        blog.setViews(999);
        blog.setCreateTime(new Date());
        mapper.addBlog(blog);

        blog.setId(IdUtils.getId());
        blog.setTitle("spring");
        mapper.addBlog(blog);

        blog.setId(IdUtils.getId());
        blog.setTitle("mvc");
        mapper.addBlog(blog);

        session.close();
    }
}

trim、where、set

where:

使用 where 对 if 中 SQL 语句 where 的优化

原来的样子

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

现在的样子

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

set:

set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号

<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}
    </where>
</update>

trim:

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

<trim prefix="前缀" prefixOverrides="前缀覆盖" suffix="后缀" suffixOverrides="后缀覆盖"></trim>
<update id="updateBlog" parameterType="map">
    update blog
    <trim prefix="set" suffixOverrides=",">
        <if test="title != null">
            title = #{title},
        </if>
        <if test="author != null">
            author = #{author},
        </if>
    </trim>
    <where>
        id = #{id}
    </where>
</update>

choose、when、otherwise

MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。

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

SQL 片段

提取相同功能,方便复用

  1. sql 标签抽取
  2. include 标签引用
<sql id="if-title-author">
    <if test="title != null">
        title = #{title},
    </if>
    <if test="author != null">
        author = #{author},
    </if>
</sql>
<update id="updateBlog" parameterType="map">
    update blog
    <set>
        <include refid="if-title-author"/>
    </set>
    <where>
        id = #{id}
    </where>
</update>

foreach

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

<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>

集合项(item)

索引(index)

开头与结尾的字符串以及集合项迭代之间的分隔符

open(开头)

clos(结尾)

separator(分隔符)

<select id="queryForEach" resultType="blog">
    select * from blog
    where id in
    <foreach collection="ids"
             item="id"
             index="index"
             open="("
             close=")"
             separator=",">
        #{id}
    </foreach>
</select>

测试:

@Test
public void queryForEach() {
    SqlSession session = MybatisUtils.getSession();
    BlogMapper mapper = session.getMapper(BlogMapper.class);
    List ids = new ArrayList();
    ids.add("70cabc74d287474f834837a6a42a9ca2");
    ids.add("575492c78d5e49ad9474caf158759180");
    Map<String, Object> map = new HashMap<>();
    map.put("ids", ids);
    List<Blog> blogList = mapper.queryForEach(map);
    for (Blog blog : blogList) {
        System.out.println(blog);
    }
    session.close();
}