JDBC Batch Processing ​

When using JDBC to operate on databases, batch operations are frequently executed.

For example, to add a number of available coupons for members at once, we can execute the following SQL code:

INSERT INTO coupons (user_id, type, expires) VALUES (123, 'DISCOUNT', '2030-12-31');
INSERT INTO coupons (user_id, type, expires) VALUES (234, 'DISCOUNT', '2030-12-31');
INSERT INTO coupons (user_id, type, expires) VALUES (345, 'DISCOUNT', '2030-12-31');
INSERT INTO coupons (user_id, type, expires) VALUES (456, 'DISCOUNT', '2030-12-31');
...

In practice, when executing JDBC, the SQL statements are essentially the same, differing only in the placeholder parameters:

for (var params : paramsList) {
    PreparedStatement ps = conn.prepareStatement("INSERT INTO coupons (user_id, type, expires) VALUES (?, ?, ?)");
    ps.setLong(1, params.get(0));
    ps.setString(2, params.get(1));
    ps.setString(3, params.get(2));
    ps.executeUpdate();
}

A similar scenario involves increasing each employee's salary by 10% to 30%:

UPDATE employees SET salary = salary * ? WHERE id = ?

While executing each PreparedStatement in a loop is feasible, it has poor performance. SQL databases can optimize the execution of several identical SQL statements with different parameters as a batch operation, which is significantly faster than executing each SQL statement in a loop.

In JDBC code, we can take advantage of this feature of SQL databases by combining multiple executions of the same SQL with different parameters into a single batch execution. Here’s an example code for batch insertion:

try (PreparedStatement ps = conn.prepareStatement("INSERT INTO students (name, gender, grade, score) VALUES (?, ?, ?, ?)")) {
    // Set parameters repeatedly for the same PreparedStatement and call addBatch():
    for (Student s : students) {
        ps.setString(1, s.name);
        ps.setBoolean(2, s.gender);
        ps.setInt(3, s.grade);
        ps.setInt(4, s.score);
        ps.addBatch(); // Add to batch
    }
    // Execute the batch:
    int[] ns = ps.executeBatch();
    for (int n : ns) {
        System.out.println(n + " inserted."); // Number of results for each SQL in the batch
    }
}

The difference between executing a batch and executing a single SQL is that you need to repeatedly set parameters for the same PreparedStatement and call addBatch(), effectively allowing multiple sets of parameters for a single SQL statement, making it equivalent to a "multi-row" SQL.

The second difference is that instead of calling executeUpdate(), you call executeBatch(). Since we set multiple sets of parameters, the return value is also multiple integers, so the return type is int[], which can be iterated over to obtain the result count for each parameter set.

Practice ​

Use batch operations.

Summary ​

Using JDBC batch operations significantly improves execution efficiency. For SQL statements that are identical but have different parameters, batch operations should be prioritized.