JDBC: Transaction Management


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.

A quick recap of java