Skip to content
Java jdbc 6 min read

Batch Processing

When you need to insert, update, or delete hundreds — or millions — of rows, sending one SQL statement per round trip to the database is painfully slow. JDBC batch processing lets you queue up many statements and fire them all in a single network call, slashing both execution time and connection overhead.

Why Batch Processing?

Every time you call executeUpdate() or executeQuery(), JDBC:

  1. Sends the SQL over the network to the database server.
  2. Waits for the server to execute it.
  3. Receives the acknowledgement and result.

For ten rows that is fine. For ten thousand rows that becomes ten thousand round trips — each one paying the full cost of network latency, parser overhead, and lock acquisition. Batching collapses all of that into one trip.

A rough comparison:

Approach10 000 inserts (typical)
Loop + executeUpdate()~8–20 seconds
Batch (executeBatch())~0.2–1 second

Note: Actual numbers depend on your database, driver, network, and hardware. Always benchmark against your real environment.

Batching with Statement

The simplest form uses a plain Statement. You call addBatch(sql) for each statement, then executeBatch() to send them all at once.

import java.sql.*;

public class StatementBatchDemo {
    public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://localhost:3306/school";

        try (Connection conn = DriverManager.getConnection(url, "root", "password");
             Statement stmt = conn.createStatement()) {

            conn.setAutoCommit(false); // wrap batch in a transaction

            stmt.addBatch("INSERT INTO students(name, grade) VALUES ('Alice', 'A')");
            stmt.addBatch("INSERT INTO students(name, grade) VALUES ('Bob',   'B')");
            stmt.addBatch("INSERT INTO students(name, grade) VALUES ('Carol', 'A')");

            int[] results = stmt.executeBatch(); // send all at once
            conn.commit();

            System.out.println("Rows affected: " + results.length);
        }
    }
}

Output:

Rows affected: 3

executeBatch() returns an int[] where each element is the update count for the corresponding statement (or Statement.SUCCESS_NO_INFO if the driver cannot determine the count).

Tip: Always disable auto-commit before batching and call conn.commit() after executeBatch(). If the batch fails halfway through, you can call conn.rollback() to undo everything. See Transaction Management for the full pattern.

For repeated inserts or updates with different values, PreparedStatement batching is far superior: the SQL is compiled once, and you just swap the parameter values for each row.

import java.sql.*;

public class PreparedBatchDemo {
    public static void main(String[] args) throws Exception {
        String url  = "jdbc:mysql://localhost:3306/school";
        String sql  = "INSERT INTO students(name, grade) VALUES (?, ?)";

        String[][] data = {
            {"Alice", "A"}, {"Bob", "B"}, {"Carol", "A"},
            {"Dave",  "C"}, {"Eve",  "B"}
        };

        try (Connection conn = DriverManager.getConnection(url, "root", "password");
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            conn.setAutoCommit(false);

            for (String[] row : data) {
                pstmt.setString(1, row[0]); // name
                pstmt.setString(2, row[1]); // grade
                pstmt.addBatch();           // queue the current bindings
            }

            int[] counts = pstmt.executeBatch();
            conn.commit();

            System.out.println("Batch size: " + counts.length);
        }
    }
}

Output:

Batch size: 5

Each call to addBatch() snapshots the current parameter values and queues them internally. The loop is cheap — no network I/O happens until executeBatch().

Handling Large Datasets with Chunked Batches

Queuing millions of rows into a single batch can exhaust driver memory. A better pattern is to flush every N rows:

import java.sql.*;

public class ChunkedBatchDemo {
    private static final int BATCH_SIZE = 500;

    public static void main(String[] args) throws Exception {
        String url = "jdbc:mysql://localhost:3306/school";
        String sql = "INSERT INTO scores(student_id, score) VALUES (?, ?)";

        try (Connection conn = DriverManager.getConnection(url, "root", "password");
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            conn.setAutoCommit(false);

            for (int i = 1; i <= 10_000; i++) {
                pstmt.setInt(1, i);
                pstmt.setDouble(2, Math.random() * 100);
                pstmt.addBatch();

                if (i % BATCH_SIZE == 0) {       // flush every 500 rows
                    pstmt.executeBatch();
                    conn.commit();
                    pstmt.clearBatch();           // optional — good practice
                }
            }

            pstmt.executeBatch(); // flush remaining rows
            conn.commit();

            System.out.println("All 10 000 rows inserted.");
        }
    }
}

Output:

All 10 000 rows inserted.

The BATCH_SIZE sweet spot varies by driver and row size — common values are 100–1 000. Experiment to find yours.

Error Handling: BatchUpdateException

If one statement in the batch fails, JDBC throws a BatchUpdateException. It contains an int[] of update counts for the statements that ran before the failure (successful ones show their count; failed or not-yet-run ones show EXECUTE_FAILED or -2).

import java.sql.*;

public class BatchErrorDemo {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/school";
        String sql = "INSERT INTO students(id, name) VALUES (?, ?)";

        try (Connection conn = DriverManager.getConnection(url, "root", "password");
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            conn.setAutoCommit(false);

            pstmt.setInt(1, 1); pstmt.setString(2, "Alice"); pstmt.addBatch();
            pstmt.setInt(1, 1); pstmt.setString(2, "Duplicate"); pstmt.addBatch(); // duplicate PK
            pstmt.setInt(1, 2); pstmt.setString(2, "Bob");   pstmt.addBatch();

            pstmt.executeBatch();
            conn.commit();

        } catch (BatchUpdateException bue) {
            System.err.println("Batch failed: " + bue.getMessage());
            int[] counts = bue.getUpdateCounts();
            for (int i = 0; i < counts.length; i++) {
                System.err.println("Statement " + i + ": " + counts[i]);
                // Statement.EXECUTE_FAILED == -3 means that statement failed
            }
            // roll back inside a try block in real code
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Warning: Different drivers behave differently on batch errors. Some stop processing the rest of the batch immediately (fail-fast); others continue and mark individual failures in the update counts array. Check your driver’s documentation.

Mixing Batches with Transactions

Batching and transactions are independent features that work well together. A single transaction can span multiple batch flushes, giving you atomicity across a large dataset while still batching for performance:

conn.setAutoCommit(false);
try {
    // ... build and executeBatch() in chunks ...
    conn.commit();       // all chunks committed atomically
} catch (Exception e) {
    conn.rollback();     // all chunks undone
    throw e;
}

If you commit after every flush (as in the chunked example), you trade full atomicity for lower memory and lock pressure — a common trade-off for very large bulk loads.

Under the Hood

When you call addBatch(), the JDBC driver appends the current SQL (or bound parameters) to an internal list — nothing leaves the JVM yet. When executeBatch() fires:

  1. PreparedStatement path — the driver sends a single protocol packet containing all the bound parameter sets. MySQL’s Connector/J, for example, combines them into one multi-row INSERT if the rewriteBatchedStatements=true connection property is set, squeezing even more performance out of bulk inserts.
  2. Statement path — each SQL string is sent as a separate command, but still within a single network write/flush cycle on most drivers, reducing round-trip overhead.
  3. The database executes each statement in order and returns an array of update counts.

On the JVM side, the driver may allocate a List or resizable array for the queued items — which is why chunking prevents out-of-memory errors for very large batches.

Tip: For MySQL users, add ?rewriteBatchedStatements=true to your JDBC URL. This rewrites PreparedStatement batches into a single INSERT ... VALUES (...),(...),... statement, which is often 10× faster than even a normal batch.

String url = "jdbc:mysql://localhost:3306/school?rewriteBatchedStatements=true";

Quick Reference

MethodWhat it does
addBatch(sql)Queues a SQL string (Statement only)
addBatch()Queues current parameter bindings (PreparedStatement)
executeBatch()Sends all queued items; returns int[] of update counts
clearBatch()Discards all queued items without executing
BatchUpdateExceptionThrown when one or more batch statements fail
  • PreparedStatement — the preferred way to build parameterised SQL for batch operations
  • Transaction Management — combine batches with commit/rollback for safe bulk writes
  • Statement — the simpler statement interface that also supports basic batching
  • Connection Interface — where you set auto-commit mode and obtain statements
  • JDBC Steps — end-to-end walkthrough of connecting to a database and executing SQL
Last updated June 13, 2026
Was this helpful?