Inserting 100M MySQL Rows: Java MyBatis vs JDBC Batch Performance

During recent research on MySQL query optimization, the author generated large synthetic datasets (name, age, gender, phone, email, address) to test insertion performance using different Java approaches.

Utilizing Java to insert data at the hundred‑million scale – efficiency evaluation

The test compared three strategies across five scenarios: MyBatis lightweight insertion (no transaction), direct JDBC handling (with and without transaction), and JDBC batch processing (with and without transaction).

Strategy 1: MyBatis lightweight insertion (no transaction)

MyBatis is a lightweight ORM; without transaction support the insertion speed is modest. The experiment creates a Spring mapper, loops over generated Person objects, and calls insert for each record.

private long begin = 33112001; // start id
private long end = begin + 100000; // batch size
private String url = "jdbc:mysql://localhost:3306/bigdata?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8";
private String user = "root";
private String password = "0203";

@Test
public void insertBigData2() {
    ApplicationContext context = new ClassPathXmlApplicationContext("applicationContext.xml");
    PersonMapper pMapper = (PersonMapper) context.getBean("personMapper");
    Person person = new Person();
    long bTime = System.currentTimeMillis();
    for (int i = 0; i < 5000000; i++) {
        person.setId(i);
        person.setName(RandomValue.getChineseName());
        person.setSex(RandomValue.name_sex);
        person.setAge(RandomValue.getNum(1, 100));
        person.setEmail(RandomValue.getEmail(4, 15));
        person.setTel(RandomValue.getTel());
        person.setAddress(RandomValue.getRoad());
        pMapper.insert(person);
        begin++;
    }
    long eTime = System.currentTimeMillis();
    System.out.println("Insert 5M rows time: " + (eTime - bTime));
}

The test attempted 5 million inserts but was stopped early; 520 k rows were inserted in about 7–9 minutes. Inserting 10 k rows with MyBatis took 28.6 seconds.

Strategy 2: Direct JDBC (with and without transaction)

The JDBC test runs two variants: auto‑commit disabled (transaction) and enabled (no transaction). The code prepares a statement once, loops to set parameters, executes, and commits per 10 k batch.

// Transactional version
private long begin = 33112001;
private long end = begin + 100000;
private String url = "jdbc:mysql://localhost:3306/bigdata?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8";
private String user = "root";
private String password = "0203";

@Test
public void insertBigData3() {
    Connection conn = null;
    PreparedStatement pstm = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection(url, user, password);
        conn.setAutoCommit(false);
        String sql = "INSERT INTO person VALUES (?,?,?,?,?,?,?)";
        pstm = conn.prepareStatement(sql);
        long bTime1 = System.currentTimeMillis();
        for (int i = 0; i < 10; i++) {
            long bTime = System.currentTimeMillis();
            while (begin < end) {
                pstm.setLong(1, begin);
                pstm.setString(2, RandomValue.getChineseName());
                pstm.setString(3, RandomValue.name_sex);
                pstm.setInt(4, RandomValue.getNum(1, 100));
                pstm.setString(5, RandomValue.getEmail(4, 15));
                pstm.setString(6, RandomValue.getTel());
                pstm.setString(7, RandomValue.getRoad());
                pstm.execute();
                begin++;
            }
            conn.commit();
            end += 10000;
            long eTime = System.currentTimeMillis();
            System.out.println("Insert 10k rows time: " + (eTime - bTime));
        }
        long eTime1 = System.currentTimeMillis();
        System.out.println("Insert 100k rows total time: " + (eTime1 - bTime1));
    } catch (Exception e) {
        e.printStackTrace();
    }
}

Results: without transaction, inserting 10 k rows averaged 21.2 seconds; with transaction, the average dropped to 3.9 seconds.

Strategy 3: JDBC batch processing (with and without transaction)

Batch mode requires enabling rewriteBatchedStatements in the URL and moving the PreparedStatement creation outside the loop. Each iteration adds the parameter set to the batch and executes the batch after the inner loop.

private long begin = 33112001;
private long end = begin + 100000;
private String url = "jdbc:mysql://localhost:3306/bigdata?useServerPrepStmts=false&rewriteBatchedStatements=true&useUnicode=true&characterEncoding=UTF-8";
private String user = "root";
private String password = "0203";

@Test
public void insertBigData() {
    Connection conn = null;
    PreparedStatement pstm = null;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection(url, user, password);
        // conn.setAutoCommit(false); // optional
        String sql = "INSERT INTO person VALUES (?,?,?,?,?,?,?)";
        pstm = conn.prepareStatement(sql);
        long bTime1 = System.currentTimeMillis();
        for (int i = 0; i < 10; i++) {
            long bTime = System.currentTimeMillis();
            while (begin < end) {
                pstm.setLong(1, begin);
                pstm.setString(2, RandomValue.getChineseName());
                pstm.setString(3, RandomValue.name_sex);
                pstm.setInt(4, RandomValue.getNum(1, 100));
                pstm.setString(5, RandomValue.getEmail(4, 15));
                pstm.setString(6, RandomValue.getTel());
                pstm.setString(7, RandomValue.getRoad());
                pstm.addBatch();
                begin++;
            }
            pstm.executeBatch();
            // conn.commit(); // if transaction enabled
            end += 100000;
            long eTime = System.currentTimeMillis();
            System.out.println("Insert 100k rows time: " + (eTime - bTime));
        }
        long eTime1 = System.currentTimeMillis();
        System.out.println("Insert 1M rows total time: " + (eTime1 - bTime1));
    } catch (Exception e) {
        e.printStackTrace();
    }
}

Results: without transaction, average 2.1 seconds per 100 k rows; with transaction, average 1.9 seconds per 100 k rows.

Conclusion

Combining JDBC batch processing with transactions yields the fastest insertion speed for massive single‑row inserts. In the author's experiments, inserting 100 million rows with batch + transaction took about 174 seconds, while variations without one of the techniques were noticeably slower.