Mybatis基于Oracle数据库实现批量插入更新的几种方式

1：MySQL

首先我们说如何基于MySQL实现批量插入：

    <insert id="insertBatch">
        INSERT INTO LY_TEST (id, name, age )
        VALUES
        <foreach collection ="list" item="user" separator ="," close = ";">
            (#{user.id}, #{user.name}, #{user.age})
        </foreach>
    </insert>

2：Oracle

2.1：第一种使用INSERT ALL

  INSERT ALL
        <foreach collection="dtoList" item="item" separator="">
            INTO LY_SJS_WDJZ_JZXJ_CYRYXX (BH, JZBH, XGH, XM, SSXYBH, SSXYMC,BMSJ,QDSJ,SFSC,SFXNRY,CZSJ)
            VALUES
            (
            #{item.id, jdbcType=VARCHAR},
            #{item.lectureNumber, jdbcType=VARCHAR},
            #{item.userId, jdbcType=VARCHAR},
            #{item.name, jdbcType=VARCHAR},
            #{item.departmentId, jdbcType=VARCHAR},
            #{item.departmentName, jdbcType=VARCHAR},
            #{item.visitTime, jdbcType=VARCHAR},
            #{item.signInTime, jdbcType=VARCHAR},
            #{item.isDeleted, jdbcType=VARCHAR},
            #{item.isSchoolPeople, jdbcType=VARCHAR},
            #{item.operateTime,jdbcType=VARCHAR}
            )
        </foreach>
        SELECT 1 FROM DUAL

2.2：外嵌foreach标签

<foreach collection="list" item="item" separator=";" open="begin" close=";end;">
            INSERT INTO LY_SJS_WDZM_YXLXD_SXLB_HISTORY (id,SSBM,yxsxlb,yxsxlx,sfsc,bbh,SXLBID,wjm,czr,czsj)
            VALUES
            (
            #{item.id, jdbcType=VARCHAR},
            #{item.department,jdbcType=VARCHAR},
            #{item.thingsTypeName, jdbcType=VARCHAR},
            #{item.isRoutine, jdbcType=VARCHAR},
            #{item.status, jdbcType=VARCHAR},
            #{item.version, jdbcType=NUMERIC},
            #{item.thingsTypeId, jdbcType=VARCHAR},
            #{item.filename, jdbcType=VARCHAR},
            #{item.operator, jdbcType=VARCHAR},
            #{item.operateDate, jdbcType=VARCHAR}
            )
        </foreach>

 

2.3：第二种使用Oracle的MERGE语句批量插入

<insert id="batchMergeInsert" parameterType="list">
    MERGE INTO LY_TEST t
    USING (SELECT #{user.id} AS id, #{user.name} AS name, #{user.age} AS age FROM dual) s
    ON (t.id = s.id)
    WHEN NOT MATCHED THEN
    INSERT (id, name, age)
    VALUES (s.id, s.name, s.age)
    <foreach collection="list" item="user" separator="UNION ALL">
        SELECT #{user.id}, #{user.name}, #{user.age} FROM dual
    </foreach>
</insert>


    <update id="updateGuidePage" parameterType="string">
        MERGE INTO LY_SJS_WDZM_YXLXD_YDYM dest
            USING (SELECT #{userId, mode=IN, jdbcType=VARCHAR} AS USERID, '0' AS YXYM
                   FROM DUAL) src
            ON (dest.USERID = #{userId, mode=IN, jdbcType=VARCHAR})
            WHEN MATCHED THEN
                UPDATE SET dest.YXYM = '0'
            WHEN NOT MATCHED THEN
                INSERT (USERID, YXYM)
                    VALUES (#{userId, mode=IN, jdbcType=VARCHAR}, '0')
    </update>

补充：

批量删除：

     <delete id="xxx" parameterType="list">
        delete from LY_TEST
        where id in
        <foreach collection="list" item="item" open="(" close=")" separator=",">
            #{item}
        </foreach>
     </delete>

批量更新：

 <update id="batchSaveDraft">
        <foreach collection="list" item="item" separator=";" open="begin" close=";end;">
            UPDATE LY_SJS SET SFSC = '2',CZSJ = SYSDATE,
            NETID = #{item.userId},
       
            WHERE SFSC = '3'
            AND ID = #{item.id}
        </foreach>
    </update>