使用mybatis 对 oracle数据批量插入、更新

正确的mybatis的mapper的sql配置如下：

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

另外一种方法是 insert into table(...) (select ... from dual) union all (select ... from dual)

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

传入的参数只要是list类型的参数就行了。。。。。。。。。。。。。。
1、批量插入

<insert id="insertBatch" parameterType="java.util.List">
    INSERT INTO T_CITY_INDEX(
    id,city_code
    )
    select SEQ_CITY_INDEX.NEXTVAL,cd.* from(
    <foreach collection="list" item="item" index="index"  close=")" open="(" separator="union">
        select
        #{item.cityCode,jdbcType=VARCHAR},
        #{item.cityName,jdbcType=VARCHAR}
        from dual
    </foreach>
    ) cd
</insert>

2、批量跟新

<update id="updateBatch"  parameterType="java.util.List">
    <foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";">
        update T_CITY_INDEX t
        set
        t.city_name= #{item.cityName,jdbcType=VARCHAR} ,
        t.district_name= #{item.districtName,jdbcType=VARCHAR} ,
        where t.id = #{item.id,jdbcType=NUMERIC}
    </foreach>
</update>

https://www.cnblogs.com/feixian/p/5960111.html

https://www.cnblogs.com/xunux/p/4882761.html