In JDBC (Java Database Connectivity), transaction management refers to the management of transactions between a Java application and a database. A transaction is a logical unit of work that contains one or more database operations, and it should either be committed (all operations completed successfully) or rolled back (any operation fails). JDBC provides transaction management to ensure data integrity and consistency.
JDBC supports two types of transactions: Auto-commit and Manual-commit transactions.
In auto-commit mode, each SQL statement is treated as a single transaction and is automatically committed immediately after it is executed. In manual-commit mode, the application explicitly starts and ends transactions.
To perform manual-commit transactions, we use the Connection object’s setAutoCommit() method to disable auto-commit mode. Then, we create transactions by calling the Connection object’s beginTransaction() method, and we commit or roll back the transaction using the commit() or rollback() method, respectively.
Here’s an example that demonstrates transaction management in JDBC:
import java.sql.*;
public class TransactionExample {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase", "root", "password");
// Disable auto-commit mode
conn.setAutoCommit(false);
// Create a statement object
stmt = conn.createStatement();
// Execute the first update
String sql = "UPDATE employee SET salary = salary + 1000 WHERE id = 1";
stmt.executeUpdate(sql);
// Execute the second update
sql = "UPDATE employee SET salary = salary - 500 WHERE id = 2";
stmt.executeUpdate(sql);
// Commit the transaction
conn.commit();
System.out.println("Transaction successful.");
} catch (SQLException e) {
// Rollback the transaction
try {
if (conn != null)
conn.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
try {
if (stmt != null)
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
In this example, we first disable auto-commit mode by calling the setAutoCommit() method on the Connection object. Then, we create two SQL update statements to modify data in the employee table. After executing both statements, we commit the transaction using the commit() method.
If any exception occurs during the transaction, we catch it and roll back the transaction using the rollback() method. Finally, we close the statement and connection objects using the close() method.