Efficient Insertion of 300,000 Records Using MyBatis and JDBC

The article demonstrates how to insert a massive amount of data (300,000 rows) into a MySQL table using Java, MyBatis, and JDBC, and evaluates different insertion strategies.

Entity, Mapper and Configuration

A simple User POJO with id, username and age fields is defined, together with a UserMapper interface that declares batchInsertUser and insertUser methods. The corresponding mapper.xml contains the batchInsertUser and insertUser SQL statements, and the MyBatis configuration files ( sqlMapConfig.xml, jdbc.properties) set up the data source and type aliases.

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='用户信息表';

Insertion Strategies

1. Direct bulk insert (no split)

All 300,000 objects are added to a list and inserted with a single MyBatis session.insert("batchInsertUser", userList) call. This fails with PacketTooBigException because the MySQL packet size limit is exceeded.

2. Per‑row loop insert

Each User is inserted individually inside a loop using session.insert("insertUser", user) followed by session.commit(). The test shows extremely high I/O and a runtime of several hours, making it impractical.

3. Batch insert with configurable batch size

The recommended approach splits the data into batches (e.g., 1,000 rows per batch). After each batch the session commits and optionally sleeps for a short period to limit memory usage. This reduces the total runtime to seconds (≈13 s for 300k rows with a batch size of 5,000).

@Test
public void testBatchInsertUser() throws IOException {
    InputStream is = Resources.getResourceAsStream("sqlMapConfig.xml");
    SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
    SqlSession session = factory.openSession();
    long start = System.currentTimeMillis();
    List<User> 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);
        if (i % 1000 == 0) {
            session.insert("batchInsertUser", list);
            session.commit();
            list.clear();
            Thread.sleep(10 * 1000); // optional wait
        }
    }
    // insert remaining records
    if (!list.isEmpty()) {
        session.insert("batchInsertUser", list);
        session.commit();
    }
    long spent = System.currentTimeMillis() - start;
    System.out.println("成功插入 30 万条数据,耗时：" + spent + "毫秒");
    session.close();
}

JDBC Batch Insert

The same logic is reproduced with plain JDBC using PreparedStatement.addBatch() and executeBatch() every 1,000 rows, disabling auto‑commit and committing manually. This yields comparable performance when the batch size is tuned.

Connection conn = DriverManager.getConnection(url, user, pwd);
conn.setAutoCommit(false);
PreparedStatement ps = conn.prepareStatement("INSERT INTO t_user (username, age) VALUES (?, ?)");
for (int i = 1; i <= 300000; i++) {
    ps.setString(1, "共饮一杯无 " + i);
    ps.setInt(2, random.nextInt(100));
    ps.addBatch();
    if (i % 1000 == 0) {
        ps.executeBatch();
        conn.commit();
    }
}
ps.executeBatch();
conn.commit();

Performance Summary and Optimization Tips

Use batch processing (1000–5000 rows per batch) to minimise round‑trips and network overhead.

Temporarily drop indexes before bulk loading and recreate them afterwards.

Configure a connection pool and increase MySQL buffers (e.g., innodb_buffer_pool_size).

Avoid extremely large single packets; adjust max_allowed_packet only when necessary.

Introduce short sleeps between batches if memory pressure is observed.

By combining these techniques, inserting hundreds of thousands of rows can be reduced from hours to a few seconds, making large‑scale data migration practical in production environments.