Mybatis系列第10篇：动态SQL，这么多种你都会？

Mybatis系列目标：从入门开始开始掌握一个高级开发所需要的Mybatis技能。

这是mybatis系列第10篇，源码位于文章尾部！

mybatis中一个比较强大的功能就是动态sql，记得在刚开始工作那会，当时使用jdbc开发系统，在java代码中搞了很多判断去拼接sql，代码看起来比较乱，也不方便维护和扩展。

mybatis在处理sql的拼接这块简直是我们的福音，基本上sql拼接的所有的痛点，mybatis都帮我们解决了。下面我们来学一下mybatis中各种动态sql的用法。

案例sql脚本 

  1. DROP DATABASE IF EXISTS `javacode2018`;
  2. CREATE DATABASE `javacode2018`;
  3.  
  4. USE `javacode2018`;
  5.  
  6. DROP TABLE IF EXISTS t_user;
  7. CREATE TABLE t_user(
  8.   id int AUTO_INCREMENT PRIMARY KEY COMMENT '用户id',
  9.   name VARCHAR(32) NOT NULL DEFAULT '' COMMENT '用户名',
  10.   age SMALLINT NOT NULL DEFAULT 1 COMMENT '年龄'
  11. ) COMMENT '用户表';
  12. INSERT INTO t_user VALUES (1,'路人甲Java',30),(2,'张学友',50),(3,'刘德华',50);

if元素

相当于java中的if判断，语法：

  1. <if test="判断条件">
  2. 需要追加的sql
  3. </if>

test的值为一个判断表达式，写法上采用OGNL表达式的方式，OGNL在struts2中用的比较多，本文暂时对ognl不做详细介绍，有兴趣的可以去查一下相关资料。

当test成立的时候，if体内部的sql会被拼接上。

如：

  1. <select id="getList1" resultType="com.javacode2018.chat05.demo8.model.UserModel" parameterType="map">
  2.     SELECT id,name,age FROM t_user
  3.     WHERE 1 = 1
  4.     <if test="id!=null">
  5.         AND id = #{id}
  6.     </if>
  7.     <if test="name!=null and name.toString()!=''">
  8.         AND name = #{name}
  9.     </if>
  10.     <if test="age!=null">
  11.         AND age = #{age}
  12.     </if>
  13. </select>

上面查询用户列表，参数为一个map，当map中id不为空的时候，将其作为条件查询，如果name不为空，将name也作为条件，如果age不为空，将age也作为条件进行查询

当只传入id的时候，sql如下：

SELECT id,name,age FROM t_user WHERE 1 = 1 AND id = ? 

当3个参数都传了，sql如下：

SELECT id,name,age FROM t_user WHERE 1 = 1 AND id = ? AND name = ? AND age = ?

上面这种写法相对于java代码看起来是不是清爽了很多，也更方便维护，大家注意一下sql中有个WHERE 1=1，如果没有这个，上面单通过if元素就不好实现了，mybatis也有解决方案，稍后会说明。

choose/when/otherwise元素

这个相当于java中的if..else if..else，语法：

  1. <choose>
  2.     <when test="条件1">
  3.         满足条件1追加的sql
  4.     </when>
  5.     <when test="条件2">
  6.         满足条件2追加的sql
  7.     </when>
  8.     <when test="条件n">
  9.         满足条件n追加的sql
  10.     </when>
  11.     <otherwise>
  12.         都不满足追加的sql
  13.     </otherwise>
  14. </choose>

choose内部的条件满足一个，choose内部的sql拼接就会结束。

otherwise属于可选的，当所有条件都不满足的时候，otherwise将起效。

如：

传入id、name、age作为条件，按顺序进行判断，如果id不为空，将id作为条件，忽略其他条件，如果id为空，会判断name是否为空，name不为空将name作为条件，如果name为空，再看看age是否为空，如果age不为空，将age作为条件。

  1. <select id="getList2" resultType="com.javacode2018.chat05.demo8.model.UserModel" parameterType="map">
  2.     SELECT id,name,age FROM t_user
  3.     WHERE 1 = 1
  4.     <choose>
  5.         <when test="id!=null">
  6.             AND id = #{id}
  7.         </when>
  8.         <when test="name!=null and name.toString()!=''">
  9.             AND name = #{name}
  10.         </when>
  11.         <when test="age!=null">
  12.             AND age = #{age}
  13.         </when>
  14.     </choose>
  15. </select>

如果id、name、age都传了，sql如下：

SELECT id,name,age FROM t_user WHERE 1 = 1 AND id = ? 

如果值传递了name、age，sql如下：

SELECT id,name,age FROM t_user WHERE 1 = 1 AND name = ? 

name判断在age前面，所以name条件先匹配上了。

where元素

上面2个案例的sql中都有where 1=1这部分代码，虽然可以解决问题，但是看起来不美观，如果将where 1=1中1=1这部分干掉，上面的两个案例都会出问题，where后面会多一个AND符号，mybatis中已经考虑到这种问题了，属于通用性的问题，mybatis中通过where 元素来解决，当使用where元素的时候，mybatis会将where内部拼接的sql进行处理，会将这部分sql前面的AND 或者 OR给去掉，并在前面追加一个where，我们使用where元素来对上面的案例1进行改造，如下：

  1. <select id="getList1" resultType="com.javacode2018.chat05.demo8.model.UserModel" parameterType="map">
  2.     SELECT id,name,age FROM t_user
  3.     <where>
  4.         <if test="id!=null">
  5.             AND id = #{id}
  6.         </if>
  7.         <if test="name!=null and name.toString()!=''">
  8.             AND name = #{name}
  9.         </if>
  10.         <if test="age!=null">
  11.             AND age = #{age}
  12.         </if>
  13.     </where>
  14. </select>

where 1=1被替换成了where 元素。

当传入id、name的时候，where内部的sql会变成这样：

AND id = ? AND name = ?

mybatis会对上面这个sql进行处理，将前面的AND给去掉，并在前面追加一个where，变成了下面这样

where id = ? AND name = ?

案例2也用where改造一下，变成了下面这样：

  1. <select id="getList2" resultType="com.javacode2018.chat05.demo8.model.UserModel" parameterType="map">
  2.     SELECT id,name,age FROM t_user
  3.     <where>
  4.         <choose>
  5.             <when test="id!=null">
  6.                 AND id = #{id}
  7.             </when>
  8.             <when test="name!=null and name.toString()!=''">
  9.                 AND name = #{name}
  10.             </when>
  11.             <when test="age!=null">
  12.                 AND age = #{age}
  13.             </when>
  14.         </choose>
  15.     </where>
  16. </select>

这下看起来是不是舒服很多了。

set元素

现在我们想通过用户id更新用户信息，参数为UserModel对象，对象中的属性如果不为空，就进行更新，我们可以这么写：

  1. <update id="update1" parameterType="com.javacode2018.chat05.demo8.model.UserModel">
  2.     UPDATE t_user SET
  3.     <if test="name!=null">
  4.         name = #{name},
  5.     </if>
  6.     <if test="age!=null">
  7.         age = #{age},
  8.     </if>
  9.     <where>
  10.         <if test="id!=null">
  11.             AND id = #{id}
  12.         </if>
  13.     </where>
  14. </update>

我们来看一下，当所有属性都传值了，sql变成了下面这样：

UPDATE t_user SET name = ?, age = ?, where id = ?

上面这个sql是有问题的，where前面多了一个逗号，得想办法将这个逗号去掉，这个逗号属于最后一个需要更新的字段后面的逗号，属于多余的，mybatis中提供了set元素来解决这个问题，将上面的代码改成下面这样：

  1. <update id="update1" parameterType="com.javacode2018.chat05.demo8.model.UserModel">
  2.     UPDATE t_user
  3.     <set>
  4.         <if test="name!=null">
  5.             name = #{name},
  6.         </if>
  7.         <if test="age!=null">
  8.             age = #{age},
  9.         </if>
  10.     </set>
  11.     <where>
  12.         <if test="id!=null">
  13.             AND id = #{id}
  14.         </if>
  15.     </where>
  16. </update>

我们将sql中的set去掉了，加了个set元素，set元素会对其内部拼接的sql进行处理，会将这部分sql前后的逗号给去掉并在前面加上set。

当传入id和age的时候，生成的sql：

UPDATE t_user SET age = ? where id = ?

trim元素

这个元素的功能比较强大，先看一下他的语法：

  1. <trim prefix="" prefixOverrides="" suffix="" suffixOverrides="">
  2. </trim>

trim元素内部可以包含各种动态sql，如where、chose、sql等各种元素，使用trim包含的元素，mybatis处理过程：

  1. 先对trim内部的sql进行拼接，比如这部分sql叫做sql1

  2. 将sql1字符串前面的部分中包含trim的prefixOverrides指定的部分给去掉，得到sql2

  3. 将sql2字符串后面的部分中包含trim的suffixOverrides指定的部分给去掉，得到sql3

  4. 在sql3前面追加trim中prefix指定的值，得到sql4

  5. 在sql4后面追加trim中suffix指定的值，得到最终需要拼接的sql5

了解了这个过程之后，说明可以通过trim来代替where和set，我们使用trim来改造一下案例1，如下：

  1. <select id="getList1" resultType="com.javacode2018.chat05.demo8.model.UserModel" parameterType="map">
  2.     SELECT id,name,age FROM t_user
  3.     <trim prefix="where" prefixOverrides="and|or">
  4.         <if test="id!=null">
  5.             AND id = #{id}
  6.         </if>
  7.         <if test="name!=null and name.toString()!=''">
  8.             AND name = #{name}
  9.         </if>
  10.         <if test="age!=null">
  11.             AND age = #{age}
  12.         </if>
  13.     </trim>
  14. </select>

注意上面的prefixOverrides的值的写法，如果有多个需要覆盖的之间用|进行分割，suffixOverrides写法和prefixOverrides的写法类似。

我们在用trim来改造一下上面的update中的，如下：

  1. <update id="update1" parameterType="com.javacode2018.chat05.demo8.model.UserModel">
  2.     UPDATE t_user
  3.     <trim prefix="SET" prefixOverrides="," suffixOverrides=",">
  4.         <if test="name!=null">
  5.             name = #{name},
  6.         </if>
  7.         <if test="age!=null">
  8.             age = #{age},
  9.         </if>
  10.     </trim>
  11.     <where>
  12.         <if test="id!=null">
  13.             AND id = #{id}
  14.         </if>
  15.     </where>
  16. </update>

上面的prefixOverrides和suffixOverrides都设置的是逗号，表示trim内部的sql前后的逗号会被去掉，最后会在前面拼接一个prefix指定的set。

大家有兴趣的可以去看一下trim的java实现，代码下面这个类中：

org.apache.ibatis.scripting.xmltags.TrimSqlNode

实际上where和set的实现是继承了TrimSqlNode，where对应的java代码：

  1. public class WhereSqlNode extends TrimSqlNode {
  2.  
  3.   private static List<String> prefixList = Arrays.asList("AND ","OR ","AND\n", "OR\n", "AND\r", "OR\r", "AND\t", "OR\t");
  4.  
  5.   public WhereSqlNode(Configuration configuration, SqlNode contents) {
  6.     super(configuration, contents, "WHERE", prefixList, null, null);
  7.   }
  8.  
  9. }

set对应的 java代码：

  1. public class SetSqlNode extends TrimSqlNode {
  2.  
  3.   private static final List<String> COMMA = Collections.singletonList(",");
  4.  
  5.   public SetSqlNode(Configuration configuration,SqlNode contents) {
  6.     super(configuration, contents, "SET", COMMA, null, COMMA);
  7.   }
  8.  
  9. }

最后都是依靠TrimSqlNode来实现的。

foreach元素

相当于java中的循环，可以用来遍历数组、集合、map等。

语法

  1. <foreach collection="需要遍历的集合" item="集合中当前元素" index="" open="" separator="每次遍历的分隔符" close="">
  2. 动态sql部分
  3. </foreach>

  • collection：可以是一个List、Set、Map或者数组

  • item：集合中的当前元素的引用

  • index：用来访问当前元素在集合中的位置

  • separator：各个元素之间的分隔符

  • open和close用来配置最后用什么前缀和后缀将foreach内部所有拼接的sql给包装起来。

案例：in多值查询

我们对案例1做个改造，map中支持放入用户的id列表（ArrayList），对应的key为idList，然后支持多个用户id查询，此时我们需要用in来查询，实现如下：

  1. <select id="getList1" resultType="com.javacode2018.chat05.demo8.model.UserModel" parameterType="map">
  2.     SELECT id,name,age FROM t_user
  3.     <where>
  4.         <if test="id!=null">
  5.             AND id = #{id}
  6.         </if>
  7.         <if test="name!=null and name.toString()!=''">
  8.             AND name = #{name}
  9.         </if>
  10.         <if test="age!=null">
  11.             AND age = #{age}
  12.         </if>
  13.         <if test="idList!=null and idList.size()>=1">
  14.             <foreach collection="idList" item="id" open="AND id in (" separator="," close=")">
  15.                 #{id}
  16.             </foreach>
  17.         </if>
  18.     </where>
  19. </select>

大家看一下上面idList那部分判断，判断这个参数不为空，并且size()大于1，表示这个集合不为空，然后会走if元素内部的foreach元素。

比如我们传递的idList对应的是[1,2]，最后产生的sql如下：

SELECT id,name,age FROM t_user WHERE id in ( ? , ? ) 

案例：批量插入

传入UserModel List集合，使用foreach实现批量插入，如下：

  1. <insert id="insertBatch" parameterType="list">
  2.     INSERT INTO t_user (id,name,age) VALUES
  3.     <foreach collection="collection" separator="," item="item">
  4.         (#{item.id}, #{item.name}, #{item.age})
  5.     </foreach>
  6. </insert>

测试用例

  1. com.javacode2018.chat05.demo8.Demo8Test#insertBatch
  2.  
  3. @Test
  4. public void insertBatch() throws IOException {
  5.     try (SqlSession sqlSession = this.sqlSessionFactory.openSession(true);) {
  6.         UserMapper mapper = sqlSession.getMapper(UserMapper.class);
  7.         List<UserModel> userModelList = new ArrayList<>();
  8.         for (int i = 0; i < 3; i++) {
  9.             userModelList.add(UserModel.builder().id(10 + i).name("mybatis-" + i).age(20 + i).build());
  10.         }
  11.         int count = mapper.insertBatch(userModelList);
  12.         log.info("{}", count);
  13.     }
  14. }

传入了3个用户信息，运行输出

  1. 39:52.241 [main] DEBUG c.j.c.d.m.UserMapper.insertBatch - ==>  Preparing: INSERT INTO t_user (id,name,age) VALUES (?, ?, ?) , (?, ?, ?) , (?, ?, ?) 
  2. 39:52.317 [main] DEBUG c.j.c.d.m.UserMapper.insertBatch - ==> Parameters: 10(Integer), mybatis-0(String), 20(Integer), 11(Integer), mybatis-1(String), 21(Integer), 12(Integer), mybatis-2(String), 22(Integer)
  3. 39:52.327 [main] DEBUG c.j.c.d.m.UserMapper.insertBatch - <==    Updates: 3
  4. 39:52.327 [main] INFO  c.j.chat05.demo8.Demo8Test - 3

sql/include元素

这两2个元素一般进行配合使用，可以实现代码重用的效果。

sql元素可以用来定义一段动态sql，语法如下：

  1. <sql id="sql片段id">
  2. 各种动态sql
  3. </sql>

其他地方需要使用的时候需要通过include关键字进行引入：

<include refid="需要引入的sql片段的id"/>

注意：refid值的写法，refid的值为mapper xml的namespace的值.sql的id，如果在同一个mapper中，namespace可以省略，直接写对应的sql的id就可以了，如：

  1. <include refid="findSql"/>
  2. <include refid="com.javacode2018.chat05.demo8.mapper.UserMapper.findSql"/>

来个案例

下面定义2个查询，他们的查询条件一样，最后将条件抽出来用sql元素定义了一个片段，然后进行共用。

  1. <sql id="findSql">
  2.     <where>
  3.         <if test="id!=null">
  4.             AND id = #{id}
  5.         </if>
  6.         <if test="name!=null and name.toString()!=''">
  7.             AND name = #{name}
  8.         </if>
  9.         <if test="age!=null">
  10.             AND age = #{age}
  11.         </if>
  12.         <if test="idList!=null and idList.size()>=1">
  13.             <foreach collection="idList" item="id" open="AND id in (" separator="," close=")">
  14.                 #{id}
  15.             </foreach>
  16.         </if>
  17.     </where>
  18. </sql>
  19.  
  20. <select id="getList1" resultType="com.javacode2018.chat05.demo8.model.UserModel" parameterType="map">
  21.     SELECT id,name,age FROM t_user
  22.     <include refid="com.javacode2018.chat05.demo8.mapper.UserMapper.findSql" />
  23. </select>
  24.  
  25. <select id="getList1Count" resultType="com.javacode2018.chat05.demo8.model.UserModel" parameterType="map">
  26.     SELECT count(*) FROM t_user
  27.     <include refid="findSql" />
  28. </select>

bind元素

bind元素允许我们通过ognl表达式在上下文中自定义一个变量，最后在动态sql中可以使用这个变量。

语法

<bind name="变量名称" value="ognl表达式">

案例

对sql、include中的案例进行扩展，添加一个按照用户名模糊查询，用户名在map中对应的key为likeName，主要修改上面sql片段部分，在sql中加入下面部分：

  1. <if test="likeName!=null and likeName.trim()!=''">
  2.   <bind name="nameLike" value="'%'+likeName.trim()+'%'" />
  3.   AND name like #{nameLike}
  4. </if>

先判断传入的参数likeName是否不为空字符串，然后使用bind元素创建了一个变量nameLike，值为'%'+likeName.trim()+'%'。

对应的测试用例：

  1. com.javacode2018.chat05.demo8.Demo8Test#getModelList
  2. @Test
  3. public void getModelList() throws IOException {
  4.     try (SqlSession sqlSession = this.sqlSessionFactory.openSession(true);) {
  5.         UserMapper mapper = sqlSession.getMapper(UserMapper.class);
  6.         Map<String, Object> paramMap = new HashMap<>();
  7.         paramMap.put("likeName","java");
  8.         List<UserModel> userModelList = mapper.getList1(paramMap);
  9.         log.info("{}", userModelList);
  10.     }
  11. }

运行输出：

  1. 06:25.633 [main] DEBUG c.j.c.d.mapper.UserMapper.getList1 - ==>  Preparing: SELECT id,name,age FROM t_user WHERE name like ? 
  2. 06:25.671 [main] DEBUG c.j.c.d.mapper.UserMapper.getList1 - ==> Parameters: %java%(String)
  3. 06:25.690 [main] DEBUG c.j.c.d.mapper.UserMapper.getList1 - <==      Total: 1
  4. 06:25.691 [main] INFO  c.j.chat05.demo8.Demo8Test - [UserModel(id=1, name=路人甲Java, age=31)]

注意输出中的第二部分，参数的值为%java%。

#和$

#和$一般都是结合变量来使用，如：#{}、${}这种来进行使用。

#{}:为参数占位符？，即sql预编译，相当于使用jdbc中的PreparedStatement中的sql占位符，可以防止sql注入

${}:为字符串替换， 即字符串拼接，不能访问sql注入。

#{}的用法上面已经有很多案例了，此处我们来一个${}的案例。

下面通过orderSql变量传入任意的排序sql，如下：

  1. <select id="getList1" resultType="com.javacode2018.chat05.demo8.model.UserModel" parameterType="map">
  2.     SELECT id,name,age FROM t_user
  3.     <if test="orderSql">
  4.         ${orderSql}
  5.     </if>
  6. </select>

传入值：

orderSql = "order by id asc,age desc"

最后运行产生的sql如下：

  1. 20:32.138 [main] DEBUG c.j.c.d.mapper.UserMapper.getList1 - ==>  Preparing: SELECT id,name,age FROM t_user order by id asc,age desc 
  2. 20:32.173 [main] DEBUG c.j.c.d.mapper.UserMapper.getList1 - ==> Parameters: 
  3. 20:32.196 [main] DEBUG c.j.c.d.mapper.UserMapper.getList1 - <==      Total: 6
  4. 20:32.197 [main] INFO  c.j.chat05.demo8.Demo8Test - [UserModel(id=1, name=路人甲Java, age=31), UserModel(id=2, name=张学友, age=50), UserModel(id=3, name=刘德华, age=50), UserModel(id=10, name=mybatis-0, age=20), UserModel(id=11, name=mybatis-1, age=21), UserModel(id=12, name=mybatis-2, age=22)]

mybatis会对$包含部分进行sql替换。

案例代码 

  1. 链接：https://pan.baidu.com/s/1vt-MAX3oJOu9gyxZAhKkbg 
  2. 提取码：i8op

MyBatis系列

  1. MyBatis系列第1篇：MyBatis未出世之前我们那些痛苦的经历

  2. MyBatis系列第2篇：入门篇，带你感受一下mybatis独特的魅力！

  3. MyBatis系列第3篇：Mybatis使用详解（1）

  4. MyBatis系列第4篇：Mybatis使用详解（2）

  5. Mybatis系列第5篇：Mapper接口多种方式传参详解、原理、源码解析

  6. Mybatis系列第6篇：恕我直言，mybatis增删改你未必玩得转!

  7. Mybatis系列第7篇：各种查询详解

  8. Mybatis系列第8篇：自动映射，使用需谨慎！

  9. Mybatis系列第9篇：延迟加载、鉴别器、继承怎么玩？

更多好文章

  1. Java高并发系列（共34篇）

  2. MySql高手系列（共27篇）

  3. Maven高手系列（共10篇）

  4. 聊聊db和缓存一致性常见的实现方式

  5. 接口幂等性这么重要，它是什么？怎么实现？

感谢大家的阅读，也欢迎您把这篇文章分享给更多的朋友一起阅读！谢谢！

路人甲java

▲长按图片识别二维码关注

路人甲Java：工作10年的前阿里P7分享Java、算法、数据库方面的技术干货！坚信用技术改变命运，让家人过上更体面的生活！来源：https://itsoku.blog.csdn.net/article/details/103900529

posted @ 2022-04-23 03:16  程序员小明1024  阅读(537)  评论(0)  编辑  收藏  举报