Three MyBatis Batch Insert Methods and Their Performance Comparison

MyBatis offers three primary approaches for batch insertion: ordinary insert (single‑row execution), foreach‑optimized insert (generating a large single SQL statement), and using ExecutorType.BATCH (reusing a prepared statement for multiple executions).

1. Ordinary Insert

The default method iterates over each INSERT statement, creating a new PreparedStatement for every row, which leads to low efficiency, especially with massive data volumes.

INSERT INTO `table1` (`field1`,`field2`) VALUES ("data1","data2");
INSERT INTO `table1` (`field1`,`field2`) VALUES ("data1","data2");
...

SQL logs show each statement is prepared and executed separately, confirming the performance drawback.

2. foreach Optimized Insert

By combining many rows into a single INSERT statement using MyBatis <foreach>, the number of round‑trips to the database is reduced. However, when the column count is high (20+) and rows exceed thousands, the generated SQL becomes extremely long, causing parsing overhead and hitting MySQL's packet size limit (default 4 MB).

<insert id="batchInsert" parameterType="java.util.List">
  INSERT INTO table1 (field1, field2) VALUES
  <foreach collection="list" item="t" separator=",">
    (#{t.field1}, #{t.field2})
  </foreach>
</insert>

Resulting SQL example:

INSERT INTO `table1` (`field1`,`field2`) VALUES ("data1","data2"), ("data1","data2"), ...;

Because the statement grows exponentially with placeholders, parsing and parameter mapping become costly, leading to execution times of many minutes for 5 000+ rows.

3. ExecutorType.BATCH Insert

MyBatis provides three executor types: SIMPLE, REUSE, and BATCH. The BATCH executor reuses a single prepared statement and batches parameter sets, dramatically improving throughput. Example usage:

SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
PatientLabelDetailMapper mapper = sqlSession.getMapper(PatientLabelDetailMapper.class);
int BATCH = 1000;
for (int i = 0; i < data.size(); i++) {
    mapper.insert(data.get(i));
    if (i % BATCH == 0 && i != 0) {
        sqlSession.commit();
    }
}
sqlSession.commit();

The commit() method internally clears the local cache and flushes statements, so explicit calls to clearCache() or flushStatements() are unnecessary.

Conclusion

For large‑scale inserts, the ExecutorType.BATCH approach is recommended due to its superior performance and lower resource consumption. When using foreach, limit the number of rows per statement (typically 20‑50) to avoid excessive SQL size and parsing overhead. Also, ensure MySQL's max_allowed_packet is configured appropriately.