Java JDBC Batch Processing

When you have hundreds or thousands of inserts or updates to run, sending them one at a time means one network round trip each — the dominant cost. Batching collects many statements and ships them to the database in a single round trip, often turning seconds into milliseconds. It is the standard technique for bulk loading.

addBatch and executeBatch

You queue statements with addBatch() and fire them all with executeBatch(), which returns an int[] of per-statement update counts:

String sql = "INSERT INTO log(msg) VALUES (?)";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
  for (String msg : messages) {
    ps.setString(1, msg);
    ps.addBatch();          // queue this set of parameters
  }
  int[] counts = ps.executeBatch();   // one round trip
}

With a PreparedStatement you bind parameters and call addBatch() per row; with a plain Statement you pass a full SQL string to addBatch(sql). The prepared form is preferred — same safety and plan-reuse benefits as always.

The return value and its special markers

executeBatch() returns one entry per queued statement. Most are the row count, but two constants signal special cases:

Statement.SUCCESS_NO_INFO (−2): the statement succeeded, but the driver does not know how many rows it touched.
Statement.EXECUTE_FAILED (−3): this particular statement failed (only seen via BatchUpdateException).

Batch + transaction

Always run a batch inside an explicit transaction (setAutoCommit(false)), so a failure rolls the whole batch back rather than leaving it half-applied. And flush large batches periodically (every ~1000 rows) to bound memory.

When a statement in the batch fails

If any statement fails, executeBatch() throws BatchUpdateException. Its getUpdateCounts() returns the counts gathered so far — letting you see which statements ran before the failure — and it carries the usual SQLException data like getSQLState().

A worked example: counts, markers, and a failed batch

This program builds a batch, prints the two special-marker constants, shows the int[] a clean run returns, and constructs a BatchUpdateException to demonstrate exactly what getUpdateCounts() reports when one statement fails — all without a live database.

java— editable, runs on the server

import java.sql.Statement;
import java.sql.BatchUpdateException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

public class BatchProcessingDemo {
  public static void main(String[] args) {
    // Batching sends many statements to the database in one round trip.
    // executeBatch() returns an int[] of per-statement update counts.
    System.out.println("SUCCESS_NO_INFO = " + Statement.SUCCESS_NO_INFO);
    System.out.println("EXECUTE_FAILED  = " + Statement.EXECUTE_FAILED);

// Build a batch by calling addBatch() repeatedly, then executeBatch().
    List<String> batch = new ArrayList<>();
    batch.add("INSERT INTO log(msg) VALUES ('boot')");
    batch.add("INSERT INTO log(msg) VALUES ('ready')");
    batch.add("INSERT INTO log(msg) VALUES ('serve')");
    System.out.println("queued statements: " + batch.size());

// A successful executeBatch() returns one count per statement (1 row each).
    int[] counts = { 1, 1, 1 };
    System.out.println("update counts: " + Arrays.toString(counts));

// If one statement fails, the driver throws BatchUpdateException, whose
    // getUpdateCounts() reports which ones ran before the failure.
    int[] partial = { 1, Statement.EXECUTE_FAILED, Statement.SUCCESS_NO_INFO };
    BatchUpdateException ex = new BatchUpdateException("constraint violated", "23505", partial);
    System.out.println("on failure, getUpdateCounts() = " + Arrays.toString(ex.getUpdateCounts()));
    System.out.println("SQLState of failure          = " + ex.getSQLState());
  }
}

What to take from the run:

addBatch() queues work without sending it; executeBatch() sends the whole queue in one round trip. The win is purely in the round-trip count — three inserts here, but the same shape scales to thousands where the saving is enormous.
A clean run returns [1, 1, 1] — one update count per queued statement, in order. You read this array to confirm each statement affected the rows you expected.
SUCCESS_NO_INFO (−2) means "it worked but I'm not counting rows." Some drivers return it for batched statements, so treat any negative-but-not-failed value as success, never as an error.
On failure the driver throws BatchUpdateException, and getUpdateCounts() returns [1, -3, -2]: the first insert succeeded, the second failed (EXECUTE_FAILED = −3), and behaviour for the rest is driver-defined. That array is how you locate the offending statement.
The exception carries a SQLState (23505 is the standard integrity-constraint-violation code). Combined with a surrounding transaction and rollback(), this is how a failed bulk load leaves the database untouched rather than partly written.

Practice

A bulk insert calls addBatch() in a loop and then executeBatch(), and one row violates a unique constraint. What does the driver do and how do you find the failing row?

It throws BatchUpdateException; its getUpdateCounts() array shows EXECUTE_FAILED (-3) at the position of the failing statement
executeBatch() returns -1 and silently skips the bad row
The batch commits every row except the bad one, with no exception raised
executeBatch() blocks and retries the failing row until it succeeds