Batch Processing

TL;DR

• Batch Processing allows you to group multiple SQL statements together and send them to the database in a single network trip.
• It provides a massive performance boost when inserting or updating thousands of rows.
• You use addBatch() to queue the statements and executeBatch() to send them.

Concept

If you need to insert 10,000 rows into a database, executing a standard pstmt.executeUpdate() 10,000 times inside a loop is devastatingly slow. It requires 10,000 individual TCP network round-trips between your Java application and the database server.

Batch Processing solves this. Instead of executing the query immediately, you bind the parameters and call addBatch(). This caches the parameters in Java’s memory. When the batch is large enough (e.g., 1,000 rows), you call executeBatch(). The JDBC driver bundles all 1,000 rows into a single giant network packet, sending them to the database at once.

Examples

import java.sql.*;

public class BatchDemo {
    public void insertBulkData(Connection conn) throws SQLException {
        String sql = "INSERT INTO logs (level, message) VALUES (?, ?)";
        
        // Always disable AutoCommit for bulk inserts to avoid flushing to disk 10,000 times
        conn.setAutoCommit(false);
        
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            for (int i = 1; i <= 10000; i++) {
                pstmt.setString(1, "INFO");
                pstmt.setString(2, "Log message " + i);
                
                // Add the current parameters to the batch queue
                pstmt.addBatch();
                
                // Execute the batch every 1000 rows to prevent OutOfMemoryError in Java
                if (i % 1000 == 0) {
                    pstmt.executeBatch();
                }
            }
            
            // Execute any remaining rows that didn't hit the 1000 threshold
            pstmt.executeBatch();
            
            conn.commit(); // Commit the transaction
            
        } catch (SQLException e) {
            conn.rollback();
            throw e;
        } finally {
            conn.setAutoCommit(true);
        }
    }
}

Interview Questions

Q: What does executeBatch() return?
A: It returns an int[] (an array of integers). Each integer in the array represents the update count (rows affected) for the corresponding statement in the batch. If you batched 1,000 statements, it returns an array of length 1,000, and each element will typically be 1 (indicating 1 row inserted).

Q: Why do you need to execute the batch in chunks (e.g., i % 1000 == 0)?
A: If you loop 1,000,000 times and just call addBatch() without executing it, the JDBC driver will store all 1,000,000 parameter sets inside the JVM Heap. This will rapidly cause an OutOfMemoryError in your Java application. By executing and flushing the batch every 1,000 or 5,000 rows, you keep the memory footprint small and stable.