Mybatis06-动态SQL

动态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.环境搭建

1. 新建一个数据库表：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;

2. 创建Mybatis基础工程

3. IDutil工具类

4. 实体类编写 【注意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....
}

5. 编写Mapper接口及xml文件

public interface BlogMapper {
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.study.mapper.BlogMapper"></mapper>

6. 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>

7. 插入初始数据

编写接口

//新增一个博客
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 {@Testpublic 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 子句的一部分。

可以实现按不同列搜索的功能（类似方法重载实现的效果）

1. BlogMapper

   List<Blog> queryBlogIF(Map map);
   

2. 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 结果；如果都传，也返回相应的结果

3. 测试

   @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 元素也会视情况将它们去除或保留。

1. 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>
   

2. 测试代码同上

set

set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号（这些逗号是在使用条件语句给列赋值时引入的）。

1. BlogMapper

   int updateBlog(Map map);
   

2. 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>
   

3. 测试

   @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>

参数说明

1. prefix：给 trim 标签内 sql 语句加上前缀

2. suffix：给 trim 标签内 sql 语句加上后缀

3. prefixOverrides：去除多余的前缀内容，如：prefixOverrides=“OR”，去除 trim 标签内 sql 语句多余的前缀 “OR”

4. 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

1. BlogMapper

   List<Blog> queryBlogChoose(Map map);
   

2. 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>
   

3. 测试

   @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

1. BlogMapper

   //查询1，2，3号记录的博客
   List<Blog> queryBlogForeach(Map map);
   

2. 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>
   

3. 测试

   @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片段提取出来方便复用

1. 使用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>
   

2. 在需要使用的地方使用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 对照着改，防止出错。多在实践中使用才是熟练掌握它的技巧。