Mybatis动态SQL

动态 SQL

官方描述

动态 SQL 指的是根据不同的查询条件 , 生成不同的 Sql 语句。

在实际工作中，我们会遇到很多比较复杂的业务，需要构建更为复杂的 SQL 语句，比如多条件组合查询、in 比较等。为此 Mybatis 提供了一套标签来解决我们的问题，其中包括 if, choose, when, otherwise, trim, where, set, foreach等标签 ，方便我们构建安全可靠的 SQL 语句，提高开发效率。

环境搭建

1、创建一个 blog 数据表

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
复制代码

2、创建一个 Maven 项目

3、IDutils 工具类，用来生成 UUID，作为博客 id 唯一值。

public class IDutils {

    public static String getId(){
        return UUID.randomUUID().toString().replace("-","");
    }
}
复制代码

4、MybatisUtil 工具类，获取 SqlSession

public class MybatisUtil {

    private static SqlSessionFactory sqlSessionFactory;

    static {
        String resource = "mybatis-config.xml";
        try {
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession(true);
    }
}
复制代码

5、Blog.java

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Blog {
    private String id;
    private String title;
    private String author;
    private Date createTime;
    private int view;
}
复制代码

6、编写 Mapper 相关文件

public interface BlogMapper {
    int addBlog(Blog blog);
}
复制代码

<?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="com.msdn.dao.BlogMapper">

        <insert id="addBlog" parameterType="Blog">
        insert into mybatis.blog(id, title, author, create_time, view)
        values (#{id}, #{title}, #{author}, #{createTime}, #{view})
    </insert>
</mapper>
复制代码

7、db.properties

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mybatis?useUnicode=true&useSSL=true&serverTimezone=UTC&characterEncoding=utf-8
username=root
password=1234567
复制代码

8、mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">

<!--核心配置文件-->
<configuration>

    <properties resource="db.properties" />

    <typeAliases>
        <package name="com.msdn.bean"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <!--jdbc.url=jdbc:mysql://localhost:3306/oto?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC-->
                <property name="url" value="${url}"/>
                <property name="username" value="${username}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="com/msdn/dao/BlogMapper.xml"/>
    </mappers>
</configuration>
复制代码

9、新增数据

    @Test
    public void add(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();

        BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);

        Blog blog = new Blog();
        blog.setId(IDutils.getId());
        blog.setAuthor("hresh");
        blog.setTitle("Spring IoC学习系列一");
        blog.setCreateTime(new Date());
        blog.setView(1234);
        blogMapper.addBlog(blog);

        blog.setId(IDutils.getId());
        blog.setTitle("Spring IoC学习系列二");
        blogMapper.addBlog(blog);

        blog.setId(IDutils.getId());
        blog.setTitle("Spring IoC学习系列三");
        blogMapper.addBlog(blog);

        sqlSession.close();
    }
复制代码

if 语句

根据作者名和博客标题进行查询，如果某一条件为空，则只根据另一条件进行查询。

1、编写接口类

List<Blog> selectByIf(Map<String,Object> map);
复制代码

2、Mapper 配置文件

<select id="selectByIf" parameterType="map" resultType="blog">
    select * from mybatis.blog where 1=1
    <if test="title != null">
        and title = #{title}
    </if>
    <if test="author != null">
        and author = #{author}
    </if>
</select>
复制代码

3、测试

    @Test
    public void select(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();

        BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
        HashMap<String, Object> m = new HashMap<String, Object>();
//        m.put("title","Spring IoC学习系列一");
        m.put("author","hresh2");

        List<Blog> blogs = blogMapper.selectByIf(m);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }

        sqlSession.close();
    }
复制代码

其中 m 集合中可以存放键值对，也可以为空，执行都不会报错。

Where

修改上述的 SQL 语句：

<select id="selectByIf" parameterType="map" resultType="blog">
    select * from mybatis.blog
    <where>
        <if test="title != null">
            and title = #{title}
        </if>
        <if test="author != null">
            and author = #{author}
        </if>
    </where>
</select>
复制代码

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

Set

1、编写接口方法

int update(Map<String,Object> map);
复制代码

2、Mapper 配置文件

    <update id="update" parameterType="map">
        update mybatis.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 updateTest(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();

        BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
        HashMap<String, Object> m = new HashMap<String, Object>();
        m.put("title","Spring IoC学习系列1");
//        m.put("author","hresh2");
        m.put("id","8b42c64435a047458d2867fad347e8d6");

        blogMapper.update(m);

        sqlSession.close();
    }
复制代码

当 SQL 语句中的 author 条件不成立时，set 标签会将 title 条件后带的逗号自动去除掉。

choose语句

有时候，我们不想用到所有的查询条件，只想选择其中的一个，查询条件有一个满足即可，使用 choose 标签可以解决此类问题，类似于 Java 的 switch 语句。

1、编写接口方法

List<Blog> queryByChoose(Map<String,Object> map);
复制代码

2、Mapper 配置文件

xml <select id="queryByChoose" parameterType="map" resultType="blog"> select * from mybatis.blog where 1=1 <choose> <when test="title != null"> and title = #{title} </when> <when test="author != null"> and author = #{author} </when> <otherwise> and id = #{id} </otherwise> </choose> </select>

3、测试

    @Test
    public void queryByChoose(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();

        BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
        HashMap<String, Object> m = new HashMap<String, Object>();

        m.put("title","Spring IoC学习系列1");
        m.put("author","hresh22");
        m.put("id","8b42c64435a047458d2867fad347e8d6");

        List<Blog> blogs = blogMapper.queryByChoose(m);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }

        sqlSession.close();
    }
复制代码

当 title 属性值不为空时，SQL 语句则执行 title = #{title} 条件，其他条件不予处理。

SQL片段

在 if 语句中如果条件过多，导致 SQL 语句变得臃肿，为了增加代码的重用性，可以考虑将部分代码提取出来，提高复用性。

同 if 语句中的案例效果一致，我们换用一种实现方式。

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

    <select id="selectByIf2" parameterType="map" resultType="blog">
        select * from mybatis.blog
        <where>
             <!-- 引用 sql 片段，如果refid 指定的不在本文件中，那么需要在前面加上 namespace -->
            <include refid="wherSql"></include>
        </where>
    </select>
复制代码

测试

    @Test
    public void select(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();

        BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
        HashMap<String, Object> m = new HashMap<String, Object>();
        m.put("title","Spring IoC学习系列1");
//        m.put("author","hresh2");

//        List<Blog> blogs = blogMapper.selectByIf(m);
        List<Blog> blogs = blogMapper.selectByIf2(m);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }

        sqlSession.close();
    }
复制代码

结果为：

2020-03-20 23:40:58,135 DEBUG [com.msdn.dao.BlogMapper.selectByIf2] - ==>  Preparing: select * from mybatis.blog WHERE title = ? 
2020-03-20 23:40:58,169 DEBUG [com.msdn.dao.BlogMapper.selectByIf2] - ==> Parameters: Spring IoC学习系列1(String)
2020-03-20 23:40:58,190 DEBUG [com.msdn.dao.BlogMapper.selectByIf2] - <==      Total: 1
Blog(id=8b42c64435a047458d2867fad347e8d6, title=Spring IoC学习系列1, author=hresh2, createTime=Wed Mar 04 19:49:04 CST 2020, view=1234)
复制代码

注意：

提取的 SQL 片段中的 and 连接符会被自动处理掉。

trim

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

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>
复制代码

Mapper 配置文件

    <select id="queryByChoose2" parameterType="map" resultType="blog">
        select * from mybatis.blog
        <trim prefix="where" prefixOverrides="and |or">
            <if test="title != null">
                title = #{title}
            </if>
            <if test="author != null">
                and author = #{author}
            </if>
        </trim>
    </select>
复制代码

测试

    @Test
    public void queryByChoose(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();

        BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
        HashMap<String, Object> m = new HashMap<String, Object>();

//        m.put("title","Spring IoC学习系列1");
        m.put("author","hresh2");

        List<Blog> blogs = blogMapper.queryByChoose2(m);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }

        sqlSession.close();
    }
复制代码

prefixOverrides 属性会忽略通过管道符分隔的文本序列。

Foreach

1、编写接口方法

List<Blog> queryByForeach(Map<String,Object> map);
复制代码

2、Mapper 配置文件

    <select id="queryByForeach" parameterType="map" resultType="blog">
        select * from mybatis.blog
        <where>
            <!--
                collection:指定输入对象中的集合属性
                item:每次遍历生成的对象
                open:开始遍历时的拼接字符串
                close:结束时拼接的字符串
                separator:遍历对象之间需要拼接的字符串
                select * from blog where 1=1 and (id=1 or id=2 or id=3)
              -->
            <foreach collection="authors" item="author" open="(" close=")" separator="or">
                author = #{author}
            </foreach>
        </where>
    </select>
复制代码

3、测试

    @Test
    public void queryByForeach(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();

        BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
        HashMap<String, Object> m = new HashMap<String, Object>();

        List<String> authors = new ArrayList<String>();
        authors.add("hresh2");
        authors.add("hresh");
        m.put("authors",authors);

        List<Blog> blogs = blogMapper.queryByForeach(m);
        for (Blog blog : blogs) {
            System.out.println(blog);
        }

        sqlSession.close();
    }
复制代码

当 authors 列表为空时，执行该代码将查询出所有的结果集。

参考文献

MyBatis：动态SQL

动态 SQL