本文已参与「新人创作礼」活动，一起开启掘金创作之路。
1. 什么是动态SQL
动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。
使用动态 SQL 并非一件易事，但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言，MyBatis 显著地提升了这一特性的易用性。
如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器，你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中，需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式，MyBatis 3 替换了之前的大部分元素，大大精简了元素种类，现在要学习的元素种类比原来的一半还要少。
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
我们之前写的 SQL 语句都比较简单，如果有比较复杂的业务，我们需要写复杂的 SQL 语句，往往需要拼接，而拼接 SQL ，稍微不注意，由于引号，空格等缺失可能都会导致错误。
那么怎么去解决这个问题呢？这就要使用 mybatis 动态SQL，通过 if, choose, when, otherwise,trim, where, set, foreach等标签，可组合成非常灵活的SQL语句，从而在提高 SQL 语句的准确性的同时，也大大提高了开发人员的效率。
2. 简单使用
2.1 基本环境搭建
新建一个表blog
DROP TABLE IF EXISTS `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 '浏览量',
PRIMARY KEY (`id`) USING BTREE
);
MybatisConfig.java
public class MybatisConfig {
private static SqlSessionFactory sqlSessionFactory;
static {
try{
String resource = "config/MybatisConfig.xml";
InputStream resourceAsStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
} catch (IOException e) {
e.printStackTrace();
}
}
// 获取SqlSession连接
public static SqlSession getSession(){
return getSession(true); // 事务自动提交
}
public static SqlSession getSession(boolean flags){
return sqlSessionFactory.openSession(flags);
}
}
id生成工具类IdUtils.java
public class IdUtils {
public static String getId(){
return UUID.randomUUID().toString().replace("-","");
}
}
MybatisConfig.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>
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
<setting name="logImpl" value="STDOUT_LOGGING"/>-
</settings>
<typeAliases>
<package name="com.lpl.mybatis.domain"/>
</typeAliases>
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<property name="helperDialect" value="mysql"/>
</plugin>
</plugins>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?useSSL=false&useUnicode=true&characterEncoding=utf8"/>
<property name="username" value="root"/>
<property name="password" value="123456"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="mapper/BlogMapper.xml"/>
</mappers>
</configuration>
实体类Blog.java
@Data
public class Blog {
private String id;
private String title;
private String author;
private Date createTime;
private int views;
}
mapper层接口BlogMapper.java
public interface BlogMapper {
int addBlog(Blog blog);
}
BlogMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.lpl.mybatis.mapper.BlogMapper">
<insert id="addBlog" parameterType="Blog">
insert into blog (id, title, author, create_time, views)
values (#{id},#{title},#{author},#{createTime},#{views});
</insert>
</mapper>
测试类MyTest.java
...
import org.junit.Test;
public class MyTest3 {
/**
* 初始化数据（插入数据）
*/
@Test
public void addInitBlog() {
SqlSession session = MybatisConfig.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
Blog blog = new Blog();
blog.setId(IdUtil.getId())
.setTitle("blog1")
.setAuthor("张三")
.setCreateTime(new Date())
.setViews(9999);
mapper.addBlog(blog);
blog.setId(IdUtil.getId())
.setTitle("blog2");
mapper.addBlog(blog);
blog.setId(IdUtil.getId())
.setTitle("blog3");
mapper.addBlog(blog);
blog.setId(IdUtil.getId())
.setTitle("blog4");
mapper.addBlog(blog);
session.close();
}
}
执行测试方法
2.2 if
需求1：根据title和author来查询博客，如果author为空，那么只根据title名字查询，反之，则根据author来查询
mapper层接口方法
// 需求1
List<Blog> queryBlogIf(Map map);
mapper.xml
<select id="queryBlogIf" parameterType="map" resultType="com.lpl.mybatis.domain.Blog">
select * from blog
where
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</select>
测试方法
/**
* 测试需求1
*/
@Test
public void testQueryBlogIf() {
SqlSession session = MybatisConfig.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
HashMap<String, String> map = new HashMap<String, String>();
map.put("title", "blog1");
map.put("author", "张三");
List<Blog> blogs1 = mapper.queryBlogIf(map);
System.out.println("条件title和author都存在-----------");
System.out.println(blogs1);
map.put("title", "blog2");
map.remove("author");
List<Blog> blogs2 = mapper.queryBlogIf(map);
System.out.println("条件只有title--------------------");
System.out.println(blogs2);
map.remove("title");
map.put("author", "张三");
List<Blog> blogs3 = mapper.queryBlogIf(map);
System.out.println("条件只有author-----R--------------");
System.out.println(blogs3);
session.close();
}
如果 author 等于 null，那么查询语句为 select from user where title=#{title}，但是如果title为空，查询语句为 select from user where and author=#{author}，这是错误的，解决这个问题需要2.3中的where标签
2.3 where
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且，若子句的开头为 “AND” 或 “OR”，where 元素也会将它们去除。
修改mapper.xml
<select id="queryBlogIf" parameterType="map" resultType="com.lpl.mybatis.domain.Blog">
select * from blog
<where>
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</where>
</select>
再次执行测试方法
2.4 set
需求2：根据id更新博客，如果title为空，那么只更新author，反之，只更新title
mapper层接口方法
int updateBlog(Map map);
mapper.xml
<update id="updateBlog" parameterType="map">
update blog
<!-- set是用的逗号隔开 -->
<set>
<if test="title != null">
title = #{title},
</if>
<if test="author != null">
author = #{author}
</if>
</set>
where id = #{id};
</update>
测试方法
/**
* 测试需求2
*/
@Test
public void testUpdateBlog() {
SqlSession session = MybatisConfig.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
HashMap<String, String> map = new HashMap<String, String>();
// 对id为63ac8f80710e4d178f6b9dd22d3968a2的博客更新title为updateTitle1,author更新为updateAuthor2
map.put("title", "updateTitle1");
map.put("author", "updateAuthor2");
map.put("id", "63ac8f80710e4d178f6b9dd22d3968a2");
mapper.updateBlog(map);
// 对id为63ac8f80710e4d178f6b9dd22d3968a2的博客更新title为updateTitle2,author不更新
map.put("title","updateTitle2");
map.remove("author");
map.put("id", "81e38abe82f945178801e2eb30ed2dff");
mapper.updateBlog(map);
// 对id为63ac8f80710e4d178f6b9dd22d3968a2的博客不更新title,author更新为updateAuthor3
map.remove("title");
map.put("author","updateAuthor3");
map.put("id", "b0d8c5e6f5cb4321918cb9b5f727e681");
mapper.updateBlog(map);
session.close();
}
2.5 choose
有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句
mapper层接口方法
List<Blog> queryBlogChoose(Map map);
mapper.xml
<select id="queryBlogChoose" resultType="com.lpl.mybatis.domain.Blog">
select * from blog
<where>
<choose>
<when test="title != null">
title = #{title}
</when>
<when test="author != null">
and author = #{author}
</when>
<otherwise>
and views = #{views}
</otherwise>
</choose>
</where>
</select>
测试方法
/**
* 测试choose
*/
@Test
public void testQueryBlogChoose() {
SqlSession session = MybatisConfig.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
HashMap<String, Object> map = new HashMap<String, Object>();
// 无条件
List<Blog> blogs1 = mapper.queryBlogChoose(map);
System.out.println("无条件-------------------------");
System.out.println(blogs1);
// 只有一个条件views
map.put("views", 9999);
List<Blog> blogs2 = mapper.queryBlogChoose(map);
System.out.println("只有一个条件views-----------------");
System.out.println(blogs2);
// 含有两个条件title和view
map.put("title","blog3");
List<Blog> blogs3 = mapper.queryBlogChoose(map);
System.out.println("含有两个条件title和view-----------");
System.out.println(blogs3);
// 含有两个条件author和view
map.remove("title");
map.put("author","张三");
List<Blog> blogs4 = mapper.queryBlogChoose(map);
System.out.println("含有两个条件author和view-----------");
System.out.println(blogs4);
// 含有两个条件title和author
map.put("title","blog3");
map.remove("views");
List<Blog> blogs5 = mapper.queryBlogChoose(map);
System.out.println("含有两个条件title和author-----------");
System.out.println(blogs5);
session.close();
}
测试结果
==> Preparing: select * from blog WHERE views = ?
==> Parameters: null
<== Total: 0
无条件-------------------------
[]
==> Preparing: select * from blog WHERE views = ?
==> Parameters: 9999(Integer)
<== Columns: id, title, author, create_time, views
<== Row: 63ac8f80710e4d178f6b9dd22d3968a2, updateTitle1, updateAuthor2, 2022-01-27 22:50:29.0, 9999
<== Row: 81e38abe82f945178801e2eb30ed2dff, updateTitle2, 张三, 2022-01-27 22:50:29.0, 9999
<== Row: b0d8c5e6f5cb4321918cb9b5f727e681, blog4, updateAuthor3, 2022-01-27 22:50:29.0, 9999
<== Row: c7c20b70b1a849609f30d104e9ee060f, blog3, 张三, 2022-01-27 22:50:29.0, 9999
<== Total: 4
只有一个条件views-----------------
[Blog(id=63ac8f80710e4d178f6b9dd22d3968a2, title=updateTitle1, author=updateAuthor2, createTime=Thu Jan 27 22:50:29 CST 2022, views=9999), Blog(id=81e38abe82f945178801e2eb30ed2dff, title=updateTitle2, author=张三, createTime=Thu Jan 27 22:50:29 CST 2022, views=9999), Blog(id=b0d8c5e6f5cb4321918cb9b5f727e681, title=blog4, author=updateAuthor3, createTime=Thu Jan 27 22:50:29 CST 2022, views=9999), Blog(id=c7c20b70b1a849609f30d104e9ee060f, title=blog3, author=张三, createTime=Thu Jan 27 22:50:29 CST 2022, views=9999)]
==> Preparing: select * from blog WHERE title = ?
==> Parameters: blog3(String)
<== Columns: id, title, author, create_time, views
<== Row: c7c20b70b1a849609f30d104e9ee060f, blog3, 张三, 2022-01-27 22:50:29.0, 9999
<== Total: 1
含有两个条件title和view-----------
[Blog(id=c7c20b70b1a849609f30d104e9ee060f, title=blog3, author=张三, createTime=Thu Jan 27 22:50:29 CST 2022, views=9999)]
==> Preparing: select * from blog WHERE author = ?
==> Parameters: 张三(String)
<== Columns: id, title, author, create_time, views
<== Row: 81e38abe82f945178801e2eb30ed2dff, updateTitle2, 张三, 2022-01-27 22:50:29.0, 9999
<== Row: c7c20b70b1a849609f30d104e9ee060f, blog3, 张三, 2022-01-27 22:50:29.0, 9999
<== Total: 2
含有两个条件author和view-----------
[Blog(id=81e38abe82f945178801e2eb30ed2dff, title=updateTitle2, author=张三, createTime=Thu Jan 27 22:50:29 CST 2022, views=9999), Blog(id=c7c20b70b1a849609f30d104e9ee060f, title=blog3, author=张三, createTime=Thu Jan 27 22:50:29 CST 2022, views=9999)]
==> Preparing: select * from blog WHERE title = ?
==> Parameters: blog3(String)
<== Columns: id, title, author, create_time, views
<== Row: c7c20b70b1a849609f30d104e9ee060f, blog3, 张三, 2022-01-27 22:50:29.0, 9999
<== Total: 1
含有两个条件title和author-----------
[Blog(id=c7c20b70b1a849609f30d104e9ee060f, title=blog3, author=张三, createTime=Thu Jan 27 22:50:29 CST 2022, views=9999)]
含有三个条件title、author、view-----------
[Blog(id=c7c20b70b1a849609f30d104e9ee060f, title=blog3, author=张三, createTime=Thu Jan 27 22:50:29 CST 2022, views=9999)]
从结果可以看出，title和author都不存在时会根据views查询，当title和author有一个存在时，会根据它查询，如果title和author都存在，先进入when test="title != null"，然后根据title查询，不再有其它条件。
2.6 SQL片段
有时候可能某些 sql 语句用的特别多，为了增加代码的复用性，需要将这些代码抽取出来，然后使用时直接调用。
例如2.3中的SQL
<select id="queryBlogIf" parameterType="map" resultType="com.lpl.mybatis.domain.Blog">
select * from blog
<where>
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</where>
</select>
提取SQL片段
<sql id="if-title-author">
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</sql>
引用SQL片段
<select id="queryBlogIf" parameterType="map" resultType="blog">
select * from blog
<where>
<include refid="if-title-author"/>
</where>
</select>
2.7 foreach
动态 SQL 的另一个常见使用场景是对集合进行遍历（尤其是在构建 IN 条件语句的时候
需求3：需要查询 blog 表中id5，6，7的博客信息
sql脚本
INSERT INTO `blog` VALUES ('5', 'blog5', 'lpl5', '2022-01-28 23:31:28', 1231);
INSERT INTO `blog` VALUES ('6', 'blog6', 'lpl6', '2022-01-29 23:31:28', 231);
INSERT INTO `blog` VALUES ('7', 'blog7', 'lpl7', '2022-01-29 23:31:28', 654);
INSERT INTO `blog` VALUES ('8', 'blog8', 'lpl8', '2022-01-29 23:31:28', 8673);
mapper层接口方法
List<Blog> queryBlogForeach(Map map);
mapper.xml
<select id="queryBlogForeach" parameterType="map" resultType="com.lpl.mybatis.domain.Blog">
select * from blog
<where>
<!--
collection:指定输入对象中的集合属性
item:每次遍历生成的对象
open:开始遍历时的拼接字符串
close:结束时拼接的字符串
separator:遍历对象之间需要拼接的字符串
select * from blog where 1=1 and (id=1 or id=2 or id=3)
-->
<foreach collection="ids" item="id" open="and (" close=")" separator="or">
id=#{id}
</foreach>
</where>
</select>
测试方法
/**
* 测试foreach
*/
@Test
public void testQueryBlogForeach() {
SqlSession session = MybatisConfig.getSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
HashMap map = new HashMap();
List<Integer> ids = new ArrayList<Integer>();
ids.add(5);
ids.add(6);
ids.add(7);
map.put("ids", ids);
List<Blog> blogs = mapper.queryBlogForeach(map);
System.out.println(blogs);
session.close();
}
