Understanding MyBatis Batch Processing: Common Pitfalls and Optimized Solutions

The article introduces MyBatis batch processing, describing how batching reduces network overhead and improves performance when inserting large numbers of records.

It starts with a real‑world scenario: an automatic reconciliation feature that must insert tens of thousands of rows, potentially scaling to hundreds of thousands, requiring an efficient batch solution.

It explains the concept of ExecutorType.BATCH and the role of BatchExecutor, emphasizing the need to limit the number of statements per batch to avoid database limits.

Each batch must stay within the database's maximum allowed statement count; exceeding it causes exceptions.

Version 1 – Initial implementation shows pseudo‑code that manually commits and clears the cache every 1000 rows, which leads to several problems.

@Resource
private 某Mapper类 mapper实例对象;
private int BATCH = 1000;
private void doUpdateBatch(Date accountDate, List<某实体类> data) {
    SqlSession batchSqlSession = null;
    try {
        if (data == null || data.size() == 0) return;
        batchSqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
        for (int index = 0; index < data.size(); index++) {
            mapper实例对象.更新/插入Method(accountDate, data.get(index).getOrderNo());
            if (index != 0 && index % 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 article then dives into the MyBatis source to show what commit, clearCache and flushStatements actually do.

@Override
public void commit() {
    commit(false);
}

@Override
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();
    }
}

public void commit(boolean required) throws SQLException {
    if (closed) throw new ExecutorException("Cannot commit, transaction is already closed");
    clearLocalCache();
    flushStatements();
    if (required) transaction.commit();
}

Key insight: calling commit already performs a clearLocalCache, so an explicit clearCache after commit is unnecessary and can even be harmful.

Version 2 – Utility class abstracts the batch logic into a reusable method that flushes every 1000 rows and handles exceptions.

@Component
@Slf4j
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:

batchUtils.batchUpdateOrInsert(dataList, item -> mapper.insert(item));

Version 3 – Standard approach stresses that the mapper must be obtained from a batch‑enabled SqlSession (created via sqlSessionFactory.openSession(ExecutorType.BATCH)) so that the underlying BatchExecutor is used.

public Executor newExecutor(Transaction transaction, ExecutorType executorType) {
    executorType = executorType == null ? defaultExecutorType : executorType;
    Executor executor;
    if (ExecutorType.BATCH == executorType) {
        executor = new BatchExecutor(this, transaction);
    } else if (ExecutorType.REUSE == executorType) {
        executor = new ReuseExecutor(this, transaction);
    } else {
        executor = new SimpleExecutor(this, transaction);
    }
    if (cacheEnabled) executor = new CachingExecutor(executor);
    return (Executor) interceptorChain.pluginAll(executor);
}

Finally, the article discusses Oracle‑specific batch insert optimization: using a native sequence in the INSERT statement instead of a separate selectKey query, which cuts the number of round‑trips dramatically.

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

With the proper batch configuration and Oracle‑level tweaks, insertion time drops from several minutes to under a second, demonstrating the huge performance gains achievable through correct batch processing.