Modern JDBC Batch Updates

Batch updates in JDBC allow you to group multiple SQL commands into a single transaction for execution in one network round trip. This approach reduces communication overhead and improves performance when processing large sets of repetitive database operations, such as inserting or updating many rows at once.

1. Understanding JDBC Batch Processing

Traditionally, developers executed each SQL command separately using Statement objects, which resulted in multiple calls between the application and the database. Batch updates solve this inefficiency by sending a collection of parameterized SQL statements as a single unit of work.

In modern JDBC (JDBC 4.2+), you should always use PreparedStatement instead of Statement. Prepared statements reduce parsing overhead, protect against SQL injection, and allow reuse of compiled SQL plans across batched executions.

2. Steps to Execute Batch Updates

Obtain a Connection from a pooled DataSource.
Disable auto-commit to control the transaction manually.
Create a PreparedStatement with placeholders (?).
Set parameter values and call addBatch() for each statement.
Execute all batched commands using executeBatch() or executeLargeBatch().
Commit the transaction, or roll back if an exception occurs.

3. Example: Updating Multiple Rows Efficiently


import javax.sql.DataSource;
import java.sql.*;

public class BatchUpdateExample {
    public static void main(String[] args) throws SQLException {
        DataSource ds = MyDataSourceProvider.getDataSource();
        String sql = "UPDATE hospitals SET phoneno = ? WHERE id = ?";

        try (Connection con = ds.getConnection()) {
            con.setAutoCommit(false);

            try (PreparedStatement ps = con.prepareStatement(sql)) {
                ps.setString(1, "(019) 788-7130");
                ps.setInt(2, 10101);
                ps.addBatch();

                ps.setString(1, "(019) 788-7034");
                ps.setInt(2, 10102);
                ps.addBatch();

                int[] results = ps.executeBatch();
                con.commit();
                System.out.println("Batch executed successfully with " + results.length + " updates.");
            } catch (BatchUpdateException bue) {
                con.rollback();
                System.err.println("Batch failed after partial execution: " +
                                   java.util.Arrays.toString(bue.getUpdateCounts()));
            } catch (SQLException e) {
                con.rollback();
                throw e;
            }
        }
    }
}

In this example, the database processes both updates in a single batch, reducing round-trip latency. The BatchUpdateException class can be used to examine partial results if a failure occurs mid-batch. Use executeLargeBatch() when processing very large batches of updates to avoid integer overflow.

4. Best Practices for Batch Updates

Use a DataSource instead of DriverManager for pooled connections.
Batch only DML operations (INSERT, UPDATE, DELETE). Avoid mixing DDL (e.g., ALTER TABLE).
Manage transactions explicitly: disable auto-commit and commit only after successful batch execution.
Handle exceptions gracefully: always roll back on errors and log partial results.
Chunk large batches: for thousands of records, execute the batch in smaller groups and call clearBatch() between chunks.
Check driver support: call DatabaseMetaData.supportsBatchUpdates() before batching.

5. Performance Considerations

Batching can dramatically improve performance when inserting or updating many rows, as it minimizes the number of network calls. Most modern JDBC drivers internally rewrite batches for efficiency. For example:

PostgreSQL: use reWriteBatchedInserts=true.
MySQL/MariaDB: use rewriteBatchedStatements=true.
SQL Server: batching is supported natively; keep batch sizes moderate.

For large datasets, combine batching with bulk-insert APIs (such as PostgreSQLâs COPY command or MySQLâs LOAD DATA INFILE) when available.

6. Summary

Batch processing in modern JDBC lets you efficiently execute large groups of parameterized DML statements within a single transaction. Using PreparedStatement, explicit transaction control, and careful exception handling ensures that your code remains both fast and reliable across databases.

Try the exercise and quiz below to test your understanding of JDBC batch updates.