MyBatis实战之动态SQL

如果使用JDBC或者其他框架,很多时候你得根据需要去拼接SQL，这是一个麻烦的事情，而MyBatis提供对SQL语句动态的组装能力，而且它只有几个基本的元素，非常简单明了，大量的判断都可以在MyBatis的映射XML文件里面配置，以达到许多我们需要大量代码才能实现的功能，大大减少了我们编写代码的工作量，这体现了MyBatis的灵活性、高度可配置性和可维护性。MyBatis也可以在注解中配置SQL，但是由于注解中配置功能受限，对于复杂的SQL而言可读性很差，所以使用较少。

关于MyBatis的注解使用可以参考我的这篇文章MyBatis实战之初步

今天主要讲解这么几个常用的动态SQL:

1.if

2.choose、when、otherwise

3.trim、where、set

4.foreach

5.test

6.bind

一、if

if通常与where一起连用比较多

如下代码所示:

    <select id="selectName"  parameterType="String" resultMap="BaseResultMap">
       select <include refid="Base_Column_List"/>
       from `user`
       <where>
       <if test="userName!=''">
       and user_name = #{userName}
       </if>
       </where>
    </select>

<if>中的test通常判断的条件是不等于Null或者空字符串，除此之外还可以进行比较判断比如大于等于小于等这样的。

二、choose、when、otherwise

 <select id="selectName"  parameterType="String" resultMap="BaseResultMap">
       select <include refid="Base_Column_List"/>
       from `user`       
       <choose>
       <when test="userName!=null and userName!=''">
       where userName = #{userName}
       </when>
       <otherwise>
       where sex = #{sex}
       </otherwise>
       </choose>
    </select>

你可以将<choose><when></when><otherwise></otherwise></choose>理解为Java中的if-else或者是if-else if-else

三、trim、where、set

trim+where示例:

    <select id="selectName"  parameterType="String" resultMap="BaseResultMap">
       select <include refid="Base_Column_List"/>
       from `user`       
       <trim prefix="where" prefixOverrides="AND |OR">
          <if test="userName !=null and userName!=''">
          and user_name = #{userName}
          </if>
       </trim>
    </select>

prefix：前缀　　　　　　

prefixoverride：去掉第一个and或者是or

trim+set示例:

　update user

　　<trim prefix="set" suffixoverride="," suffix=" where id = #{id} ">

　　　　<if test="name != null and name.length()>0"> name=#{name} , </if>

　　　　<if test="gender != null and gender.length()>0"> gender=#{gender} ,  </if>

　　</trim>

prefix同trim+where的意思是一样，都是前缀。

suffixoverride：去掉最后一个逗号（也可以是其他的标记，就像是上面前缀中的and一样）

suffix：后缀

四、foreach

批量更新示例:

 <update id="udpateUserLogoStatu" parameterType="java.util.List">
   
 <foreach collection="users" item="user" index="index" separator=";">
        update `user`
        <set>
            logo = 1
        </set>
        where logo = #{user.logo}
    </foreach>     
 
 </update>

可以参考这篇文章:mybatis的批量更新实例

foreach相关参数解释:

collection配置的users是传递进来的参数名称，它可以是一个数组或者List、Set等集合;

item配置的是循环中当前的元素;

index配置的是当前元素在集合的位置下标;

separator是各个元素的间隔符;

还有代码中没有展示的open和close,它们的含义是以什么符号将这些元素包装起来。

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

五、test

至于test就不提太多了，在<where>+<if>中用到非常频繁。

六、bind

bind通常用于绑定参数，然后引用，在实际开发中用的也不少。

bind的示例:

<select id="getUserList" resultType="com.blog.entity.User">
          <!-- bind:可以将OGNL表达式的值绑定到一个变量中，方便后来引用这个变量的值 -->
          <bind name="userName" value="'%'+userName+'%'"/> eName是employee中一个属性值
          SELECT * FROM `user`
          <if test="userName!=null">
            where ename like #{userName}
          </if>
</select>

动态SQL一般常见的问题，就是查询条件出错，一般要么是参数问题，或者是多个字段and拼接出问题。

另外还有一点要提醒，在要用动态SQL之前，最好还是将SQL先执行一遍，确定没有问题后，再改为动态SQL，最后再单元测试多个边界条件测试下，确保没有问题后，再编写对应的逻辑Controller。