Batch Insertion Techniques in Java Using JDBC and MyBatis/MyBatis Plus

When faced with a scenario that requires inserting more than 100,000 rows with many columns, the author demonstrates several approaches using Java, JDBC, MyBatis, and MyBatis‑Plus, measuring their execution times and providing practical optimization tips.

1. JDBC Batch Insertion

First a MySQL test table fee is created (MySQL 5.7.19) and the required Maven dependency for the MySQL driver is added.

DROP TABLE IF EXISTS `fee`;
CREATE TABLE `fee` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '编号',
  `owner` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '归属人',
  `fee1` decimal(30,5) NULL COMMENT '费用1',
  `fee2` decimal(30,5) NULL COMMENT '费用2',
  `fee3` decimal(30,5) NULL COMMENT '费用3',
  `fee4` decimal(30,5) NULL COMMENT '费用4',
  `fee5` decimal(30,5) NULL COMMENT '费用5',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 CHARACTER SET utf8 COLLATE=utf8_unicode_ci COMMENT='费用表' ROW_FORMAT=Dynamic;

Plain (single‑row) insertion code:

public class JDBCDemo {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/test";
        String user = "root";
        String password = "123456";
        String driver = "com.mysql.jdbc.Driver";
        String sql = "INSERT INTO fee(`owner`,`fee1`,`fee2`,`fee3`,`fee4`,`fee5`) VALUES (?,?,?,?,?,?);";
        Connection conn = null;
        PreparedStatement ps = null;
        long start = System.currentTimeMillis();
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, password);
            ps = conn.prepareStatement(sql);
            for (int i = 1; i <= 100000; i++) {
                ps.setString(1, "o" + i);
                ps.setBigDecimal(2, new BigDecimal("11111.111"));
                ps.setBigDecimal(3, new BigDecimal("11111.111"));
                ps.setBigDecimal(4, new BigDecimal("11111.111"));
                ps.setBigDecimal(5, new BigDecimal("11111.111"));
                ps.setBigDecimal(6, new BigDecimal("11111.111"));
                ps.executeUpdate();
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) try { conn.close(); } catch (SQLException e) { e.printStackTrace(); }
            if (ps != null) try { ps.close(); } catch (SQLException e) { e.printStackTrace(); }
            long end = System.currentTimeMillis();
            System.out.println("十万条数据插入时间（普通插入方式）：" + (end - start) + " ms");
        }
    }
}

The plain insert takes about 80 seconds for 100 k rows.

Batch insertion with manual transaction:

public class JDBCPlusDemo {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true";
        String user = "root";
        String password = "123456";
        String driver = "com.mysql.jdbc.Driver";
        String sql = "INSERT INTO fee(`owner`,`fee1`,`fee2`,`fee3`,`fee4`,`fee5`) VALUES (?,?,?,?,?,?)";
        Connection conn = null;
        PreparedStatement ps = null;
        long start = System.currentTimeMillis();
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, user, password);
            ps = conn.prepareStatement(sql);
            conn.setAutoCommit(false);
            for (int i = 1; i <= 100000; i++) {
                ps.setString(1, "o" + i);
                ps.setBigDecimal(2, new BigDecimal("11111.111"));
                ps.setBigDecimal(3, new BigDecimal("11111.111"));
                ps.setBigDecimal(4, new BigDecimal("11111.111"));
                ps.setBigDecimal(5, new BigDecimal("11111.111"));
                ps.setBigDecimal(6, new BigDecimal("11111.111"));
                ps.addBatch();
                if (i % 1000 == 0) {
                    ps.executeBatch();
                    ps.clearBatch();
                }
            }
            ps.executeBatch();
            ps.clearBatch();
            conn.commit();
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            if (conn != null) try { conn.close(); } catch (SQLException e) { e.printStackTrace(); }
            if (ps != null) try { ps.close(); } catch (SQLException e) { e.printStackTrace(); }
            long end = System.currentTimeMillis();
            System.out.println("十万条数据插入时间（批处理插入）：" + (end - start) + " ms");
        }
    }
}

With rewriteBatchedStatements=true and manual commit, the same 100 k rows finish in roughly 1 second.

2. MyBatis / MyBatis‑Plus Implementations

Dependencies for MyBatis‑Plus, Lombok, and Spring‑Boot Test are added, and application.properties is configured with the same rewrite flag.

# 配置数据库
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true
spring.datasource.username=root
spring.datasource.password=123456

Entity class Fee:

@TableName("fee")
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Fee {
    @TableId(type = IdType.AUTO)
    private Long id;
    private String owner;
    private BigDecimal fee1;
    private BigDecimal fee2;
    private BigDecimal fee3;
    private BigDecimal fee4;
    private BigDecimal fee5;
}

Mapper interface and XML provide a single‑row insert ( insertByOne) and a foreach‑based bulk insert ( insertByForeach).

Service layer defines three methods: saveByFor – loops and calls insertByOne (plain insert). saveByForeach – uses the mapper’s foreach SQL. saveByBatch – opens a MyBatis SqlSession with ExecutorType.BATCH, inserts one by one, and commits every 100 000 rows (similar to the JDBC batch).

Test class MPDemo measures each method. Results:

Plain loop ≈ 80 s (same as JDBC).

Foreach dynamic SQL fails with the default max_allowed_packet; after increasing it to 10 MB the time drops to ~3 s.

MyBatis batch (using ExecutorType.BATCH) finishes in ~1 s, comparable to JDBC batch.

MyBatis‑Plus built‑in saveBatch() takes about 2 s, slightly slower than the custom batch but requires no extra code.

3. Key Take‑aways

Always enable rewriteBatchedStatements=true in the JDBC URL; otherwise batch mode degrades to single inserts.

For pure JDBC, a manual batch with disabled auto‑commit and reasonable chunk size (e.g., 1000) yields the best performance.

When using MyBatis or MyBatis‑Plus, either implement a custom batch using ExecutorType.BATCH or rely on the framework’s saveBatch() method.

Be aware of MySQL’s max_allowed_packet limit when constructing very large dynamic SQL statements.

Overall, the article provides a complete, reproducible guide for developers needing high‑throughput bulk inserts in Java‑based backend systems.