How to Supercharge MyBatis Batch Inserts: Tips, Tricks, and ExecutorType.BATCH

Problem Overview

A long‑running job in a Java project showed high CPU usage because a MyBatis mapper used a <foreach> loop to batch insert thousands of rows. The mapper XML looked like this:

<insert id="batchInsert" parameterType="java.util.List">
  insert into USER (id, name) values
  <foreach collection="list" item="model" index="index" separator=",">
    (#{model.id},{model.name})
  </foreach>
</insert>

The approach concatenates many individual INSERT statements into a single gigantic SQL command.

Why the foreach Approach Is Slow

MySQL documentation recommends combining many small inserts into one large statement, but when the statement contains thousands of rows and dozens of columns, the generated PreparedStatement becomes huge. Each execution requires MyBatis to parse the statement, build parameter mappings, and cannot cache the statement because the <foreach> element makes the SQL vary each time. This leads to exponential growth in parsing time and can hit database limits (e.g., ~2000 parameters per statement).

Practically, inserting 5,000+ rows with 20+ columns took about 14 minutes.

Better Approach: Java Loop with ExecutorType.BATCH

Instead of iterating in the XML, execute a simple insert inside a Java for loop while using MyBatis’s batch executor. The executor prepares the statement once and reuses it for each row.

SqlSession session = sessionFactory.openSession(ExecutorType.BATCH);
for (Model model : list) {
    session.insert("insertStatement", model);
}
session.flushStatements();

The default ExecutorType.SIMPLE creates a new PreparedStatement for every row, which is why it is much slower.

Using MyBatis Dynamic SQL Batch Insert Support

MyBatis’s dynamic‑SQL module provides a fluent API for batch inserts that also works with ExecutorType.BATCH:

SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
try {
    SimpleTableMapper mapper = session.getMapper(SimpleTableMapper.class);
    List<SimpleTableRecord> records = getRecordsToInsert();
    BatchInsert<SimpleTableRecord> batchInsert = insert(records)
        .into(simpleTable)
        .map(id).toProperty("id")
        .map(firstName).toProperty("firstName")
        .map(lastName).toProperty("lastName")
        .map(birthDate).toProperty("birthDate")
        .map(employed).toProperty("employed")
        .map(occupation).toProperty("occupation")
        .build()
        .render(RenderingStrategy.MYBATIS3);
    batchInsert.insertStatements().forEach(mapper::insert);
    session.commit();
} finally {
    session.close();
}

JDBC Batch Example

When using plain JDBC, enable statement rewriting for MySQL and add batches manually:

Connection connection = DriverManager.getConnection(
    "jdbc:mysql://127.0.0.1:3306/mydb?useUnicode=true&characterEncoding=UTF-8&useServerPrepStmts=false&rewriteBatchedStatements=true",
    "root", "root");
connection.setAutoCommit(false);
PreparedStatement ps = connection.prepareStatement("insert into tb_user (name) values(?)");
for (int i = 0; i < stuNum; i++) {
    ps.setString(1, name);
    ps.addBatch();
}
ps.executeBatch();
connection.commit();
connection.close();

Performance Result

Using ExecutorType.BATCH reduced the total insertion time to under 2 seconds for the same data set.

Conclusion

For MyBatis batch inserts, prefer the ExecutorType.BATCH executor with Java‑side loops or the Dynamic SQL batch API. If you must use the <foreach> style, keep each batch to roughly 20‑50 rows to avoid the massive parsing overhead.