动态SQL
1.什么是动态SQL
什么是动态SQL：动态SQL指的是根据不同的查询条件 , 生成不同的Sql语句.
类似JSTL标签
官网描述：
MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其它类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句的痛苦。例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。
虽然在以前使用动态 SQL 并非一件易事，但正是 MyBatis 提供了可以被用在任意 SQL 映射语句中的强大的动态 SQL 语言得以改进这种情形。
动态 SQL 元素和 JSTL 或基于类似 XML 的文本处理器相似。在 MyBatis 之前的版本中，有很多元素需要花时间了解。MyBatis 3 大大精简了元素种类，现在只需学习原来一半的元素便可。MyBatis 采用功能强大的基于 OGNL 的表达式来淘汰其它大部分元素。
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
我们之前写的 SQL 语句都比较简单，如果有比较复杂的业务，我们需要写复杂的 SQL 语句，往往需要拼接，而拼接 SQL ，稍微不注意，由于引号，空格等缺失可能都会导致错误。
那么怎么去解决这个问题呢？这就要使用 mybatis 动态SQL，通过 if, choose, when, otherwise, trim, where, set, foreach等标签，可组合成非常灵活的SQL语句，从而在提高 SQL 语句的准确性的同时，也大大提高了开发人员的效率。
2.环境搭建
- 新建一个数据库表：blog
字段：id，title，author，create_time，views
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;
-
创建Mybatis基础工程
-
IDutil工具类
-
实体类编写 【注意set方法作用】
注意：Date类为java.util.Date，不是java.sql.Date
import java.util.Date;
public class Blog {
private String id;
private String title;
private String author;
private Date createTime;
private int views;
//set，get....
}
- 编写Mapper接口及xml文件
public interface BlogMapper {
}
<?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.study.mapper.BlogMapper">
</mapper>
- mybatis核心配置文件，下划线驼峰自动转换
<settings>
<setting name="mapUnderscoreToCamelCase" value="true"/>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<!--注册Mapper.xml-->
<mappers>
<mapper resource="com/study/dao/BlogMapper.xml"/>
</mappers>
- 插入初始数据
编写接口
//新增一个博客
int addBlog(Blog blog);
映射文件
<insert id="addBlog" parameterType="blog">
insert into blog (id, title, author, create_time, views)
values (#{id},#{title},#{author},#{createTime},#{views});
</insert>
初始化博客方法
import com.study.dao.BlogMapper;
import com.study.pojo.Blog;
import com.study.utils.IDUtil;
import com.study.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.Date;
public class MyTest {
@Test
public void addInitBlog(){
SqlSession session = MybatisUtils.getSqlSession();
BlogMapper mapper = session.getMapper(BlogMapper.class);
Blog blog = new Blog();
blog.setId(IDUtil.genId());
blog.setTitle("Mybatis如此简单");
blog.setAuthor("狂神说");
blog.setCreateTime(new Date());
blog.setViews(9999);
mapper.addBlog(blog);
blog.setId(IDUtil.genId());
blog.setTitle("Java如此简单");
mapper.addBlog(blog);
blog.setId(IDUtil.genId());
blog.setTitle("Spring如此简单");
mapper.addBlog(blog);
blog.setId(IDUtil.genId());
blog.setTitle("微服务如此简单");
mapper.addBlog(blog);
session.close();
}
}
初始化数据完毕！
3.if标签
使用动态 SQL 最常见情景是根据条件包含 where 子句的一部分。
可以实现按不同列搜索的功能（类似方法重载实现的效果）
-
BlogMapper
List<Blog> queryBlogIF(Map map); -
BlogMapper.xml
<select id="queryBlogIF" parameterType="map"> select * from mybatis.blog where 1=1 <if test="title != null"> and title=#{title} </if> <if test="author != null"> and author=#{author} </if> </select>这条语句提供了可选的查找文本功能。如果不传入 “title”和“author”，那么所有BLOG 都会返回；如果传入了 “title” 参数，那么就会对 “title” 一列进行查找并返回对应的 BLOG 结果（细心的读者可能会发现，“title” 的参数值需要包含查找掩码或通配符字符）；如果传入了 “author” 参数，那么就会对 “author” 一列进行查找并返回对应的 BLOG 结果；如果都传，也返回相应的结果
-
测试
@Test public void queryBlog(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); BlogMapper mapper=sqlSession.getMapper(BlogMapper.class); Map map=new HashMap(); map.put("title","Mybatis如此简单"); // map.put("author","狂神说"); List<Blog> blogList = mapper.queryBlogIF(map); for (Blog blog : blogList) { System.out.println(blog); } sqlSession.close(); }
4.trim，where，set
where
- where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 关键字。
- 若子句的开头为 “AND” 或 “OR”，where 元素也会视情况将它们去除或保留。
-
BlogMapper.xml
<select id="queryBlogIF" parameterType="map"> <!-- select * from mybatis.blog where 1=1--> <!-- <if test="title != null">--> <!-- and title=#{title}--> <!-- </if>--> <!-- <if test="author != null">--> <!-- and author=#{author}--> <!-- </if>--> <!--为什么需要1=1--> <!--如果没有匹配的条件会怎么样？最终这条 SQL 会变成这样：SELECT * FROM BLOG WHERE 这会导致查询失败--> <!--如果匹配的只是第二个条件又会怎样？这条 SQL 会是这样:SELECT * FROM BLOG WHERE AND title = ‘someTitle’ 这个查询也会失败。--> select * from mybatis.blog where <!-- <if test="title != null">--> <!-- title=#{title}--> <!-- </if>--> <!-- <if test="author != null">--> <!-- and author=#{author}--> <!-- </if>--> <!--如何不使用where 1=1--> select * from mybatis.blog <where> <if test="title != null"> title=#{title} </if> <if test="author != null"> and author=#{author} </if> </where> </select> -
测试代码同上
set
set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号（这些逗号是在使用条件语句给列赋值时引入的）。
-
BlogMapper
int updateBlog(Map map); -
BlogMapper.xml
<update id="updateBlog" parameterType="map" > update blog <set> <if test="title != null"> title=#{title}, </if> <if test="author != null"> author=#{author} </if> </set> where id={id} </update> -
测试
@Test public void updateBlog(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); BlogMapper mapper=sqlSession.getMapper(BlogMapper.class); Map map=new HashMap(); map.put("title","Mybatis如此简单2"); map.put("author","狂神说"); map.put("id","5482dbf65d264012833e78b74e9fd95b"); mapper.updateBlog(map); sqlSession.close(); }
只传title，运行正常
只传author，运行正常
传title和author，运行正常
不传titl和author，报错
trim
<trim prefix="" suffix="" suffixOverrides="" prefixOverrides=""></trim>
参数说明
-
prefix：给 trim 标签内 sql 语句加上前缀
-
suffix：给 trim 标签内 sql 语句加上后缀
-
prefixOverrides：去除多余的前缀内容，如：prefixOverrides=“OR”，去除 trim 标签内 sql 语句多余的前缀 “OR”
-
suffixOverrides：去除多余的后缀内容，如：suffixOverrides=“，”，去除 trim 标签内 sql 语句多余的后缀 “，”
如果 where 元素与你期望的不太一样，你也可以通过自定义 trim 元素来定制 where 元素的功能。比如，和 where 元素等价的自定义 trim 元素为：
<trim prefix="WHERE" prefixOverrides="AND |OR ">
...
</trim>
prefixOverrides 属性会忽略通过管道符分隔的文本序列（注意此例中的空格是必要的）。上述例子会移除所有 prefixOverrides 属性中指定的内容，并且插入 prefix 属性中指定的内容。
你可以通过使用trim元素来达到中同样的效果：
<trim prefix="SET" suffixOverrides=",">
...
</trim>
注意，我们覆盖了后缀值设置，并且自定义了前缀值。
choose、when、otherwise
类似与switch…case…default
-
BlogMapper
List<Blog> queryBlogChoose(Map map); -
BlogMapper.xml
<select id="queryBlogChoose" parameterType="map" resultType="blog"> select * from mybatis.blog <choose> <when test="title != null"> title=#{title} </when> <when test="author != null"> author=#{author} </when> <otherwise> and views=#{views} </otherwise> </choose> </select> -
测试
@Test public void queryBlogChoose(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); BlogMapper mapper=sqlSession.getMapper(BlogMapper.class); Map map=new HashMap(); // map.put("title","Mybatis如此简单"); // map.put("author","狂神说"); map.put("views",9999); List<Blog> blogList = mapper.queryBlogChoose(map); for (Blog blog : blogList) { System.out.println(blog); } sqlSession.close(); }不传参数时，正常运行
传title和author时，正常运行
传author时，正常运行
foreach
将blog表中的id改为1，2，3，4
-
BlogMapper
//查询1，2，3号记录的博客 List<Blog> queryBlogForeach(Map map); -
BlogMapper.xml
<!-- select * from blog where 1=1 and (id=1 or id=2 or id=3)--> <select id="queryBlogForeach" parameterType="map" resultType="blog"> select * from blog <where> <!--我们现在传递一个万能的map，map中存在一个集合--> <foreach collection="ids" item="id" open="and (" close=")" separator="or"> id=#{id} </foreach> </where> </select> -
测试
@Test public void queryBlogForeach(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); BlogMapper mapper=sqlSession.getMapper(BlogMapper.class); Map map=new HashMap(); ArrayList<Integer> ids = new ArrayList<>(); ids.add(1); ids.add(2); ids.add(3); map.put("ids",ids); mapper.queryBlogForeach(map); sqlSession.close(); }
SQL片段
将需要重复编写的sql片段提取出来方便复用
-
使用sql标签抽取出重复sql片段：
<sql id="sql片段id名"><sql id="if-title-author"> <if test="title != null"> title=#{title}, </if> <if test="author != null"> author=#{author} </if> </sql> -
在需要使用的地方使用include标签引用即可：
<include refid="sql片段id名"><select id="queryBlogIF" parameterType="map" resultType="com.study.pojo.Blog"> select * from mybatis.blog <where> <include refid="if-title-author"/> </where> </select>
动态SQL就是在拼接SQL语句，我们只要保证SQL的正确性，按照SQL的语法格式，去排列组合就可以了。
建议：先在MYSQL中写出完整的SQL再对应的去修改成动态SQL实现
小结：其实动态 sql 语句的编写往往就是一个拼接的问题，为了保证拼接准确，我们最好首先要写原生的 sql 语句出来，然后在通过 mybatis 动态sql 对照着改，防止出错。多在实践中使用才是熟练掌握它的技巧。
5242
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
