Premium

Batch Operations

Basic Java
≈ 2 min read

When there are many similar commands

If you need to update hundreds of rows, you can perform hundreds of separate JDBC calls. But it is often better to collect several commands into a batch and pass them to the driver as one group. This reduces the number of separate interactions between the application and the database and can noticeably speed up bulk operations.

Batching with PreparedStatement

String sql = "INSERT INTO tags(name) VALUES (?)";

try (PreparedStatement statement = connection.prepareStatement(sql)) {
    for (String tag : tags) {
        statement.setString(1, tag);
        statement.addBatch();
    }

    int[] counts = statement.executeBatch();
    System.out.println(counts.length);
}

Each call to addBatch() adds the current set of parameters to the object's queue. executeBatch() sends the accumulated commands for execution and returns an array of update results for the batch elements.

A batch does not replace a transaction

Grouping commands for transmission and atomicity are different problems. If a series of changes must be applied completely or not applied at all, the batch is usually executed inside an explicitly managed transaction.

Do not build an unlimited batch. For very large amounts of data, commands are usually sent in chunks so it is easier to control memory, request size, and the duration of one operation.
Back Next