Bulk Insertion of 300,000 Records Using MyBatis and JDBC: Strategies and Performance Evaluation

The article presents a case study of inserting 300,000 user records into a MySQL table, illustrating the definition of the entity class, mapper interface, XML configuration, and JDBC properties required for the operation.

Entity and Mapper Definitions

/**
 *
用户实体
*
 * @Author zjq
 */
@Data
public class User {

    private int id;
    private String username;
    private int age;

}

public interface UserMapper {

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

}

<!-- 批量插入用户信息 -->
<insert id="batchInsertUser" parameterType="java.util.List">
    insert into t_user(username,age) values
    <foreach collection="list" item="item" index="index" separator=",">
        (
        #{item.username},
        #{item.age}
        )
    </foreach>
</insert>

The MySQL table schema used is:

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 ("All‑in‑One")

@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 PacketTooBigException because the MySQL packet size limit is exceeded.

Row‑by‑Row Insertion

@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 the insertion but takes several hours and causes high disk I/O.

Optimized Batch Insertion

@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;
    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 further shortens the duration to about 13 seconds, though CPU and disk usage spike briefly.

JDBC Batch Insertion

@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 example follows the same principles: disable auto‑commit, add rows to a batch, execute the batch every 1,000 rows, and commit manually.

Key Optimization Recommendations

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

Choose an appropriate batch size (1,000–5,000 rows) and, if necessary, insert short pauses to limit memory consumption.

Temporarily drop indexes before bulk loading and recreate them afterward.

Employ a connection pool to avoid the overhead of repeatedly opening and closing connections.

Adjust MySQL parameters such as max_allowed_packet and buffer sizes for higher throughput.

By combining these techniques, inserting 300,000 records can be reduced from several hours to under a minute, with the fastest configuration completing in about 13 seconds.