How to Efficiently Insert 300,000 Records with MyBatis and JDBC

Overview

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

Table Definition

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

Entity, Mapper, and Configuration

Entity class:

/**
 * 用户实体
 */
@Data
public class User {
    private int id;
    private String username;
    private int age;
}

Mapper interface:

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

Mapper XML (batch insert):

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

Database connection properties ( jdbc.properties) and MyBatis configuration ( sqlMapConfig.xml) are set up with the MySQL driver, URL, username, and password.

Direct MyBatis Batch (All‑at‑once)

Attempting to insert all 300,000 rows in a single batch caused a PacketTooBigException because the packet exceeded MySQL's max_allowed_packet limit.

Per‑Row Insertion (Loop)

Inserting rows one by one via session.insert("insertUser", user) and committing each transaction resulted in extremely slow performance (over 4 hours) and high disk I/O.

Optimized MyBatis Batch (Chunked)

By accumulating 1,000 rows in a list, inserting them as a batch, committing, clearing the list, and optionally sleeping for a few seconds, the insertion time dropped dramatically. Increasing the batch size to 5,000 further reduced the total time to about 13 seconds, with brief spikes in CPU and disk usage.

JDBC Batch Insertion

The same data volume can be inserted using plain JDBC with PreparedStatement.addBatch() and executeBatch() every 1,000 rows, committing manually (auto‑commit disabled). This approach yields comparable performance when batch size and commit intervals are tuned.

Summary of Optimization Strategies

Use batch processing (addBatch/executeBatch) to reduce round‑trips.

Choose an appropriate batch size (1,000–5,000 rows) to balance memory usage and speed.

Introduce short wait times between batches if memory pressure is a concern.

Temporarily drop indexes before bulk load and recreate them afterward.

Employ a connection pool to avoid repeated connection overhead.

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

Images