Batch Processing in JDBC
Batch processing means collecting many SQL statements and sending them to the database together, in one trip, in place of one network round trip per statement. When a program inserts thousands of rows one at a time, most of the time is spent waiting on the network between each statement. A batch removes most of that waiting. In JDBC, you queue work with addBatch() and send it with executeBatch(). In this lesson, you will learn how batching works with PreparedStatement, how to choose a batch size, why to combine it with a transaction, and what happens when one statement in a batch fails.
Methods
| Method | Description |
|---|---|
ps.addBatch() | It is used to add the current set of parameter values to the batch. |
st.addBatch(sql) | It adds a complete SQL string to a Statement's batch. |
executeBatch() | It sends the whole batch and returns an int[] with one result per statement. |
executeLargeBatch() | The same, returning long[] for very large counts. |
clearBatch() | It empties the batch without sending it. |
Each element of the returned array is the number of rows that statement affected, or one of two constants: Statement.SUCCESS_NO_INFO (-2: it worked, count unknown) or Statement.EXECUTE_FAILED (-3).
How It Works
| One at a time | Batched |
|---|---|
Set values, executeUpdate(), wait for the reply. Repeat 3,000 times. | Set values, addBatch(). Repeat. Every 1,000 rows, executeBatch(). |
| 3,000 round trips | 3 round trips |
Example: Inserting 6,000 Rows
import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Arrays;
public class Batch {
static int count(Connection con) throws Exception {
try (Statement st = con.createStatement();
ResultSet rs = st.executeQuery("SELECT COUNT(*) FROM reading")) {
rs.next();
return rs.getInt(1);
}
}
public static void main(String[] args) throws Exception {
try (Connection con =
DriverManager.getConnection("jdbc:h2:mem:lab")) {
try (Statement st = con.createStatement()) {
st.execute("CREATE TABLE reading ("
+ "id INT PRIMARY KEY, student VARCHAR(30), marks INT)");
}
String sql = "INSERT INTO reading VALUES (?, ?, ?)";
String[] names = {"Navika", "Srihitha", "Advitha"};
// 1. One at a time: 3,000 separate executeUpdate calls.
con.setAutoCommit(false);
try (PreparedStatement ps = con.prepareStatement(sql)) {
for (int i = 1; i <= 3000; i++) {
ps.setInt(1, i);
ps.setString(2, names[i % 3]);
ps.setInt(3, 50 + i % 50);
ps.executeUpdate();
}
}
con.commit();
System.out.println("One by one: " + count(con) + " rows");
// 2. As a batch: queue the rows, send them in groups of 1,000.
try (PreparedStatement ps = con.prepareStatement(sql)) {
int sent = 0;
for (int i = 3001; i <= 6000; i++) {
ps.setInt(1, i);
ps.setString(2, names[i % 3]);
ps.setInt(3, 50 + i % 50);
ps.addBatch();
if (i % 1000 == 0) {
int[] results = ps.executeBatch();
sent += results.length;
}
}
con.commit();
System.out.println("Batched: " + sent
+ " rows in 3 batches");
}
System.out.println("Total: " + count(con) + " rows");
// 3. A failure inside a batch.
try (PreparedStatement ps = con.prepareStatement(sql)) {
for (int id : new int[] {7001, 7002, 1, 7003}) {
ps.setInt(1, id);
ps.setString(2, "Navika");
ps.setInt(3, 90);
ps.addBatch();
}
ps.executeBatch();
con.commit();
} catch (BatchUpdateException e) {
System.out.println("Batch failed. Update counts: "
+ Arrays.toString(e.getUpdateCounts()));
con.rollback();
}
System.out.println("After rollback: " + count(con) + " rows");
}
}
}One by one: 3000 rows
Batched: 3000 rows in 3 batches
Total: 6000 rows
Batch failed. Update counts: [1, 1, -3, 1]
After rollback: 6000 rowsExplanation:
- Part 1 inserts 3,000 rows with 3,000 separate
executeUpdate()calls. - Part 2 inserts 3,000 more with
addBatch(). Every 1,000 rows,executeBatch()sends the queued rows in one go and returns an array with one count per row. Only three trips to the database are made. - Auto-commit is off for both, and
commit()is called once at the end. Committing after every row would force the database to save to disk each time, which is the other big cost of row-by-row inserts. - Part 3 shows a failure. The third row reuses id 1, which already exists. The driver throws
BatchUpdateException, andgetUpdateCounts()reports what happened to each statement:[1, 1, -3, 1]. The third failed (-3 isEXECUTE_FAILED); H2 carried on and ran the fourth. rollback()then undoes the three rows that did go in, so the table still has 6,000 rows. Without the transaction, a partly applied batch would have been left behind.
This in-memory database has no network, so the example shows the technique, not a speed-up. Against a real server, batching inserts is commonly many times faster, because each round trip avoided saves network time.
Choosing a Batch Size
| Size | Effect |
|---|---|
| Too small (under 50) | Little saving; still many round trips |
| A few hundred to a few thousand | The usual sweet spot |
| Everything in one batch | High memory use in the driver and long locks in the database |
Start around 500 to 1,000 and measure. Call executeBatch() each time the chunk is full, and once more after the loop for the leftover rows.
When One Statement Fails
Databases differ in what they do after a failure inside a batch:
| Behavior | What getUpdateCounts() holds |
|---|---|
| Continue with the remaining statements | An entry for every statement, with EXECUTE_FAILED for the bad ones |
| Stop at the first failure | Entries only for the statements before the failure |
Either way, the safe pattern is the one in the example: run the batch inside a transaction, and roll back if BatchUpdateException is thrown.
Driver Settings That Matter
| Database | Setting | Effect |
|---|---|---|
| MySQL | rewriteBatchedStatements=true in the URL | Rewrites a batch of inserts into multi-row INSERT statements; a large speed-up |
| PostgreSQL | reWriteBatchedInserts=true in the URL | The same idea |
Common Mistakes
Leaving auto-commit on. Each statement in the batch is committed separately, losing much of the benefit and all of the safety.
Forgetting the final executeBatch(). Rows queued after the last full chunk are never sent.
One enormous batch. Memory grows with every queued row; send in chunks.
Ignoring BatchUpdateException. Part of the batch may have been applied.
Interview Corner
What is batch processing in JDBC?
Batch processing groups multiple SQL statements and submits them to the database in a single call, reducing network round trips and improving performance for bulk inserts, updates and deletes. With a PreparedStatement, set the parameters and call addBatch() for each row, then executeBatch(), which returns an int[] of update counts. It should be combined with a transaction (setAutoCommit(false) and one commit()), executed in chunks of a few hundred to a few thousand, and wrapped in handling for BatchUpdateException, whose getUpdateCounts() shows which statements succeeded.
Practice
- Easy: Change the batch size from 1,000 to 250 and print how many batches are sent.
- Harder: Write
insertAll(Connection con, List<Student> students, int batchSize)that handles any list size, including a last partial batch, inside one transaction.
Key Takeaways
Was this tutorial helpful?