skip to content

How do JDBC's addBatch and executeBatch work, and what does executeBatch return?

level: middleimportance: should knowfreq 50%

answer

  1. The goal is fewer round trips, not less work
  2. One entry per command, in order
  3. Two special negative constants exist
  4. A dedicated exception carries partial progress

basics

~20 s

On a PreparedStatement you bind parameters and call addBatch() to buffer that parameter set, then executeBatch() sends the buffered commands together. It returns an int[] of update counts, one per command, where a value may be SUCCESS_NO_INFO when the count is unknown.

solid answer

~40 s

Batching exists to cut network round trips. With a `PreparedStatement` you set the parameters, call `addBatch()` to stash that parameter set, repeat, then call `executeBatch()` to send them. `Statement` has an `addBatch(String)` overload for whole SQL strings, but the prepared form is what you normally want. `executeBatch()` returns `int[]` with one update count per command, in order; a driver that cannot report the count returns `Statement.SUCCESS_NO_INFO`. If a command fails the driver throws `BatchUpdateException`, whose `getUpdateCounts()` tells you how far it got — entries may be `Statement.EXECUTE_FAILED` — and whether execution stopped at the failure or continued is driver-dependent. `executeLargeBatch()` returns `long[]` for counts beyond `Integer.MAX_VALUE`. Batching is orthogonal to transactions: turn auto-commit off and commit yourself, and flush in chunks so the client-side buffer stays bounded.

code

java · 19 lines
java
conn.setAutoCommit(false);
try (PreparedStatement ps = conn.prepareStatement(
        "INSERT INTO event (id, payload) VALUES (?, ?)")) {
    int n = 0;
    for (Event e : events) {
        ps.setLong(1, e.id());
        ps.setString(2, e.payload());
        ps.addBatch();
        if (++n % 1000 == 0) {
            ps.executeBatch();   // flush, keep the transaction open
        }
    }
    int[] counts = ps.executeBatch();
    conn.commit();
} catch (BatchUpdateException e) {
    int[] partial = e.getUpdateCounts();  // how far it got
    conn.rollback();
    throw e;
}

go deeper

for a junior

Know the shape: bind parameters, addBatch, repeat, executeBatch — and that the point is fewer round trips to the database than one execute per row.

for a middle

Explain the return type and its special values, what BatchUpdateException carries, and that batching and transactions are independent so auto-commit must still be managed deliberately.

for a senior

Discuss chunk sizing against client memory and lock duration, the driver-dependent behaviour after a failed command, and how you verify a batch is actually being merged rather than trusting the calling code's shape.

for a principal

Own the ingestion design: when batched JDBC is the right tool versus a native bulk-load path, how the job stays resumable and idempotent, and what throughput target justifies the added failure-handling complexity.

## The problem batching solves Inserting fifty thousand rows one `executeUpdate()` at a time means fifty thousand request/response round trips. Even on a fast network the latency dominates: the client spends almost all its time waiting, and the database spends almost all of its time idle between tiny statements. Batching lets the driver hand over many parameter sets in far fewer exchanges. Note what it does *not* solve. Batching reduces round trips; it does not reduce the work the database does per row, and it does not replace a bulk-load path where one exists. ## The API On a `PreparedStatement`: bind the parameters as usual, then call `addBatch()` with no arguments to capture that parameter set. Repeat for each row. Call `executeBatch()` to send everything buffered so far; the buffer is cleared by that call, and `clearBatch()` discards it without executing. On a plain `Statement` there is `addBatch(String sql)`, which queues complete SQL strings. It is useful for a series of different administrative statements, but for repeated inserts or updates the prepared form is both safer and far more efficient, because the statement text is sent once. ## Return values and failure `executeBatch()` returns an `int[]` whose length equals the number of commands and whose entries are the update counts in submission order. An entry may be `Statement.SUCCESS_NO_INFO` (the constant is -2), which means the command succeeded but the driver could not report how many rows it touched — normal for some drivers and not an error. Since JDBC 4.2 (Java 8) there is also `executeLargeBatch()`, returning `long[]`, for counts that do not fit in an `int`. When a command fails, the driver throws `BatchUpdateException`, a subclass of `SQLException`. Its `getUpdateCounts()` returns the counts recorded before the failure — entries may include `Statement.EXECUTE_FAILED` (-3) for commands the driver attempted and that failed. The critical portability point is that the specification permits two behaviours: a driver may stop at the first failure, or continue with the remaining commands and mark the failed ones. Code that assumes one behaviour breaks when the driver changes. If you need to know precisely which rows landed, either run the batch inside a transaction you roll back wholesale, or use a natural key that lets you re-derive state afterwards. ## Batching and transactions are separate concerns A batch is not a transaction. Whether the commands in a batch are atomic depends on the connection's transaction state, so the disciplined pattern is: `setAutoCommit(false)`, run the batches, `commit()` at the end (or at chunk boundaries), `rollback()` on failure. Leaving auto-commit on while batching leaves the transactional grouping to the driver's discretion and is not something to rely on. ## Chunking Do not add a million commands before executing. Every buffered parameter set occupies client memory until the flush, some drivers materialise the whole payload before sending it, and a single enormous transaction holds locks and undo/version data for its entire duration. Flushing every few hundred to a few thousand rows keeps memory bounded and makes progress visible. Whether you also commit at those boundaries is a design decision: committing per chunk bounds lock duration and recovery time but gives up all-or-nothing semantics, so it needs an idempotent or resumable job design. ## What the driver actually sends The API guarantees the grouping; it does not guarantee one network packet. A driver may split a large batch, may pipeline the commands as separate protocol messages within one exchange, or may rewrite them into a more efficient form. Rewriting is often opt-in via a connection property — MySQL's Connector/J exposes `rewriteBatchedStatements=true` and the PostgreSQL driver exposes `reWriteBatchedInserts=true`, both of which merge suitable batched inserts into multi-row statements and can change the measured speedup by an order of magnitude. If a batch shows disappointing gains, checking whether such a property is available and enabled is the first thing to try, before redesigning anything. ## Practical cautions Generated keys and batching mix badly: support for `getGeneratedKeys()` after `executeBatch()` is driver-dependent, so if you need the keys, assign them yourself (from a sequence, or a client-generated identifier) rather than depending on it. Batches of `SELECT` statements are not a thing — `executeBatch()` throws if a command returns a result set. And measure: the honest way to confirm batching is working is a timed run and, where available, the database's own statement statistics, not the shape of the calling code.

  • Why chunk a large batch instead of adding every command before one executeBatch call?
    Buffered parameter sets sit in client memory until the flush, and some drivers materialise the whole payload before sending, so an unbounded batch is an OOM waiting to happen. Chunking also bounds how long locks and row versions are held, and makes progress observable in a long-running job.
  • Does executeBatch guarantee a single network round trip?
    No. It guarantees the grouping, not the wire behaviour. Drivers may split large batches, pipeline commands, or merge them only when an opt-in connection property is set — MySQL's rewriteBatchedStatements and PostgreSQL's reWriteBatchedInserts are the well-known examples. Measure rather than assume.
  • After a BatchUpdateException, how do you know which rows were written?
    getUpdateCounts() gives the counts recorded before the failure, with EXECUTE_FAILED marking attempted commands that failed, but whether the driver stopped or continued past the failure is driver-dependent. The reliable answer is to run the batch in a transaction and roll it back, or design the job to be re-runnable from a natural key.

saying these in an interview costs you the question

  • Thinks a batch is automatically one transaction
  • Adds a million commands before executing once
  • Expects executeBatch to return a single total count
  • Assumes every driver stops at the first failed command
  • Relies on getGeneratedKeys after a batch execution

context

resolving links…