MyBatis动态SQL语句

末蓝、 2021-07-24 13:34 760阅读 0赞

在Mapper配置文件中，有时候需要根据一些查询条件来选择不同的SQL语句，或者将一些使用频率高的SQL语句单独配置，在需要的地方引用。MyBatis提供了一种可以根据条件动态配置SQL语句，以及单独配置SQL语句块的机制。

# 1、<where>标签、<if>标签 #

当查询语句的查询条件由于输入参数的不同而无法确切定义时，可以使用<where>标签对来包裹需要动态指定的SQL查询条件，而在<where>标签对中，可以使用<if test="...">条件来分情况设置SQL查询条件。

**【示例】**MyBatis中使用动态SQL语句，获取用户信息。

<select id="queryUserInfo" parameterType="com.pjb.mybatis.po.UserParam" resultType="com.pjb.mybatis.po.User">
        SELECT * FROM tb_user
        <where>
            <if test="userId>0">
                and user_id = #{userId}
            </if>
            <if test="userName!=null and userName!=''">
                and user_name like '%${userName}%'
            </if>
            <if test="sex!=null and sex!=''">
                and sex = #{sex}
            </if>
        </where>
    </select>

值得注意的是，当使用<where>标签对包裹 if 条件语句时，将会忽略查询条件中的第一个and或or（这样才能组成一个可执行的SQL语句）。

# 2、<sql>片段 #

MyBatis提供了可以将复用性比较强的SQL语句封装成“SQL片段”，在需要使用该SQL片段的映射配置中声明一下，即可引入该SQL语句，声明SQL片段的格式如下：

其中id是SQL片段的唯一标识，是不可重复的。另外，SQL片段是支持动态SQL语句的，**但建议，在SQL片段中不要使用<where>标签，而是在调用的SQL方法中写<where>标签，因为该SQL方法可能还会引入其他的SQL片段，如果这些多个的SQL片段中都有<where>标签，那么会引起语句冲突。**

**【示例】**将上面的SQL语句的查询条件封装为一个“SQL片段”，然后提供给SQL映射配置使用。

<sql id="query_user_where">
        <if test="userId>0">
            AND user_id = #{userId}
        </if>
        <if test="userName!=null and userName!=''">
            AND user_name like '%${userName}%'
        </if>
        <if test="sex!=null and sex!=''">
            AND sex = #{sex}
        </if>
    </sql>
    
    
    <select id="queryUserInfo" parameterType="com.pjb.mybatis.po.UserParam" resultType="com.pjb.mybatis.po.User">
        SELECT * FROM tb_user
        <where>
            <include refid="query_user_where"/>
            
        </where>
    </select>
    
    
    <select id="queryUserCount" parameterType="com.pjb.mybatis.po.UserParam" resultType="int">
        SELECT COUNT(*) FROM tb_user
        <where>
            <include refid="query_user_where"/>
            
        </where>
    </select>

除了自身所在的Mapper文件，每个SQL映射配置还可以引入外部Mapper文件中的SQL片段，只需要在refid属性填写的SQL片段的id前添加其所在Mapper文件的namespace信息即可（如：test.query\_user\_where）。

# 3、<foreach>标签 #

有些时候查询语句中可能包含多个查询信息，例如查询多个id（如id为2、4、5）的User用户，会这样写SQL语句：

SELECT * FROM tb_user WHERE user_id=2 OR user_id=4 OR user_id=5;
    -- 或者
    SELECT * FROM tb_user WHERE user_id IN (2,4,5);

而此时如果在Mapper文件中配置这样的语句，则需要向SQL配置传递一个数组或者List类型的输入参数，然后MyBatis使用<foreach>标签去遍历并解析这些数组或List中的值。

**<foreach>标签属性说明：**

<table> 
 <thead> 
  <tr> 
   <th style="width:143px;">属性</th> 
   <th style="width:656px;">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="width:143px;">index</td> 
   <td style="width:656px;">当迭代对象是数组，列表时，表示的是当前迭代的次数。</td> 
  </tr> 
  <tr> 
   <td style="width:143px;">item</td> 
   <td style="width:656px;">当迭代对象是数组，列表时，表示的是当前迭代的元素。</td> 
  </tr> 
  <tr> 
   <td style="width:143px;">collection</td> 
   <td style="width:656px;">当前遍历的对象。</td> 
  </tr> 
  <tr> 
   <td style="width:143px;">open</td> 
   <td style="width:656px;">遍历的SQL以什么开头。</td> 
  </tr> 
  <tr> 
   <td style="width:143px;">close</td> 
   <td style="width:656px;">遍历的SQL以什么结尾。</td> 
  </tr> 
  <tr> 
   <td style="width:143px;">separator</td> 
   <td style="width:656px;">遍历完一次后，在末尾添加的字符等。</td> 
  </tr> 
 </tbody> 
</table>

**【示例】**使用MyBatis提供的<foreach>标签拼接SQL语句，实现查询多个用户编号信息。

（1）在Java包中类中，增加一个包含多个id信息的数组属性。

package com.pjb.mybatis.po;
    
    /**
     * 用户信息参数类
     * @author pan_junbiao
     **/
    public class UserParam
    {
        private int userId; //用户ID
        private String userName; //用户姓名
        private String sex; //性别
        private int[] ids; //多个用户ID
    
        //省略getter与setter方法...
    }

（2）创建SQL片段，使用foreach标签，拼接or语句。

<sql id="query_user_or">
        <if test="ids!=null and ids.length>0">
            <foreach collection="ids" item="user_id" open="AND (" close=")" separator="OR">
                user_id=#{user_id}
            </foreach>
        </if>
    </sql>

（3）创建SQL片段，使用foreach标签，拼接in语句。

<sql id="query_user_in">
        <if test="ids!=null and ids.length>0">
            AND user_id IN
            <foreach collection="ids" item="user_id" open="(" close=")" separator=",">
                #{user_id}
            </foreach>
        </if>
    </sql>

> **注意：**在SQL片段里的“and”用来拼接已有一个或多个查询条件的语句，当此语句为第一个查询条件时，会因为<where>标签的存在而屏蔽第一个“and”。

# 4、<choose>标签、<when>标签、<otherwise>标签 #

有时我们不想应用到所有的条件语句，而只想从中择其一项。针对这种情况，MyBatis提供了choose元素，它有点像Java中的switch语句。

**【示例】**使用<choose>标签、<when>标签、<otherwise>标签，根据查询条件，获取用户信息。

<select id="queryUserChoose" parameterType="com.pjb.mybatis.po.UserParam" resultType="com.pjb.mybatis.po.User">
        SELECT * FROM tb_user
        <where>
            <choose>
                <when test="userId>0">
                    AND user_id = #{userId}
                </when>
                <when test="userName!=null and userName!=''">
                    AND user_name like '%${userName}%'
                </when>
                <otherwise>
                    AND sex = '女'
                </otherwise>
            </choose>
        </where>
    </select>

# 5、<trim>标签、<set>标签 #

在上面的示例中<where>标签会在至少有一个子元素的条件返回SQL子句的情况下才去插入“WHERE”子句。而且，若语句的开头为“AND”或“OR”，<where>标签也会将它们去除。

MyBatis还提供了<trim>标签，我们可以通过自定义<trim>标签来定制<where>标签的功能。比如，和<where>标签等价的自定义 <trim>标签为：

prefixOverrides 属性会忽略通过管道分隔的文本序列（注意此例中的空格也是必要的）。它的作用是移除所有指定在 prefixOverrides 属性中的内容，并且插入 prefix 属性中指定的内容。

**【示例】**使用自定义<trim>标签来定制<where>标签的功能，获取用户信息。

<select id="queryUserTrim" parameterType="com.pjb.mybatis.po.UserParam" resultType="com.pjb.mybatis.po.User">
        SELECT * FROM tb_user
        <trim prefix="WHERE" prefixOverrides="AND |OR ">
            <if test="userId>0">
                and user_id = #{userId}
            </if>
            <if test="userName!=null and userName!=''">
                and user_name like '%${userName}%'
            </if>
            <if test="sex!=null and sex!=''">
                and sex = #{sex}
            </if>
        </trim>
    </select>

类似的用于动态更新语句的解决方案叫做<set>标签。<set>标签可以用于动态包含需要更新的列，而舍去其它的。比如：

**【示例】**在修改用户信息的SQL配置方法中，使用<set>标签过滤多余的逗号。

<update id="updateUser" parameterType="com.pjb.mybatis.po.UserParam">
        UPDATE tb_user
        <set>
            <if test="userName != null">user_name=#{userName},</if>
            <if test="sex != null">sex=#{sex},</if>
            <if test="age >0 ">age=#{age},</if>
            <if test="blogUrl != null">blog_url=#{blogUrl}</if>
        </set>
        where user_id = #{userId}
    </update>

这里，<set>标签会动态前置SET关键字，同时也会删掉无关的逗号，因为用了条件语句之后很可能就会在生成的SQL语句的后面留下这些逗号。因为用的是“if”元素，若最后一个“if”没有匹配上而前面的匹配上，SQL 语句的最后就会有一个逗号遗留。

若你对 set 元素等价的自定义 trim 元素的代码感兴趣，那这就是它的真面目：

注意这里我们删去的是后缀值，同时添加了前缀值。