Oracle,MySql批量删除，插入，修改,批量merge带sequence

MySql

批量新增

<!-- 批量保存用户,并返回每个用户插入的ID -->
<insert id="batchSave" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO `test`.`tb_user`(`username`, age)
    VALUES
    <foreach collection="list" item="item" separator=",">
        (#{item.username}, #{item.age})
    </foreach>
</insert>

批量删除

<!-- 批量删除用户 -->
<delete id="batchDelete" parameterType="java.util.List">
    DELETE FROM `test`.`tb_user` WHERE id IN
    <foreach collection="list" item="item" open="(" close=")" separator=",">
        #{id}
    </foreach>
</delete>

UserDao

/**
     * 批量保存用户
     * @param userList
     */
    int batchSave(List<User> userList);

    /**
     * 批量删除用户
     * @param idList
     */
    int batchDelete(List<Integer> idList);

 /**

注意!因为Mapper里collection=list 所以UserDao这么写 否则应该这样写

    int batchSave(@Param("userList")List<User> userList);

    int batchDelete(@Param("idList")List<Integer> idList);

Oracle

批量新增 写法1

<insert id="addList" parameterType="java.util.List" useGeneratedKeys="false">
        INSERT ALL
        <foreach item="item" index="index" collection="list">
        INTO T_APPLAUD
        (
            ID,
            USER_ID,
            BUSINESS_TYPE,
            PRODUCT_ID,
            CREATE_TIME
        ) VALUES
        (
            #{item.id, jdbcType=NUMERIC},
            #{item.userId, jdbcType=VARCHAR},
            #{item.businessType, jdbcType=VARCHAR},
            #{item.productId, jdbcType=VARCHAR},
            #{item.createdTime, jdbcType=NUMERIC} 
        )
        </foreach>
        SELECT 1 FROM DUAL
    </insert>

批量新增 写法2

<insert id="addList" parameterType="java.util.List" useGeneratedKeys="false">
        INSERT INTO T_APPLAUD
        (
            ID,
            USER_ID,
            BUSINESS_TYPE,
            PRODUCT_ID,
            CREATE_TIME
        )
        <foreach item="item" index="index" collection="list" separator="union all">
        (
            SELECT 
                #{item.id},
                #{item.userId},
                #{item.businessType},
                #{item.productId},
                #{item.createdTime} 
            FROM DUAL
        )
        </foreach>
    </insert>

以上两种写法都有一个问题，那就是不支持sequence,如果要使用sequence就需使用如下写法

 <insert id="insertBatch" parameterType="java.util.List">
            INSERT INTO T_SVC_COMMENT_CATEGORY
            (
                ID,
                COMMENT_ID,
                CLASSFY_LEVEL1_CODE,
                CLASSFY_LEVEL2_CODE,
                CLASSFY_LEVEL3_CODE
            )
           SELECT
               seq_svc_comment_category.nextval ID,CC.*FROM (
            <foreach item="comment" collection="ccList" separator="UNION ALL">
                SELECT
                        #{comment.commentId,jdbcType=VARCHAR} COMMENT_ID,
                        #{comment.classfyLevel1Code,jdbcType=VARCHAR} CLASSFY_LEVEL1_CODE,
                        #{comment.classfyLevel2Code,jdbcType=VARCHAR} CLASSFY_LEVEL2_CODE,
                        #{comment.classfyLevel3Code,jdbcType=VARCHAR} CLASSFY_LEVEL3_CODE
                FROM DUAL
            </foreach>
        ) CC
    </insert>

批量删除 与MySql一样

<delete id="deleteBatch" parameterType="java.util.List">
        DELETE FROM T_SVC_COMMENT_CATEGORY C
        WHERE COMMENT_ID in
        <foreach item="commentId" collection="commentIds" open="(" close=")" separator=",">
              #{commentId}
        </foreach>
    </delete>

批量修改DAO

int updateComment(@Param("commentList")List<Comment> commentList);

 批量修改xml

<update id="updateComment" parameterType="java.util.List">
        <foreach item="comment" collection="commentList" open="begin" close=";end;"  separator=";">
            UPDATE T_SVC_COMMENT
            <set>
                <if test="comment.emotion != null and comment.emotion != ''">
                     emotion = #{comment.emotion, jdbcType = VARCHAR},
                </if>
                last_modified_time = SYSDATE
            </set>
            where id =  #{comment.id, jdbcType = VARCHAR}
        </foreach>
    </update>

 Oracle批量merge

不带sequence，注意值都放在using中，然后插入时从构造的临时表中取

<insert id="batchInsert" parameterType="java.util.List">
   merge into T_SVC_COMMENT_INCREMENT C
   using
   (
      <foreach item="increment" collection="cIList" index="index" separator="union">
         SELECT
         #{increment.id, jdbcType = VARCHAR} ID,
         #{increment.remarkID, jdbcType = VARCHAR} REMARKID,
         #{increment.orderId, jdbcType = VARCHAR} ORDER_NO,
         #{increment.custId, jdbcType = VARCHAR} CUST_ID,
         #{increment.flightDate, jdbcType = TIMESTAMP} FLIGHT_DATE,
         #{increment.flightNo, jdbcType = VARCHAR} FLIGHT_NO,
         #{increment.remark, jdbcType = VARCHAR} CMT_TEXT,
         #{increment.remark_Level, jdbcType = VARCHAR} CMT_LEVEL,
         #{increment.remark_From, jdbcType = VARCHAR} TERMINAL_ID,
         #{increment.nickName, jdbcType = VARCHAR} CUST_NAME,
         #{increment.classification, jdbcType = VARCHAR} EMOTION,
         #{increment.remarkTime, jdbcType = TIMESTAMP} CMT_TIME,
         #{increment.customerName, jdbcType = VARCHAR} C_NAME,
         #{increment.customerMobile, jdbcType = VARCHAR} C_TEL,
         'initByItf' CREATE_USER,
         SYSDATE CREATE_TIME
         FROM DUAL
      </foreach>
   ) D
      ON (C.ID = D.ID)
      when not matched then
      insert
      <trim prefix="(" suffix=")" suffixOverrides=",">
         ID,
         REMARKID,
         ORDER_NO,
         CUST_ID,
         FLIGHT_DATE,
         FLIGHT_NO,
         CMT_TEXT ,
         CMT_LEVEL,
         TERMINAL_ID,
         CUST_NAME,
         EMOTION,
         CMT_TIME,
         C_NAME,
         C_TEL,
         CREATE_USER,
         CREATE_TIME
      </trim>
      <trim prefix="values (" suffix=")" suffixOverrides=",">
         D.ID,
         D.REMARKID,
         D.ORDER_NO,
         D.CUST_ID,
         D.FLIGHT_DATE,
         D.FLIGHT_NO,
         D.CMT_TEXT ,
         D.CMT_LEVEL,
         D.TERMINAL_ID,
         D.CUST_NAME,
         D.EMOTION,
         D.CMT_TIME,
         D.C_NAME,
         D.C_TEL,
         D.CREATE_USER,
         D.CREATE_TIME
      </trim>
      when matched then update
      <set>
         REMARKID =D.REMARKID,
         ORDER_NO =D.ORDER_NO,
         CUST_ID = D.CUST_ID,
         FLIGHT_DATE = D.FLIGHT_DATE,
         FLIGHT_NO =D.FLIGHT_NO,
         CMT_TEXT = D.CMT_TEXT ,
         CMT_LEVEL = D.CMT_LEVEL,
         TERMINAL_ID = D.TERMINAL_ID,
         CUST_NAME = D.CUST_NAME,
         EMOTION = D.EMOTION,
         CMT_TIME = D.CMT_TIME,
         C_NAME = D.C_NAME,
         C_TEL = D.C_TEL,
         MODIFY_USER = D.CREATE_USER,
         last_modified_time = D.CREATE_TIME
      </set>
      where C.ID =  D.ID
</insert>

批量带sequence

<insert id="insertBatchRemote" parameterType="java.util.List">
    merge into T_SVC_COMMENT_CATEGORY C using
    (
        <foreach item="remote" collection="rccList" separator="union">
            SELECT
            #{remote.id, jdbcType = VARCHAR} COMMENT_ID,
            #{ remote.categoryOneID,jdbcType=VARCHAR} CLASSFY_LEVEL1_CODE,
            #{remote.categoryTwoID,jdbcType=VARCHAR} CLASSFY_LEVEL2_CODE,
            #{remote.categoryThreeID,jdbcType=VARCHAR} CLASSFY_LEVEL3_CODE,
            #{remote.isManual,jdbcType=VARCHAR} MANUAL_FLAG,
            'initByItf' CREATE_USER,
            SYSDATE CREATE_TIME
            FROM DUAL
        </foreach>
    ) D ON
    (
    C.COMMENT_ID = D.COMMENT_ID and
    C.CLASSFY_LEVEL1_CODE = D.CLASSFY_LEVEL1_CODE and
    C.CLASSFY_LEVEL2_CODE = D.CLASSFY_LEVEL2_CODE and
    C.CLASSFY_LEVEL3_CODE = D.CLASSFY_LEVEL3_CODE
    )
    when not matched then
    insert
    <trim prefix="(" suffix=")" suffixOverrides=",">
            ID,
            COMMENT_ID,
            CLASSFY_LEVEL1_CODE,
            CLASSFY_LEVEL2_CODE,
            CLASSFY_LEVEL3_CODE,
            MANUAL_FLAG,
            CREATE_USER,
            CREATE_TIME
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            seq_svc_comment_category.nextval,
            D.COMMENT_ID,
            D.CLASSFY_LEVEL1_CODE,
            D.CLASSFY_LEVEL2_CODE,
            D.CLASSFY_LEVEL3_CODE,
            D.MANUAL_FLAG,
            D.CREATE_USER,
            D.CREATE_TIME
        </trim>
        when matched then update
        <set>
            MANUAL_FLAG = D.MANUAL_FLAG,
            MODIFY_USER = D.CREATE_USER,
            last_modified_time = D.CREATE_TIME
        </set>
        where
            C.COMMENT_ID = D.COMMENT_ID and
            C.CLASSFY_LEVEL1_CODE = D.CLASSFY_LEVEL1_CODE and
            C.CLASSFY_LEVEL2_CODE = D.CLASSFY_LEVEL2_CODE and
            C.CLASSFY_LEVEL3_CODE = D.CLASSFY_LEVEL3_CODE
</insert>