MyBatis+Oracle批量更新、插入数据

1.批量更新

<update id="updateBaseProduct" parameterType="List">
    <foreach collection="productCodes" item="item" index="index" open="begin" separator=";" close=";end;">
        UPDATE  t_ams_base_product a
        <set>
            fcom_pro_code=#{item.comProCode}
        </set>
        where
            a.fid=#{item.id}
    </foreach>
</update>

 

2.批量插入

a> 主键不使用序列产生

<insert id="addHandler" parameterType="java.util.List">
    insert into t_task_handler (fremind_id,fremind_user_id,fremind_user_status)
    <foreach collection="list" item="taskHandler" separator="union all">
        select
        #{taskHandler.remindId},#{taskHandler.remindUserId},#{taskHandler.remindStatus}
        from dual
    </foreach>
</insert>

 

b> 主键使用序列产生

<insert id="addAll" parameterType="java.util.List">
    insert into t_scheme_log(fid,fscheme_id, fscheme_type,foperation_time, foperator_id, foperation_type,fperform_content,fremarks)
    select s_t_scheme_log.nextval,cd.* from(
    <foreach collection="list" item="item" index="index" separator="union all">
        select
        #{item.schemeId} fscheme_id,
        #{item.schemeType} fscheme_type,
        #{item.operationTime} foperation_time,
        #{item.operatorId} foperator_id,
        #{item.operationType} foperation_type,
        #{item.performContent} fperform_content,
        #{item.remarks} fremarks
        from dual
    </foreach>
    ) cd
</insert>