Inserting 300,000 Records into MySQL Using MyBatis and JDBC: Techniques and Performance Analysis

This article introduces several methods for inserting 300,000 records into a MySQL database, including MyBatis and JDBC batch processing, per‑record loop insertion, and an attempt to insert all rows at once, and evaluates each method's execution time, resource consumption, and error handling.

It begins by defining a User entity class, its corresponding Mapper interface, the XML mapping file, and the MySQL CREATE TABLE statement for t_user.

Three insertion strategies are then presented:

Direct bulk insert of all 300,000 rows ("all‑in‑one"). Example code:

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

This fails with a PacketTooBigException because the MySQL max_allowed_packet limit is exceeded.

Loop‑by‑loop insertion with a commit after each row. Example code:

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

This approach takes about 4 hours, with high disk I/O.

Batch insertion with a configurable batch size (e.g., 1,000 rows) and optional wait time. Example code:

@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<>(); int waitTime = 10; 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(waitTime * 1000); } } if (!batch.isEmpty()) { session.insert("batchInsertUser", batch); session.commit(); } long spent = System.currentTimeMillis() - start; System.out.println("Inserted 300k rows in " + spent + " ms"); session.close(); }

Tests with different batch sizes (1,000‑5,000) achieve insertion times between 13 seconds and 24 seconds.

A comparable JDBC batch implementation is also provided, using PreparedStatement with addBatch() and executeBatch(), committing every 1,000 rows.

The article concludes with key optimization recommendations: choose an appropriate batch size and wait interval, temporarily drop indexes during bulk load, employ a connection pool, and tune MySQL parameters such as max_allowed_packet and buffer sizes to improve throughput while controlling memory usage.