Java JDBC Batch Update

Su Suriya Ravichandran Updated 18 Nov 2026
12 min read ·Lesson 220 of 306

A Java JDBC batch update groups many INSERT, UPDATE or DELETE commands and sends them to the database together, instead of one at a time. Each separate executeUpdate() is a round trip over the network, so batching can make bulk work many times faster.

Batches are used for imports, data migrations, nightly jobs and anything else that writes hundreds or thousands of rows. In this lesson you will batch plain statements and prepared statements, read the update counts that executeBatch() returns, combine batches with transactions, handle a failed row with BatchUpdateException, split very large jobs into chunks, and build a CSV importer that validates rows before batching them.


How a JDBC Batch Works

Without batching, every command waits for the database to answer before the next one is sent. With batching, the commands are collected on the client and sent in one go.

Without batch:  Java --INSERT--> DB --OK--> Java --INSERT--> DB --OK--> ...   (N round trips)
With batch:     Java --[INSERT, INSERT, INSERT, ...]--> DB --[OK, OK, OK, ...]--> Java   (1 round trip)
MethodPurpose
addBatch()Add the current parameter values (PreparedStatement) to the batch
addBatch(sql)Add a complete SQL command (Statement only)
executeBatch()Send the batch; returns one update count per command as int[]
executeLargeBatch()Same, but returns long[] for very large counts
clearBatch()Discard the commands collected so far without running them

Batch with a Statement

A plain Statement can batch different SQL commands. This is useful for fixed maintenance scripts. The previous lesson on Java JDBC Statement covers it in more detail.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;

public class StatementBatchExample {
    public static void main(String[] args) throws SQLException {
        try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:school", "sa", "");
             Statement statement = connection.createStatement()) {

            statement.addBatch("CREATE TABLE courses (code VARCHAR(10) PRIMARY KEY, title VARCHAR(40), seats INT)");
            statement.addBatch("INSERT INTO courses VALUES ('CS101', 'Java Basics', 30)");
            statement.addBatch("INSERT INTO courses VALUES ('CS102', 'Databases', 25)");
            statement.addBatch("UPDATE courses SET seats = seats + 5");

            int[] counts = statement.executeBatch();
            System.out.println("Update counts: " + Arrays.toString(counts));
        }
    }
}

Output:

Update counts: [0, 1, 1, 2]

How it works:

  • Each addBatch(sql) stores a command; nothing runs until executeBatch().
  • The counts come back in the same order as the commands: 0 for CREATE TABLE, 1 for each insert and 2 for the update, which changed both courses.
  • A batch must not contain SELECT. Queries return rows, and a batch can only return update counts.

Java JDBC Batch Update with PreparedStatement

The most common batch repeats one SQL command with different values. With a PreparedStatement, you set the parameters and call addBatch() once per row.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;

public class PreparedBatchInsert {
    public static void main(String[] args) throws SQLException {
        String[] names = {"Asha", "Ravi", "Meena", "Kavin", "Divya"};
        int[] marks = {88, 72, 92, 81, 95};

        try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:school", "sa", "");
             Statement setup = connection.createStatement()) {

            setup.executeUpdate("CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(30), marks INT)");

            try (PreparedStatement insert = connection.prepareStatement("INSERT INTO students VALUES (?, ?, ?)")) {
                for (int i = 0; i < names.length; i++) {
                    insert.setInt(1, i + 1);
                    insert.setString(2, names[i]);
                    insert.setInt(3, marks[i]);
                    insert.addBatch(); // store this row's values
                }
                int[] counts = insert.executeBatch(); // send all rows at once
                System.out.println("Update counts: " + Arrays.toString(counts));
                System.out.println("Rows inserted: " + Arrays.stream(counts).sum());
            }

            try (ResultSet rows = setup.executeQuery("SELECT name FROM students WHERE marks >= 90 ORDER BY name")) {
                while (rows.next()) {
                    System.out.println("Top scorer: " + rows.getString("name"));
                }
            }
        }
    }
}

Output:

Update counts: [1, 1, 1, 1, 1]
Rows inserted: 5
Top scorer: Divya
Top scorer: Meena

How it works:

  • The SQL is prepared once. Inside the loop, the setters fill the placeholders and addBatch() saves a copy of those values.
  • executeBatch() sends all five rows together and returns one count per row.
  • After executeBatch(), the batch is empty again, so the same statement can collect a new batch.

Understanding Batch Update Counts

Each element of the array returned by executeBatch() describes one command.

ValueMeaning
0 or moreThe command succeeded and changed this many rows
Statement.SUCCESS_NO_INFO (-2)The command succeeded, but the driver does not know how many rows changed
Statement.EXECUTE_FAILED (-3)The command failed (only reported by drivers that continue after an error)
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedHashMap;
import java.util.Map;

public class BatchUpdateCounts {
    public static void main(String[] args) throws SQLException {
        try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:school", "sa", "");
             Statement setup = connection.createStatement()) {

            setup.executeUpdate("CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(30), marks INT)");
            setup.executeUpdate("INSERT INTO students VALUES (1, 'Asha', 88), (2, 'Ravi', 72), (3, 'Meena', 92)");

            Map<Integer, Integer> bonusByStudentId = new LinkedHashMap<>();
            bonusByStudentId.put(1, 5);
            bonusByStudentId.put(3, 2);
            bonusByStudentId.put(9, 10); // no student with id 9

            try (PreparedStatement addBonus = connection.prepareStatement("UPDATE students SET marks = marks + ? WHERE id = ?")) {
                for (Map.Entry<Integer, Integer> entry : bonusByStudentId.entrySet()) {
                    addBonus.setInt(1, entry.getValue());
                    addBonus.setInt(2, entry.getKey());
                    addBonus.addBatch();
                }

                int[] counts = addBonus.executeBatch();
                int index = 0;
                for (Integer studentId : bonusByStudentId.keySet()) {
                    int count = counts[index++];
                    String result = count > 0 ? "updated"
                            : count == Statement.SUCCESS_NO_INFO ? "succeeded (count unknown)"
                            : "no matching student";
                    System.out.println("Student " + studentId + ": " + result);
                }
            }
        }
    }
}

Output:

Student 1: updated
Student 3: updated
Student 9: no matching student

How it works:

  • The three updates are sent as one batch. The counts are [1, 1, 0].
  • Because the counts follow the order in which rows were added, walking the map's keys in the same order pairs each count with its student. LinkedHashMap keeps that order.
  • A count of 0 is not an error: the UPDATE ran but its WHERE matched nothing.

Note: Some drivers rewrite batches into fewer, larger SQL commands for speed. MySQL Connector/J does this with rewriteBatchedStatements=true in the URL, and the PostgreSQL driver with reWriteBatchedInserts=true. When rewriting is on, counts may come back as SUCCESS_NO_INFO (-2).


Batch Updates Inside a Transaction

In auto-commit mode, a driver may commit parts of a batch separately. To make the whole batch all-or-nothing, turn auto-commit off, run the batch, and commit. This is also faster, because the database commits once instead of once per row.

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;

public class BatchWithTransaction {

    static int countStudents(Statement statement) throws SQLException {
        try (ResultSet row = statement.executeQuery("SELECT COUNT(*) FROM students")) {
            row.next();
            return row.getInt(1);
        }
    }

    public static void main(String[] args) throws SQLException {
        int[] ids = {1, 2, 2, 3};                       // id 2 appears twice
        String[] names = {"Asha", "Ravi", "Meena", "Kavin"};

        try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:school", "sa", "");
             Statement setup = connection.createStatement()) {

            setup.executeUpdate("CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(30))");

            connection.setAutoCommit(false);
            try (PreparedStatement insert = connection.prepareStatement("INSERT INTO students VALUES (?, ?)")) {
                for (int i = 0; i < ids.length; i++) {
                    insert.setInt(1, ids[i]);
                    insert.setString(2, names[i]);
                    insert.addBatch();
                }
                insert.executeBatch();
                connection.commit();
                System.out.println("Batch committed");
            } catch (BatchUpdateException e) {
                int[] counts = e.getUpdateCounts();
                System.out.println("Batch failed. Counts: " + Arrays.toString(counts));
                for (int i = 0; i < counts.length; i++) {
                    if (counts[i] == Statement.EXECUTE_FAILED) {
                        System.out.println("Failed row " + i + ": id=" + ids[i] + ", name=" + names[i]);
                    }
                }
                connection.rollback();
                System.out.println("Rolled back. Rows in table: " + countStudents(setup));
            } finally {
                connection.setAutoCommit(true);
            }
        }
    }
}

Output:

Batch failed. Counts: [1, 1, -3, 1]
Failed row 2: id=2, name=Meena
Rolled back. Rows in table: 0

How it works:

  • The third row repeats id 2, which breaks the primary key, so executeBatch() throws BatchUpdateException, a subclass of SQLException.
  • getUpdateCounts() shows what happened to each row. H2 keeps going after a failed row and marks it -3 (EXECUTE_FAILED), which pinpoints Meena's row.
  • Because auto-commit is off, rollback() undoes the rows that did succeed. The table is empty, so you can fix the data and run the whole import again.

Note: Drivers differ after a failure. Some, like H2 and MySQL Connector/J (by default), continue with the remaining commands; others, like PostgreSQL, stop at the first error, so the commands after it never run. Never assume the counts array tells the whole story, and use a transaction when the batch must be all-or-nothing.


Sending Large Batches in Chunks

Adding a million rows to one batch keeps them all in memory until executeBatch(). The usual pattern is to send the batch every few hundred rows, then send whatever is left at the end, and commit once.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ChunkedBatch {

    static final int BATCH_SIZE = 500;

    static void flush(PreparedStatement insert) throws SQLException {
        int[] counts = insert.executeBatch();
        System.out.println("Flushed " + counts.length + " rows");
    }

    public static void main(String[] args) throws SQLException {
        try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:sensors", "sa", "");
             Statement setup = connection.createStatement()) {

            setup.executeUpdate("CREATE TABLE readings (id INT PRIMARY KEY, sensor VARCHAR(20), celsius DOUBLE)");

            connection.setAutoCommit(false);
            try (PreparedStatement insert = connection.prepareStatement("INSERT INTO readings VALUES (?, ?, ?)")) {
                int pending = 0;
                for (int id = 1; id <= 1250; id++) {
                    insert.setInt(1, id);
                    insert.setString(2, "sensor-" + (id % 5));
                    insert.setDouble(3, 20 + (id % 10) * 0.5);
                    insert.addBatch();
                    pending++;

                    if (pending == BATCH_SIZE) { // send every 500 rows
                        flush(insert);
                        pending = 0;
                    }
                }
                if (pending > 0) {               // send the remainder
                    flush(insert);
                }
                connection.commit();
            } catch (SQLException e) {
                connection.rollback();
                throw e;
            }

            try (ResultSet row = setup.executeQuery("SELECT COUNT(*), COUNT(DISTINCT sensor) FROM readings")) {
                row.next();
                System.out.println("Total rows: " + row.getInt(1) + " from " + row.getInt(2) + " sensors");
            }
        }
    }
}

Output:

Flushed 500 rows
Flushed 500 rows
Flushed 250 rows
Total rows: 1250 from 5 sensors

How it works:

  • pending counts rows added since the last flush. At 500, the batch is sent and the counter resets.
  • 1250 rows give two full batches of 500 and a final batch of 250. Forgetting the last flush() is a common bug that silently loses the remainder.
  • There is one commit() at the end, so the import is still all-or-nothing. If the whole job is too large for one transaction, commit after each flush instead and record how far it got, so a failed run can resume.
  • Batch sizes between 100 and 1000 work well for most databases. Larger batches give little extra speed and use more memory.

Real-World Example: Importing Students from CSV

A real import must reject bad lines, insert the good ones quickly, and leave the database untouched if something unexpected happens. This importer validates every line, batches valid rows in chunks, and runs everything in one transaction.

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class CsvStudentImporter {

    static final int BATCH_SIZE = 3; // small so the chunks are visible; use 500 in production

    static final String CSV = """
            id,name,email,marks
            1,Asha,asha@example.com,88
            2,Ravi,ravi@example.com,72
            3,Meena,meena[at]example.com,92
            4,Kavin,kavin@example.com,abc
            5,Divya,divya@example.com,95
            6,Arun,arun@example.com,67
            7,Priya,priya@example.com,105
            8,Sanjay,sanjay@example.com,79
            """;

    // Returns an error message, or null when the line is valid
    static String validate(String[] fields) {
        if (fields.length != 4) {
            return "expected 4 fields but found " + fields.length;
        }
        if (!fields[2].contains("@")) {
            return "invalid email '" + fields[2] + "'";
        }
        int marks;
        try {
            marks = Integer.parseInt(fields[3]);
        } catch (NumberFormatException e) {
            return "marks is not a number '" + fields[3] + "'";
        }
        if (marks < 0 || marks > 100) {
            return "marks out of range '" + marks + "'";
        }
        return null;
    }

    static void importCsv(Connection connection, String csv) throws SQLException {
        String[] lines = csv.strip().split("\n");
        int imported = 0;
        int skipped = 0;

        connection.setAutoCommit(false);
        try (PreparedStatement insert = connection.prepareStatement(
                "INSERT INTO students (id, name, email, marks) VALUES (?, ?, ?, ?)")) {
            int pending = 0;
            for (int lineIndex = 1; lineIndex < lines.length; lineIndex++) { // index 0 is the header
                String[] fields = lines[lineIndex].strip().split(",");
                String error = validate(fields);
                int lineNumber = lineIndex + 1; // human-friendly, counting the header as line 1

                if (error != null) {
                    System.out.println("Skipped line " + lineNumber + ": " + error);
                    skipped++;
                    continue;
                }

                insert.setInt(1, Integer.parseInt(fields[0]));
                insert.setString(2, fields[1]);
                insert.setString(3, fields[2]);
                insert.setInt(4, Integer.parseInt(fields[3]));
                insert.addBatch();
                pending++;

                if (pending == BATCH_SIZE) {
                    imported += insert.executeBatch().length;
                    System.out.println("Sent batch of " + pending + " row(s)");
                    pending = 0;
                }
            }
            if (pending > 0) {
                imported += insert.executeBatch().length;
                System.out.println("Sent batch of " + pending + " row(s)");
            }

            connection.commit();
            System.out.println("Import committed: " + imported + " imported, " + skipped + " skipped");
        } catch (BatchUpdateException e) {
            connection.rollback();
            System.out.println("Database rejected a row (" + e.getSQLState() + "). Nothing was imported.");
        } catch (SQLException e) {
            connection.rollback();
            throw e;
        } finally {
            connection.setAutoCommit(true);
        }
    }

    public static void main(String[] args) throws SQLException {
        try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:school", "sa", "");
             Statement setup = connection.createStatement()) {

            setup.executeUpdate("""
                    CREATE TABLE students (
                        id INT PRIMARY KEY,
                        name VARCHAR(30) NOT NULL,
                        email VARCHAR(60) NOT NULL UNIQUE,
                        marks INT
                    )""");

            importCsv(connection, CSV);

            try (ResultSet totals = setup.executeQuery("SELECT COUNT(*), SUM(marks) FROM students")) {
                totals.next();
                int count = totals.getInt(1);
                int sumOfMarks = totals.getInt(2);
                System.out.printf("Students in table: %d, class average: %.1f%n", count, (double) sumOfMarks / count);
            }
        }
    }
}

Output:

Skipped line 4: invalid email 'meena[at]example.com'
Skipped line 5: marks is not a number 'abc'
Sent batch of 3 row(s)
Skipped line 8: marks out of range '105'
Sent batch of 2 row(s)
Import committed: 5 imported, 3 skipped
Students in table: 5, class average: 80.2

How it works:

  1. Validate in Java first. validate() checks the field count, the email and the marks before anything reaches the database. Meena (bad email), Kavin (non-numeric marks) and Priya (105 marks) are skipped with clear messages, using line numbers that match the file.
  2. Batch only good rows. Valid rows are added with addBatch(). After Asha, Ravi and Divya, the first chunk of 3 is sent; Arun and Sanjay go in the final chunk of 2.
  3. One transaction for the whole file. Auto-commit is off and commit() runs once at the end. If the database rejects a row, for example a duplicate email hitting the UNIQUE constraint, the BatchUpdateException handler rolls back everything, so a failed import never leaves half the file in the table.
  4. Check the result. The five imported students scored 88, 72, 95, 67 and 79. Their sum is 401, so the average is 401 / 5 = 80.2.

Note: This simple parser splits on commas, so it cannot handle quoted fields such as "Kumar, Arun". For real CSV files, use a library such as Apache Commons CSV or OpenCSV, and keep the same validate-batch-commit structure.

Batching reduces round trips for a single job, but opening a new connection for every request is also slow. The next lesson covers connection pooling. To review the commit and rollback rules used here, see Java JDBC Transactions, and for the basics of placeholders, see Java PreparedStatement.

0 Comments

Reviewed before they appear

No comments yet.

Java
Java Introduction Java Features Java Syntax Java Comments Java Statements Java Output Java Variables Java Data Types Java Type Casting Java Operators Java Strings Java Numbers Java Math Java Booleans Java User Input Java Conditions Java If Else Java Switch Java While Loop Java For Loop Java Break Java Continue Java Arrays Java Methods Java Parameters Java Return Values Java Scope Java Recursion Install Java Install JDK Java IDEs Java JDK Java JRE Java JVM Java Compiler Java File Compile Java Run Java JShell Basics Java Packages Java OOP Java Classes Java Objects Class Methods Constructors Constructor Overload this Keyword static Keyword final Keyword Encapsulation Inheritance Polymorphism Abstraction Access Modifiers super Keyword Method Overload Method Override Instanceof Operator Nested Classes Inner Classes Anonymous Classes Enums Records Array Basics Array Length Loop Arrays Multidimensional Arrays Array Copy Array Sorting Array Search ArrayList LinkedList HashSet TreeSet HashMap TreeMap Queue Interface Deque Interface Stack Class Iterator List Interface Set Interface Map Interface Collections Class Comparable Comparator Generics Basics Generic Classes Generic Methods Wildcards String Basics String Length String Methods String Concatenation String Compare String Search String Replace String Split String Format StringBuilder StringBuffer Text Blocks Regular Expressions Number Types Integer Class Float Class Double Class BigInteger BigDecimal Math Class Random Numbers Number Format Date Basics LocalDate LocalTime LocalDateTime ZonedDateTime Date Formatting Date Parsing Period Class Duration Class Java Instant Class Java Exception Basics Java try...catch Java Multiple Catch Blocks Java finally Block Java throw Keyword Java throws Keyword Java Checked Exceptions Java Unchecked Exceptions Java Custom Exceptions Java try-with-resources Java Exception Chaining Java File Basics Java Create Files Java Read Files Java Write Files Java Delete Files Java File Paths Java File Class Java BufferedReader Java BufferedWriter Java FileInputStream Java FileOutputStream Java Object Streams Java Directory Handling Java NIO Basics Java Files API Java Input Java Scanner Class Java Console Input Java PrintStream Java InputStream Java OutputStream Java Reader Class Java Writer Class Java Buffered Streams Java Data Streams Java Generics Introduction Java Generic Types Java Generic Methods Java Bounded Types Java Wildcards Java Type Erasure Java Generic Interfaces Java Generic Collections Java Lambda Basics Java Lambda Syntax Java Functional Interfaces Java Method References Java Stream API Java Stream Creation Java Stream Filter Java Stream Map Java Stream Reduce Java Stream Sort Java Stream Collect Java Collectors Class Java Optional Class Java Parallel Streams Java Threads: Thread Basics Create a Thread in Java Java Thread Class Java Runnable Interface Java Thread States Java Thread Sleep Java Thread Join Java Thread Priority Java Synchronization Java Thread Safety Java Race Conditions Java Deadlocks Java Executor Service Java Thread Pools Java Callable Interface Java Future Interface Java CompletableFuture Java Concurrent Collections Java Atomic Classes Java Virtual Threads Java Networking Basics Java URL Class Java URI Class Java HttpClient Java HTTP Requests Java HTTP Responses Java Socket Basics Java ServerSocket Java DatagramSocket Java WebSocket Basics Java JDBC Basics Java JDBC Setup Java Database Connection Java JDBC Connection Interface Java JDBC Statement Java PreparedStatement Java ResultSet Java JDBC CRUD Operations Java JDBC Transactions Java JDBC Batch Update Java Connection Pooling with HikariCP Java JPA Basics: Entities, EntityManager and JPQL Java Hibernate Basics: SessionFactory, Session and HQL Package in Java: Basics Explained with Examples Create Package in Java: Step-by-Step Guide with Examples Import Package in Java: The import Statement Explained Access Control in Java: Packages, Classes and Members JAR File in Java: Create, Run and Inspect JAR Files Java Modules: Introduction to the Java Module System module-info in Java: Every Module Directive Explained Java Module Path: How --module-path Finds Your Modules Java Classpath: How -cp and CLASSPATH Find Your Classes Java Annotations: Basics of @ Metadata in Java Java Built-in Annotations: @Override, @Deprecated and More Java Custom Annotations: Create Your Own @interface Java Annotation Targets: @Target and ElementType Explained Java Reflection API: Inspect and Use Classes at Runtime Java Class Metadata: Names, Modifiers, Hierarchy and Generic Java Dynamic Objects: Create Objects Dynamically With Reflec Java Dynamic Proxy: Intercept Method Calls With Proxy and In Java Security Java Secure Coding Java Password Hashing Java Input Validation Java Cryptography Basics Java KeyStore Basics Java TLS Basics Java SecureRandom Java JUnit Basics Java JUnit Test Methods JUnit Assertions JUnit Test Fixtures JUnit Parameterized Tests Mockito in Java Java Mock Objects Java Integration Testing Java Code Coverage Maven in Java Create a Maven Project in Java Maven Dependencies Maven Plugins Gradle in Java Gradle Build Gradle Dependencies Build Executable JAR in Java Build WAR File in Java HTTP Basics in Java Java Servlet Basics Servlet Lifecycle in Java HttpServletRequest in Java HttpServletResponse in Java JSP in Java JSTL in JSP REST API in Java JSON in Java Java Web Application Spring Framework Basics Spring Boot Tutorial Spring MVC Spring Data JPA Spring Security Spring Boot REST API Jakarta EE Basics Java Deployment JAR Deployment Java WAR Deployment Run Java as a Service Docker Java: Dockerize a Java Application Java Environment Variables and Configuration Java Logging Basics Java Production Setup Java JVM Options Java JVM Monitoring Java Hello World App Java Calculator App Java Number Guess Game Java Student Manager Java Library System Java Bank App Java Todo App Java File Manager Java Quiz App Java REST API Project Java Chat Server Java Employee Manager Java E-commerce API
Ask about this post
AI Ask about this post

Ask questions about Java JDBC Batch Update and get answers drawn from it.

Signed-in readers only.