Java JDBC Batch Update
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)
| Method | Purpose |
|---|---|
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 untilexecuteBatch(). - The counts come back in the same order as the commands:
0forCREATE TABLE,1for each insert and2for 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.
| Value | Meaning |
|---|---|
0 or more | The 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.
LinkedHashMapkeeps that order. - A count of
0is not an error: theUPDATEran but itsWHEREmatched nothing.
Note: Some drivers rewrite batches into fewer, larger SQL commands for speed. MySQL Connector/J does this with
rewriteBatchedStatements=truein the URL, and the PostgreSQL driver withreWriteBatchedInserts=true. When rewriting is on, counts may come back asSUCCESS_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, soexecuteBatch()throwsBatchUpdateException, a subclass ofSQLException. 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:
pendingcounts 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:
- 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. - 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. - 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 theUNIQUEconstraint, theBatchUpdateExceptionhandler rolls back everything, so a failed import never leaves half the file in the table. - 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.