Mybatis-动态SQL、重用SQL片段

动态 SQL

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

动态SQL环境搭建

数据库表字段、实体类

CREATE TABLE `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 DEFAULT CHARSET=utf8

表数据：

package cn.kexing.mybatis.entity;

import java.util.Date;

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

    public Blog() {
    }

    public Blog(String id, String title, String author, Date createTime, int views) {
        this.id = id;
        this.title = title;
        this.author = author;
        this.createTime = createTime;
        this.views = views;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getTitle() {
        return title;
    }

    public void setTitle(String title) {
        this.title = title;
    }

    public String getAuthor() {
        return author;
    }

    public void setAuthor(String author) {
        this.author = author;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

    public int getViews() {
        return views;
    }

    public void setViews(int views) {
        this.views = views;
    }

    @Override
    public String toString() {
        return "Blog{" +
                "id='" + id + '\'' +
                ", title='" + title + '\'' +
                ", author='" + author + '\'' +
                ", createTime=" + createTime +
                ", views=" + views +
                '}';
    }
}

一、if

SQL片段：

//if标签查询
    public List<Blog> dynamicSelect(Map map);

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

通过refid属性引用

<select id="dynamicSelect" parameterType="map" resultType="Blog">
        select * from blog
        <where>
           <include refid="dynamicSelect-title-author"></include>
        </where>
    </select>

Test:

    @Test
    public void dynamicSelect(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map<String,Object> map = new HashMap<>();
        map.put("author","可星");
        List<Blog> blogs = mapper.dynamicSelect(map);
        for(Blog blog:blogs){
            System.out.println(blog);
        }
        sqlSession.close();
    }

Output;

Blog{id='1', title='Spring如此神奇', author='可星', createTime=Wed Jul 22 15:19:54 CST 2020, views=1000}
Blog{id='2', title='Mybatis如此微妙', author='可星', createTime=Wed Jul 22 15:22:38 CST 2020, views=200000}
Blog{id='3', title='SpringMVC如此简单', author='可星', createTime=Wed Jul 22 15:22:38 CST 2020, views=300000}
Blog{id='e24d301b75b74a4882c80760d02183c0', title='世界和平', author='可星', createTime=Wed Jul 22 15:22:38 CST 2020, views=1000}

二、choose、when、otherwise

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

//choose、when、otherwise标签
    public List<Blog> dynamicSelect2(Map map);

<select id="dynamicSelect2" resultType="Blog" parameterType="map">
        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：

    @Test
    public void dynamicSelect2(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map<String,Object> map = new HashMap<>();
        map.put("views","200000");
        List<Blog> blogs = mapper.dynamicSelect2(map);
        for(Blog blog:blogs){
            System.out.println(blog);
        }
        sqlSession.close();
    }

Output：

Blog{id='2', title='Mybatis如此微妙', author='可星', createTime=Wed Jul 22 15:22:38 CST 2020, views=200000}

三、where、set

• where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where
  元素也会将它们去除。也就是当where后面没有任何传参的情况下，mybatis会自动去掉where，当where后跟的第一个子句有AND或着OR时，mybaits也会自动去掉
• set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号（这些逗号是在使用条件语句给列赋值时引入的）。
• 注意，mybaits只会给你删掉多余的，并不会给你加上分隔符

//set标签、更新一条语句
    public int dynamicUpdate(Map map);

    <update id="dynamicUpdate" parameterType="map">
        update blog
        <set>title=#{title}</set>
        <where>id=#{id}</where>
    </update>

    @Test
    public void dynamicUpdate(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map<String,Object> map = new HashMap<>();
        map.put("title","世界和平");
        map.put("id","e24d301b75b74a4882c80760d02183c0");
        mapper.dynamicUpdate(map);
        sqlSession.commit();
        sqlSession.close();
    }

四、foreach

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

//Foreach查询前三条记录
    public List<Blog> selectForEach(Map map);

<select id="selectForEach" parameterType="map" resultType="blog">
        select * from blog
        <where>
            <foreach collection="ids" item="id" open="and (" close=")" separator="or">
                id=#{id}
            </foreach>
        </where>
    </select>

  @Test
    public void selectForEach(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
        Map<String,Object> map = new HashMap<>();
        List<Integer> ids = new ArrayList<>();
        ids.add(1);
        ids.add(2);
        ids.add(3);
        map.put("ids",ids);
        List<Blog> blogs = mapper.selectForEach(map);
        for (Blog blog:blogs){
            System.out.println(blog);
        }
        sqlSession.close();
    }

Output：

Blog{id='1', title='Spring如此神奇', author='可星', createTime=Wed Jul 22 15:19:54 CST 2020, views=1000}
Blog{id='2', title='Mybatis如此微妙', author='可星', createTime=Wed Jul 22 15:22:38 CST 2020, views=200000}
Blog{id='3', title='SpringMVC如此简单', author='可星', createTime=Wed Jul 22 15:22:38 CST 2020, views=300000}