动态SQL
动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架,你应该能理解根据不同条件拼接 SQL 语句有多痛苦,例如拼接时要确保不能忘记添加必要的空格,还要注意去掉列表最后一个列名的逗号。利用动态 SQL,可以彻底摆脱这种痛苦。
在博主看来,所谓动态SQL就是可以在不同的条件下拼接出不同的SQL语句。
Mybatis中主要包含以下这些设置动态SQL的标签:
- if
- choose(when,otherwise)
- trim(where,set)
- foreach
数据库环境准备:
CREATE DATABASE mybatis;
USE mybatis;
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;
insert into `blog`(`id`,`title`,`author`,`create_time`,`views`) values
('46931d7526684bf5907a364bef060c49','Java基础','Ara_Hu','2020-03-08 21:36:28',999),
('94c7f67852df4b0f8a5b3622bb019a37','HTML基础','Ara_Hu','2020-03-08 21:36:28',1999),
('0180aead57b94a9c891a65dadf3c4f70','MYSQL基础','Ara_Hu','2020-03-08 21:36:28',999),
('7604b27e80f249dbb293ac44a07eaddd','JSP基础','Ara_Hu','2020-03-08 21:36:28',3999),
('94c61b1e77a24866bfb0663ec3b3f914','Java环境搭建','Admin-TY','2020-03-08 21:38:58',9999),
('cae412bd87f040f7a95ea2e72c445630','HTML入门','Admin-TY','2020-03-08 21:38:58',9299),
('e496001fdb0d43eaad4c9dd40c079af2','MYSQL进阶','Admin-TY','2020-03-08 21:38:58',9999),
('393220b217264af2bd711defa3b97219','JSP常用指令','Admin-TY','2020-03-08 21:38:58',999);
这里我们为了更直观的看到执行的SQL语句,建议在Mybatis配置文件中打开日志功能,这样更能直观的感受到我们的动态SQL的执行:
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
其它配置就不予赘述了。
if标签
这对于我们学了这么久的程序来说,理解起来并不难,它就是判断的标签,根据if标签中的条件是否成立,来拼接if中的SQL语句。
我们还是直接看一段具体的例子:
BlogMapper接口中的演示方法:
/**
* 查询博客列表
* 采用if标签
*
* @param map 传入的map集合
* 如果不含任何key,就全部查询
* 如果含有title 不含有author 就按照title查询
* 如果含有author 不含有title 就按照author查询
* 如果含有author 而且含有title 就按照author和title的条件查询
* @return 返回对应的Blog列表
*/
List<Blog> queryBlogIf(Map map);
BlogMapper.xml文件实现上述方法的SQL:
<select id="queryBlogIf" 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>
这里的<if>
标签就像是JSTL中的标签一样,test属性就是用来设置判断的条件。
上述xml中的<if>
标签的意思就是,如果title值不为空,就拼接其中的SQL,后者的author一致。
测试代码如下:
@Test
public void queryBlogIfTest() {
SqlSession sqlSession = MybatisUtil.getSqlSession();
BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
map.put("title", "HTML基础");
map.put("author", "Ara_Hu");
List<Blog> blogs = blogMapper.queryBlogIf(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
感兴趣的读者可以自行测试,将map添加值的两句代码分别省略和同时省略,还有都不省略,都会产生不一样的效果,特别注意它每次运行的SQL语句。
<if>
标签- 如果
<if>
的条件成立,返回<if>
中的SQL - 如果
<if>
的条件不成立,返回空
- 如果
choose标签
和<choose>
标签搭配的还有另外两个标签,<when>
和<otherwise>
,它们就相当于switch语句,其对应方式如下:
<choose>
:switch<when>
:case<otherwise>
:default
举例来说明吧
BlogMapper接口中添加方法:
/**
* 查询博客列表
* choose标签
*
* @param map 传入的map集合(至少含有title、author或者views其中的一个)
* 如果含有title 就按照title条件查询
* 如果含有author 就按照author条件查询
* 如果含有views 就按照views条件查询
* @return 返回对应的Blog列表
*/
List<Blog> queryBlogChoose(Map map);
BlogMapper.xml中实现的查询语句:
<select id="queryBlogChoose" parameterType="map" resultType="Blog">
select * from blog where 1 = 1
<choose>
<when test="title != null">
and title = #{title}
</when>
<when test="author != null">
and author = #{author}
</when>
<otherwise>
and views = #{views}
</otherwise>
</choose>
</select>
这里就是说,它会进行判断选择,先看传入Map中的title是否为空,如果不为空,就拼接title条件的查询语句,这里只要拼接上了,就不会再向下判断,如果为空就进行对author的判断,如果author不为空就拼接author条件的SQL,这里也是一样,拼接上了就结束拼接,如果前两个判断都不满足,就拼接<otherwise>
中的sql语句。
测试代码:
@Test
public void queryBlogChooseTest() {
SqlSession sqlSession = MybatisUtil.getSqlSession();
BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
map.put("views", 999);
//map.put("title","Java基础");
map.put("author","Ara_Hu");
List<Blog> blogs = blogMapper.queryBlogChoose(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
感兴趣的读者可以对上述的代码进行测试,分别注释添加title和author的代码即可,注意观察它拼接执行的SQL语句。
<choose>
标签- 进入
<choose>
后就会按顺序执行 - 当遇到一个
<when>
满足其中的条件时,返回这个<when>
其中的SQL - 如果所有的
<when>
都不满足,则返回<otherwise>
中的SQL - 如果所有的
<when>
都不满足,并且也没有<otherwise>
,就返回空
- 进入
trim标签
<trim>
标签似乎有点难以理解,我们先看两个基于它实现的<where>
和<set>
标签。
我们发现前面的SQL语句在拼接时,我们都加了一句where 1 = 1
,我们动态拼接SQL语句,如果不添加这句,后面拼接出的SQL语句就会有问题,就会产生例如select * from blog and author = ?
的SQL语句,这样的SQL语句就不能正常的执行,于是Mybatis提出了<trim>
标签来解决这个问题。
where
强行的解释它是干什么的,博主并不擅长,还是举例说明吧,我们来对上面queryBlogIf进行改进,我们去掉SQL语句中的where 1 = 1
:
<!-- queryBlogIf改进版 -->
<select id="queryBlogIf" parameterType="map" resultType="Blog">
select * from blog
<where>
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</where>
</select>
这里的添加where,它的作用就是检查where中的sql语句,
如果其中的title不为空,就会在 title = ?
前面添加where从而拼接为 where title = ? ....
如果title为空,在<where>
留下的SQL片段就是and author = ?
,<where>
标签就会将and去掉,从而拼接为where author = ?
<where>
标签的原型就是如下:
<trim prefix="WHERE" prefixOverrides="AND |OR ">
...
</trim>
如果Mybatis发现在<where>
中的sql,它会自动在这段SQL之前拼接where(前缀为where),如果这段SQL是and或者or开头的,它就会将and或者or去掉(and 或者or 都会被覆盖)。
<where>
标签- 如果
<where>
中的SQL为空,则为空 - 如果
<where>
中的SQL不为空,则在这个SQL之前添加where - 如果
<where>
中的SQL是以and或者or开头,则去掉这个and或者or
- 如果
set
<set>
标签一般在修改语句中用到,还是直接举例说明:
BlogMapper接口中添加方法:
/**
* 根据传入的map集合更新博客信息
* set标签
*
* @param map 传入的map集合至少包含id和(title、author、views)字段中的一个
* @return 返回受影响的行数
*/
int updateBlog(Map map);
BlogMapper.xml中的SQL实现:
<update id="updateBlog" parameterType="map">
update blog
<set>
<if test="title != null">
title = #{title},
</if>
<if test="author != null">
author = #{author},
</if>
<if test="views != null">
views = #{views}
</if>
</set>
where id = #{id};
</update>
测试代码:
@Test
public void updateBlogTest(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
HashMap map = new HashMap();
map.put("id","46931d7526684bf5907a364bef060c49");
map.put("title","Java基础");
map.put("author","Ara_Hu");
map.put("views",9999);
int i = blogMapper.updateBlog(map);
sqlSession.commit();
System.out.println(i);
sqlSession.close();
}
测试结果读者可以自行观察一下,尝试注释title、author和views其中的任意两个,分别查看执行的SQL语句。
这里<set>
解决的就是字段设置之后的,
的问题,如果<set>
中的SQL的结果是,
结尾,整个SQL就会拼接成update blog xxx = ?, where id = ?
这样的SQL显然就有问题,Mybatis为了解决这个问题,就提出了<set>
标签来解决它,凡是在<set>
中的SQL语句,如果其中的SQL语句不为空,它就会在这段SQL之前添加set,如果其中的SQL是,
结尾,它就会省略这个,
。
<set>
标签的<trim>
原型如下:
<trim prefix="SET" suffixOverrides=",">
...
</trim>
这个就表示,在该标签中的SQL如果不为空,就以set
开头(set为前缀),如果这个SQL语句的是以,
结尾,则将这个,
省略(后缀自动被覆盖)。
<set>
标签:- 如果
<set>
中的SQL片段为空,则为空 - 如果
<set>
中的SQL片段不为空,则在开头添加set - 如果
<set>
中的SQl片段是以,
结尾,则去掉这个,
- 如果
相信读者看到这里,对<trim>
标签就有一定的理解了。
foreach标签
这个标签,一看就知道是循环的标签,当然,它就是表示循环的,举个例子吧。
BlogMapper接口添加的方法:
/**
* 查询map中指定集合中的Id文章列表
*
* @param map 指定的Map集合
* 这个map集合中需要包含集合ids 用于展示文章列表的Id
* @return 返回对应的文章列表
*/
List<Blog> queryBlogForeach(Map map);
BlogMapper.xml实现的SQL:
<select id="queryBlogForeach" parameterType="map" resultType="Blog">
select * from blog
<where>
<if test="ids != null">
id in
<foreach collection="ids" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</if>
</where>
</select>
这里使用了几个标签的嵌套:
- 首先是
<where>
- 如果在
<where>
中的SQL为空,整个SQL就是select * from blog
- 如果在
<where>
中的SQL不为空,整个SQL就是select * from blog where ...
- 如果在
- 其次是
<if>
- 如果
<if>
中的的判断不成立,则这句SQL就为空。 - 如果
<if>
中的判断成立,这个SQL就是select * from blog where id in ...
- 如果
- 然后是
<foreach>
- 当执行到
<foreach>
中,说明ids不为空,可以循环遍历。 - collection:表示我们需要遍历的集合
- item:表示我们每次遍历取出的变量名
- open:第一个需要添加的前缀
- separator:表示每个遍历元素的分隔符
- close:表示结尾用什么包裹
- 当执行到
测试代码:
@Test
public void queryBlogForeachTest(){
SqlSession sqlSession = MybatisUtil.getSqlSession();
BlogMapper blogMapper = sqlSession.getMapper(BlogMapper.class);
ArrayList<String> ids = new ArrayList<String>();
ids.add("46931d7526684bf5907a364bef060c49");
ids.add("94c7f67852df4b0f8a5b3622bb019a37");
ids.add("0180aead57b94a9c891a65dadf3c4f70");
ids.add("e496001fdb0d43eaad4c9dd40c079af2");
HashMap map = new HashMap();
map.put("ids",ids);
List<Blog> blogs = blogMapper.queryBlogForeach(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
感兴趣的读者可以自行尝试,然后注意看SQL的执行语句。