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,

          < if test = "type ==2" > <!-- 半月 -->

             DATE_ADD(NOW(),INTERVAL 15 DAY)

          </ if >

          < if test = "type ==3" > <!-- 包月 -->

             DATE_ADD(NOW(),INTERVAL 1 MONTH)

          </ if >

          < if test = "type ==4" > <!-- 包季 -->

             DATE_ADD(NOW(),INTERVAL 3 MONTH)

          </ if >

          < if test = "type ==5" > <!-- 包半年 -->

             DATE_ADD(NOW(),INTERVAL 6 MONTH)

          </ if >

          < if test = "type ==6" > <!-- 包年 -->

             DATE_ADD(NOW(),INTERVAL 12 MONTH)

          </ if >

          < if test = "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可以在更新语句中，帮我们去掉多的逗号

< update id = "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,

          < if test = "type !=null and type!='' " >

             v.type = #{type},

          </ if >

          < if test = " updatetime !=null and   updatetime !='' " >

             v.` updatetime `=#{ updatetime }

          </ if >

     </ update >

foreach：in语句中使用

< select id = "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>