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

置顶入夏忆梦

已于 2024-01-26 17:07:25 修改

阅读量5.9k

点赞数 42

文章标签：数据库 mybatis oracle

于 2023-11-03 14:59:57 首次发布

本文链接：https://blog.csdn.net/XikYu/article/details/134203099

版权

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 id="insertBatch">
        INSERT ALL
        <foreach collection="list" item="user" separator=" "
                 close="SELECT * FROM dual" index="index">
            INTO LY_TEST (id, name, age)
            VALUES (#{user.id}, #{user.name}, #{user.age})
        </foreach>
    </insert>

2.2：第二种

    <insert id="insertBatch">
        INSERT INTO LY_TEST (id, name, age )
        <foreach collection ="list" item="user" index="index" separator ="union all">
            (SELECT #{user.id,jdbcType=INTEGER},
             #{user.name,jdbcType=VARCHAR},
             #{user.age,jdbcType=INTEGER}
            from dual)
        </foreach>
    </insert>

2.3：第三种

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

2.4 ：第四种

        INSERT ALL
        <foreach collection="list" item="item" separator="">
            INTO LY_SJS_WDZM_YXLXD_SXLB_ZX_YZ (SXLBID, YZLXID, ZXCLID, ZT, BBH)
            VALUES
            (
            #{item.thingsTypeId, jdbcType=VARCHAR},
            #{item.thingsYzId, jdbcType=VARCHAR},
            #{item.thingsClId, jdbcType=VARCHAR},
            #{item.status, jdbcType=VARCHAR},
            #{item.version, jdbcType=NUMERIC}
            )
        </foreach>
        SELECT 1 FROM DUAL

补充：

批量删除：

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