JDBC Batch Processing

📦 Batch Processing

Batch processing allows you to execute multiple SQL statements together as a group, improving performance by reducing network round trips and database overhead when processing large amounts of data.


// Basic batch processing
stmt.addBatch("INSERT INTO users VALUES (1, 'John')");
stmt.addBatch("INSERT INTO users VALUES (2, 'Jane')");
int[] results = stmt.executeBatch();

Batch Processing Benefits

⚡

Performance

Faster execution of multiple statements

// Execute 1000 inserts in one batch

🌐

Network Efficiency

Reduces network round trips

// One network call instead of many

💾

Resource Optimization

Better database resource utilization

stmt.executeBatch(); // Optimized execution

🔄

Transaction Control

All operations in single transaction

// Atomic batch execution

🔹 Statement Batch Processing

Using Statement for batch processing multiple SQL commands:

import java.sql.*;

public class StatementBatch {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/testdb";
        String username = "root";
        String password = "password";
        
        try (Connection conn = DriverManager.getConnection(url, username, password)) {
            
            // Disable auto-commit for better performance
            conn.setAutoCommit(false);
            
            Statement stmt = conn.createStatement();
            
            // Create table
            stmt.executeUpdate("CREATE TABLE IF NOT EXISTS employees (" +
                             "id INT PRIMARY KEY, " +
                             "name VARCHAR(50), " +
                             "department VARCHAR(30), " +
                             "salary DECIMAL(10,2))");
            
            System.out.println("Adding statements to batch...");
            
            // Add multiple INSERT statements to batch
            stmt.addBatch("INSERT INTO employees VALUES (1, 'John Smith', 'IT', 75000.00)");
            stmt.addBatch("INSERT INTO employees VALUES (2, 'Jane Doe', 'HR', 65000.00)");
            stmt.addBatch("INSERT INTO employees VALUES (3, 'Mike Johnson', 'Finance', 70000.00)");
            stmt.addBatch("INSERT INTO employees VALUES (4, 'Sarah Wilson', 'IT', 80000.00)");
            stmt.addBatch("INSERT INTO employees VALUES (5, 'Tom Brown', 'Marketing', 60000.00)");
            
            // Add UPDATE statements to batch
            stmt.addBatch("UPDATE employees SET salary = salary * 1.1 WHERE department = 'IT'");
            stmt.addBatch("UPDATE employees SET department = 'Human Resources' WHERE department = 'HR'");
            
            System.out.println("Executing batch...");
            
            // Execute all statements in batch
            int[] results = stmt.executeBatch();
            
            // Commit the transaction
            conn.commit();
            
            System.out.println("Batch executed successfully!");
            System.out.println("Results:");
            for (int i = 0; i < results.length; i++) {
                System.out.println("Statement " + (i + 1) + ": " + results[i] + " rows affected");
            }
            
            // Verify the results
            ResultSet rs = stmt.executeQuery("SELECT * FROM employees ORDER BY id");
            System.out.println("\nEmployees in database:");
            System.out.println("ID\tName\t\tDepartment\t\tSalary");
            System.out.println("--------------------------------------------------------");
            
            while (rs.next()) {
                System.out.printf("%d\t%-15s\t%-15s\t$%.2f%n",
                    rs.getInt("id"),
                    rs.getString("name"),
                    rs.getString("department"),
                    rs.getDouble("salary"));
            }
            
        } catch (SQLException e) {
            System.out.println("Error: " + e.getMessage());
        }
    }
}

Output:

Adding statements to batch...

Executing batch...

Batch executed successfully!

Results:

Statement 1: 1 rows affected

Statement 2: 1 rows affected

Statement 3: 1 rows affected

Statement 4: 1 rows affected

Statement 5: 1 rows affected

Statement 6: 2 rows affected

Statement 7: 1 rows affected

Employees in database:

ID Name Department Salary

--------------------------------------------------------

1 John Smith IT $82500.00

2 Jane Doe Human Resources $65000.00

3 Mike Johnson Finance $70000.00

4 Sarah Wilson IT $88000.00

5 Tom Brown Marketing $60000.00

🔹 PreparedStatement Batch Processing

Using PreparedStatement for efficient batch processing with parameters:

import java.sql.*;

public class PreparedStatementBatch {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/testdb";
        String username = "root";
        String password = "password";
        
        try (Connection conn = DriverManager.getConnection(url, username, password)) {
            
            conn.setAutoCommit(false);
            
            // Prepare the INSERT statement
            String insertSQL = "INSERT INTO employees (id, name, department, salary) VALUES (?, ?, ?, ?)";
            
            try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
                
                System.out.println("Preparing batch insert...");
                
                // Employee data to insert
                Object[][] employees = {
                    {6, "Alice Cooper", "IT", 85000.00},
                    {7, "Bob Martin", "Finance", 72000.00},
                    {8, "Carol White", "Marketing", 68000.00},
                    {9, "David Lee", "IT", 90000.00},
                    {10, "Emma Davis", "HR", 63000.00},
                    {11, "Frank Miller", "Finance", 75000.00},
                    {12, "Grace Taylor", "Marketing", 66000.00},
                    {13, "Henry Wilson", "IT", 88000.00},
                    {14, "Ivy Chen", "HR", 64000.00},
                    {15, "Jack Robinson", "Finance", 77000.00}
                };
                
                // Add each employee to the batch
                for (Object[] employee : employees) {
                    pstmt.setInt(1, (Integer) employee[0]);
                    pstmt.setString(2, (String) employee[1]);
                    pstmt.setString(3, (String) employee[2]);
                    pstmt.setDouble(4, (Double) employee[3]);
                    
                    pstmt.addBatch(); // Add to batch
                }
                
                System.out.println("Executing batch of " + employees.length + " employees...");
                
                // Execute the batch
                long startTime = System.currentTimeMillis();
                int[] results = pstmt.executeBatch();
                long endTime = System.currentTimeMillis();
                
                // Commit the transaction
                conn.commit();
                
                System.out.println("Batch execution completed in " + (endTime - startTime) + " ms");
                
                // Count successful inserts
                int successCount = 0;
                for (int result : results) {
                    if (result > 0) successCount++;
                }
                
                System.out.println("Successfully inserted " + successCount + " employees");
                
                // Verify total count
                Statement countStmt = conn.createStatement();
                ResultSet rs = countStmt.executeQuery("SELECT COUNT(*) FROM employees");
                if (rs.next()) {
                    System.out.println("Total employees in database: " + rs.getInt(1));
                }
                
            }
            
        } catch (SQLException e) {
            System.out.println("Error: " + e.getMessage());
        }
    }
}

Output:

Preparing batch insert...

Executing batch of 10 employees...

Batch execution completed in 45 ms

Successfully inserted 10 employees

Total employees in database: 15

🔹 Batch Processing with Error Handling

Handling errors and partial failures in batch processing:

import java.sql.*;

public class BatchErrorHandling {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/testdb";
        String username = "root";
        String password = "password";
        
        try (Connection conn = DriverManager.getConnection(url, username, password)) {
            
            conn.setAutoCommit(false);
            
            String insertSQL = "INSERT INTO employees (id, name, department, salary) VALUES (?, ?, ?, ?)";
            
            try (PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
                
                System.out.println("Testing batch with potential errors...");
                
                // Add valid records
                pstmt.setInt(1, 20);
                pstmt.setString(2, "Valid User 1");
                pstmt.setString(3, "IT");
                pstmt.setDouble(4, 75000.00);
                pstmt.addBatch();
                
                pstmt.setInt(1, 21);
                pstmt.setString(2, "Valid User 2");
                pstmt.setString(3, "Finance");
                pstmt.setDouble(4, 80000.00);
                pstmt.addBatch();
                
                // Add duplicate ID (will cause error)
                pstmt.setInt(1, 1); // This ID already exists
                pstmt.setString(2, "Duplicate ID User");
                pstmt.setString(3, "HR");
                pstmt.setDouble(4, 70000.00);
                pstmt.addBatch();
                
                // Add another valid record
                pstmt.setInt(1, 22);
                pstmt.setString(2, "Valid User 3");
                pstmt.setString(3, "Marketing");
                pstmt.setDouble(4, 65000.00);
                pstmt.addBatch();
                
                try {
                    // Execute batch
                    int[] results = pstmt.executeBatch();
                    conn.commit();
                    
                    System.out.println("Batch executed successfully!");
                    for (int i = 0; i < results.length; i++) {
                        System.out.println("Statement " + (i + 1) + ": " + results[i] + " rows affected");
                    }
                    
                } catch (BatchUpdateException e) {
                    System.out.println("❌ Batch update exception occurred!");
                    System.out.println("Error message: " + e.getMessage());
                    
                    // Get partial results
                    int[] updateCounts = e.getUpdateCounts();
                    System.out.println("Partial results:");
                    
                    for (int i = 0; i < updateCounts.length; i++) {
                        if (updateCounts[i] >= 0) {
                            System.out.println("Statement " + (i + 1) + ": SUCCESS (" + updateCounts[i] + " rows)");
                        } else if (updateCounts[i] == Statement.EXECUTE_FAILED) {
                            System.out.println("Statement " + (i + 1) + ": FAILED");
                        } else {
                            System.out.println("Statement " + (i + 1) + ": UNKNOWN (" + updateCounts[i] + ")");
                        }
                    }
                    
                    // Rollback the transaction
                    conn.rollback();
                    System.out.println("🔄 Transaction rolled back");
                    
                    // Try again with only valid records
                    System.out.println("\nRetrying with valid records only...");
                    pstmt.clearBatch();
                    
                    // Add only valid records
                    pstmt.setInt(1, 20);
                    pstmt.setString(2, "Valid User 1");
                    pstmt.setString(3, "IT");
                    pstmt.setDouble(4, 75000.00);
                    pstmt.addBatch();
                    
                    pstmt.setInt(1, 21);
                    pstmt.setString(2, "Valid User 2");
                    pstmt.setString(3, "Finance");
                    pstmt.setDouble(4, 80000.00);
                    pstmt.addBatch();
                    
                    pstmt.setInt(1, 22);
                    pstmt.setString(2, "Valid User 3");
                    pstmt.setString(3, "Marketing");
                    pstmt.setDouble(4, 65000.00);
                    pstmt.addBatch();
                    
                    int[] retryResults = pstmt.executeBatch();
                    conn.commit();
                    
                    System.out.println("✅ Retry successful! Inserted " + retryResults.length + " records");
                }
                
            }
            
        } catch (SQLException e) {
            System.out.println("Database error: " + e.getMessage());
        }
    }
}

Output:

Testing batch with potential errors...

❌ Batch update exception occurred!

Error message: Duplicate entry '1' for key 'PRIMARY'

Partial results:

Statement 1: SUCCESS (1 rows)

Statement 2: SUCCESS (1 rows)

Statement 3: FAILED

🔄 Transaction rolled back

Retrying with valid records only...

✅ Retry successful! Inserted 3 records

🔹 Performance Comparison

Comparing batch processing vs individual statements:

import java.sql.*;

public class PerformanceComparison {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/testdb";
        String username = "root";
        String password = "password";
        
        try (Connection conn = DriverManager.getConnection(url, username, password)) {
            
            // Create test table
            Statement setup = conn.createStatement();
            setup.executeUpdate("DROP TABLE IF EXISTS performance_test");
            setup.executeUpdate("CREATE TABLE performance_test (id INT PRIMARY KEY, data VARCHAR(100))");
            
            int recordCount = 1000;
            
            // Test 1: Individual statements
            System.out.println("Testing individual statements...");
            long startTime = System.currentTimeMillis();
            
            conn.setAutoCommit(false);
            PreparedStatement individualStmt = conn.prepareStatement(
                "INSERT INTO performance_test VALUES (?, ?)");
            
            for (int i = 1; i <= recordCount; i++) {
                individualStmt.setInt(1, i);
                individualStmt.setString(2, "Data for record " + i);
                individualStmt.executeUpdate(); // Execute immediately
            }
            conn.commit();
            
            long individualTime = System.currentTimeMillis() - startTime;
            System.out.println("Individual statements: " + individualTime + " ms");
            
            // Clear table for next test
            setup.executeUpdate("DELETE FROM performance_test");
            
            // Test 2: Batch processing
            System.out.println("\nTesting batch processing...");
            startTime = System.currentTimeMillis();
            
            conn.setAutoCommit(false);
            PreparedStatement batchStmt = conn.prepareStatement(
                "INSERT INTO performance_test VALUES (?, ?)");
            
            for (int i = 1; i <= recordCount; i++) {
                batchStmt.setInt(1, i);
                batchStmt.setString(2, "Data for record " + i);
                batchStmt.addBatch(); // Add to batch
                
                // Execute batch every 100 records for memory efficiency
                if (i % 100 == 0) {
                    batchStmt.executeBatch();
                    batchStmt.clearBatch();
                }
            }
            
            // Execute remaining records
            batchStmt.executeBatch();
            conn.commit();
            
            long batchTime = System.currentTimeMillis() - startTime;
            System.out.println("Batch processing: " + batchTime + " ms");
            
            // Calculate improvement
            double improvement = ((double)(individualTime - batchTime) / individualTime) * 100;
            System.out.println("\nPerformance improvement: " + String.format("%.1f", improvement) + "%");
            System.out.println("Batch processing is " + String.format("%.1f", (double)individualTime / batchTime) + "x faster");
            
            // Verify record count
            ResultSet rs = setup.executeQuery("SELECT COUNT(*) FROM performance_test");
            if (rs.next()) {
                System.out.println("Records inserted: " + rs.getInt(1));
            }
            
        } catch (SQLException e) {
            System.out.println("Error: " + e.getMessage());
        }
    }
}

Output:

Testing individual statements...

Individual statements: 2847 ms

Testing batch processing...

Batch processing: 156 ms

Performance improvement: 94.5%

Batch processing is 18.2x faster

Records inserted: 1000

🔹 Best Practices for Batch Processing

Tips for effective batch processing:

✅ Best Practices:

Batch Size: Use optimal batch sizes (100-1000 records)
Memory Management: Clear batches periodically to avoid memory issues
Transaction Control: Disable auto-commit for better performance
Error Handling: Always handle BatchUpdateException
Connection Pooling: Use connection pools for better resource management

❌ Avoid:

Large Batches: Don't add thousands of statements without executing
Mixed Operations: Don't mix different types of SQL in same batch
Auto-commit: Don't leave auto-commit enabled during batch operations
Ignoring Errors: Always check for partial failures

📦 Batch Processing

Batch Processing Benefits

Performance

Network Efficiency

Resource Optimization

Transaction Control

🔹 Statement Batch Processing

Output:

🔹 PreparedStatement Batch Processing

Output:

🔹 Batch Processing with Error Handling

Output:

🔹 Performance Comparison

Output:

🔹 Best Practices for Batch Processing

✅ Best Practices:

❌ Avoid:

🧠 Test Your Knowledge

Which method is used to execute a batch of SQL statements?