Efficient Insertion of 300,000 Records Using MyBatis and JDBC

This article demonstrates how to insert a large volume of data (300,000 rows) into a MySQL table using both MyBatis and plain JDBC, compares different insertion strategies, and provides performance‑tuning tips.

User entity definition (Lombok @Data used)

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

Mapper interface

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

Mapper XML for batch insert

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

MyBatis test method (single‑batch, 1000 rows per commit)

@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);
            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 + "毫秒");
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        session.close();
    }
}

JDBC batch‑insert test method

@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 ignored) {}
        }
        if (connection != null) {
            try { connection.close(); } catch (SQLException ignored) {}
        }
    }
}

Performance observations:

Direct "one‑shot" batch of 300k rows exceeds MySQL's max_allowed_packet and fails.

Inserting row‑by‑row via MyBatis takes several hours (≈4 h) and saturates disk I/O.

Batching 1000 rows with a short pause reduces memory pressure; total time drops to ~5 min.

Increasing batch size to 5000 rows and removing the pause can finish in ~13 s on a fast machine.

Optimization recommendations:

Use batch size between 1000‑5000 rows to balance memory usage and network overhead.

Temporarily drop indexes before bulk load, rebuild them afterward.

Configure a connection pool (e.g., HikariCP) to avoid repeated connection creation.

Adjust MySQL parameters such as innodb_buffer_pool_size and max_allowed_packet as needed.

Consider using prepared statements with addBatch() and manual transaction control.

In summary, efficient large‑scale data insertion in Java requires proper batching, transaction management, and database tuning.