mybatis的动态SQL（五）

你的名字 2020-11-01 08:11 863阅读 0赞

mybatis提供对SQL语句动态的组装能力，而且它只有几个基本的元素，十分简单明了，大量的判断都可以在mybatis的映射XML文件里面配置，以达到许多我们需要大量代码才能实现的功能，大大减少了我们编写代码的工作量，这体现了mybatis的灵活性、高度可配置性和可维护性。mybatis也可以在注解中配置SQL，但是由于注解中配置功能受限，对于复杂的SQL而言可读性很差，所以使用较少。

5.1 概述

**mybatis的动态SQL包括以下几种元素：**

| 元素                      | 作用                        | 备注                    |
| ------------------------- | --------------------------- | ----------------------- |
| if                        | 判断语句                    | 单条件分支判断          |
| choose（when、otherwise） | 相当于Java中的case when语句 | 多条件分支判断          |
| trim（when、set）         | 辅助元素                    | 用于处理一些SQL拼装问题 |
| foreach                   | 循环语句                    | 在in语句等列举条件常用  |
|                           |                             |                         |

5.2 if元素

最常用的判断语句，相当于Java中的if语句。常常与test属性联合使用。

基本用法：

<select id="findRoles" paramterType="string" resultMap="roleResultMap">
        select role_no,role_name,note from t_role where 1=1
        <if test="roleName !=null and roleName !=''">
            and role_name like concat('%',#{roleName},'%')
        </if>
    </select>

这句话的含义就是当我们将参数roleName传递进入到映射器中，采取构造对roleName的模糊查询。如果这个参数为空，就不要去构造这个条件，显然这样的场景在我们实际的工作中是十分常见的。通过mybatis的条件语句我们可以节省许多拼接SQL的工作，把精力集中在XML的维护上。

5.3 choose、when、otherwise元素

if语句是一种非此即彼的关系，但是有些时候我们还需要第三种选择甚至更多的选择，也就是说我们也需要switch…case…default语句，而在映射器的动态语句中choose、when、otherwise元素承担了这个功能。场景如下：

*  当角色编号不为空，则只用角色编号作为条件查询。
 *  当角色编号为空，而角色名称不为空，则角色名称作为条件进行模糊查询。
 *  当角色编号和角色名称都为空，则要求角色备注不为空。

让我们看看如何使用choose、when、otherwise元素去实现。

<select id="findRoles" parameterType="role" resultMap="roleResultMap">
        select role_no,role_name,note from t_role
        where 1=1
        <choose>
            <when test="roleNo != null and roleNo !=''">
                AND role_no = #{roleNo}
            </when>
            <when test="roleName != null and roleName !=''">
                AND role_name like concat('%',#{roleName},'%')
            </when> 
            <otherwise>
                AND note is not null
            </otherwise>
        </choose>
    </select>

5.4 trim、where、set元素

上面的SQL语句中加入了条件“1=1”，这样的条件显得有些奇怪，但是不加上条件又会造成语法错误。这时候我们可以用where元素处理。

<select id="findRoles" parameterType="string" resultMap="roleResultMap">
        select role_no,role_name,note from t_role
        <where>
        <if test="roleName != null and roleName !=''">
            and role_name like concat('%',#{roleName},'%')    
        </if>
        </where>
    </select>

这样当where元素里面的条件成立的时候，才会加入where这个SQL关键字到组装的SQL里面，否则就不加入。

**使用trim元素**

<select id="findRoles" parameterType="string" resultMap="roleResultMap">
        select role_no,role_name,note from t_role
        <trim prefix="where" prefixOverrides="and">
            <if test="roleName != null and roleName !=''">
                and role_name like concat('%',#{roleName},'%')
            </if>
        </trim>
    </select>

稍微解释一下：

*  trim元素就意味着我们需要去掉一些特殊的字符串
 *  prefix代表的是语句前缀
 *  prefixOverrides代表的是需要去掉的那种字符串。

我们常常需要更新某一对象，发送所有的字段给持久对象。现实中的场景是，我只想更新一个字段，如果发送所有的属性去更新一遍，对网络带宽消耗较大，性能最佳的办法是把主键和更新字段的值传递给SQL更新即可。例如，角色表有一个主键和两个字段，如果一个个去更新需要写2条SQL，如果有1000个字段呢？显然这种做法是不方便的，而在Hibernate中我们做更新都是用全部字段发送给SQL的方法来避免这一情况发生。

在mybatis中，我们常常可以使用set元素来完成这些功能。

<update id="updateRole" parameterType="role">
        update t_role
        <set>
            <if test="roleName != null and roleName !=''">
                role_name = #{roleName},
            </if>
            <if test="note != null and note != ''">
                note = #{note}
            </if>
        </set>
        where role_no = #{roleNo}
    </update>

set元素遇到了逗号，它会把对应的逗号去掉，如果自己编写会用很多次的判断。当我只想更新备注，我们只需要传递备注信息和角色编号即可，而不需要再传递角色名称。mybatis就会根据参数的规则进行动态SQL组装，这样便能满足需求，同时避免了全部字段更新的麻烦。

同样也可以把它转变为对应的trim元素。

5.5 foreach元素

foreach元素是一个循环语句，作用是遍历集合。它能很友好的支持数和List、set集合的接口，对此提供遍历功能。

在数据库中，数据字典是经常使用的内容，比如说在用户表中，性别可以分为男、女或者未知，我们把性别作为一个字典，定义如下。

1-男，2-女，0-未知

实际中，用户可能查找未知性别的用户，也可能查找男性和女性的用户，或者其他组合灯，具体的参数需要使用foreach元素去确定，如代码：

<select id="findUserBySex" resultType="user">
        select * from t_user where sex in
        <foreach item="sex" index="index" collection="sexList" open="(" separator="," close=")">
        #{sex}
        </foreach>
    </select>

稍稍解释一下：

*  collection配置的sexList是传递进来的参数名称，它可以是一个数组或者List、Set等集合。
 *  item配置的是循环中当前的元素。
 *  index配置的是当前元素在集合的位置下标。
 *  open和close配置的是以什么符号将这些集合元素包装起来。
 *  separator是各个元素的间隔符。

在SQL中对于in语句我们常常使用，对于大量数据的in语句需要我们特别注意，因为它会消耗大量的性能，还有一些数据库的SQL对执行的SQL长度也有限制，所以我们使用它的时候需要预估一下这个collection对象的长度。

5.6 test的属性

test的属性用于条件判断的语句中。它的作用相当于判断真假。在大部分的场景中我们都是用它判断空和费控。有时候我们需要判断字符串、数字和枚举等。下面列举一下如何用if判断字符串。

<select id="getRoleTest" parameterType="string" resultMap="roleResultMap">
        select role_no,role_name,note from t_role
        <if test="type = 'Y'">
        where 1=1
        </if>
    </select>

5.7 bind元素

作用是通过OGNL表达式去自定义一个上下文变量，这样更方便我们使用，在我们进行模糊查询的时候，如果是MySQL数据库，我们常常用到的是一个concat用“%”和参数相连接。然而在Oracle数据库则是用连接符号“||”，这样SQL就需要提供两种形式去实现。但是有了bind元素，我们就完全不必使用数据库的语言，只要使用mybatis的语言即可与所需参数相连。

比如我们要按角色名称进行模糊查询，我们可以把映射文件写成这样。

<select id="findRole" resultType="com.learn.chapter5.mybatis.bean.RoleBean">
        <bind name="patternn" value="'%' + _parameter + '%'" />
        select id,role_name as roleName,create_date as createDate,end_date as endFlag,
        end_flag as endFlag,note from t_role
        where role_name like #{pattern}
    </select>

“\_parameter”代表的就是传递进来的参数，和通配符连接后，赋给了pattern，我们就可以在select语句中使用这个变量进行模糊查询，不管是MySQL数据库还是Oracle数据库都可以使用这样的语句，提高了其可移植性。