Optimizing MyBatis Batch Inserts with ExecutorType.BATCH and foreach Limits

A long‑running job in a Spring Boot + MyBatis project was found to consume excessive CPU because the bulk insert was implemented with a MyBatis <foreach> that generated a single massive SQL statement. When inserting thousands of rows (e.g., 5000+), the generated statement becomes huge, causing costly parsing, placeholder mapping, and hitting database limits.

The traditional approach of issuing many separate INSERT INTO ... VALUES (...) statements is inefficient. Combining many rows into one INSERT ... VALUES statement reduces round‑trips but still creates an overly large statement if the batch size is too big.

Experiments showed that using MyBatis ExecutorType.BATCH dramatically improves performance, completing the same workload in under 2 seconds. The recommended pattern is to open a session with batch executor and insert each record individually, letting MyBatis batch the statements at the JDBC level:

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

When the <foreach> method must be used, the number of rows per statement should be limited to roughly 20‑50 to avoid the performance penalty.

An alternative MyBatis dynamic‑SQL batch insert (recommended by MyBatis documentation) builds a series of small insert statements and executes them with a batch executor:

SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
try {
    SimpleTableMapper mapper = session.getMapper(SimpleTableMapper.class);
    List
records = getRecordsToInsert();
    BatchInsert
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();
}

The same effect can be achieved directly with JDBC batch execution:

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();

In summary, for high‑performance bulk inserts in MyBatis:

Prefer ExecutorType.BATCH with individual inserts.

If using <foreach> , keep each batch to 20‑50 rows.

Consider MyBatis dynamic‑SQL batch support or plain JDBC batch for the best results.