Mybatis (四) Mybatis动态SQL

阿里云国内75折回扣微信号：monov8

阿里云国际，腾讯云国际，低至75折。AWS 93折免费开户实名账号代冲值优惠多多微信号：monov8 飞机：@monov6

Mybatis动态SQL

☞ 博客导航，`带你有序的阅读和学习！`

文章目录

概念
if 标签
choose 标签
trim 标签
foreach标签
bind 标签

概念

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标签，如下：

<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接口方法：

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

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

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

对应的取值xml：

<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

select * from tb_user where password = ?

username 不为null，password为null

select * from tb_user where username = ?

username 不为null，password不为null

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

username 为null，password为null

select * from tb_user

注意：

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

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

if标签里，判断相等或不相等

单个字符需要加.toString()，如：auditidentified =='2'.toString()

if标签里，判断相等或不相等

多个字符不需要加toString()，如：auditidentified !='2155'

choose 标签

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

语句模板：

<choose>
    <when test = "a">A</when>
    <when test = "b">B</when>
    <otherwise>C</otherwise>
</choose>

语句解释：

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

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

示例语句如下：

<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接口方法：

/*查询，如果有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,如下：

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

我们看一下下面这个sql:

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

select * from tb_user where and password = ?

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

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

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

<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 (四) Mybatis动态SQL_List

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

<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代码需要如何操作：

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标签：

<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实现：

<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接口的方法：

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

bind 标签

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

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