Mastering Batch Updates in MyBatis: From foreach to ON DUPLICATE KEY

Introduction

A colleague asked how to write batch‑update code in real projects. The article shares several approaches and tips to help developers.

1. Scene

Someone in the community asked whether to use when case for batch updates or other methods. The author points to a sample batch password‑reset update in a mall project.

<update id="updateForBatch" parameterType="cn.net.susan.entity.sys.UserEntity">
    <foreach collection="list" item="entity" separator=";">
        UPDATE sys_user
        SET password = #{entity.password},
            update_user_id = #{entity.updateUserId},
            update_user_name = #{entity.updateUserName}
        <where>
            id = #{entity.id}
        </where>
    </foreach>
</update>

Compared with a simple Java for loop that issues an update per iteration, the foreach approach sends a single batch to the database, reducing network overhead.

for(UserEntity userEntity : list){
    userMapper.update(userEntity);
}

2. Other Batch‑Update Methods

Some developers prefer the CASE WHEN style, which builds a large UPDATE statement with many conditional assignments. The article provides an example but notes its complexity and poorer performance.

<update id="updateForBatch" parameterType="cn.net.susan.entity.sys.UserEntity">
    update sys_user
    <trim prefix="set" suffixOverrides=",">
        <trim prefix="password = case id" suffix="end,">
            <foreach collection="list" item="item">
                when #{item.id} then #{item.password}
            </foreach>
        </trim>
        ... (similar blocks for update_user_id and update_user_name)
    </trim>
    <where>id in (
        <foreach collection="list" separator="," item="item">
            #{item.id}
        </foreach>
    )</where>
</update>

Another technique uses INSERT ... ON DUPLICATE KEY UPDATE to insert rows and update existing ones in a single statement.

<update id="updateForBatch" parameterType="cn.net.susan.entity.sys.UserEntity">
    insert into sys_user (id,username,password) values
    <foreach collection="list" index="index" item="item" separator=",">
        (#{item.id}, #{item.username}, #{item.password})
    </foreach>
    ON DUPLICATE KEY UPDATE password = values(password)
</update>

This method requires a unique index (e.g., on username) and works well performance‑wise, though it may cause deadlocks in large systems.

3. Encountered Problem

When using the foreach batch update, an exception sql injection violation, multi‑statement not allow was thrown by Druid’s WallFilter. The filter blocks multi‑statement SQL by default.

4. Solution

Enable multi‑statement execution in the JDBC URL:

jdbc:mysql://localhost:3306/console?useUnicode=true&characterEncoding=utf8&useSSL=false&allowMultiQueries=true

For Druid, set the filter configuration to allow multi‑statements:

spring:
  datasource:
    druid:
      filter:
        wall:
          config:
            multi-statement-allow: true
            none-base-statement-allow: true

If the project uses Baomidou’s dynamic datasource, the same properties must be placed under spring.datasource.dynamic.druid.wall so that the filter respects the new settings.

5. Final Recommendations

Four common batch‑update approaches were discussed: individual updates in a loop, foreach‑generated UPDATE, CASE WHEN, and INSERT ON DUPLICATE KEY.

The author recommends the foreach method as the most straightforward and efficient, provided the batch size is kept below about 1,000 rows.

When encountering multi‑statement restrictions, add &allowMultiQueries=true to the JDBC URL and enable multi-statement-allow (and optionally none-base-statement-allow) in Druid’s WallFilter configuration.