Improving MyBatis Batch Insert Performance with ExecutorType.BATCH and foreach Optimization

Recently a job in a project suffered high CPU usage due to a MyBatis batch insert that built a huge single INSERT statement using <foreach> in the mapper XML.

The original approach generated many individual INSERT statements or a single statement with thousands of VALUES, which caused long parsing and placeholder‑mapping time, especially when inserting 5000+ rows.

Best practice is to limit the number of VALUES per statement to 20‑50 rows, or better, use MyBatis ExecutorType.BATCH so that each row is inserted with a prepared statement executed in batch mode.

Example of a foreach mapper:

<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>

Using a single multi‑row INSERT reduces round‑trips but still creates a massive statement; MySQL documentation recommends combining rows only up to a reasonable size.

With MyBatis batch executor:

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

Alternatively, MyBatis Dynamic SQL can generate a series of small insert statements and execute them via a mapper.

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")
        // ... other columns
        .build()
        .render(RenderingStrategy.MYBATIS3);
    batchInsert.insertStatements().forEach(mapper::insert);
    session.commit();
} finally {
    session.close();
}

Plain JDBC batch example:

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

Experiments show that using ExecutorType.BATCH reduces the insertion time to under 2 seconds for thousands of rows, while the foreach approach should keep each batch to about 20‑50 rows for acceptable performance.

In summary, prefer MyBatis batch executor for bulk inserts; if <foreach> must be used, limit the batch size to 20‑50 rows.