十、动态SQL
- 什么是动态SQL?
- 根据给出参数的不同,通过拼接等操作,生成不同的SQL语句
10.1 构造实验环境
-
数据库设计
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 NOT NULL COMMENT ‘浏览量‘ ) ENGINE=InnoDB DEFAULT CHARSET=utf8
-
mybatis-config.xml
<settings> <!--开启日志以及自动转换驼峰命名--> <setting name="logImpl" value="STDOUT_LOGGING"/> <setting name="mapUnderscoreToCamelCase" value="true"/> </settings>
-
实体类
package com.pbx.pojo; import java.util.Date; /** * @author BruceXu * @date 2020/11/8 */ public class Blog { private String id; private String title; private String author; private Date createTime; private int views; }
-
id工具类
package com.pbx.utils; import java.util.UUID; /** * @author BruceXu * @date 2020/11/8 */ public class IDUtils { public static String getId() { return UUID.randomUUID().toString().replaceAll("-",""); } }
-
BlogMapper接口
package com.pbx.mapper; import com.pbx.pojo.Blog; import org.apache.ibatis.annotations.Insert; /** * @author BruceXu * @date 2020/11/8 */ public interface BlogMapper { @Insert("insert into blog (id, title, author, create_time, views) values (#{id},#{title},#{author},#{createTime},#{views});") int addBlog(Blog blog); }
-
插入数据
@Test public void addBlog() { SqlSession session = MyBatisUtils.getSqlSession(); BlogMapper mapper = session.getMapper(BlogMapper.class); Blog blog = new Blog(); blog.setId(IDUtils.getId()); blog.setTitle("Mybatis如此简单"); blog.setAuthor("BruceXu"); blog.setCreateTime(new Date()); blog.setViews(9999); mapper.addBlog(blog); blog.setId(IDUtils.getId()); blog.setTitle("Java如此简单"); mapper.addBlog(blog); blog.setId(IDUtils.getId()); blog.setTitle("Spring如此简单"); mapper.addBlog(blog); blog.setId(IDUtils.getId()); blog.setTitle("微服务如此简单"); mapper.addBlog(blog); blog.setId(IDUtils.getId()); blog.setTitle("狂神说Java真的好"); mapper.addBlog(blog); session.commit(); session.close(); }
10.2 if
-
这个if和高级语言里面的判断语句if是一样的功能
-
需求
- 根据author和title来查询博客,如果一个为空,则就按照另外一个进行查询
-
BlogMapper接口
List<Blog> getBlog(Map map);
-
BlogMapper.xml
<mapper namespace="com.pbx.mapper.BlogMapper"> <select id="getBlog" 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> </mapper>
-
测试
@Test public void getBlog() { SqlSession session = MyBatisUtils.getSqlSession(); BlogMapper mapper = session.getMapper(BlogMapper.class); Map<String, String> map = new HashMap<>(); // map.put("title", "Mybatis如此简单"); // map.put("author", "BruceXu"); List<Blog> blogList = mapper.getBlog(map); for (Blog blog : blogList) { System.out.println(blog); } session.close(); }
-
如果说if中的条件有满足的话,则拼接上SQL语句如果没有满足的话,则不进行拼接
10.3 choose (when, otherwise)
-
choose的作用类似于高级语言中的switch~case语句块
-
需求:根据author或title来查询博客,有谁查谁,如果都没有就按照views查询
-
BlogMapper接口
List<Blog> getBlog2(Map map);
-
BlogMapper.xml
<mapper> <select id="getBlog2" 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 = 9999 </otherwise> </choose> </select> </mapper>
-
测试
@Test public void getBlog2() { SqlSession session = MyBatisUtils.getSqlSession(); BlogMapper mapper = session.getMapper(BlogMapper.class); Map<String, String> map = new HashMap<>(); // map.put("title", "Java如此简单"); // map.put("author", "BruceXu"); List<Blog> blogList = mapper.getBlog2(map); for (Blog blog : blogList) { System.out.println(blog); } session.close(); }
-
可以看到,如果when中的条件没有一个匹配上,那么拼接上otherwise中的内容。如果when中有多个匹配,那么则只拼接第一个匹配的
10.4 trim (where, set)
-
如果仔细看上面两个测试环境中使用的SQL语句,都会发现存在这么一点
where 1=1
,这个是为了保证在进行拼接的时候不出现这样的错误:select * from blog where and author = ...
-
那么有没有这样的一种方法,能够自动判断是不是要加上and或者空格等符号以保证SQL语句的正确性?
-
trim标签,当然,MyBatis也为我们提前封装好了where和set标签
<trim prefix="前缀匹配" prefixOverrides="前缀替换" suffix="后缀匹配" suffixOverrides="后缀替换">...</trim>
-
where和set示例
- BlogMapper接口
List<Blog> getBlog3(Map map); int updateBlog(Map map);
- BlohMapper.xml
<mapper> <select id="getBlog3" parameterType="map" resultType="Blog"> select * from blog <where> <if test="title != null"> and title = #{title} </if> <if test="author != null"> and author = #{author} </if> </where> </select> <update id="updateBlog" parameterType="map"> update blog <set> <if test="title != null"> title = #{title}, </if> <if test="author != null"> author = #{author}, </if> <if test="view != null"> views = #{view}, </if> </set> </update> </mapper>
- 测试
@Test public void getBlog3() { SqlSession session = MyBatisUtils.getSqlSession(); BlogMapper mapper = session.getMapper(BlogMapper.class); Map<String, String> map = new HashMap<>(); // map.put("title", "Mybatis如此简单"); map.put("author", "BruceXu"); List<Blog> blog3 = mapper.getBlog3(map); for (Blog blog : blog3) { System.out.println(blog); } session.close(); } @Test public void updateBlog() { SqlSession session = MyBatisUtils.getSqlSession(); BlogMapper mapper = session.getMapper(BlogMapper.class); Map<String, String> map = new HashMap<>(); map.put("view", "6666"); map.put("id", "ccfb770fc1b64dbf8fd7379a564dcd9f"); map.put("title", "狂神Java说的是真的好"); mapper.updateBlog(map); List<Blog> blog3 = mapper.getBlog3(map); for (Blog blog : blog3) { System.out.println(blog); } session.commit(); session.close(); }
- 可以看到,MyBatis都帮助我们成功的完成了SQL拼接,并没有出现任何错误
10.5 SQL片段
-
有些时候,在业务的处理过程中,可能某一部分SQL语句使用比较频繁,所以我们可以将其抽取出来,单独成为一个片段。然后在需要的使用通过
<include>
标签导入即可 -
提取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> <!-- 引用 sql 片段,如果refid 指定的不在本文件中,那么需要在前面加上 namespace--> <include refid="if-title-author" /> <!-- 在这里还可以引用其他的 sql 片段 --> </where> </select>
10.6 foreach
- 动态 SQL 的另一个常见使用场景是对集合进行遍历(尤其是在构建 IN 条件语句的时候)。比如:
<select id="selectPostIn" resultType="domain.blog.Post">
SELECT *
FROM POST P
WHERE ID in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>
</select>
-
foreach 元素的功能非常强大,
- 它允许你指定一个集合,声明可以在元素体内使用的集合项(item)和索引(index)变量。
- 它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符。这个元素也不会错误地添加多余的分隔符,
-
提示
- 你可以将任何可迭代对象(如 List、Set 等)、Map 对象或者数组对象作为集合参数传递给 foreach。
- 当使用可迭代对象或者数组时,index 是当前迭代的序号,item 的值是本次迭代获取到的元素。
- 当使用 Map 对象(或者 Map.Entry 对象的集合)时,index 是键,item 是值。