快捷導(dǎo)航

MyBatis批量操作XML的完整實(shí)現(xiàn)方案

更新時(shí)間：2025年12月19日 09:15:48 作者：Full Stack Developme

本文介紹了MyBatis通過XML映射文件實(shí)現(xiàn)批量插入、更新和刪除的完整方案,包括單條SQL批量插入、批量插入并返回主鍵、批量更新、批量刪除以及動(dòng)態(tài)批量操作,最后,分享了最佳實(shí)踐建議,需要的朋友可以參考下

1. 批量插入(insert)

1.1 單條SQL批量插入(推薦)

<insert id="batchInsert" parameterType="java.util.List">
    INSERT INTO user(name, age, email) VALUES
    <foreach collection="list" item="item" separator=",">
        (#{item.name}, #{item.age}, #{item.email})
    </foreach>
</insert>

1.2 批量插入并返回主鍵(MySQL)

<insert id="batchInsert" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO user(name, age, email) VALUES
    <foreach collection="list" item="item" separator=",">
        (#{item.name}, #{item.age}, #{item.email})
    </foreach>
</insert>

1.3 批量插入(Oracle特殊語法)

<insert id="batchInsert">
    INSERT ALL
    <foreach collection="list" item="item">
        INTO user(id, name, age, email) 
        VALUES(#{item.id}, #{item.name}, #{item.age}, #{item.email})
    </foreach>
    SELECT 1 FROM DUAL
</insert>

2. 批量更新(update)

2.1 使用CASE WHEN批量更新(MySQL)

<update id="batchUpdate" parameterType="java.util.List">
    UPDATE user
    <trim prefix="SET" suffixOverrides=",">
        <trim prefix="name = CASE" suffix="END,">
            <foreach collection="list" item="item">
                WHEN id = #{item.id} THEN #{item.name}
            </foreach>
        </trim>
        <trim prefix="age = CASE" suffix="END,">
            <foreach collection="list" item="item">
                WHEN id = #{item.id} THEN #{item.age}
            </foreach>
        </trim>
        <trim prefix="email = CASE" suffix="END">
            <foreach collection="list" item="item">
                WHEN id = #{item.id} THEN #{item.email}
            </foreach>
        </trim>
    </trim>
    WHERE id IN
    <foreach collection="list" item="item" open="(" separator="," close=")">
        #{item.id}
    </foreach>
</update>

2.2 批量更新(Oracle)

<update id="batchUpdate" parameterType="java.util.List">
    <foreach collection="list" item="item" open="BEGIN" close=";END;" separator=";">
        UPDATE user SET
            name = #{item.name},
            age = #{item.age},
            email = #{item.email}
        WHERE id = #{item.id}
    </foreach>
</update>

3. 批量刪除(delete)

3.1 普通批量刪除

<delete id="batchDelete" parameterType="java.util.List">
    DELETE FROM user WHERE id IN
    <foreach collection="list" item="item" open="(" separator="," close=")">
        #{item.id}
    </foreach>
</delete>

3.2 批量刪除(Oracle)

<delete id="batchDelete" parameterType="java.util.List">
    <foreach collection="list" item="item" open="BEGIN" close=";END;" separator=";">
        DELETE FROM user WHERE id = #{item.id}
    </foreach>
</delete>

4. 動(dòng)態(tài)批量操作

4.1 動(dòng)態(tài)批量插入(根據(jù)條件)

<insert id="dynamicBatchInsert">
    INSERT INTO user(name, age, email) VALUES
    <foreach collection="list" item="item" separator=",">
        <if test="item.name != null and item.email != null">
            (#{item.name}, #{item.age}, #{item.email})
        </if>
    </foreach>
</insert>

4.2 批量更新非空字段

<update id="batchUpdateSelective" parameterType="java.util.List">
    UPDATE user
    <trim prefix="SET" suffixOverrides=",">
        <trim prefix="name = CASE" suffix="END,">
            <foreach collection="list" item="item">
                <if test="item.name != null">
                    WHEN id = #{item.id} THEN #{item.name}
                </if>
            </foreach>
        </trim>
        <trim prefix="age = CASE" suffix="END,">
            <foreach collection="list" item="item">
                <if test="item.age != null">
                    WHEN id = #{item.id} THEN #{item.age}
                </if>
            </foreach>
        </trim>
    </trim>
    WHERE id IN
    <foreach collection="list" item="item" open="(" separator="," close=")">
        #{item.id}
    </foreach>
</update>