动态SQL
前言
- 动态SQL,简单而言就是根据不同条件生成不同的SQL语句。
- 动态SQL和 JSTL 或任何基于类 XML 语言的文本处理器相似。在 MyBatis 之前的版本中,需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式,MyBatis 3 替换了之前的大部分元素,大大精简了元素种类,现在要学习的元素种类比原来的一半还要少。
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
一、数据库建表
1.建表语句
CREATE TABLE `blog` (
`id` varchar(50) NOT NULL ,
`title` varchar(100) NOT NULL ,
`author` varchar(30) NOT NULL ,
`create_time` datetime NOT NULL ,
`views` int(30) NOT NULL ,
PRIMARY KEY (`id`)
)
;
2.表增加数据
- BlogMapper接口
package dao.userdao;
import dao.pojo.Blog;
public interface BlogMapper {
//表增加数据
void 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">
<!--namespace=绑定一个对应的dao接口/mapper接口-->
<mapper namespace="dao.userdao.BlogMapper">
<insert id="addBlog" parameterType="Blog">
insert into blog values (#{id},#{title},#{author},#{create_time},#{views})
</insert>
</mapper>
- 测试类
package dao.userdao;
import dao.pojo.Blog;
import dao.utils.IDUtils;
import dao.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.Date;
public class UserMapperTest {
@Test
public void test2(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Blog blog = new Blog(IDUtils.getId(),"标题1","作者1",new Date(),2000);
Blog blog2 = new Blog(IDUtils.getId(),"标题2","作者2",new Date(),2000);
Blog blog3 = new Blog(IDUtils.getId(),"标题3","作者3",new Date(),2000);
Blog blog4 = new Blog(IDUtils.getId(),"标题4","作者4",new Date(),2000);
Blog blog5 = new Blog(IDUtils.getId(),"标题5","作者5",new Date(),2000);
mapper.addBlog(blog);
mapper.addBlog(blog2);
mapper.addBlog(blog3);
mapper.addBlog(blog4);
mapper.addBlog(blog5);
sqlSession.commit();
sqlSession.close();
}
}
- 补充:UUID是指在一台机器上生成的数字,它保证对在同一时空中的所有机器都是唯一的。通常平台会提供生成的API。按照开放软件基金会(OSF)制定的标准计算,用到了以太网卡地址、纳秒级时间、芯片ID码和许多可能的数字。
package dao.utils;
import java.util.UUID;
public class IDUtils {
/*
* UUID是指在一台机器上生成的数字,它保证对在同一时空中的所有机器都是唯一的。通常平台会提供生成的API。
* 按照开放软件基金会(OSF)制定的标准计算,用到了以太网卡地址、纳秒级时间、芯片ID码和许多可能的数字
* */
public static String getId(){
return UUID.randomUUID().toString().replace("-","");
}
}
- 数据库blog表
二、IF
我们需要解决的问题:动态查询blog表,根据title或者author或者title和author
1.BlogMapper接口
package dao.userdao;
import dao.pojo.Blog;
import java.util.List;
import java.util.Map;
public interface BlogMapper {
//IF
List<Blog> queryBlogIF(Map map);
}
2.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">
<!--namespace=绑定一个对应的dao接口/mapper接口-->
<mapper namespace="dao.userdao.BlogMapper">
<select id="queryBlogIF" parameterType="map" resultType="Blog">
select * from blog
<!--
where标签:
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。
而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
-->
<where>
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</where>
</select>
</mapper>
3.测试类
package dao.userdao;
import dao.pojo.Blog;
import dao.utils.IDUtils;
import dao.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class UserMapperTest {
@Test
public void test(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Map map = new HashMap();
List<Blog> blogs = mapper.queryBlogIF(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
map.put("title","标题3");
blogs = mapper.queryBlogIF(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
map.put("title","标题2");
map.put("author","作者2");
blogs = mapper.queryBlogIF(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
}
4.运行结果
与测试类对比图:
分析:
我们可以看到Mapper.xml实现配置:
- 第一种情况:map为空,两个if判断都为假,执行SQL:
select * from blog
- 第二种情况:
map.put("title","标题3");
,第一个if为真,第二个为假,执行SQL:select * from blog where title = #{title}
- 第三种情况:
map.put("title","标题2");map.put("author","作者2");
两个if判断都为真,执行SQL:select * from blog where title = #{title} and author = #{author}
三、choose (when, otherwise)
-
有时候,我们不想使用所有的条件,而只是想从多个条件中选择一个使用。针对这种情况,MyBatis 提供了 choose 元素,它有点像 Java 中的 switch 语句。
-
我们需要解决的问题:title!=null时,根据title查询blog表,title==null,author!=null时,根据author查询blog表,title和author都为null的话,最终根据views查询
1.BlogMapper接口
package dao.userdao;
import dao.pojo.Blog;
import java.util.List;
import java.util.Map;
public interface BlogMapper {
//choose (when, otherwise)
List<Blog> queryBlogChoose(Map map);
}
2.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">
<!--namespace=绑定一个对应的dao接口/mapper接口-->
<mapper namespace="dao.userdao.BlogMapper">
<select id="queryBlogChoose" parameterType="map" resultType="Blog">
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>
</mapper>
3.测试类
package dao.userdao;
import dao.pojo.Blog;
import dao.utils.IDUtils;
import dao.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class UserMapperTest {
@Test
public void test3(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Map map = new HashMap();
map.put("views",2000);
map.put("author","作者2");
map.put("title","标题3");
List<Blog> blogs = mapper.queryBlogChoose(map);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
}
4.运行结果
分析:
title不为空,执行SQL:select * from blog WHERE title = "标题3"
四、trim (where, set)
1.trim
(1)trim 元素的主要功能是可以在自己包含的内容前加上某些前缀,也可以在其后加上某些后缀,与之对应的属性是 prefix 和 suffix。
(2)可以把包含内容的首部某些内容覆盖,即忽略,也可以把尾部的某些内容覆盖,对应的属性是 prefixOverrides 和 suffixOverrides。正因为 trim 元素有这样的功能,所以也可以非常简单地利用 trim来代替 where 元素的功能。
(3)trim来代替 where 元素
<select id="queryBlogIF" parameterType="map" resultType="Blog">
select * from blog
<!--
where标签:
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。
而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
-->
<trim prefix="WHERE" prefixOverrides="AND |OR ">
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</trim>
</select>
(4)trim来代替 set 元素
<trim prefix="SET" suffixOverrides=",">
...
</trim>
2.where
- where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
- 此外,在 where元素中不需要考虑空格的问题,MyBatis 将智能加上。
- 前面的代码都有用到。
3.set
- set 元素会动态地在行首插入 SET 关键字,并会删掉额外的逗号(这些逗号是在使用条件语句给列赋值时引入的)。
- 在动态 update 语句中可以使用 set元素动态更新列。
- 我们需要解决的问题:动态更新blog表根据id
(1)BlogMapper接口
package dao.userdao;
import dao.pojo.Blog;
import java.util.List;
import java.util.Map;
public interface BlogMapper {
//set
int queryBlogSet(Map map);
}
(2)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">
<!--namespace=绑定一个对应的dao接口/mapper接口-->
<mapper namespace="dao.userdao.BlogMapper">
<update id="queryBlogSet" parameterType="map" >
update blog
<set>
<if test="title != null">
title = #{title},
</if>
<if test="author != null">
author = #{author}
</if>
</set>
where id = #{id}
</update>
</mapper>
(3)测试类
package dao.userdao;
import dao.pojo.Blog;
import dao.utils.IDUtils;
import dao.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class UserMapperTest {
@Test
public void test4(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Map map = new HashMap();
map.put("author","小明");
map.put("id","10c152062a89404dafc12d190080dc81");
mapper.queryBlogSet(map);
sqlSession.commit();
sqlSession.close();
}
}
(4)运行结果
五、foreach
-
foreach元素主要用在构建
in
条件中,它可以在 SQL 语句中迭代一个集合。 -
foreach元素的属性主要有 item、index、collection、open、separator、close。
item 表示集合中每一个元素进行迭代时的别名。
index 指定一个名字,用于表示在迭代过程中每次迭代到的位置。
open 表示该语句以什么开始。
separator 表示在每次进行迭代之间以什么符号作为分隔符。
close 表示以什么结束。 -
在使用 foreach元素时,最关键、最容易出错的是 collection 属性,该属性是必选的,但在不同情况下该属性的值是不一样的,主要有以下 3 种情况:
-
如果传入的是单参数且参数类型是一个 List,collection 属性值为 list。
-
如果传入的是单参数且参数类型是一个 array 数组,collection 的属性值为 array。
-
如果传入的参数是多个,需要把它们封装成一个 Map,当然单参数也可以封装成 Map。Map 的 key 是参数名,collection 属性值是传入的 List 或 array 对象在自己封装的 Map 中的 key。
1.BlogMapper接口
package dao.userdao;
import dao.pojo.Blog;
import java.util.List;
import java.util.Map;
public interface BlogMapper {
//foreach 同时查询几个id对应的blog
List<Blog> getBlogById(Map map);
}
2.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">
<!--namespace=绑定一个对应的dao接口/mapper接口-->
<mapper namespace="dao.userdao.BlogMapper">
<select id="getBlogById" parameterType="map" resultType="Blog">
select * from blog where id in
<foreach collection="ids" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</select>
</mapper>
3.测试类
package dao.userdao;
import dao.pojo.Blog;
import dao.utils.IDUtils;
import dao.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.*;
public class UserMapperTest {
@Test
public void test5(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
Map map = new HashMap();
ArrayList ids = new ArrayList();
ids.add("10c152062a89404dafc12d190080dc81");
ids.add("1f435e09d29e4e8488a622afaa95f123");
map.put("ids",ids);
mapper.getBlogById(map);
sqlSession.close();
}
}
4.运行结果
六、SQL片段
- 我们可以将一些功能的部分抽出来,方便复用。用sql标签抽出公共的部分。
- 例子:
<select id="queryBlogIF" parameterType="map" resultType="Blog">
select * from blog
<!--
where标签:
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。
而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
-->
<where>
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</where>
</select>
我们可以把上面的拆成两个部分,其他部分也需要sql标签里面的话,直接用include标签导入即可。
<sql id="if-title-author">
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</sql>
<select id="queryBlogIF" parameterType="map" resultType="Blog">
select * from blog
<!--
where标签:
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。
而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
-->
<where>
<include refid="if-title-author"></include>
</where>
</select>
- 注意事项:
最好基于单表来定义sql片段,不要存在where标签。