mybatis的动态SQL

​ 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元素。

<trim prefix="SET" suffxOverrides=",">...</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数据库都可以使用这样的语句，提高了其可移植性。