Efficient Insertion of 300,000 Records Using MyBatis and JDBC

The article presents a practical case of inserting 300,000 records into a MySQL database using Java, MyBatis, and JDBC, aiming to evaluate different insertion strategies and their performance.

Entity, Mapper, and Configuration Definition

The User entity class, UserMapper interface, and corresponding mapper.xml are defined, along with jdbc.properties and sqlMapConfig.xml to configure the data source and MyBatis environment.

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

Direct Batch Insertion ("梭哈")

Attempting to insert all 300,000 rows in a single MyBatis batch leads to a PacketTooBigException because the MySQL packet size exceeds the default limit. The article notes that increasing max_allowed_packet is not a practical solution for such a large volume.

Row‑by‑Row Insertion

Inserting each record individually via a loop results in extremely high I/O and a runtime of over 4 hours, demonstrating the inefficiency of this approach.

Optimized Batch Insertion with MyBatis

The recommended method 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 control memory usage. This approach reduces the total execution time to about 13–24 seconds depending on batch size and waiting strategy.

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

Optimized Batch Insertion with JDBC

The JDBC version uses PreparedStatement.addBatch() and commits every 1,000 rows, achieving similar performance while allowing fine‑grained control over transaction boundaries.

@Test
public void testJDBCBatchInsertUser() throws IOException {
    Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
    connection.setAutoCommit(false);
    String sqlInsert = "INSERT INTO t_user (username, age) VALUES (?, ?)";
    PreparedStatement ps = connection.prepareStatement(sqlInsert);
    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();
            connection.commit();
        }
    }
    ps.executeBatch();
    connection.commit();
    ps.close();
    connection.close();
}

Summary and Best Practices

Use batch processing (1,000–5,000 rows per batch) to minimize network round‑trips and transaction overhead.

Temporarily drop indexes before massive inserts and recreate them afterward.

Leverage a connection pool to reduce connection creation cost.

Adjust MySQL parameters such as innodb_buffer_pool_size and max_allowed_packet as needed.

Introduce short waits between batches if memory consumption becomes a concern.

The article also contains promotional material for a WeChat community and related services, which is not part of the technical tutorial.