Question

Explain Transaction Management in JDBC

Answer 1

Transaction management in JDBC involves handling database transactions within a Java application using the Java Database Connectivity (JDBC) API. Transactions ensure the consistency and integrity of the data by grouping a set of database operations that should be executed atomically (all or nothing).

Here's an example code that demonstrates transaction management in JDBC:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class TransactionExample {

    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;

        try {
            // Step 1: Establish the connection to the database
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase", "username", "password");

            // Step 2: Disable auto-commit mode
            connection.setAutoCommit(false);

            // Step 3: Create a statement object
            statement = connection.createStatement();

            // Step 4: Execute multiple SQL statements as a transaction
            statement.executeUpdate("INSERT INTO customers (name, email) VALUES ('John Doe', '[email protected]')");
            statement.executeUpdate("UPDATE accounts SET balance = balance - 100 WHERE account_id = 1");

            // Step 5: Commit the transaction
            connection.commit();

            System.out.println("Transaction committed successfully.");
        } catch (SQLException e) {
            // Step 6: Handle exceptions and rollback the transaction
            try {
                if (connection != null)
                    connection.rollback();
            } catch (SQLException ex) {
                System.out.println("Error rolling back transaction: " + ex.getMessage());
            }
            System.out.println("Transaction rolled back: " + e.getMessage());
        } finally {
            // Step 7: Close the statement and connection
            try {
                if (statement != null)
                    statement.close();
                if (connection != null)
                    connection.close();
            } catch (SQLException e) {
                System.out.println("Error closing resources: " + e.getMessage());
            }
        }
    }
}
 

Let's go through the code:

1. First, we establish a connection to the database using the DriverManager.getConnection() method, providing the appropriate URL, username, and password for your database.

2. We disable the auto-commit mode of the connection by calling connection.setAutoCommit(false). This ensures that the changes made within the transaction are not automatically committed to the database.

3. Next, we create a Statement object using the connection.createStatement() method.

4. Within the transaction block, we execute multiple SQL statements that form a transaction. In this example, we insert a new customer record and update an account balance.

5. If all the statements execute successfully, we commit the transaction by calling connection.commit(). This makes the changes permanent in the database.

6. If any exception occurs during the transaction, we catch the SQLException, rollback the transaction by calling connection.rollback(), and handle the exception. Rolling back means that all the changes made within the transaction are discarded.

7. Finally, in the finally block, we close the Statement and Connection objects to release the database resources.

Remember to replace the database URL, username, and password with the appropriate values for your database system.