数据库操作_批量新增/修改

MyBatis相关拼接语法

1. 批量新增

批量新增table_A表中的（column1、column2、column3）三列

<insert id="saveBatch" parameterType="java.util.List">
        insert into table_A (column1,column2,column3)
        values
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.property1}, #{item.property2}, #{item.property3})
        </foreach>
</insert>

拼接sql展示：

       insert into table_A (column1,column2,column3)
       values
       (a.property1, a.property2, a.property3),
       (b.property1, b.property2, b.property3),
       (c.property1, c.property2, c.property3)
       ...

2. 批量修改

接收参数：DO的List集合，根据DO的主键id批量修改（column1、column2、column3）三列

<update id="updateBatch" parameterType="java.util.List">
        update table_A
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="column1=case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    <if test="item.property1 != null">
                        when id=#{item.id} then #{item.property1}
                    </if>
                </foreach>
            </trim>
            <trim prefix="column2=case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    <if test="item.property2 != null">
                        when id=#{item.id} then #{item.property2}
                    </if>
                </foreach>
            </trim>
            <trim prefix="column3=case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                    <if test="item.property3 != null">
                        when id=#{item.id} then #{item.property3}
                    </if>
                </foreach>
            </trim>
        </trim>
        where
        <foreach collection="list" separator="or" item="item" index="index">
            id=#{item.id}
        </foreach>
</update>

拼接sql展示：

    UPDATE table_A
    SET 
    column1 = CASE id 
        WHEN 1 THEN 'a.property1'
        WHEN 2 THEN 'a.property2'
        WHEN 3 THEN 'a.property3'
        ...
    END, 
    column2 = CASE id 
        WHEN 1 THEN 'b.property1'
        WHEN 2 THEN 'b.property2'
        WHEN 3 THEN 'b.property3'
        ...
    END
    ...
    WHERE 
    id IN (1,2,3...)

3. 批量新增的时候希望通过“主键/唯一索引”保证数据某些列的唯一性，有重复的时候则更新

• table_A执行批量操作；
• table_A其中有唯一索引 “UNIQUE KEY uniqueKey (column_1, column_2) USING BTREE”；
• 当column_1和column_2两列决定的唯一索引出现重复，则执行更新操作更新column_3和column_4

    <insert id="batchInsertOrUpdate">
        insert into table_A(column_1, column_2, column_3, column_4) values
        <foreach collection="list" index="index" item="item" separator=",">
            (#{item.property1}, #{item.property2}, #{item.property3}, #{item.property4})
        </foreach>
        on duplicate key update
        column_3 = values(column_3), column_4 = values(column_4)
    </insert>