Inserting 300,000 Records with MyBatis and JDBC: Batch vs Loop Approaches and Performance Optimization

The article explains how to insert a large volume of data (300,000 rows) into a MySQL t_user table using both MyBatis and plain JDBC, and evaluates different insertion strategies.

Database schema :

CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `username` varchar(64) DEFAULT NULL COMMENT '用户名称',
  `age` int(4) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户信息表';

Entity class (User):

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

MyBatis mapper interface :

public interface UserMapper {
    /** Batch insert users */
    void batchInsertUser(@Param("list") List
userList);
    /** Insert a single user */
    void insertUser(User user);
}

MyBatis mapper XML (batch insert):

insert into t_user(username,age) values
(#{item.username}, #{item.age})

MyBatis batch insertion code (insert 300k rows in one go) :

@Test
public void testBatchInsertUser() throws IOException {
    InputStream is = Resources.getResourceAsStream("sqlMapConfig.xml");
    SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
    SqlSession session = factory.openSession();
    System.out.println("===== 开始插入数据 =====");
    long start = System.currentTimeMillis();
    try {
        List
list = new ArrayList<>();
        for (int i = 1; i <= 300000; i++) {
            User u = new User();
            u.setId(i);
            u.setUsername("共饮一杯无 " + i);
            u.setAge((int)(Math.random()*100));
            list.add(u);
        }
        session.insert("batchInsertUser", list);
        session.commit();
        long spent = System.currentTimeMillis() - start;
        System.out.println("成功插入 30 万条数据,耗时：" + spent + "毫秒");
    } finally {
        session.close();
    }
}

The above attempt fails with PacketTooBigException because the single batch exceeds MySQL's max_allowed_packet limit.

MyBatis per‑row loop insertion (insert each record individually):

@Test
public void testCirculateInsertUser() throws IOException {
    InputStream is = Resources.getResourceAsStream("sqlMapConfig.xml");
    SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
    SqlSession session = factory.openSession();
    System.out.println("===== 开始插入数据 =====");
    long start = System.currentTimeMillis();
    try {
        for (int i = 1; i <= 300000; i++) {
            User u = new User();
            u.setId(i);
            u.setUsername("共饮一杯无 " + i);
            u.setAge((int)(Math.random()*100));
            session.insert("insertUser", u);
            session.commit();
        }
        long spent = System.currentTimeMillis() - start;
        System.out.println("成功插入 30 万条数据,耗时：" + spent + "毫秒");
    } finally {
        session.close();
    }
}

This method works but is extremely slow (several hours) and causes high disk I/O.

Optimized MyBatis batch strategy : split the 300k rows into batches of 1,000 (or 5,000) rows, insert each batch, commit, and optionally pause between batches to control memory usage.

@Test
public void testBatchInsertUser() throws IOException, InterruptedException {
    InputStream is = Resources.getResourceAsStream("sqlMapConfig.xml");
    SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
    SqlSession session = factory.openSession();
    System.out.println("===== 开始插入数据 =====");
    long start = System.currentTimeMillis();
    List
batch = new ArrayList<>();
    for (int i = 1; i <= 300000; i++) {
        User u = new User();
        u.setId(i);
        u.setUsername("共饮一杯无 " + i);
        u.setAge((int)(Math.random()*100));
        batch.add(u);
        if (i % 1000 == 0) {
            session.insert("batchInsertUser", batch);
            session.commit();
            batch.clear();
            Thread.sleep(10_000); // optional wait
        }
    }
    if (!batch.isEmpty()) {
        session.insert("batchInsertUser", batch);
        session.commit();
    }
    long spent = System.currentTimeMillis() - start;
    System.out.println("成功插入 30 万条数据,耗时：" + spent + "毫秒");
    session.close();
}

With this approach the insertion completes in about 13–50 seconds depending on batch size and waiting time.

JDBC batch insertion follows the same principle using PreparedStatement.addBatch() and executeBatch() every 1,000 rows, with manual transaction control ( setAutoCommit(false) ) and final commit.

@Test
public void testJDBCBatchInsertUser() throws IOException {
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
    conn.setAutoCommit(false);
    String sql = "INSERT INTO t_user (username, age) VALUES (?, ?)";
    PreparedStatement ps = conn.prepareStatement(sql);
    long start = System.currentTimeMillis();
    for (int i = 1; i <= 300000; i++) {
        ps.setString(1, "共饮一杯无 " + i);
        ps.setInt(2, new Random().nextInt(100));
        ps.addBatch();
        if (i % 1000 == 0) {
            ps.executeBatch();
            conn.commit();
        }
    }
    ps.executeBatch();
    conn.commit();
    long spent = System.currentTimeMillis() - start;
    System.out.println("成功插入 30 万条数据,耗时：" + spent + "毫秒");
    ps.close();
    conn.close();
}

Key optimization recommendations :

Use batch processing (addBatch/executeBatch) to reduce network round‑trips.

Choose an appropriate batch size (1,000–5,000 rows) to balance memory usage and performance.

Temporarily drop indexes before bulk loading and recreate them afterward.

Configure MySQL max_allowed_packet if a single batch must be larger.

Employ a connection pool to avoid repeated connection overhead.

Adjust MySQL buffer sizes and ensure fast disk I/O for large inserts.

By applying these techniques, inserting hundreds of thousands of records becomes fast and resource‑friendly.