当前位置：首页 > web >正文

MyBatis 批量操作 XML 实现方式

web 2025/8/5 15:52:25

MyBatis 通过 XML 映射文件实现批量插入(insert)、更新(update)和删除(delete)的完整方案

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 SETname = #{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. 动态批量操作

4.1 动态批量插入(根据条件)

<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>