Optimizing SpringBoot Batch Insert/Update with MyBatis: Real‑World Tips for High‑Volume Data

Why Batch Insert/Update Matters in SpringBoot

In enterprise SpringBoot projects, batch insertion and update are frequent scenarios such as data migration, Excel import, scheduled synchronization, and log archiving, often involving thousands to millions of records.

Three Common Mistakes

1. Looping Single Inserts/Updates

Core logic: a for loop calls mapper.insert or mapper.update for each record, creating a new DB connection and executing a new SQL each time.

Frequent connection creation/release consumes huge resources.

Each SQL undergoes parse‑optimize‑execute, causing repeated overhead.

When the count exceeds 1,000, timeouts, throttling, or crashes may occur.

@Service
public class UserService {
    @Autowired
    private UserMapper userMapper;
    public void batchInsert(List<User> userList) {
        // Loop inserts one by one
        for (User user : userList) {
            userMapper.insert(user);
        }
    }
}

2. Manual SQL Concatenation

Core logic: concatenate many INSERT statements into a single SQL string and execute it once.

insert into user (username, password, dept_id) values
('zhangsan','123456',10),
('lisi','654321',11),
('wangwu','111111',10);

SQL length limit may be exceeded for large batches.

Syntax errors (missing commas, mismatched quotes) are hard to debug.

Potential SQL‑injection risk if parameters are not filtered.

Batch update via UPDATE ... WHERE id IN (...) cannot set different field values per row.

3. Not Enabling Transactions

Core logic: each SQL runs in its own transaction, causing heavy commit overhead. Even with batch logic, performance drops >50% without a single transaction.

How MyBatis Batch Improves Performance

MyBatis Batch works on the principle of “pre‑compiled SQL + batch commit”. The key optimizations are:

Reuse DB connection : only one connection is obtained for the whole batch.

Reuse prepared statement : MyBatis prepares the SQL once and reuses the PreparedStatement for all rows.

Batch commit : after all rows are queued, the transaction is committed once.

Execution Flow

Start a Spring transaction and obtain a connection.

MyBatis creates a BatchExecutor that reuses the connection.

When the developer calls the batch method, MyBatis prepares the statement once.

Each row’s parameters are set and stored in the executor without immediate execution.

After all rows are queued, commit() executes the batch and commits the transaction.

Connection is released.

Comparison: single‑row loop = “1 connection + 1 prepare + 1 commit per row”; MyBatis batch = “1 connection + 1 prepare + 1 commit for N rows”.

Applicable and Non‑Applicable Scenarios

Data volume ≥100 rows (e.g., Excel import, data migration, log archiving).

SQL structure is identical across rows (same table, same columns).

High performance requirement to avoid timeouts.

Not suitable for very small batches (<100 rows) or when each row has a different SQL structure.

Complete SpringBoot + MyBatis Batch Implementations

Two common approaches: annotation‑based (simple) and XML‑based (flexible).

1. Annotation Approach

Define batch methods in the mapper and use @InsertProvider / @UpdateProvider to generate SQL dynamically.

public interface UserMapper {
    int batchInsert(List<User> userList);
    int batchUpdate(List<User> userList);
}

@InsertProvider(type = UserSqlProvider.class, method = "batchInsertSql")
int batchInsert(List<User> userList);

class UserSqlProvider {
    public String batchInsertSql(List<User> userList) {
        StringBuilder sql = new StringBuilder();
        sql.append("insert into user (username, password, dept_id, create_time, update_time) values ");
        for (int i = 0; i < userList.size(); i++) {
            sql.append("(#{item.username}, #{item.password}, #{item.deptId}, #{item.createTime}, #{item.updateTime})");
            if (i != userList.size() - 1) sql.append(",");
        }
        return sql.toString();
    }
    // batchUpdateSql omitted for brevity
}

2. XML Approach

Create UserMapper.xml and use <foreach> to build batch statements.

<insert id="batchInsert" parameterType="java.util.List">
    insert into user (username, password, dept_id, create_time, update_time)
    values
    <foreach collection="list" item="item" separator=",">
        (#{item.username}, #{item.password}, #{item.deptId}, #{item.createTime}, #{item.updateTime})
    </foreach>
</insert>

<update id="batchUpdate" parameterType="java.util.List">
    update user
    <set>
        username = case id
        <foreach collection="list" item="item">
            when #{item.id} then #{item.username}
        </foreach>
        end,
        ...
    </set>
    where id in
    <foreach collection="list" item="item" open="(" separator="," close=")">
        #{item.id}
    </foreach>
</update>

Service Layer

All batch methods must be wrapped with @Transactional and split into manageable batch sizes (e.g., 1,000 rows per batch).

@Service
public class UserService {
    @Resource
    private UserMapper userMapper;

    @Transactional(rollbackFor = Exception.class)
    public void batchInsertUser(List<User> userList) {
        int batchSize = 1000;
        for (int i = 0; i < userList.size(); i += batchSize) {
            int end = Math.min(i + batchSize, userList.size());
            List<User> sub = userList.subList(i, end);
            sub.forEach(u -> { u.setCreateTime(LocalDateTime.now()); u.setUpdateTime(LocalDateTime.now()); });
            userMapper.batchInsert(sub);
        }
    }

    @Transactional(rollbackFor = Exception.class)
    public void batchUpdateUser(List<User> userList) {
        int batchSize = 1000;
        for (int i = 0; i < userList.size(); i += batchSize) {
            int end = Math.min(i + batchSize, userList.size());
            List<User> sub = userList.subList(i, end);
            sub.forEach(u -> u.setUpdateTime(LocalDateTime.now()));
            userMapper.batchUpdate(sub);
        }
    }
}

Core Configuration Optimizations

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/springboot_demo?rewriteBatchedStatements=true
    username: root
    password: 123456
  hikari:
    maximum-pool-size: 20
    minimum-idle: 5
    connection-timeout: 30000
mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.xxx.entity
  configuration:
    map-underscore-to-camel-case: true
    default-executor-type: BATCH
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

Key settings: rewriteBatchedStatements=true enables true batch execution in MySQL. default-executor-type: BATCH switches MyBatis to batch executor globally. maximum-pool-size ensures enough connections for concurrent batches.

Handling Millions of Rows

1. Split Into Small Batches

Load 1,000–5,000 rows per batch to avoid OOM and keep memory usage low.

2. Disable MyBatis First‑Level Cache

mybatis:
  configuration:
    local-cache-scope: STATEMENT

3. Manual BatchExecutor Control

@Service
public class UserBatchService {
    @Resource
    private SqlSessionFactory sqlSessionFactory;

    public void batchInsertLargeData(List<User> userList) {
        try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false)) {
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            int batchSize = 1000;
            int count = 0;
            for (User user : userList) {
                mapper.insert(user);
                count++;
                if (count % batchSize == 0) {
                    sqlSession.commit();
                    sqlSession.clearCache();
                }
            }
            sqlSession.commit();
        }
    }
}

4. Database‑Level Tweaks

Turn off auto‑commit; let the application control commits.

Reduce indexes on target tables during bulk load, rebuild after.

Increase max_allowed_packet to avoid packet size errors.

Consider LOAD DATA INFILE for tens of millions of rows.

Common Pitfalls to Avoid

Missing @Transactional – batch becomes per‑row commits.

Omitting rewriteBatchedStatements=true – MySQL executes each row separately.

Improper batch size – too large causes OOM, too small causes frequent commits.

For batch updates, forgetting WHERE id IN (...) leads to full‑table updates.

Loading the entire dataset into memory at once.

Conclusion

Mastering MyBatis batch boils down to three points: reuse the connection, reuse the prepared statement, and commit once. With correct Spring transaction settings, proper MyBatis configuration, and sensible batch sizing, developers can eliminate bottlenecks in bulk insert/update scenarios ranging from thousands to millions of rows.