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

Table schema

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 and mapper definitions

User entity

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

UserMapper interface

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

mapper.xml for batch insert

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

JDBC properties

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

MyBatis configuration (sqlMapConfig.xml)

<?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>

MyBatis batch insert (single large batch)

Attempting to insert all 300,000 rows at once leads to a packet‑size error.

@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);
    }
    session.insert("batchInsertUser", list); // too large packet
    session.commit();
    System.out.println("Inserted 300k rows in " + (System.currentTimeMillis()-start) + " ms");
    session.close();
}

Cause: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (27759038 > 4194304). You can change this value on the server by setting the max_allowed_packet variable.

MyBatis single‑row loop insert

Adding a method to insert one user and calling it inside a loop.

/** Insert a single user */
void insertUser(User user);

<!-- Insert single user -->
<insert id="insertUser" parameterType="user">
    insert into t_user(username,age) values (#{username}, #{age})
</insert>

@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();
    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("Inserted 300k rows in " + (System.currentTimeMillis()-start) + " ms");
    session.close();
}

The console shows very high I/O and the process takes several hours (≈4 h 8 min, 14,909,367 ms).

Optimized MyBatis batch insert (size 1000, 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();
            Thread.sleep(10 * 1000); // optional wait
        }
    }
    if (!batch.isEmpty()) {
        session.insert("batchInsertUser", batch);
        session.commit();
    }
    System.out.println("Inserted 300k rows in " + (System.currentTimeMillis()-start) + " ms");
    session.close();
}

With a 10‑second pause the whole operation finishes in about 50 minutes; removing the pause reduces the time to roughly 13 seconds for 300k rows.

JDBC batch insert

@Test
public void testJDBCBatchInsertUser() throws IOException {
    Connection conn = null;
    PreparedStatement ps = null;
    try {
        conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
        conn.setAutoCommit(false);
        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("Inserted " + i + " rows");
            }
        }
        ps.executeBatch();
        conn.commit();
        System.out.println("Inserted 300k rows in " + (System.currentTimeMillis()-start) + " ms");
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        if (ps != null) try { ps.close(); } catch (SQLException ignored) {}
        if (conn != null) try { conn.close(); } catch (SQLException ignored) {}
    }
}

The steps are: obtain a connection, disable auto‑commit, prepare a statement, add rows to the batch, execute the batch every 1,000 rows, commit, and finally close resources.

Summary of optimization strategies

Batch processing : Use addBatch() and executeBatch() with a sensible batch size (1,000‑5,000) to reduce round‑trips.

Index handling : Drop indexes before massive inserts and recreate them afterward.

Connection pooling : Reuse connections via a pool and close them properly in finally.

Database parameters : Increase max_allowed_packet, enlarge MySQL buffers, and tune I/O settings.

Wait intervals : Introduce short sleeps between batches when system resources are limited.