Efficient Insertion of 300,000 Records Using MyBatis and JDBC in Java

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

The target table t_user is defined as follows:

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

A simple POJO User is used to represent a record:

/**
 *
用户实体
*
 * @Author zjq
 * @Date 2021/8/3
 */
@Data
public class User {
    private int id;
    private String username;
    private int age;
}

The MyBatis mapper interface defines batch and single‑insert methods:

public interface UserMapper {
    /**
     * 批量插入用户
     * @param userList
     */
    void batchInsertUser(@Param("list") List
userList);

    /**
     * 新增单个用户
     * @param user
     */
    void insertUser(User user);
}

The corresponding XML mapping includes a foreach for batch insertion and a simple insert for a single row:

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

Database connection properties are stored in jdbc.properties :

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/test
jdbc.username=root
jdbc.password=root

The MyBatis configuration ( sqlMapConfig.xml ) loads the properties, defines type aliases, and sets up the environment and data source.

Attempt 1 – Direct batch insertion with MyBatis

@Test
public void testBatchInsertUser() throws IOException {
    InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
    SqlSession session = sqlSessionFactory.openSession();
    System.out.println("===== 开始插入数据 =====");
    long startTime = System.currentTimeMillis();
    try {
        List
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);
        }
        session.insert("batchInsertUser", userList); // 最后插入剩余的数据
        session.commit();
        long spendTime = System.currentTimeMillis() - startTime;
        System.out.println("成功插入 30 万条数据,耗时：" + spendTime + "毫秒");
    } finally {
        session.close();
    }
}

This approach fails with a PacketTooBigException because the MySQL packet size limit (default 4 MB) is exceeded.

Attempt 2 – Row‑by‑row insertion with MyBatis

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

This method completes but takes about 4 hours (≈14,909,367 ms) and causes high disk I/O.

Attempt 3 – Optimized batch insertion with MyBatis

@Test
public void testBatchInsertUser() throws IOException {
    InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
    SqlSession session = sqlSessionFactory.openSession();
    System.out.println("===== 开始插入数据 =====");
    long startTime = System.currentTimeMillis();
    int waitTime = 10; // seconds
    try {
        List
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(waitTime * 1000);
            }
        }
        if (!CollectionUtils.isEmpty(userList)) {
            session.insert("batchInsertUser", userList);
            session.commit();
        }
        long spendTime = System.currentTimeMillis() - startTime;
        System.out.println("成功插入 30 万条数据,耗时：" + spendTime + "毫秒");
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        session.close();
    }
}

With a batch size of 1,000 rows and a 10‑second pause, the insertion finishes in about 50 minutes; removing the pause reduces the time to roughly 24 seconds. Increasing the batch size to 5,000 rows brings the runtime down to about 13 seconds, at the cost of a short CPU and disk spike.

Attempt 4 – Batch insertion with JDBC

@Test
public void testJDBCBatchInsertUser() throws IOException {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    String databaseURL = "jdbc:mysql://localhost:3306/test";
    String user = "root";
    String password = "root";
    try {
        connection = DriverManager.getConnection(databaseURL, user, password);
        connection.setAutoCommit(false);
        System.out.println("===== 开始插入数据 =====");
        long startTime = System.currentTimeMillis();
        String sqlInsert = "INSERT INTO t_user ( username, age) VALUES ( ?, ?)";
        preparedStatement = connection.prepareStatement(sqlInsert);
        Random random = new Random();
        for (int i = 1; i <= 300000; i++) {
            preparedStatement.setString(1, "共饮一杯无 " + i);
            preparedStatement.setInt(2, random.nextInt(100));
            preparedStatement.addBatch();
            if (i % 1000 == 0) {
                preparedStatement.executeBatch();
                connection.commit();
                System.out.println("成功插入第 " + i + " 条数据");
            }
        }
        preparedStatement.executeBatch();
        connection.commit();
        long spendTime = System.currentTimeMillis() - startTime;
        System.out.println("成功插入 30 万条数据,耗时：" + spendTime + "毫秒");
    } catch (SQLException e) {
        System.out.println("Error: " + e.getMessage());
    } finally {
        if (preparedStatement != null) {
            try { preparedStatement.close(); } catch (SQLException e) { e.printStackTrace(); }
        }
        if (connection != null) {
            try { connection.close(); } catch (SQLException e) { e.printStackTrace(); }
        }
    }
}

The JDBC version follows the same batch‑size principle (default 1,000 rows) and achieves comparable performance when the batch size and commit frequency are tuned.

Summary of best practices

Use batch inserts (1,000–5,000 rows per batch) to reduce network round‑trips.

Introduce a short wait between batches if memory pressure is observed.

Temporarily drop indexes before bulk loading and rebuild them afterward.

Employ a connection pool to avoid repeated connection overhead.

Adjust MySQL parameters (e.g., max_allowed_packet , buffer sizes) for large payloads.

Monitor CPU and disk I/O; larger batches finish faster but cause brief spikes.