SSM / mybatis
13 分钟 读完 (大约 2000 个字) 0次访问

四、Mybatis动态SQL

Mybatis动态SQL

概念

摘自官网：https://mybatis.org/mybatis-3/zh/dynamic-sql.html

​ MyBatis 的强大特性之一便是它的动态 SQL。如果你有使用 JDBC 或其它类似框架的经验，你就能体会到根据不同条件拼接 SQL 语句的痛苦。例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL 这一特性可以彻底摆脱这种痛苦。

​ 虽然在以前使用动态 SQL 并非一件易事，但正是 MyBatis 提供了可以被用在任意 SQL 映射语句中的强大的动态 SQL 语言得以改进这种情形。

​ 动态 SQL 元素和 JSTL 或基于类似 XML 的文本处理器相似。在 MyBatis 之前的版本中，有很多元素需要花时间了解。MyBatis 3 大大精简了元素种类，现在只需学习原来一半的元素便可。MyBatis 采用功能强大的基于 OGNL 的表达式来淘汰其它大部分元素。

动态sql语句标签主要包括下面这几种：

  • if
  • choose…when…otherwise
  • trim
  • foreach
  • bind

if 标签

当我们需要做一些判断的时候，可以使用到if标签，如下：

1
2
3
4
5
6
7
8
9
10
11
<select id="findUsersByUsernamePassword" resultType="user" >
  select * from tb_user
  <where>
    <if test="username != null">
      username = #{username}
    </if>
    <if test="password != null">
      and password = #{password}
    </if>
  </where>
</select>

所对应的Mapper接口方法：

1
2
/*根据username和password查询*/
List<User> findUsersByUsernamePassword(User user);

我们可以直接使用对象中的属性来取值。如果此时我们加了@Param("user")

1
2
/*根据username和password查询*/
List<User> findUsersByUsernamePassword(@Param("user") User user);

对应的取值xml：

1
2
3
4
5
6
7
8
9
10
11
<select id="findUsersByUsernamePassword" resultType="user" >
  select * from tb_user
  <where>
    <if test="user.username != null">
      username = #{user.username}
    </if>
    <if test="user.password != null">
      and password = #{user.password}
    </if>
  </where>
</select>

解释：上述示例中，sql拼接结果如下：

  • username 为null，password 不为null

    1
    select * from tb_user where password = ?

  • username 不为null，password为null

1
select * from tb_user where username = ?

  • username 不为null，password不为null

    1
    select * from tb_user where username = ? and password = ?

  • username 为null，password为null

1
select * from tb_user

注意：

1
2
3
<if test="id != null and userId != '' and username =='2'">
    refusalreason=#{remark,jdbcType=VARCHAR}
</if>

mybatis是用OGNL表达式来解析的，在OGNL的表达式中，’2’会被解析成字符，java是强类型的，char 和一个string 会导致不等，所以if标签中的sql不会被解析。

  • if标签里，判断相等或不相等
1
单个字符需要加.toString()，如：auditidentified =='2'.toString()
  • if标签里，判断相等或不相等
1
多个字符不需要加toString()，如：auditidentified !='2155'

choose 标签

​ 有时我们不希望用到所有的条件，比如用户有记录有id，user_id, username这几个属性，但是查询的时候希望有优先级，比如id存在的时候，只按id查询，如果id不存在，但是user_id存在，则按照user_id查询，如果前面两者都不存在，则按照username查询。

语句模板：

1
2
3
4
5
<choose>
  	<when test = "a">A</when>
  	<when test = "b">B</when>
  	<otherwise>C</otherwise>
</choose>

语句解释：

1
2
3
4
5
6
7
8
9
解释：（只会执行一个条件,也必定会有一个条件执行）
  这个语句相当Java中的 switch...case...default
  switch 	==> choose:定义语句范围
  case   	==> when  :满足当前条件 test 时执行
  default 	==> otherwise :当前面的所有when都不满足的时候，使用otherwise的语句

即：当表达式a计算结果为true时，则执行语句A, choose结束
    当表达式b计算结果为true时，则执行语句B, choose结束
    当前面的when都不满足时，执行C。choose结束。

示例语句如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<select id="findUsersByIdOrUserIdOrUsername" resultType="user">
    select * from tb_user
    <where>
        <choose>
            <when test="id != null">
             	 id = #{id}
            </when>
            <when test="userId != null">
            	  user_id = #{userId}
            </when>
            <otherwise>
            	  username = #{username}
            </otherwise>
        </choose>
    </where>
</select>

对应的Mapper接口方法：

1
2
3
4
5
/*查询，如果有id不为空，则直接按id查询，如果id为空，则按userId查询，否则按username查询*/
List<User> findUsersByIdOrUserIdOrUsername(
    @Param("id") Integer id,
    @Param("userId") String userId,
    @Param("username") String  username);

​ 注意的同学可以发现，这里与之前的传参方法不同。后面将会有一篇专门介绍参数传递问题。这里先不细说。

trim 标签

与trim相关的标签还有set和where标签，这是用来我们解决sql拼接的问题，之前自己手动凭借SQL的时候，需要非常注意 and，or和逗号问题，对应and|or，我们会在sql之前使用where 1 = 1,如下：

1
select * from tb_user where 1 = 1 and ...

我们看一下下面这个sql:

1
2
3
4
5
6
7
8
9
<select id="findUsersByUsernamePassword" resultType="user" >
  select * from tb_user where
    <if test="username != null">
      username = #{username}
    </if>
    <if test="password != null">
      and password = #{password}
    </if>
</select>

当我username为空，password不为空时，处理之后就会生成如下sql：

1
select * from tb_user where and password = ?

上面这个sql就是一个错误的sql，但是mybatis中的动态sql帮我们提供了方法，下面我们看一下trim标签：

1
2
3
4
5
6
7
<trim prefix="" prefixOverrides="" suffix="" suffixOverrides=""></trim>
<!-- 解释：
prefix: 前缀,即在当前使用trim标签的最开头处添加指定元素。
suffix: 后缀,即在当前使用trim标签的最结束处添加指定元素。
prefixOverrides: 在最前面将元素指定的数据移除掉。
suffixOverrides: 在最后面将元素指定的数据移除掉。
-->

所以我们使用trim标签来解决上面标签产生的问题，如下：

1
2
3
4
5
6
7
8
9
10
11
<select id="findUsersByUsernamePassword" resultType="user" >
  select * from tb_user
  <trim prefix="where" prefixOverrides="and |or">
    <if test="username != null">
      username = #{username}
    </if>
    <if test="password != null">
      and password = #{password}
    </if>
  </trim>
</select>

注意：and后面的空格是必要添加的(官网介绍)。但是我测试的空格没有影响，为了规避问题，建议加上空格。

mybatis鉴于上述使用环境非常常见，将其封装为where标签，以及set标签，如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
<select id="findUsersByUsernamePassword" resultType="user" >
  select * from tb_user
  <where>
    <if test="username != null">
      username = #{username}
    </if>
    <if test="password != null">
      and password = #{password}
    </if>
  </where>
</select>

<update id="updateUserByUser" >
  update tb_user
  <set>
    <if test="userId != null">
      user_id = #{userId},
    </if>
    <if test="username != null">
      username = #{username},
    </if>
    <if test="password != null">
      password = #{password},
    </if>
    <if test="email != null">
      email = #{email},
    </if>
    <if test="phone != null">
      phone = #{phone},
    </if>
    <if test="gender != null">
      gender = #{gender},
    </if>
    <if test="birthday != null">
      birthday = #{birthday},
    </if>
    <if test="status != null">
      status = #{status},
    </if>
    <if test="createTime != null">
      create_time = #{createTime},
    </if>
    <if test="createUser != null">
      create_user = #{createUser},
    </if>
    <if test="modifyUser != null">
      modify_user = #{modifyUser},
    </if>
    <if test="modifyTime != null">
      modify_time = #{modifyTime},
    </if>
  </set>
    where id = #{id}
</update>

foreach标签

​ 这个标签对我们来说从名字上，其实就知道是用来干啥的了。对，foreach标签就是用来循环迭代一些可迭代的元素，如，list，set，map，数组等。

试想以下，当我们需要根据多个id批量查询的时候，我们使用java代码需要如何操作：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
public void test() {
  String sql = " select * from tb_user where id in ";
  List<Integer> ids = new ArrayList<>();
  ids.add(1);
  ids.add(2);
  ids.add(3);
  ids.add(4);
  String idSql = "(";
  for (int i = 0; i < ids.size(); i++) {
    if (i == ids.size() - 1) {
      idSql += ids.get(i) +")";
    }else{
      idSql += ids.get(i) + ",";
    }
  }
  sql += idSql;
  System.out.println(sql);
}

//select * from tb_user where id in (1,2,3,4);

可以看出，拼接这个sql非常繁琐，但是Mybatis提供的foreach可以很简单的实现,在此之前，我们看一下foreach标签：

1
<foreach collection="" index="" item="" open="" separator="" close="" ></foreach>

注意：

你可以将任何可迭代对象 如 List、Map 对象或者数组对象传递给 foreach 作为集合参数。当使用可迭代对象或者数组时。

  • collection 属性的书写方式，List -> list/collection,Set->collection,[]数组 -> array,Map -> map.

  • index 是当前迭代的次数，item 的值是本次迭代获取的元素。

    当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值。

  • open表示拼接最前的元素
  • close表示拼接在最后的元素
  • separator表示遍历的各个元素之间的分隔符。

使用foreach实现：

1
2
3
4
5
6
7
8
9
<select id="findUsersByIds" resultType="user">
  select * from tb_user
  <where>
    id in
    <foreach collection="list" item="ite" open="(" separator="," close=")">
      #{ite}
    </foreach>
  </where>
</select>

对应的mapper接口的方法：

1
List<User> findUsersByIds(List<Integer> id);

bind 标签

bind 元素可以从 OGNL 表达式中创建一个变量并将其绑定到上下文：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<select id="findUserById" resultType="user">
  <bind name="id" value=" id + 1"/>
  select * from tb_user where id = #{id}
</select>


<!--
   ==>  Preparing: select * from tb_user where username like ?
   ==> Parameters: %xxx%(String)
-->
<select id="findUsersByLikeUsername" resultType="user">
  <bind name="name" value=" '%' +username+ '%' "/>
  select * from tb_user where username like #{name}
</select>

<!-- 模糊查询还可以这样 -->
<select id="findUserByQueryVo" parameterType="QueryVo"
	resultType="User">
	select * from tb_user where username like concat('%',#{user.username},'%')
</select>

bind 标签比较简单，如上所示，可以将字符串进行拼接，也可以对传递进来的参数进行处理。

源码地址：

https://gitee.com/ooyhao/JavaRepo_Public/tree/master/Mybatis

#
五、Mybatis参数传递
三、Mybatis映射文件

评论

关注我

链接

分类

标签云

JavaSE Redis feign gateway hessian idea mybatis mysql nacos rabbitmq ribbon sentinel spring spring4x springboot 新冠肺炎

最新文章

归档

标签

JavaSE 9
Redis 15
feign 3
gateway 5
hessian 1
idea 1
mybatis 9
mysql 1
nacos 10
rabbitmq 1
ribbon 1
sentinel 7
spring 1
spring4x 11
springboot 14
新冠肺炎 1
Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×