mybatis 批量插入以及merge into用法

一、mybiats foreach标签

<insert id="insertBatch" parameterType="List">
INSERT INTO TStudent(name,age)
<foreach collection="list" item="item" index="index" open="("close=")"separator="union all">
SELECT #{item.name} as a, #{item.age} as b FROM DUAL
</foreach>
</insert>

<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>

<update id="mergeBank" parameterType="java.util.List">
		merge into T_MY_BANK a
		using
		(
		<foreach collection="list" index="index" item="item" open=""
				 close="" separator="union all">
			SELECT
			sys_guid() as id,
			#{item.bankCode,jdbcType=VARCHAR} as bankCode,
			#{item.bankName,jdbcType=VARCHAR} as bankName,
			#{item.bankName,jdbcType=VARCHAR} as subBankName,
			#{item.bankId,jdbcType=VARCHAR} as bankId,
			'1' as  isOverseas
			FROM dual
		</foreach>
		) b
		on (
		a.BANK_ID = b.bankId
		)
		when
		matched then
		UPDATE SET
		a.BANK_CODE = b.bankCode,
		a.BANK_NAME=b.bankName,
		a.SUB_BANK_NAME = b.subBankName,
		a.UPDATE_DT=sysdate
		when
		not matched then
		INSERT(
		a.ID,
		a.BANK_CODE,
		a.BANK_NAME,
		a.SUB_BANK_NAME,
		a.BANK_ID,
		a.CREATE_DT,
		a.UPDATE_DT
		) VALUES(
		b.id,
		b.bankCode,
		b.bankName,
		b.subBankName,
		b.bankId,
		sysdate,
		sysdate
		)
	</update>

<insert id="insert" parameterType="com.pojo.UserInfo">
    <selectKey resultType="java.lang.String" order="BEFORE" keyProperty="id">
        SELECT to_char(sysdate,'yyyymmdd')||USER_SEQ.nextval AS ID FROM dual
    </selectKey>
    MERGE INTO user_info ui 
    USING (SELECT #{userId} user_id FROM dual) d  
    ON (ui.userId = d.user_id)  
    WHEN matched THEN  
         UPDATE SET ui.name = #{name}
    WHEN not matched THEN 
         INSERT (ID,user_id,name
    )VALUES(
        #{id,jdbcType=VARCHAR},#{userId,jdbcType=VARCHAR},#{name,jdbcType=VARCHAR}
    )
</insert>

<update id="updateBatch" parameterType="java.util.List">
 begin  
<foreach collection="list" item="item" index="index" separator=";" > 
    UPDATE USER_INFO
    <set>
        <if test="null != item.name">
            <![CDATA[name = #{item.name,jdbcType=VARCHAR}]]>
        </if>
    </set>
	<![CDATA[
		WHERE  ID = #{item.id,jdbcType=VARCHAR} 
	]]>
  </foreach>
   ;end;
</update>