WS WhereIsStuff
Tutorial Guides
Log In Sign Up

Advertisement

Google Ad Slot: content-top

JDBC Batch Processing

Previous Next

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.");
}

Previous Next