HuntExams Academy logo
JavaAdvanced#jdbc

What is batch processing in JDBC?

Answer

Batch processing in JDBC means grouping many SQL statements and sending them to the database in a single round trip using addBatch() and executeBatch(), instead of executing them one at a time. It is the standard fix for slow bulk inserts and updates in Java, because per-statement network latency \u2014 not the SQL itself \u2014 is usually the bottleneck. How it works: - PreparedStatement: set the parameters, call addBatch(), repeat, then executeBatch(). The SQL is parsed once and reused. - Statement: addBatch(sqlString) when the statements differ. - executeBatch() returns an int[] of update counts, or Statement.SUCCESS_NO_INFO / EXECUTE_FAILED. - Always turn auto-commit off with conn.setAutoCommit(false) and commit once per chunk, otherwise each statement gets its own transaction and you lose most of the gain. - Flush in chunks of 500-1000 rows and call clearBatch() so driver memory stays bounded. Error handling: a failing statement makes the driver throw BatchUpdateException, and getUpdateCounts() tells you how far it got. Some drivers stop at the first failure, others continue \u2014 decide whether to roll back the whole chunk or retry rows individually. Driver notes: with MySQL you must add rewriteBatchedStatements=true to the JDBC URL, otherwise the driver silently loops row by row and batching barely helps. Avoid generated-key retrieval with very large batches, and keep the batch on one connection and one transaction. With JPA/Hibernate the equivalent switches are hibernate.jdbc.batch_size, order_inserts and order_updates. Typical result: 10,000 single inserts that took many seconds drop to well under a second.

Example

String sql = "INSERT INTO students (name, email, marks) VALUES (?,?,?)";

try (Connection conn = ds.getConnection();
     PreparedStatement ps = conn.prepareStatement(sql)) {

  conn.setAutoCommit(false);
  int count = 0;

  for (Student s : students) {
    ps.setString(1, s.getName());
    ps.setString(2, s.getEmail());
    ps.setInt(3, s.getMarks());
    ps.addBatch();

    if (++count % 1000 == 0) {      // flush in chunks
      ps.executeBatch();
      ps.clearBatch();
      conn.commit();
    }
  }
  ps.executeBatch();
  conn.commit();

} catch (BatchUpdateException e) {
  int[] done = e.getUpdateCounts();  // how many succeeded before the failure
  throw e;
}

// MySQL must enable real batching in the URL:
// jdbc:mysql://host:3306/db?rewriteBatchedStatements=true

Detailed explanation

Batch processing in JDBC groups multiple SQL commands for execution through addBatch() and executeBatch(), rather than calling executeUpdate() separately for every operation. It is useful for bulk inserts, updates, and deletes, such as importing student records. Batching can reduce communication overhead and improve throughput, but JDBC does not guarantee a single network round trip. Actual execution depends on the driver and database; indexing, constraints, locking, and transaction logging also affect performance.

For repeated operations with the same SQL structure, use PreparedStatement: bind each record’s parameters, call addBatch() to capture that parameter set, and finally call executeBatch(). This reuses the statement and enables driver optimisations, although server-side preparation and parsing behaviour vary. Statement.addBatch(sqlString) supports commands with different SQL text. executeBatch() returns an int[] whose entries correspond to commands in batch order: a nonnegative number is an affected-row count, Statement.SUCCESS_NO_INFO means successful execution without an exact count, and Statement.EXECUTE_FAILED identifies a failed command when processing continues after an error.

Batching and transaction management are separate concerns. With transactional tables, disabling auto-commit using connection.setAutoCommit(false) lets you explicitly commit successful work or roll it back on failure. A batch is not automatically atomic. Execute manageable chunks to limit driver memory and database resource pressure; 500–1000 records is a starting point for measurement, not a universal rule. Committing each chunk limits transaction size but permits partial completion of the overall job. If all records must succeed together, execute multiple chunks and commit only after all succeed. Successful executeBatch() calls reset the batch; clearBatch() explicitly discards queued commands when needed.

On failure, catch BatchUpdateException and inspect getUpdateCounts(); drivers may stop at the first error or continue, so the array does not always describe only a successful prefix. Roll back while the connection remains accessible, then apply a deliberate retry policy. Driver options also matter: MySQL Connector/J’s rewriteBatchedStatements=true can enable rewriting eligible batches, but it is not required to use JDBC batching. Interviewers look for correct API usage, transaction boundaries, failure handling, and measured tuning—not guaranteed speedups.

Key points to remember

  • Use PreparedStatement.addBatch() for repeated parameter sets and Statement.addBatch(sql) for different commands.
  • executeBatch() returns ordered update counts or special status values; batching does not guarantee one network round trip.
  • Control commits and rollbacks explicitly when transactional consistency is required.
  • Choose chunk sizes through measurement, balancing throughput, memory, and transaction costs.
  • Handle BatchUpdateException according to driver behaviour and verify driver-specific batching options.

Common mistakes candidates make

  • Assuming every batch is automatically atomic or committing each chunk while claiming whole-job atomicity.
  • Treating getUpdateCounts() as always containing only successful row counts, ignoring special values and continued execution.
  • Promising fixed speedups or retrying failed batches without considering already committed work and duplicate writes.

Likely follow-up questions

What is the difference between batching and a transaction?

Batching groups commands for execution efficiency, whereas a transaction defines which changes commit or roll back together. One transaction can contain several batches, and a batch alone does not guarantee atomicity.

How should you handle a batch failure?

Catch BatchUpdateException, inspect its update counts, and explicitly roll back the intended transaction scope while the connection is still available. Before retrying, determine which earlier chunks were committed and ensure the retry cannot create unintended duplicates.

How do you choose a batch size?

Start with a moderate size, such as 500–1000 records, and benchmark representative workloads. Adjust for row size, driver limits, memory usage, lock duration, and database performance rather than assuming larger batches are always faster.

Interview tip: Explain the bind–addBatch–executeBatch sequence first, then distinguish batching from transactions and mention one driver-dependent limitation.

Related Interview Questions

1
JavaBeginner#spring

What is Inversion of Control (IoC) in Spring?

Open
2
JavaIntermediate#spring

What are the different types of dependency injection in Spring?

Open
3
JavaBeginner#spring

What is the difference between @Component, @Service, @Repository, and @Controller?

Open
More Java interview questions →