Advertisement
Google Ad Slot: content-top
JDBC Batch Processing
JDBC Batch Processing allows executing multiple SQL statements in a single request, improving performance by reducing the number of database hits.
Performing Multiple Queries in a Batch Processing:
setAutoCommit(false)→ Ensures queries are executed as a single unit.addBatch(sql)→ Adds SQL queries to the batch.executeBatch()→ Executes all queries at once.commit()→ Commits the transaction after successful execution.rollback()→ Rolls back all changes if any error occurs.
import java.sql.*;
public class JDBCBatchExample {
public static void main(String[] args) {
String url = "jdbc:mysql://localhost:3306/mydb";
String user = "root";
String password = "password";
try (Connection conn = DriverManager.getConnection(url, user, password);
Statement stmt = conn.createStatement()) {
conn.setAutoCommit(false); // Disable auto-commit
// Adding SQL queries to batch
stmt.addBatch("INSERT INTO employees (id, name, role) VALUES (201, 'Alice', 'Engineer')");
stmt.addBatch("INSERT INTO employees (id, name, role) VALUES (202, 'Bob', 'Manager')");
stmt.addBatch("INSERT INTO employees (id, name, role) VALUES (203, 'Charlie', 'Analyst')");
// Execute batch
int[] result = stmt.executeBatch();
conn.commit(); // Commit transaction
System.out.println("Batch executed successfully! Rows affected: " + result.length);
} catch (SQLException e) {
conn.rollback(); // ❌ Rollback if any query fails
e.printStackTrace();
}
}
}
Savepoint in a Batch Processing:
Instead of rolling back everything, Savepoints allow rolling back to a specific point.
- It marks a specific point in the transaction.
- If an error occurs after the savepoint, you can roll back to that savepoint instead of rolling back the entire transaction.
- It helps in handling batch updates more efficiently.
try {
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
stmt.addBatch("INSERT INTO employees (id, name, role) VALUES (501, 'Jake', 'Developer')");
Savepoint savepoint1 = conn.setSavepoint("BeforeBatch");
stmt.addBatch("INSERT INTO employees (id, name, role) VALUES (502, 'Mia', 'HR')");
stmt.executeBatch();
conn.commit();
} catch (SQLException e) {
conn.rollback(); // Rollback to the savepoint
System.out.println("Rolled back to savepoint.");
}