Advertisement
Google Ad Slot: content-top
JDBC Transaction Management
JDBC Transaction Management allows us to group multiple SQL operations into a single unit. If all operations succeed, we commit the transaction; otherwise, we rollback to maintain data integrity.
Auto-Commit Mode in JDBC:
By default, JDBC runs in auto-commit mode, meaning each SQL statement is treated as a separate transaction.
Disable Auto-Commit for Manual Control:
conn.setAutoCommit(false);
After this, changes will not be committed until we explicitly call:
conn.commit(); // Save changes permanently
conn.rollback(); // Undo all changes
Performing Multiple Queries in a Transaction:
setAutoCommit(false)→ Disables auto-commit.executeUpdate()→ Executes multiple SQL queries.commit()→ Commits all changes if no error occurs.rollback()→ Rolls back all changes if any error occurs.
import java.sql.*;
public class JDBCTransactionExample {
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)) {
conn.setAutoCommit(false); // Disable auto-commit
try (Statement stmt = conn.createStatement()) {
// Insert a new employee
stmt.executeUpdate("INSERT INTO employees (id, name, role) VALUES (101, 'Alice', 'Engineer')");
// Update another employee's role
stmt.executeUpdate("UPDATE employees SET role = 'Manager' WHERE id = 102");
conn.commit(); // ✅ Commit transaction if both queries succeed
System.out.println("Transaction committed successfully!");
} catch (SQLException e) {
conn.rollback(); // ❌ Rollback if any query fails
System.out.println("Transaction rolled back due to an error.");
e.printStackTrace();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
Insert Multiple Records in a Transaction:
import java.sql.*;
public class JDBCTransactionPreparedStatement {
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)) {
conn.setAutoCommit(false); // Disable auto-commit
String sql = "INSERT INTO employees (id, name, role) VALUES (?, ?, ?)";
try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
// Insert first record
pstmt.setInt(1, 103);
pstmt.setString(2, "Bob");
pstmt.setString(3, "Analyst");
pstmt.executeUpdate();
// Insert second record
pstmt.setInt(1, 104);
pstmt.setString(2, "Charlie");
pstmt.setString(3, "Developer");
pstmt.executeUpdate();
conn.commit(); // ✅ Commit transaction
System.out.println("Transaction committed successfully!");
} catch (SQLException e) {
conn.rollback(); // ❌ Rollback on error
System.out.println("Transaction rolled back.");
e.printStackTrace();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}