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

}

❮ Previous

Next ❯

Whereisstuff is simple learing platform for beginer to advance level to improve there skills in technologies.we will provide all material free of cost.you can write a code in runkit workspace and we provide some extrac features also, you agree to have read and accepted our terms of use, cookie and privacy policy.