Solving MyBatis Native Batch Insert Issues with Guava List Partitioning

In a previous article we introduced three ways to perform batch inserts with MyBatis (loop insert, MyBatis‑Plus batch insert, and native MyBatis batch insert). This follow‑up focuses on the shortcomings of the native approach when inserting a large volume of records.

The native MyBatis batch insert builds a single INSERT statement that can become several megabytes in size. When the generated SQL exceeds MySQL's default 4 MB limit, the program throws an error and the batch operation fails.

Below is a typical test case that inserts 100,000 User objects using a native SQL statement:

import com.example.demo.model.User;
import com.example.demo.service.impl.UserServiceImpl;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.ArrayList;
import java.util.List;

@SpringBootTest
class UserControllerTest {
    private static final int MAXCOUNT = 100000;
    @Autowired
    private UserServiceImpl userService;
    /**
     * Native SQL batch insert
     */
    @Test
    void saveBatchByNative() {
        long stime = System.currentTimeMillis();
        List
list = new ArrayList<>();
        for (int i = 0; i < MAXCOUNT; i++) {
            User user = new User();
            user.setName("test:" + i);
            user.setPassword("123456");
            list.add(user);
        }
        // batch insert
        userService.saveBatchByNative(list);
        long etime = System.currentTimeMillis();
        System.out.println("执行时间：" + (etime - stime));
    }
}

The corresponding UserMapper.xml uses a <foreach> element to concatenate the values:

<?xml version="1.0" encoding="UTF-8"?>
INSERT INTO `USER`(`NAME`,`PASSWORD`) VALUES
        <foreach collection="list" separator="," item="item">
            (#{item.name},#{item.password})
        </foreach>

Running the test produces an error because the generated SQL is about 4.56 MB, exceeding MySQL's 4 MB limit.

The remedy is to split the large list into smaller sub‑lists ("partitioning") so that each generated INSERT statement stays within the size limit. Guava's Lists.partition method provides a concise way to achieve this.

Add Guava to the project:

<!-- google guava utility library -->
<dependency>
  <groupId>com.google.guava</groupId>
  <artifactId>guava</artifactId>
  <version>31.0.1-jre</version>
</dependency>

Demo of partitioning a list of seven names into groups of three:

import com.google.common.collect.Lists;
import java.util.Arrays;
import java.util.List;

/** Guava partition demo */
public class PartitionByGuavaExample {
    private static final List
OLD_LIST = Arrays.asList(
        "唐僧,悟空,八戒,沙僧,曹操,刘备,孙权".split(","));
    public static void main(String[] args) {
        List
> newList = Lists.partition(OLD_LIST, 3);
        newList.forEach(i -> System.out.println("集合长度：" + i.size()));
    }
}

Applying the same idea to the MyBatis batch insert:

@Test
void saveBatchByNativePartition() {
    long stime = System.currentTimeMillis();
    List
list = new ArrayList<>();
    // build data
    for (int i = 0; i < MAXCOUNT; i++) {
        User user = new User();
        user.setName("test:" + i);
        user.setPassword("123456");
        list.add(user);
    }
    // calculate partition size (e.g., 1000 rows per batch)
    int count = (int) Math.ceil(MAXCOUNT / 1000.0);
    List
> listPartition = Lists.partition(list, count);
    // insert each partition
    for (List
item : listPartition) {
        userService.saveBatchByNative(item);
    }
    long etime = System.currentTimeMillis();
    System.out.println("执行时间：" + (etime - stime));
}

Execution results show that the previous SQL‑size error disappears and the partitioned native batch insert even outperforms MyBatis‑Plus batch insert for 100,000 records.

Summary of steps:

Determine the number of partitions (N batches) based on a suitable batch size.

Use Lists.partition to split the original list into N sub‑lists.

Iterate over each sub‑list and invoke the native MyBatis batch insert method.