Efficient Insertion of 300,000 Records Using MyBatis and JDBC

The article explains a case study of inserting a large volume of data (300,000 rows) into a MySQL database using MyBatis and JDBC, showing the code, configuration, and performance outcomes.

Entity, Mapper and Configuration Files

User entity

/**
 *
用户实体
*
 * @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
userList);
}

mapper.xml

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

jdbc.properties

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

sqlMapConfig.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!-- Load external properties -->
<!-- Type alias -->
<!-- Environments -->
<!-- Mappers -->

Insertion Approaches

Direct Batch Insert (No Partition)

MyBatis attempts to insert all 300,000 rows in a single batch, which fails with PacketTooBigException because the packet exceeds MySQL's max_allowed_packet limit.

Row‑by‑Row Insertion

Inserts each record individually inside a loop. The console shows extremely high I/O and the total execution time reaches several hours (≈4 h 8 min for 300k rows).

Optimized Batch Insert (Partitioned)

Divides the data into batches of 1,000 rows, inserts each batch, commits, and optionally sleeps for a configurable wait time. This reduces memory pressure and improves speed dramatically (≈24 seconds for 300k rows when batch size is increased to 5,000 and no sleep is used).

JDBC Batch Insert

Uses plain JDBC with PreparedStatement.addBatch() and commits every 1,000 rows. The logic mirrors the MyBatis batch approach and achieves similar performance when batch size and wait time are tuned.

Performance Summary

• Direct batch without partition → packet size error. • Row‑by‑row → >4 hours, high CPU/IO. • Partitioned batch (1,000 rows) → ~150 seconds. • Larger batch (5,000 rows) → ~13 seconds. • Proper transaction handling, connection pooling, and MySQL buffer tuning further improve throughput.

Best Practices

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.

Commit after each batch and optionally pause to avoid overwhelming the DB.

Disable indexes during massive inserts and rebuild them afterward.

Configure a connection pool and increase MySQL buffers (e.g., innodb_buffer_pool_size ).

Set max_allowed_packet high enough if a single large batch is required.

By applying these strategies, inserting hundreds of thousands of records becomes fast and resource‑efficient.