Efficient Insertion of 300,000 Records Using MyBatis and JDBC

The article explains how to perform large‑scale data insertion (300,000 rows) into a MySQL table using Java, MyBatis, and plain JDBC, compares different strategies, and provides practical performance tips.

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

/**
 *
用户实体
*
 * @Author zjq
 */
@Data
public class User {
    private int id;
    private String username;
    private int age;
}

MyBatis Mapper Interface

public interface UserMapper {
    /**
     * 批量插入用户
     * @param userList
     */
    void batchInsertUser(@Param("list") List
userList);

    /**
     * 新增单个用户
     * @param user
     */
    void insertUser(User user);
}

MyBatis Mapper XML (Batch Insert)

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

MyBatis Mapper XML (Single Insert)

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

Configuration Files

jdbc.properties

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

sqlMapConfig.xml (excerpt)

<?xml version="1.0" encoding="UTF-8" ?>

MyBatis Batch Insert Test (All 300k at once – fails due to packet size)

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

The above throws PacketTooBigException because the packet exceeds MySQL's max_allowed_packet limit.

MyBatis Loop Insert (single row per iteration)

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

This method works but is very slow (several hours) and causes high disk I/O.

Optimized MyBatis Batch Insert (commit every 1000 rows)

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

With a batch size of 1000 and a short wait, the insertion finishes in a few minutes.

JDBC Batch Insert

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

Performance Tips and Best Practices

Use batch size of 1000‑5000 rows to balance memory usage and network overhead.

Commit the transaction after each batch to avoid huge packets.

Adjust MySQL max_allowed_packet if you really need larger batches.

Disable indexes before bulk load and rebuild them afterwards.

Employ a connection pool to reduce connection overhead.

Tune MySQL buffer sizes and I/O subsystem for high‑throughput inserts.

Conclusion

By combining MyBatis batch execution, proper transaction handling, and JDBC batch APIs, inserting hundreds of thousands of rows can be reduced from several hours to under a minute, while keeping memory consumption and database load under control.