MyBatis动态SQL大全

一、什么是动态SQL

MyBatis的动态SQL是基于OGNL的表达式的。它对SQL语句进行灵活的操作，通过表达式判断来实现对SQL的灵活拼接、组装。

二、动态SQL介绍

下面是用于实现动态SQL的主要元素：

if
choose（when，otherwise）
trim
where
set
foreach

(1)、if元素
在动态SQL中所作的最通用的事情就是包含部分where子句的条件

<select id="getList" parameterType="java.util.Map" resultType="com.boya.app.loan.blo.model.BLoLoanInfo">
		SELECT
			<include refid="column"/>
		FROM B_LO_LOAN_INFO t
		<trim prefix="WHERE" prefixOverrides="AND |OR ">
			<if test="id != null and id != ''">
				AND t.ID = #{id}
			</if>
		</trim>
	</select>

2.choose,when,otherwise元素。类似java中的switch…case…,MyBatis提提供choose

<select id="getLoanInfoList" parameterType="java.util.Map" resultType="com.by.loan.manage.dto.BLoLoanInfoDto">
    <choose>
      <!-- 有机构查询条件，查询它及它以下 -->
      <when test="orgIds != null and orgIds != ''">
        SELECT aa.*,aa.staffNo instUserNo FROM
        (
        SELECT
        <include refid="column"/>
        FROM B_LO_LOAN_INFO t
        LEFT JOIN b_lo_loan_prod c on c.LOAN_NO = t.LOAN_NO
        LEFT JOIN b_lo_cust_info b on b.CUST_NO = t.CUST_NO
        LEFT JOIN b_lo_loan_fund_match m ON t.LOAN_NO = m.LOAN_NO
        LEFT JOIN b_lo_loan_branch h on h.LOAN_NO = t.LOAN_NO
        LEFT JOIN b_lo_loan_saler s ON s.LOAN_NO = t.LOAN_NO
        <trim prefix="WHERE" prefixOverrides="AND |OR ">
          <if test="loanNo != null and loanNo != ''">
            AND t.LOAN_NO like CONCAT('%',#{loanNo},'%')
          </if>
        </trim>
        ) aa
        JOIN
        (
        select distinct c.LOGIN_NAME from
        (SELECT id FROM A_SYS_ORG WHERE FIND_IN_SET(ID,#{orgIds})>0) a
        join a_sys_user_org b on a.id=b.ORG_ID
        join a_sys_user c on c.ID=b.USER_ID
        ) bb
        ON bb.LOGIN_NAME=aa.staffNo
        ORDER BY aa.applyDate DESC, aa.regDate DESC
      </when>
      <!-- 没有机构查询条件 -->
      <otherwise>
        SELECT
        s.STAFF_NO instUserNo,
        n.channel_Code channelCode,
        <include refid="column"/>
        FROM B_LO_LOAN_INFO t
        LEFT JOIN b_lo_loan_prod c on c.LOAN_NO = t.LOAN_NO
        LEFT JOIN b_lo_cust_info b on b.CUST_NO = t.CUST_NO
        LEFT JOIN b_lo_loan_fund_match m ON t.LOAN_NO = m.LOAN_NO
        LEFT JOIN b_lo_loan_branch h on h.LOAN_NO = t.LOAN_NO
        LEFT JOIN b_lo_loan_saler s ON s.LOAN_NO = t.LOAN_NO
        LEFT join b_cu_cust_channel_info n on n.PHONE_NO = b.PHONE_NO 
			
        <trim prefix="WHERE" prefixOverrides="AND |OR ">
	      <if test='channelCode == "nChannel"'>
			   AND ( n.channel_Code = '' or n.CHANNEL_CODE  IS NULL )
		  </if>
        </trim>
        ORDER BY t.APPLY_DATE DESC, t.REG_DATE DESC
      </otherwise>
    </choose>
  </select>

3.trim元素
trim和where同级，其主要功能是可以在自己包含的内容前或后加上某些前后缀，与之对应的属性是prefix和suffix；也可以把内容的首部或尾部的某些内容覆盖，对应的属性是prefixOverrides和suffixOverrides;

<update id="updateByPrimaryKey" parameterType="java.util.Map">
		<trim prefix="SET" suffixOverrides=",">
			<if test="loanNo != null">
				LOAN_NO = #{loanNo},
			</if>
			<if test="custNo != null">
				CUST_NO = #{custNo},
			</if>
		</trim>
		WHERE LOAN_NO=#{loanNo}
	</update>

4.set元素

set元素可以被用于动态包含更新的列，不包含不需要更新的，并且会动态的前置set关键字，也可以相除任意无关的逗号。

<update id="updateUser" parameterType="com.mybatis.VO.UserVO">    
    update user_table   
    <set>   
        <if test="userId !=null and userId !=''">    
            AND A.userId = #{userId,jdbcType=CHAR}    
        </if>    
        <if test="userName !=null and userName !=''">    
            AND A.userName = #{userName,jdbcType=CHAR}    
        </if>   
    </set>          
    where userId=#{id}    
</update>  

5.foreach元素
foreach的主要用在构建in条件中，他可以迭代一个集合。foreach元素的属性主要有：item,index,collection,open,separator,close。

下面对属性进行简单的介绍：

item:表示集合中每一个元素进行迭代时的别名。

index:指定一个名字，用于表示在迭代过程中每次迭代的位置。

open:表示以什么开始。

separator:每次迭代以什么分割。

close:以什么关闭。

collection:最重要且必须指定的有三种情况：

1.如果传入的是单独参数的List类型时，collection的属性值为list。

2.如果传入的是单独参数的数组时，collection的属性值为array.

3.如果传入多个参数时，我们把多个参数放入map中，单参数也可以放入map中。map中的key就是参数名，所以collection属性值就是传入的List或者array对象在Map里的key。

下面列举每种情况的案例：

1.单参数List类型：

<select id="batchSelect" resultType="UserVO" parameterType="java.util.Map>  
        select userId,userName,userAge,userPhone from user_table where userId in  
        <foreach collection="list" index="index" item="item" open="(" separator="," close=")">  
            #{item}  
        </foreach>  
    </select>  

2.单参数数组array类型

<select id="batchSelect" resultType="UserVO" parameterType="java.util.Map>  
    select userId,userName,userAge,userPhone from user_table where userId in  
    <foreach collection="array" index="index" item="item" open="(" separator="," close=")">  
        #{item}  
    </foreach>  
</select>  

3.多参数Map类型

<select id="batchSelect" resultType="UserVO" parameterType="java.util.Map">  
     select userId,userName,userAge,userPhone from user_table where userId in  
     <foreach collection="ids" index="index" item="item" open="(" separator="," close=")">  
         #{item}  
     </foreach>  
 </select>  

对应的分装成map的部分java代码：

List<Integer> ids = new ArrayList<Integer>();  
        ids.add(1);  
        ids.add(2);  
        ids.add(3);  
        ids.add(6);  
        ids.add(7);  
        ids.add(9);  
        Map<String, Object> params = new HashMap<String, Object>();  
        params.put("ids", ids);  

批量插入方法：

<insert id="insertBatch">
    INSERT INTO t_user
            (id, name, del_flag)
    VALUES
    <foreach collection ="list" item="user" separator =",">
         (#{user.id}, #{user.name}, #{user.delFlag})
    </foreach >
</insert>

---

作者：谈笑_风生
来源：CSDN
原文：https://blog.csdn.net/m0_37981235/article/details/79131493
版权声明：本文为博主原创文章，转载请附上博文链接！

5、Case when then else end用法：
直接上一次例子：

<select id="getAllStatList" parameterType="java.lang.String" resultType="java.util.HashMap">
		SELECT
			s.REPAY_DATE repayDate,
			s.RCV_TOTAL_AMT rcvTotalAmt,
			s.ACT_TOTAL_AMT actTotalAmt,
			s.REPAY_NUM repayNum,
			s.OVDU_FLAG ovduFlag,
		  (CASE WHEN s.ACT_TOTAL_AMT>=s.RCV_TOTAL_AMT THEN '13900001' ELSE '13900002' END) isHaveRepayed
		FROM
			c_acc_loan_acct_stat s
		WHERE
			LOAN_NO = #{loanNo};
	</select>