Inserting 300,000 Records into MySQL Using MyBatis and JDBC: Strategies and Performance Comparison

The article explains a case study of inserting a large volume of data (300,000 rows) into a MySQL table using MyBatis and JDBC, showing the required entity, mapper, and configuration files.

Database 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 class (User):

/**
 * <p>用户实体</p>
 *
 * @Author zjq
 */
@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<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" index="index" separator=",">
        (#{item.username}, #{item.age})
    </foreach>
</insert>

MyBatis direct "all‑in‑one" batch (fails due to max_allowed_packet):

@Test
public void testBatchInsertUser() throws IOException {
    InputStream is = Resources.getResourceAsStream("sqlMapConfig.xml");
    SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
    SqlSession session = factory.openSession();
    System.out.println("===== 开始插入数据 =====");
    long start = System.currentTimeMillis();
    try {
        List<User> list = new ArrayList<>();
        for (int i = 1; i <= 300000; i++) {
            User u = new User();
            u.setId(i);
            u.setUsername("共饮一杯无 " + i);
            u.setAge((int)(Math.random()*100));
            list.add(u);
        }
        session.insert("batchInsertUser", list); // exceeds packet size
        session.commit();
        System.out.println("成功插入 30 万条数据,耗时：" + (System.currentTimeMillis()-start) + "毫秒");
    } finally {
        session.close();
    }
}

The execution throws PacketTooBigException, indicating that a single batch of 300k rows exceeds MySQL's max_allowed_packet limit.

Per‑row insertion with MyBatis (very slow):

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

This method takes several hours (≈4 h 8 min) and causes high disk I/O.

Optimized MyBatis batch (1000 rows per commit, optional wait):

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

With a 10‑second pause between batches the whole run finishes in about 50 minutes; removing the pause reduces the time to ~24 seconds for 300k rows.

JDBC batch insertion:

@Test
public void testJDBCBatchInsertUser() throws IOException {
    String url = "jdbc:mysql://localhost:3306/test";
    String user = "root";
    String password = "root";
    Connection conn = DriverManager.getConnection(url, user, password);
    conn.setAutoCommit(false);
    String sql = "INSERT INTO t_user (username, age) VALUES (?, ?)";
    PreparedStatement ps = conn.prepareStatement(sql);
    long start = System.currentTimeMillis();
    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();
            conn.commit();
        }
    }
    ps.executeBatch();
    conn.commit();
    System.out.println("成功插入 30 万条数据,耗时：" + (System.currentTimeMillis()-start) + "毫秒");
    ps.close();
    conn.close();
}

The JDBC version follows the same batch‑size principle and achieves comparable performance when the batch size is tuned.

Key optimisation recommendations:

Use batch commits (1000‑5000 rows per batch) to reduce round‑trips.

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

Temporarily drop indexes before bulk load and recreate them afterwards.

Employ a connection pool to avoid repeated connection creation.

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

By combining these techniques, inserting 300,000 records can be reduced from several hours to under a minute.