Master MyBatis Batch Processing: Common Pitfalls and Optimized Solutions

Introduction

This article guides beginners step‑by‑step through MyBatis batch processing, offering practical tips for handling large data imports such as inserting tens of thousands of rows.

Why Use Batch Processing?

Batch processing reduces network payload and communication overhead by caching multiple SQL statements on the JDBC client and sending them together when the cache is full or flushed, thereby improving system performance.

Be aware that the database imposes a limit on the number of statements per batch; exceeding this limit triggers an exception, so batch size and frequency must be controlled.

Version 1 – Initial Implementation

@Resource
private SomeMapper mapper;
private int BATCH = 1000;
private void doUpdateBatch(Date accountDate, List<SomeEntity> data) {
    SqlSession batchSqlSession = null;
    try {
        if (data == null || data.size() == 0) { return; }
        batchSqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
        for (int i = 0; i < data.size(); i++) {
            mapper.updateOrInsert(accountDate, data.get(i).getOrderNo());
            if (i != 0 && i % BATCH == 0) {
                batchSqlSession.commit();
                batchSqlSession.clearCache();
            }
        }
        batchSqlSession.commit();
    } catch (Exception e) {
        batchSqlSession.rollback();
        log.error(e.getMessage(), e);
    } finally {
        if (batchSqlSession != null) { batchSqlSession.close(); }
    }
}

The above code has several issues, especially the manual commit and clearCache calls.

Understanding commit, clearCache, and flushStatements

The MyBatis commit method internally calls clearLocalCache, so an explicit clearCache after commit is unnecessary. flushStatements forces the cached SQL statements to be sent to the database via statement.executeBatch.

public void commit() { commit(false); }
public void commit(boolean force) {
    try { executor.commit(isCommitOrRollbackRequired(force)); dirty = false; }
    catch (Exception e) { throw ExceptionFactory.wrapException("Error committing transaction.  Cause: " + e, e); }
    finally { ErrorContext.instance().reset(); }
}
private boolean isCommitOrRollbackRequired(boolean force) { return (!autoCommit && dirty) || force; }

Version 2 – Improved Utility

public class MybatisBatchUtils {
    private static final int BATCH = 1000;
    @Resource private SqlSessionFactory sqlSessionFactory;
    public <T> int batchUpdateOrInsert(List<T> data, ToIntFunction<T> function) {
        int count = 0;
        SqlSession batchSqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
        try {
            for (int i = 0; i < data.size(); i++) {
                count += function.applyAsInt(data.get(i));
                if (i != 0 && i % BATCH == 0) { batchSqlSession.flushStatements(); }
            }
            batchSqlSession.commit();
        } catch (Exception e) {
            batchSqlSession.rollback();
            log.error(e.getMessage(), e);
        } finally { batchSqlSession.close(); }
        return count;
    }
}

Usage example:

@Resource private SomeMapper mapper;
batchUtils.batchUpdateOrInsert(dataList, item -> mapper.insert(item));

Version 3 – Standardized Approach

public class MybatisBatchUtils {
    private static final int BATCH_SIZE = 1000;
    @Resource private SqlSessionFactory sqlSessionFactory;
    public <T,U,R> int batchUpdateOrInsert(List<T> data, Class<U> mapperClass, BiFunction<T,U,R> function) {
        int i = 1;
        SqlSession batchSqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
        try {
            U mapper = batchSqlSession.getMapper(mapperClass);
            for (T element : data) {
                function.apply(element, mapper);
                if (i % BATCH_SIZE == 0 || i == data.size()) { batchSqlSession.flushStatements(); }
                i++;
            }
            batchSqlSession.commit(!TransactionSynchronizationManager.isSynchronizationActive());
        } catch (Exception e) {
            batchSqlSession.rollback();
            throw new CustomException(e);
        } finally { batchSqlSession.close(); }
        return i - 1;
    }
}

This version detects transaction context and forces a commit only when not in a transactional environment.

Oracle Batch Insert Optimization

Oracle requires a sequence for primary keys. Using MyBatis Generator’s <selectKey> leads to two database interactions per row (insert + sequence query), which can take >10 seconds for 10 k rows. Switching to native batch inserts reduces the time to ~0.5 seconds.

<insert id="insert" parameterType="user">
    insert into table_name(id, username, password)
    values(SEQ_USER.NEXTVAL, #{username}, #{password})
</insert>

After applying batch processing and the above SQL optimization, the operation time dropped from several minutes to just a few seconds.

Conclusion

Proper use of MyBatis batch execution, correct handling of commit, flushStatements, and database‑specific optimizations can dramatically improve bulk data processing performance.