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

• 新建一个数据库表：blog
  

字段：id，title，author，create_time，views

1. CREATE TABLE `blog` (
2. `id` varchar(50) NOT NULL COMMENT '博客id',
3. `title` varchar(100) NOT NULL COMMENT '博客标题',
4. `author` varchar(30) NOT NULL COMMENT '博客作者',
5. `create_time` datetime NOT NULL COMMENT '创建时间',
6. `views` int(30) NOT NULL COMMENT '浏览量'
7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

复制代码

• 创建Mybatis基础工程
  
• IDutil工具类
  
• 实体类编写 【留意set方法作用】
  

留意：Date类为java.util.Date，不是java.sql.Date

1. import java.util.Date;
3. public class Blog {
5.    private String id;
6.    private String title;
7.    private String author;
8.    private Date createTime;
9.    private int views;
10.    //set，get....
11. }

复制代码

• 编写Mapper接口及xml文件
  

1. public interface BlogMapper {
2. }

复制代码

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

复制代码

• mybatis核心设置文件，下划线驼峰自动转换
  

1. <settings>
2.    <setting name="mapUnderscoreToCamelCase" value="true"/>
3.    <setting name="logImpl" value="STDOUT_LOGGING"/>
4. </settings>
5. <!--注册Mapper.xml-->
6. <mappers>
7. <mapper resource="com/study/dao/BlogMapper.xml"/>
8. </mappers>

复制代码

• 插入初始数据
  

编写接口

1. //新增一个博客
2. int addBlog(Blog blog);

复制代码

映射文件

1. <insert id="addBlog" parameterType="blog">
2.   insert into blog (id, title, author, create_time, views)
3.   values (#{id},#{title},#{author},#{createTime},#{views});
4. </insert>

复制代码

初始化博客方法

1. import com.study.dao.BlogMapper;
2. import com.study.pojo.Blog;
3. import com.study.utils.IDUtil;
4. import com.study.utils.MybatisUtils;
5. import org.apache.ibatis.session.SqlSession;
6. import org.junit.Test;
8. import java.util.Date;
10. public class MyTest {
11.     @Test
12.     public void addInitBlog(){
13.         SqlSession session = MybatisUtils.getSqlSession();
14.         BlogMapper mapper = session.getMapper(BlogMapper.class);
16.         Blog blog = new Blog();
17.         blog.setId(IDUtil.genId());
18.         blog.setTitle("Mybatis如此简单");
19.         blog.setAuthor("狂神说");
20.         blog.setCreateTime(new Date());
21.         blog.setViews(9999);
23.         mapper.addBlog(blog);
25.         blog.setId(IDUtil.genId());
26.         blog.setTitle("Java如此简单");
27.         mapper.addBlog(blog);
29.         blog.setId(IDUtil.genId());
30.         blog.setTitle("Spring如此简单");
31.         mapper.addBlog(blog);
33.         blog.setId(IDUtil.genId());
34.         blog.setTitle("微服务如此简单");
35.         mapper.addBlog(blog);
37.         session.close();
38.     }
39. }

复制代码

初始化数据完毕！
3.if标签

使用动态 SQL 最常见情形是根据条件包罗 where 子句的一部分。
可以实现按不同列搜刮的功能（类似方法重载实现的效果）

• BlogMapper
  
  1. List<Blog> queryBlogIF(Map map);

  复制代码
• BlogMapper.xml
  
  1. <select id="queryBlogIF" parameterType="map">
  2.     select * from mybatis.blog where 1=1
  3.     <if test="title != null">
  4.         and title=#{title}
  5.     </if>
  6.     <if test="author != null">
  7.         and author=#{author}
  8.     </if>
  10. </select>

  复制代码
  这条语句提供了可选的查找文本功能。如果不传入 “title”和“author”，那么全部BLOG 都会返回；如果传入了 “title” 参数，那么就会对 “title” 一列进行查找并返回对应的 BLOG 效果（细心的读者大概会发现，“title” 的参数值需要包罗查找掩码或通配符字符）；如果传入了 “author” 参数，那么就会对 “author” 一列进行查找并返回对应的 BLOG 效果；如果都传，也返回相应的效果
  
• 测试
  
  1. @Test
  2. public void queryBlog(){
  3.     SqlSession sqlSession = MybatisUtils.getSqlSession();
  4.     BlogMapper mapper=sqlSession.getMapper(BlogMapper.class);
  6.     Map map=new HashMap();
  7.     map.put("title","Mybatis如此简单");
  8.     //        map.put("author","狂神说");
  10.     List<Blog> blogList = mapper.queryBlogIF(map);
  11.     for (Blog blog : blogList) {
  12.         System.out.println(blog);
  13.     }
  15.     sqlSession.close();
  16. }

  复制代码

4.trim，where，set

where

• where 元素只会在子元素返回任何内容的环境下才插入 “WHERE” 关键字。
  
• 若子句的开头为 “AND” 或 “OR”，where 元素也会视环境将它们去除或保存。
  

• BlogMapper.xml
  
  1. <select id="queryBlogIF" parameterType="map">
  2.     <!--        select * from mybatis.blog where 1=1-->
  3.     <!--        <if test="title != null">-->
  4.     <!--            and title=#{title}-->
  5.     <!--        </if>-->
  6.     <!--        <if test="author != null">-->
  7.     <!--            and author=#{author}-->
  8.     <!--        </if>-->
  10.     <!--为什么需要1=1-->
  11.     <!--如果没有匹配的条件会怎么样？最终这条 SQL 会变成这样：SELECT * FROM BLOG WHERE 这会导致查询失败-->
  12.     <!--如果匹配的只是第二个条件又会怎样？这条 SQL 会是这样:SELECT * FROM BLOG WHERE AND title = ‘someTitle’ 这个查询也会失败。-->
  13.     select * from mybatis.blog where
  14.     <!--        <if test="title != null">-->
  15.     <!--            title=#{title}-->
  16.     <!--        </if>-->
  17.     <!--        <if test="author != null">-->
  18.     <!--            and author=#{author}-->
  19.     <!--        </if>-->
  21.     <!--如何不使用where 1=1-->
  22.     select * from mybatis.blog
  23.     <where>
  24.         <if test="title != null">
  25.             title=#{title}
  26.         </if>
  27.         <if test="author != null">
  28.             and author=#{author}
  29.         </if>
  30.     </where>
  31. </select>

  复制代码
• 测试代码同上
  

set

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

• BlogMapper
  
  1. int updateBlog(Map map);

  复制代码
• BlogMapper.xml
  
  1. <update id="updateBlog" parameterType="map" >
  2.     update blog
  3.     <set>
  4.         <if test="title != null">
  5.             title=#{title},
  6.         </if>
  7.         <if test="author != null">
  8.             author=#{author}
  9.         </if>
  10.     </set>
  11.     where id={id}
  12. </update>

  复制代码
• 测试
  
  1. @Test
  2. public void updateBlog(){
  3.     SqlSession sqlSession = MybatisUtils.getSqlSession();
  4.     BlogMapper mapper=sqlSession.getMapper(BlogMapper.class);
  6.     Map map=new HashMap();
  7.     map.put("title","Mybatis如此简单2");
  8.     map.put("author","狂神说");
  9.     map.put("id","5482dbf65d264012833e78b74e9fd95b");
  11.     mapper.updateBlog(map);
  13.     sqlSession.close();
  14. }

  复制代码

只传title，运行正常
只传author，运行正常
传title和author，运行正常
不传titl和author，报错
trim

1. <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 元素为：

1. <trim prefix="WHERE" prefixOverrides="AND |OR ">
2.   ...
3. </trim>

复制代码

prefixOverrides 属性会忽略通过管道符分隔的文本序列（留意此例中的空格是必要的）。上述例子会移除全部 prefixOverrides 属性中指定的内容，而且插入 prefix 属性中指定的内容。
你可以通过使用trim元素来到达中同样的效果：

1. <trim prefix="SET" suffixOverrides=",">
2.   ...
3. </trim>

复制代码

留意，我们覆盖了后缀值设置，而且自界说了前缀值。
choose、when、otherwise

类似与switch…case…default

• BlogMapper
  
  1. List<Blog> queryBlogChoose(Map map);

  复制代码
• BlogMapper.xml
  
  1. <select id="queryBlogChoose" parameterType="map" resultType="blog">
  2.     select * from mybatis.blog
  3.     <choose>
  4.         <when test="title != null">
  5.             title=#{title}
  6.         </when>
  7.         <when test="author != null">
  8.             author=#{author}
  9.         </when>
  10.         <otherwise>
  11.             and views=#{views}
  12.         </otherwise>
  13.     </choose>
  14. </select>

  复制代码
• 测试
  
  1. @Test
  2. public void queryBlogChoose(){
  3.     SqlSession sqlSession = MybatisUtils.getSqlSession();
  4.     BlogMapper mapper=sqlSession.getMapper(BlogMapper.class);
  6.     Map map=new HashMap();
  7.     //        map.put("title","Mybatis如此简单");
  8.     //        map.put("author","狂神说");
  9.     map.put("views",9999);
  11.     List<Blog> blogList = mapper.queryBlogChoose(map);
  12.     for (Blog blog : blogList) {
  13.         System.out.println(blog);
  14.     }
  16.     sqlSession.close();
  17. }

  复制代码
  不传参数时，正常运行
  传title和author时，正常运行
  传author时，正常运行
  

foreach

将blog表中的id改为1，2，3，4

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

  复制代码
• BlogMapper.xml
  
  1. <!-- select * from blog where 1=1 and (id=1 or id=2 or id=3)-->
  2. <select id="queryBlogForeach" parameterType="map" resultType="blog">
  3.     select * from blog
  4.     <where>
  5.         <!--我们现在传递一个万能的map，map中存在一个集合-->
  6.         <foreach collection="ids" item="id" open="and (" close=")" separator="or">
  7.             id=#{id}
  8.         </foreach>
  9.     </where>
  10. </select>

  复制代码
• 测试
  
  1. @Test
  2. public void queryBlogForeach(){
  3.     SqlSession sqlSession = MybatisUtils.getSqlSession();
  4.     BlogMapper mapper=sqlSession.getMapper(BlogMapper.class);
  6.     Map map=new HashMap();
  7.     ArrayList<Integer> ids = new ArrayList<>();
  8.     ids.add(1);
  9.     ids.add(2);
  10.     ids.add(3);
  11.     map.put("ids",ids);
  13.     mapper.queryBlogForeach(map);
  15.     sqlSession.close();
  16. }

  复制代码

SQL片断

将需要重复编写的sql片断提取出来方便复用

• 使用sql标签抽取出重复sql片断：<sql id="sql片断id名">
  
  1.     <sql id="if-title-author">
  2.         <if test="title != null">
  3.             title=#{title},
  4.         </if>
  5.         <if test="author != null">
  6.             author=#{author}
  7.         </if>
  8.     </sql>

  复制代码
• 在需要使用的地方使用include标签引用即可：<include refid="sql片断id名">
  
  1. <select id="queryBlogIF" parameterType="map" resultType="com.study.pojo.Blog">
  2.     select * from mybatis.blog
  3.     <where>
  4.         <include refid="if-title-author"/>
  5.     </where>
  6. </select>

  复制代码

动态SQL就是在拼接SQL语句，我们只要保证SQL的精确性，按照SQL的语法格式，去排列组合就可以了。
建议：先在MYSQL中写出完整的SQL再对应的去修改成动态SQL实现
小结：着实动态 sql 语句的编写往往就是一个拼接的问题，为了保证拼接准确，我们最好起首要写原生的 sql 语句出来，然后在通过 mybatis 动态sql 对照着改，防止出错。多在实践中使用才是熟练把握它的本事。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。