Batch Insert/Update Patterns

When performing batch inserts or updates in MyBatis, executing row-by-row causes many network round-trips and transaction overhead. Using ExecutorType.BATCH or foreach with a single multi-row SQL reduces round-trips and improves throughput. This article explains both approaches, when to use each, and practical considerations with examples and a comparison table.

Overview

• ExecutorType.BATCH: Within a single SqlSession, multiple insert or update calls are batched at the JDBC driver level. They are sent to the database on flushStatements() or commit, reducing round-trips. Best when you loop and call Mapper methods repeatedly, or when each row has different logic.
• foreach (single SQL, multiple rows): Use <foreach> in XML to build one SQL like INSERT INTO t (a,b) VALUES (1,2),(3,4),... or UPDATE ... CASE WHEN .... One round-trip inserts or updates many rows. Best for medium-sized batches (hundreds to a few thousand rows). Keep single-SQL size within database limits (e.g. max_allowed_packet, parameter count).
• Choice: Use foreach for small, simple batches. Use BATCH when the batch is large or when each row needs different logic. Both can be combined with chunking to avoid oversized operations.

Example

Example 1: foreach batch insert

XMLCopy
<insert id="batchInsert" useGeneratedKeys="true" keyProperty="id">
  INSERT INTO orders (user_id, amount, status)
  VALUES
  <foreach collection="list" item="o" separator=",">
    (#{o.userId}, #{o.amount}, #{o.status})
  </foreach>
</insert>

JavaCopy
List<Order> list = buildOrderList();
mapper.batchInsert(list);  // Single SQL inserts all rows; keep list size reasonable (e.g. &lt; 1000)

• Use #{} for all parameters to avoid SQL injection. useGeneratedKeys with keyProperty fills generated IDs into the objects. Some databases/drivers only return the last ID for multi-row inserts; check your driver docs.

Example 2: ExecutorType.BATCH

JavaCopy
try (SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
    OrderMapper mapper = session.getMapper(OrderMapper.class);
    for (Order o : list) {
        mapper.insert(o);
    }
    session.flushStatements();
    session.commit();
}

• Multiple insert calls are batched at the driver level, reducing round-trips. Note: In BATCH mode, some drivers do not return generated keys immediately; if you need per-row IDs, use foreach or query after each chunk.

Example 3: Chunking to avoid oversized SQL

JavaCopy
int chunkSize = 500;
for (int i = 0; i < list.size(); i += chunkSize) {
    List<Order> sub = list.subList(i, Math.min(i + chunkSize, list.size()));
    mapper.batchInsert(sub);
}

• Process 500 rows per batch. This limits SQL size and avoids long transactions and locks. Tune chunk size (e.g. 200–1000) based on your database and network.

Example 4: Batch update with CASE (MySQL)

XMLCopy
<update id="batchUpdateStatus">
  UPDATE orders SET status = CASE id
    <foreach collection="list" item="o" separator=" ">
      WHEN #{o.id} THEN #{o.status}
    </foreach>
  END
  WHERE id IN
  <foreach collection="list" item="o" open="(" separator="," close=")">#{o.id}</foreach>
</update>

• Updates multiple rows in one statement. Use #{} for all values.

Example 5: Comparison

Approach	Pros	Considerations
foreach (single SQL)	One round-trip, simple	SQL length and parameter limits; limited useGeneratedKeys support for multi-row in some DBs
BATCH Executor	Fewer round-trips, flexible per-row logic	Generated keys may not be returned per row; must call flushStatements/commit
Chunking	Controls transaction and SQL size	Tune chunk size (e.g. 200–1000) for DB and network

Core Mechanism / Behavior

• foreach: MyBatis expands the collection into multiple (?,?,?) groups and binds via PreparedStatement placeholders. This is still parameterized and safe; no SQL injection.
• BATCH: Uses JDBC addBatch/executeBatch. The driver may batch statements and send them on commit. MyBatis does not auto-flush; you must call flushStatements() or commit().
• Transaction: Run a batch within one transaction. When chunking, one transaction per chunk avoids very long transactions.

Key Rules

• Use #{} for all batch parameters; never use ${} to inline values (injection and escaping risks).
• Keep per-SQL row count moderate (e.g. 500–2000); chunk if needed. Respect max_allowed_packet and parameter limits.
• For per-row generated keys: prefer foreach with database support for multi-row useGeneratedKeys, or BATCH with a follow-up query per chunk. Do not assume BATCH returns IDs per insert.

What's Next

See Dynamic SQL for conditional batch updates (e.g. <foreach> + CASE). See Parameter Binding for safe parameter usage.