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

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

User Entity

@Data
public class User {
    private int id;
    private String username;
    private int age;
}

Mapper Interface

public interface UserMapper {
    /**
     * Batch insert users
     * @param userList
     */
    void batchInsertUser(@Param("list") List<User> userList);
}

mapper.xml

<!-- Batch insert users -->
<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>

JDBC Properties

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

sqlMapConfig.xml (simplified)

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <properties resource="jdbc.properties"/>
    <typeAliases>
        <typeAlias type="com.zjq.domain.User" alias="user"/>
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="com/zjq/mapper/UserMapper.xml"/>
    </mappers>
</configuration>

Attempt 1: One‑Shot MyBatis Batch (30 万 rows)

@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);
        session.commit();
        long spent = System.currentTimeMillis() - start;
        System.out.println("成功插入 30 万条数据,耗时：" + spent + "毫秒");
    } finally {
        session.close();
    }
}

The execution fails with PacketTooBigException because the MySQL packet size limit is exceeded.

Attempt 2: Row‑by‑Row MyBatis Insert

@Test
public void testCirculateInsertUser() 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 {
        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();
        }
        long spent = System.currentTimeMillis() - start;
        System.out.println("成功插入 30 万条数据,耗时：" + spent + "毫秒");
    } finally {
        session.close();
    }
}

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

Optimized MyBatis Batch (1000‑row batches with pause)

@Test
public void testBatchInsertUser() throws IOException, InterruptedException {
    InputStream is = Resources.getResourceAsStream("sqlMapConfig.xml");
    SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(is);
    SqlSession session = factory.openSession();
    System.out.println("===== 开始插入数据 =====");
    long start = System.currentTimeMillis();
    int waitTime = 10; // seconds
    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);
            if (i % 1000 == 0) {
                session.insert("batchInsertUser", list);
                session.commit();
                list.clear();
                Thread.sleep(waitTime * 1000);
            }
        }
        if (!list.isEmpty()) {
            session.insert("batchInsertUser", list);
            session.commit();
        }
        long spent = System.currentTimeMillis() - start;
        System.out.println("成功插入 30 万条数据,耗时：" + spent + "毫秒");
    } finally {
        session.close();
    }
}

With a 10‑second pause the whole job finishes in about 50 minutes; removing the pause reduces the time to ~24 seconds but spikes CPU and disk usage.

JDBC Batch Insert

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

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

Key Optimization Strategies

Batch Processing

Group inserts (e.g., 1000‑5000 rows per batch) to reduce network round‑trips and transaction overhead. Adjust batch size and optional wait intervals to balance memory usage and throughput.

Indexes

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

Connection Pool

Use a connection pool to reuse connections and avoid the cost of repeatedly opening/closing them.

Database Parameters

Increase MySQL buffers (e.g., innodb_buffer_pool_size) and raise max_allowed_packet if necessary.