How to Insert 300,000 Records in 13 Seconds with MyBatis and JDBC

Overview

This article demonstrates how to insert 300,000 rows into a MySQL t_user table using MyBatis and JDBC, evaluates three different approaches, and presents performance results and optimization recommendations.

Entity, Mapper and Configuration

The User POJO, its mapper interface, and the MyBatis XML mapping are defined as follows:

/**
 * <p>用户实体</p>
 *
 * @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<User> 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>

Database connection properties are stored in jdbc.properties and referenced in sqlMapConfig.xml.

Attempt 1 – Direct MyBatis Batch (No Partition)

Calling session.insert("batchInsertUser", userList) with the full 300,000‑row list triggers a PacketTooBigException because the packet exceeds MySQL's max_allowed_packet (27 MB > 4 MB). The author notes that simply raising the server limit is impractical for this volume.

Attempt 2 – Per‑Row Insert

The mapper is extended with a single‑row insertUser method and invoked inside a loop. Execution logs show massive disk I/O and the test runs for 14,909,367 ms (≈4 h 8 min), confirming that naïve row‑by‑row insertion is far too slow.

Attempt 3 – Optimized MyBatis Batch

The list is flushed every 1,000 rows and the session is committed after each batch. An optional Thread.sleep(waitTime) (10 s) is added to limit memory pressure. With this strategy the total time drops to 13 seconds, as shown in the console output and accompanying screenshots.

@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> 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);
        if (i % 1000 == 0) {
            session.insert("batchInsertUser", list);
            session.commit();
            list.clear();
            Thread.sleep(10_000);
        }
    }
    if (!list.isEmpty()) {
        session.insert("batchInsertUser", list);
        session.commit();
    }
    System.out.println("成功插入 30 万条数据,耗时：" + (System.currentTimeMillis()-start) + "毫秒");
    session.close();
}

JDBC Batch Implementation

The same data set is inserted using plain JDBC with PreparedStatement.addBatch() and a commit every 1,000 rows. The code mirrors the MyBatis version and also achieves the 13‑second runtime.

@Test
public void testJDBCBatchInsertUser() throws IOException {
    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
    conn.setAutoCommit(false);
    String sql = "INSERT INTO t_user (username, age) VALUES (?, ?)";
    PreparedStatement ps = conn.prepareStatement(sql);
    Random rnd = new Random();
    long start = System.currentTimeMillis();
    for (int i = 1; i <= 300000; i++) {
        ps.setString(1, "共饮一杯无 " + i);
        ps.setInt(2, rnd.nextInt(100));
        ps.addBatch();
        if (i % 1000 == 0) {
            ps.executeBatch();
            conn.commit();
            System.out.println("成功插入第 " + i + " 条数据");
        }
    }
    ps.executeBatch();
    conn.commit();
    System.out.println("成功插入 30 万条数据,耗时：" + (System.currentTimeMillis()-start) + "毫秒");
    ps.close();
    conn.close();
}

Key Findings and Recommendations

Batching dramatically reduces network round‑trips; committing every 1,000–5,000 rows balances memory usage and speed.

Temporarily drop indexes before bulk load and recreate them afterward to avoid per‑row index maintenance.

Use a connection pool and tune MySQL buffers (e.g., increase innodb_buffer_pool_size) for better throughput.

Adjust max_allowed_packet only when necessary; the preferred solution is to split the batch.

Introduce a short wait between batches (a few seconds) to keep memory consumption stable.

By applying these strategies, the author demonstrates that inserting 300,000 rows can be reduced from several hours to just 13 seconds.