MyBatis知识盘点【伍】_动态sql

上篇文章：MyBatis知识盘点【肆】_映射器

支持的元素有if、choose、trim、foreach和bind，下面分别介绍：

if：单条件分支判断

SELECT `user_id`,`platform`,`orderId`,`product_id`,`product_name`,NOW(),NOW(),NOW(),app_business_id,app_group_id,

         <iftest="type ==2"><!-- 半月 -->

             DATE_ADD(NOW(),INTERVAL 15 DAY)

         </if>

         <iftest="type ==3"><!-- 包月 -->

             DATE_ADD(NOW(),INTERVAL 1 MONTH)

         </if>

         <iftest="type ==4"><!-- 包季 -->

             DATE_ADD(NOW(),INTERVAL 3 MONTH)

         </if>

         <iftest="type ==5"><!-- 包半年 -->

             DATE_ADD(NOW(),INTERVAL 6 MONTH)

         </if>

         <iftest="type ==6"><!-- 包年 -->

             DATE_ADD(NOW(),INTERVAL 12 MONTH)

         </if>

         <iftest="type ==7"><!-- 走运营商产品订购,失效时间为空 -->

             null

         </if>

         FROM `user_order` WHERE `orderId` =#{orderId};

choose(when、otherwise)：相当于java中的case when，多条件分支判断

SELECT `user_id`,`platform`,`orderId`,`product_id`,`product_name`,NOW(),NOW(),NOW(),app_business_id,app_group_id    FROM `user_order` WHERE 1=1 

    <choose>

        <when test="user_id != null and user_id !=''">

            AND user_id = #{user_id}

         </when>

        <when test="orderId!= null and orderId !=''">

            AND orderId= #{orderId}

         </when>

          <otherwise>

            AND product_id is not null

         </otherwise>

    </choose>

trim(where、set)：处理sql拼装

trim可以去掉一些特殊的字符串，prefix代表前缀，prefixOverrdes是要去掉的字符串。

SELECT `user_id`,`platform`,`orderId`,`product_id`,`product_name`,NOW(),NOW(),NOW(),app_business_id,app_group_id    FROM `user_order` 

          <trim prefix="where"prefixOverrdes="and">

    <choose>

        <when test="user_id != null and user_id !=''">

            AND user_id = #{user_id}

         </when>

        <when test="orderId!= null and orderId !=''">

            AND orderId= #{orderId}

         </when>

          <otherwise>

            AND product_id is not null

         </otherwise>

    </choose>

    </trim>

set可以在更新语句中，帮我们去掉多的逗号

<updateid="updateVIP" parameterType="java.util.Map">

         UPDATE `user_order_vip` v  SET v.`platform`=o.`platform`,v.`orderId`=o.`orderId`,v.`product_id`=o.`product_id`,v.`product_name`=o.`product_name`,

         v.`updatetime`=NOW(),

         v.starttime= CASE WHEN v.overtime>NOW() THEN v.starttime ELSE NOW() END,

         <iftest="type !=null andtype!=''">

             v.type = #{type},

         </if>

         <iftest="updatetime!=nulland updatetime!=''">

             v.`updatetime`=#{updatetime}

         </if>

    </update>

foreach：in语句中使用

<selectid="selectOrders"parameterType="java.lang.String"resultType="com.hzdracom.core.bean.Order">

         <!-- SELECT * FROM `user_order` WHERE `orderId`=#{orderId} -->

         select *,CASE WHEN (is_unified_product is NULL) then 0 else is_unified_product end   isUnifiedProduct from user_order

         WHERE orderId IN 

       <foreach item="order_id" index = "index" collection="orderList" open="(" separator="," close=")">

       #{order_id}

   </foreach>

</select>

bind 模糊查询时用

<select id="selectFlowForUpdate" resultMap="BaseResultMap"

        parameterType="java.lang.String">

        <bind name="ENTR_ID" value="'%' + _parameter" />

        select

        *

        from

        TC_ENTR_FLOW

        where ENTR_ID like #{ENTR_ID}

        for update

    </select>