动态SQL详解

发表于 更新于 分类于 本文字数： 6.3k

动态SQL详解

什么是动态SQL：动态SQL就是指根据不同的条件生成不同的SQL语句

1
2
3
4
5
如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器，你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中，需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式，MyBatis 3 替换了之前的大部分元素，大大精简了元素种类，现在要学习的元素种类比原来的一半还要少。
if
choose (when, otherwise)
trim (where, set)
foreach

所谓的动态SQL本质上还是SQL语句，只是我们可以在SQL层面上，去执行逻辑代码。

一、环境搭建

1. 创建blog表

1
2
3
4
5
6
7
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. 新建Blog实体类

1
2
3
4
5
6
7
8
9
@Data
@NoArgsConstructor
public class Blog {
    private int id;
    private String title;
    private String author;
    private Date create_time;
    private int views;
}

3. 新建BlogMapper接口

1
2
3
4
package com.nichu.dao;

public interface BlogMapper {
}

4. 编写Blog.xml

1
2
3
4
5
6
7
<?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.nichu.dao.BlogMapper">

</mapper>

5. 在核心配置文件中绑定接口

1
2
3
4
<!--绑定接口-->
<mappers>
    <mapper class="com.nichu.dao.BlogMapper"/>
</mappers>

6. 编写IDutils工具类用于生成UUID

1
2
3
4
5
6
7
import java.util.UUID;

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

7. 开启驼峰命名自动转换

在核心配置文件中配置

1
2
3
4
5
6
7
<settings>
    <!--配置日志-->
    <!--<setting name="logImpl" value="LOG4J"/>-->
    <setting name="logImpl" value="STDOUT_LOGGING"/>
    <!--开启驼峰命名自动转换-->
    <setting name="mapUnderscoreToCamelCase " value="true"/>
</settings>

编写测试类并插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
public void addBlogTest(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    Blog blog = new Blog();
    blog.setId(IDutils.getId());
    blog.setTitle("Mybatis");
    blog.setAuthor("狂神说");
    blog.setCreateTime(new Date());
    blog.setViews(9999);

    mapper.addBook(blog);

    blog.setId(IDutils.getId());
    blog.setTitle("Java");
    mapper.addBook(blog);

    blog.setId(IDutils.getId());
    blog.setTitle("Spring");
    mapper.addBook(blog);

    blog.setId(IDutils.getId());
    blog.setTitle("微服务");
    mapper.addBook(blog);

    sqlSession.close();
}

二、if

  • 使用if编写查询语句

1
2
3
4
5
6
7
8
9
10
11
<select id="queryBlogIF" parameterType="Map" resultType="com.nichu.pojo.Blog">
    select * from mybatis.blog where 1=1
    <!-- 如果title不为空拼接 and title = #{title} -->
    <if test="title!=null">
        and title = #{title}
    </if>
    <!-- 如果author不为空拼接 and author = #{author} -->
    <if test="author!=null">
        and author = #{author}
    </if>
</select>

  • 测试类

1
2
3
4
5
6
7
8
9
10
11
12
public void addBlogTest() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    Map map = new HashMap();
    // map.put("title","Java");
    map.put("author","狂神说");
    List<Blog> blogs = mapper.queryBlogIF(map);
    for (Blog blog : blogs) {
        System.out.println(blog);
    }
    sqlSession.close();
}

三、 choose (when, otherwise)

用法相当于java中的switch (case default)

chose --> switch
when --> case
otherwise --> default

执行时只要满足其中一个条件就结束

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<select id="queryBlogChoose" parameterType="Map" resultType="com.nichu.pojo.Blog">
    select * from mybatis.blog
    <where>
        <choose>
            <when test="title != null">
                and title = #{title}
            </when>
            <when test="author != null">
                and author = #{author}
            </when>
            <otherwise>
                and views = #{views}
            </otherwise>
        </choose>
    </where>
</select>

四、 trim (where, set)

  • <where>标签

因此只要将查询语句中的where部分用 <where> 标签包裹即可

  • <set>标签

<set>标签的意义和<where>标签一样，每设置一个属性，都要在语句后加 “,” 由于不知道哪个语句结尾，所有要用<set>标签自动处理。

1
2
3
4
5
6
7
8
9
10
11
12
<update id="updateBlog" 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>

  • <trim>标签

<trim>可以自定义<where>标签和<set>标签

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

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

1
2
3
<trim prefix="WHERE" prefixOverrides="AND |OR ">
  ...
</trim>
  • set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号（这些逗号是在使用条件语句给列赋值时引入的）。

以下是set 元素等价的自定义trim

1
2
3
<trim prefix="SET" suffixOverrides=",">
  ...
</trim>

五、SQL片段

有时候，我们可能会将一些功能的部分抽取出来，方便复用！

  1. 使用SQL标签抽取公共的部分
1
2
3
4
5
6
7
8
<sql id="if-title-author">
    <if test="title!=null">
        and title = #{title}
    </if>
    <if test="author!=null">
        and author = #{author}
    </if>
</sql>
  1. 在需要使用的地方使用Include标签引用即可
1
2
3
4
5
6
7
<select id="queryBlogIF" parameterType="Map" resultType="com.nichu.pojo.Blog">
    select * from mybatis.blog
    <where>
        <!-- 使用Include标签引用 -->
        <include refid="if-title-author"></include>
    </where>
</select>

注意事项

  • 最好基于单表来定义SQL片段！
  • 不要存在where标签

六、<foreach>标签

1. 查询id为（1-2-3）的博客记录

SQL语句：

1
2
select * from mybatis.blog where id in (1,2,3)
select * from mybatis.blog where (id=1 or id=2 or id=3)

2. 使用foreach遍历id

1
2
3
4
5
6
7
8
<select id="queryBlogForEach" parameterType="map" resultType="com.nichu.pojo.Blog">
    select * from mybatis.blog
        <where>
            <foreach collection="ids" open="and (" close=")" separator="or" item="id">
                id=#{id}
            </foreach>
        </where>
</select>

3. 编写测试类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
public void addBlogTest() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
    Map map = new HashMap();
    // 使用ArrayList存放id
    ArrayList<Integer> ids = new ArrayList<>();
    ids.add(1);
    ids.add(2);
    ids.add(4);
    map.put("ids",ids);

    List<Blog> blogs = mapper.queryBlogForEach(map);
    for (Blog blog : blogs) {
        System.out.println(blog);
    }
    sqlSession.close();
}

关于动态SQL的一些建议

先在MySQL中写出完整的SQL语句，再对应的去修改成为动态SQL实现通用即可。

-------------本文结束感谢您的阅读-------------