MyBatis 批量新增（INSERT）与批量新增及修改(MERGE INTO)

最新推荐文章于 2025-02-27 14:49:59 发布

石图

最新推荐文章于 2025-02-27 14:49:59 发布

阅读量692

点赞数

分类专栏： Spring boot MyBatis

本文链接：https://blog.youkuaiyun.com/cuierdan/article/details/109583648

版权

Spring boot 同时被 2 个专栏收录

2 篇文章

订阅专栏

MyBatis

1 篇文章

订阅专栏

批量新增（INSERT）

    <!-- 批量插入成本表 -->
    <insert id="insertCostAccountOld" parameterType="java.util.Map">
        INSERT INTO
        COST_ACCOUNT (SUMMARY_MONTH, COST_CODE, COST_NAME, DEPT_CODE, DEPT_NAME, PRICE_SUM)
        select item.* from
        (
        <foreach collection="list" item="item" index="index" separator="union all">
            select
            <if test="item.summary_month != null"> #{item.summary_month}, </if>
            <if test="item.cost_code != null"> #{item.cost_code}, </if>
            <if test="item.cost_name != null"> #{item.cost_name}, </if>
            <if test="item.dept_code != null"> #{item.dept_code}, </if>
            <if test="item.dept_name != null"> #{item.dept_name}, </if>
            <if test="item.price_sum != null"> #{item.price_sum} </if>
            from dual
        </foreach>
        ) item
    </insert>

批量新增及修改(MERGE INTO)

    <!-- 批量插入或更新成本表 -->
    <insert id="insertUpdateCostAccount" parameterType="java.util.List">
        MERGE INTO COST_ACCOUNT T1
        USING (
        <foreach collection="list" item="item" index="index" separator="union" >
            SELECT
            TEMP.SUMMARY_MONTH,
            TEMP.COST_CODE,
            TEMP.COST_NAME,
            TEMP.DEPT_CODE,
            TEMP.DEPT_NAME,
            TEMP.PRICE_SUM
            FROM
            (SELECT
            #{item.summary_month} SUMMARY_MONTH,
            #{item.cost_code} COST_CODE,
            #{item.cost_name} COST_NAME,
            #{item.dept_code} DEPT_CODE,
            #{item.dept_name} DEPT_NAME,
            #{item.price_sum} PRICE_SUM
            FROM DUAL) TEMP
            LEFT JOIN
            (SELECT * FROM  COST_ACCOUNT T2
            WHERE  T2.COST_CODE = #{item.cost_code} AND T2.DEPT_CODE = #{item.dept_code} AND T2.SUMMARY_MONTH = #{item.summary_month}
            )TEMP1 ON TEMP1.COST_CODE = TEMP.COST_CODE AND TEMP1.DEPT_CODE = TEMP.DEPT_CODE AND TEMP1.SUMMARY_MONTH = TEMP.SUMMARY_MONTH
        </foreach>
        ) T
        ON (T1.COST_CODE = T.COST_CODE AND T1.DEPT_CODE = T.DEPT_CODE AND T1.SUMMARY_MONTH = T.SUMMARY_MONTH)
        WHEN MATCHED THEN
        UPDATE SET T1.PRICE_SUM = T1.PRICE_SUM
        WHEN NOT MATCHED THEN
        INSERT (SUMMARY_MONTH, COST_CODE, COST_NAME, DEPT_CODE, DEPT_NAME, PRICE_SUM)
        VALUES (T.SUMMARY_MONTH, T.COST_CODE, T.COST_NAME, T.DEPT_CODE, T.DEPT_NAME, T.PRICE_SUM)
    </insert>