❤️oracle mybatis 批量更新和新增

小明的学习圈子2023-08-13后端批量更新

oracle mybatis 批量更新和新增

<insert id ="insertQuasiPersonList" parameterType="map" >

<foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";">

insert into T_CADRE_QUASI_PERSON(
<trim prefix="" suffixOverrides=",">

<if test="item.NUM_ID !=null and item.NUM_ID!=''">NUM_ID,</if>
<if test="item.PROCESS_ID !=null and PROCESS_ID!=''">PROCESS_ID,</if>
<if test="item.EMP_ID !=null and EMP_ID!=''">EMP_ID,</if>
<if test="item.BASE_INFO_ID !=null and item.BASE_INFO_ID!=''">BASE_INFO_ID,</if>
<if test="item.NUMBER_ID !=null and item.NUMBER_ID !=''">NUMBER_ID,</if>
<if test="item.PERSON_POST !=null and item.PERSON_POST !=''">PERSON_POST,</if>
<if test="item.QUASI_STAGE !=null and item.QUASI_STAGE !=''">QUASI_STAGE,</if>
<if test="item.CREATE_BY!=null and item.CREATE_BY!=''">CREATE_BY,</if>
<if test="item.CREATE_DATE!=null and item.CREATE_DATE!=''">CREATE_DATE,</if>
<if test="item.DEL_FLAG!=null and item.DEL_FLAG!=''">DEL_FLAG,</if>
</trim>
)values(
<trim prefix="" suffixOverrides=",">

<if test="item.NUM_ID !=null and item.NUM_ID !=''">#{item.NUM_ID},</if>
<if test="item.PROCESS_ID !=null and item.PROCESS_ID !=''">#{item.PROCESS_ID},</if>
<if test="item.EMP_ID !=null and item.EMP_ID !=''">#{item.EMP_ID},</if>
<if test="item.BASE_INFO_ID !=null and item.BASE_INFO_ID !=''">#{item.BASE_INFO_ID},</if>
<if test="item.NUMBER_ID !=null and item.NUMBER_ID !=''">#{item.NUMBER_ID},</if>
<if test="item.PERSON_POST !=null and item.PERSON_POST !=''">#{item.PERSON_POST},</if>
<if test="item.QUASI_STAGE !=null and item.QUASI_STAGE !=''">#{item.QUASI_STAGE},</if>
<if test="item.CREATE_BY!=null and item.CREATE_BY!=''">#{item.CREATE_BY},</if>
<if test="item.CREATE_DATE !=null and item.CREATE_DATE !=''">#{item.CREATE_DATE},</if>
<if test="item.DEL_FLAG!=null and item.DEL_FLAG!=''">#{item.DEL_FLAG},</if>
</trim>
)
</foreach>

</insert >
===========================

<if test="BASE_INFO_ID != null and BASE_INFO_ID != ''">
AND rp.NUM_ID IN
<foreach collection="BASE_INFO_ID" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>

</if>
=================

<update id="agreedsPartTimeList" parameterType="map">
<foreach collection="NUM_ID" item="item" index="index" open="begin" close=";end;" separator=";">

update T_CADRE_PART_TIME

<set>
STATE=(CASE
WHEN (select serial from T_CADRE_APPROVAL where PART_TIME_ID = #{item} and APPROVAL_TYPE = #{APPROVALTYPESAGREEDS})
=(select max(serial) from T_CADRE_APPROVAL where PART_TIME_ID = #{item}) THEN '4'
ELSE '1'
END),UPDATE_BY=#{UPDATE_BY},UPDATE_DATE=#{UPDATE_DATE}

</set>

where NUM_ID = #{item}
</foreach>
</update>

=====================================================================================

Mybatis 向oracle批量插入与更新数据

插入

<insert id="batchSave" parameterType="java.util.List">
        INSERT INTO T_UPLOAD_CELL_DATA (CELL_SN, PRODUCT_SN, TEST_DATE,
        VOLTAGE_VALUE)
        SELECT A.*
        FROM(
        <foreach collection="list" item="item" index="index"
            separator="UNION ALL">
            SELECT
            #{item.cellSn} CELL_SN,
            #{item.productSn} PRODUCT_SN,
            #{item.testDate} TEST_DATE,
            #{item.voltageValue} VOLTAGE_VALUE
            FROM
            dual
        </foreach>
        )A
</insert>

更新

<update id="batchUpdate" parameterType="java.util.List">
        <foreach collection="list" item="item" index="index" open="begin"
            close=";end;" separator=";">
            update T_UPLOAD_CELL_DATA t
            set
            t.PRODUCT_SN=#{item.productSn},
            t.TEST_DATE=#{item.testDate},
            t.VOLTAGE_VALUE=#{item.voltageValue}
            where t.CELL_SN = #{item.cellSn}
        </foreach>
</update>
Last Updated 2024/4/6 10:47:15