Mybatis系列(五)动态SQL

时间:2021-04-16 05:09:49

Mybatis系列之动态SQL

引言

凡是写过数据库程序的朋友,都能体会到根据不同条件拼接SQL语句的痛苦,在这中间也会犯各种各样的错误,where子句中多个括号,少个空格,set语句中缺个逗号什么的,各种累觉不爱。使用Mybatis自带的动态SQL处理机制,可以把我们从这种痛苦中解救出来。今天我们就来讲讲Mybatis的动态SQL。(本文结尾处有彩蛋噢 :))))

 

通常使用动态 SQL 不可能是独立的一部分,MyBatis 当然使用一种强大的动态 SQL 语言来改进这种情形,这种语言可以被用在任意的 SQL 映射语句中。

Mybatis提供了几下几种动态SQL元素:

  • if
  • choose
  • foreach
  • where / set
  • trim

IF元素

在讲IF元素之前,我们先回忆一下在不使用Mybatis的情况下,我们是如何拼接WHERE子句的。

通常的做法,是在查询语句后面跟上一个多余的WHERE条件,就像下面这样

select * from table_name where (1=1)

之所以这样写,主要是为了后面较方便地把查询条件直接使用 and XXX 或是 or XXX的形式追加在查询语句之后,而无需判断查询语句中是否有WHERE关键字。在拼接的过程中还中注意空格的使用。

有了Mybatis之后,情况就大有不同了,我们再也不是操心SQL语句拼接的问题了。在之前的文章《Mybatis系列之简单示例》中,曾经出现过IF元素。如:

<select id="queryByName" parameterType="User" resultType="User">
    SELECT <include refid="columns"></include>
    FROM sys_user
    WHERE is_valid = 1
    <if test="userName != null">AND user_name like '%' #{userName} '%'</if>
</select>
这条语句查询用户表,如果没有传入userName参数,那么就查询出所有is_Valid=1的记录;反之,如果传入了userName参数,则查询is_Valid=1且userName包含传入值的记录。

 

这里有一点需要提醒各位朋友,IF元素,如果不是配合WHERE元素一起使用,请确保她封装的只是WHERE条件子句的一部分,而非全部。这句话怎么理解呢?我们来看个例子,仍用上面的查询语句,只是略做修改:

<select id="queryByName" parameterType="User" resultType="User">
    SELECT <include refid="columns"></include>
    FROM sys_user
    WHERE 
    <if test="userName != null">user_name like '%' #{userName} '%'</if>
</select>
这条映射语句是存在陷阱的。不知道大家有没有看出来?就是当IF条件不成立的时候,这条SQL语句就会被转换为
SELECT user_name, user_password, nick_name, email, user_type_id, is_valid, created_time FROM sys_user WHERE
这条查询语句在数据库中是无法执行的!!

 

那么我们要如何修改才能够避免这种情况发生呢?有两种方法,一个就是像第一段查询语句那样,让IF元素只是WHERE条件子句的一部分;另一个就是使用Mybaits提供的WHERE元素。这个后面会讲到。

 

CHOOSE元素

有些时候,我们并不是想用到所有条件语句,而是只从其中选择一个。针对这种情况Mybatis提供了类似Java中的switch语句的choose元素。

还是用查询用户为例,如果查询条件提供了userName就按userName查询,如果提供了nickName就按nickName查询,如果两者都没有提供就返回所有有效的用户(或是符合其他条件的用户,为简单起见,这里就返回所有有效用户)。

<select id="queryByName" parameterType="User" resultType="User">
    SELECT <include refid="columns"></include>
    FROM sys_user
    WHERE user_type_id = 1
    <choose>
        <when test="userName != null">user_name like '%' #{userName} '%'</when>
        <when test="nickName != null">nick_name like '%' #{nickName} '%'</when>
        <otherwise>is_valid = 1</otherwise>
    </choose>
</select>

 

既然已经把IF,CHOOSE元素讲了,这里顺道也就把FOREACH元素提前讲一讲,就像是讲编程语言的时候,条件语句和循环语句总是放在相邻的章节一样。

 

FOREACH元素

这个元素的使用场景是在需要对一个集合进行遍历的时候使用,如批量删除、批量插入等语句。

下面就以批量删除为例,来讲一个这个元素的使用。

<!-- 根据传入的Id值列表,删除多条记录 -->
<delete id="deleteBatch" parameterType="java.util.List">
    DELETE FROM sys_user WHERE user_id in
    <foreach collection="list" item="item" index="index" open="(" close=")" separator=",">
        #{item}
    </foreach>
</delete>

我们知道Mybatis进行SQL映射时,传入参数只能有一个,如果想传入多个参数,只能使用Java的List或是Array进行封装后再传入。上面的语句就是将要删除的多条记录的Id值放在了List对象中传入。

foreach 元素的功能是非常强大的,它允许你指定一个集合,声明可以用在元素体内的集合项和索引变量。它也允许你指定开闭匹配的字符串(上例中的open和close属性)以及在迭代中间放置分隔符(separator属性)。这个元素是很智能的,因此它不会偶然地附加多余的分隔符。

我们可以将一个 List 实例或者数组作为参数对象传给 MyBatis,当我们这么做的时候,MyBatis 会自动将它包装在一个 Map 中并以名称为键。List 实例将会以“list”作为键,而数组实例的键将是“array”。

 

WHERE / SET元素

在前面讲解IF元素时,我们已经提到了WHERE元素,使用这个元素可以避免在查询语句中出现只有WHERE关键字而没有作何查询条件的情况出现。

<select id="queryByName" parameterType="User" resultType="User">
    SELECT  <include refid="columns"></include>
    FROM sys_user
    <where>
        <if test="userName != null">user_name LIKE '%' #{userName} '%'</if>
        <if test="nickName != null"> OR nick_name LIKE '%' #{userName} '%'</if>
    </where>
</select>

Mybatis会判断只有在WHERE元素中至少有一个条件成立时,才会在查询语句中添加WHERE关键字。

细心的朋友可能会有疑问了,在上述SQL语句中,如果第一个条件不成立,而第二个条件成立时,是不是会在WHERE语句中多个OR关键字呀?可以告诉大家,完全不心担心这个问题,Mybatis早已考虑到了,她会将多余的AND或是OR关键字自动剔除掉(所谓多余,紧跟在WHERE关键字后的第一个AND或是OR)。

 

SET元素和WHERE元素类似,只是她是使用在数据更新语句中而已。

<!-- 更新用户信息,并写回到数据表中 -->
<update id="udpateUser" parameterType="User">
    UPDATE sys_user
    <set>
        <if test="userName != null">user_name = #{userName},</if>
        <if test="userPassword != null">user_password = #{userPassword},</if>
        <if test="nickName != null">nick_name = #{nickName},</if>
        <if test="userTypeId != null">user_type_id = #{userTypeId},</if>
        <if test="isValid != null">is_valid = #{isValid}</if>
    </set>
    WHERE user_id = #{userId}
</update>

 

彩蛋 

如果恰好您使用的是Mysql数据库,那么这个彩蛋对你而言一定是会让你惊喜的。

 

批量插入,这个功能我们大部分朋友在其编写的程序中会有用到,比如提交一张采购订单时,我们会对订单行行遍历,一笔笔地写入到数据库中。这样操作是可以完成作务的,只是效率上不是很好,因为每次遍历都需要单独和数据库做一次交互,而数据库的IO操作是很耗性能的。有没有什么办法可以一次性的将所有订单行写入到数据库中呢?这里介绍一种在Mysql数据库中的实现方法。

使用Mysql数据库的朋友都知道,Mysql的insert语句支持一次写多行数据的,我们就是利用这个语句,实现一次性写入多条数据。

我们以批量增加用户为例,将数据封装在一个List对象中,这样在映射文件里,我们就可以这样写:

<!-- 批量新增 -->
<insert id="insertBatch" parameterType="java.util.List">
    INSERT INTO sys_user(<include refid="columns"></include>) VALUES
    <foreach collection="list" item="u" separator=",">
        (#{u.userName}, #{u.userPassword}, #{u.nickName}, #{u.userTypeId}, #{u.isValid}, #{u.createdTime})
    </foreach>
</insert>

需要注意的是,不要忘记为foreach元素指定separator属性;另外,在循环内部引用对象属性时,一定要加上前缀的。


 

附录

Mybatis系列(一)入门

Mybatis系列(二)配置

Mybatis系列(三)简单示例

Mybatis系列(四)映射文件

Mybatis系列(六)接口式编程

Mybatis系列(七)关联映射

Mybatis系列(八)集合映射

Mybatis系列(九)Spring & Mybatis整合