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

Introduction

A colleague asked how to write batch‑update code in a real project. The article shares several practical solutions and configuration tricks to make batch updates efficient and reliable.

1. Using foreach for batch update

The following MyBatis XML uses

<foreach>

to generate a single UPDATE statement for each entity in a list, avoiding multiple round‑trips to the database:

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

Compared with a simple

for

loop that issues an UPDATE per iteration, the foreach approach reduces network overhead by sending a single batch request.

2. Other batch‑update approaches

Case‑when method – builds a massive UPDATE with conditional assignments:

<code>&lt;update id="updateForBatch" parameterType="cn.net.susan.entity.sys.UserEntity"&gt;
  UPDATE sys_user
  &lt;trim prefix="set" suffixOverrides=","&gt;
    &lt;trim prefix="password = case id" suffix=","&gt;
      &lt;foreach collection="list" item="item"&gt;
        WHEN #{item.id} THEN #{item.password}
      &lt;/foreach&gt;
    &lt;/trim&gt;
    ... (similar blocks for other columns) ...
  &lt;/trim&gt;
  &lt;where&gt; id IN ( &lt;foreach collection="list" item="item" separator=","&gt;#{item.id}&lt;/foreach&gt; ) &lt;/where&gt;
&lt;/update&gt;
</code>

While flexible, this method generates a very long SQL statement and may have poorer performance.

INSERT … ON DUPLICATE KEY UPDATE – inserts rows and updates existing ones in a single statement:

<code>&lt;update id="updateForBatch" parameterType="cn.net.susan.entity.sys.UserEntity"&gt;
  INSERT INTO sys_user (id, username, password) VALUES
  &lt;foreach collection="list" item="item" separator=","&gt;
    (#{item.id}, #{item.username}, #{item.password})
  &lt;/foreach&gt;
  ON DUPLICATE KEY UPDATE password = VALUES(password)
&lt;/update&gt;
</code>

This requires a unique index on the key column (e.g.,

username

) and works well when the data volume is moderate.

3. Druid WallFilter blocking multi‑statement batch updates

When using the foreach approach, Druid’s

WallFilter

may raise the error

sql injection violation, multi‑statement not allow

. The filter disallows multiple statements in a single SQL string by default.

To enable batch updates, add

&allowMultiQueries=true

to the JDBC URL:

<code>jdbc:mysql://localhost:3306/console?useUnicode=true&amp;characterEncoding=utf-8&amp;useSSL=false&amp;allowMultiQueries=true</code>

And adjust Druid’s filter configuration:

<code>spring:
  datasource:
    druid:
      filter:
        wall:
          config:
            multi-statement-allow: true
            none-base-statement-allow: true
</code>

4. Interaction with ShardingSphere and dynamic datasource

In projects that use ShardingSphere and Baomidou’s dynamic datasource, the Druid configuration must be placed under the same level as the datasource definition. Example YAML snippet:

<code>spring:
  datasource:
    dynamic:
      primary: master
      datasource:
        master:
          url: jdbc:mysql://localhost:3306/susan_mall?serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8&amp;useSSL=false
          driver-class-name: com.mysql.cj.jdbc.Driver
          username: root
          password: 123456
          druid:
            wall:
              multiStatementAllow: true
              noneBaseStatementAllow: true
</code>

After this change, the foreach batch‑update works correctly.

5. Conclusion

The article outlines four batch‑update strategies:

Individual updates inside a for‑loop.

Foreach‑generated batch UPDATE (recommended).

Case‑when conditional UPDATE.

INSERT … ON DUPLICATE KEY UPDATE.

The foreach method provides the best balance of simplicity and performance, but the batch size should be kept under 1,000 rows to avoid excessive load. When encountering the Druid multi‑statement error, enable

allowMultiQueries

in the JDBC URL and set the appropriate WallFilter flags.