Common MyBatis Batch Insert Techniques and Performance Comparison

The author, a senior architect, shares a comprehensive guide on five batch insertion techniques for MyBatis and MyBatis‑Plus, evaluates their performance, and provides practical code snippets for each method.

Preparation

1. Add Maven dependencies for MySQL, MyBatis‑Spring‑Boot, MyBatis‑Plus, and Lombok in pom.xml:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

<!-- MyBatis dependency -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>2.2.2</version>
</dependency>

<!-- MyBatis‑Plus dependency -->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.2</version>
</dependency>

<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

2. Configure application.yml with datasource and MyBatis mapper locations.

server:
  port: 8080

spring:
  datasource:
    username: mysql用户名
    password: mysql密码
    url: jdbc:mysql://localhost:3306/数据库名字?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=UTC
    driver-class-name: com.mysql.cj.jdbc.Driver

mybatis:
  mapper-locations: classpath:mapping/*.xml

3. Define a simple User POJO with Lombok @Data annotation.

@Data
public class User {
    private int id;
    private String username;
    private String password;
}

1. MyBatis For‑Loop Batch Insert

Service iterates 10,000 times, creates a User object each loop, and calls userMapper.insertUsers(user). Mapper XML contains a single insert statement.

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

    public void InsertUsers() {
        long start = System.currentTimeMillis();
        for (int i = 0; i < 10000; i++) {
            User user = new User();
            user.setUsername("name" + i);
            user.setPassword("password" + i);
            userMapper.insertUsers(user);
        }
        long end = System.currentTimeMillis();
        System.out.println("一万条数据总耗时：" + (end - start) + "ms");
    }
}

Result: 26,348 ms.

2. MyBatis Manual Batch Commit

Opens a MyBatis SqlSession with ExecutorType.BATCH, disables auto‑commit, performs the same loop, then calls sqlSession.commit().

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

    public void InsertUsers() {
        SqlSession sqlSession = sqlSessionTemplate.getSqlSessionFactory()
            .openSession(ExecutorType.BATCH, false);
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        long start = System.currentTimeMillis();
        for (int i = 0; i < 10000; i++) {
            User user = new User();
            user.setUsername("name" + i);
            user.setPassword("password" + i);
            mapper.insertUsers(user);
        }
        sqlSession.commit();
        long end = System.currentTimeMillis();
        System.out.println("一万条数据总耗时：" + (end - start) + "ms");
    }
}

Result: 24,516 ms.

3. MyBatis Collection Batch Insert (Recommended)

Collect all User objects into a List<User> and invoke a single mapper method that uses MyBatis <foreach> to generate a bulk INSERT statement.

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

    public void InsertUsers() {
        long start = System.currentTimeMillis();
        List<User> userList = new ArrayList<>();
        for (int i = 0; i < 10000; i++) {
            User user = new User();
            user.setUsername("name" + i);
            user.setPassword("password" + i);
            userList.add(user);
        }
        userMapper.insertUsers(userList);
        long end = System.currentTimeMillis();
        System.out.println("一万条数据总耗时：" + (end - start) + "ms");
    }
}

// Mapper XML
<insert id="insertUsers" parameterType="java.util.List">
    INSERT INTO user (username, password)
    VALUES
    <foreach collection="userList" item="user" separator=",">
        (#{user.username}, #{user.password})
    </foreach>
</insert>

Result: 521 ms.

4. MyBatis‑Plus saveBatch Method

Extend ServiceImpl and call saveBatch(userList) directly.

@Service
public class UserService extends ServiceImpl<UserMapper, User> implements IService<User> {
    public void InsertUsers() {
        long start = System.currentTimeMillis();
        List<User> userList = new ArrayList<>();
        for (int i = 0; i < 10000; i++) {
            User user = new User();
            user.setUsername("name" + i);
            user.setPassword("password" + i);
            userList.add(user);
        }
        saveBatch(userList);
        long end = System.currentTimeMillis();
        System.out.println("一万条数据总耗时：" + (end - start) + "ms");
    }
}

Result: 24,674 ms.

5. MyBatis‑Plus insertBatchSomeColumn (Recommended)

Define a custom EasySqlInjector that adds the InsertBatchSomeColumn method, register it as a Spring bean, and use the generated mapper method.

public class EasySqlInjector extends DefaultSqlInjector {
    @Override
    public List<AbstractMethod> getMethodList(Class<?> mapperClass, TableInfo tableInfo) {
        List<AbstractMethod> methodList = super.getMethodList(mapperClass, tableInfo);
        methodList.add(new InsertBatchSomeColumn(i -> i.getFieldFill() != FieldFill.UPDATE));
        return methodList;
    }
}

@Configuration
public class MybatisPlusConfig {
    @Bean
    public EasySqlInjector sqlInjector() {
        return new EasySqlInjector();
    }
}

@Service
public class UserService {
    @Resource
    private UserMapper userMapper;
    public void InsertUsers() {
        long start = System.currentTimeMillis();
        List<User> userList = new ArrayList<>();
        for (int i = 0; i < 10000; i++) {
            User user = new User();
            user.setUsername("name" + i);
            user.setPassword("password" + i);
            userList.add(user);
        }
        userMapper.insertBatchSomeColumn(userList);
        long end = System.currentTimeMillis();
        System.out.println("一万条数据总耗时：" + (end - start) + "ms");
    }
}

Result: 575 ms.

Summary

The collection‑based batch insert using MyBatis <foreach> and the custom MyBatis‑Plus insertBatchSomeColumn method achieve the best performance (≈0.5 s for 10 k rows), while simple for‑loop approaches are orders of magnitude slower. Developers should prefer the bulk‑insert strategies for large data loads in Spring Boot applications.