Programming Tutorials

Using Transactions in JDBC

By: Ramlak in JDBC Tutorials on 2007-04-06  

By default, JDBC classes operate in auto-commit mode. This means that each SQL statement executed is considered a separate transaction ( a singleton transaction) and a commit is made at the completion of the statement. In order to group a set of transactions together, this autocommit mode must be disabled using the connection class setAutoCommit method and passing the method boolean false value.

With autocommit disabled, there is always an implicit transaction in place. To commit a series of previously executed SQL statements to the database, an explicit commit can be made by calling the Connection method commit. Alternatively, a rollback can be made by calling the Connection method rollback. This rolls back the current transaction and restores the database to the state bit was in before the start of the current transaction. Failure to commit a transaction before closing the corresponding Connection object will lead to an automatic rollback of the database updates; all work will be lost. Developers should be sure that all work is committed to the database before closing the Connection.

Various database-dependant isolation levels can be set. There are methods in the DatabaseMetaData class to learn the existing defaults in place in the current session and methods in the Connection class to change the current isolation level.

Here's a sample program that demonstrates how to use transactions in JDBC:

import java.sql.*;

public class TransactionExample {

    public static void main(String[] args) {
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost/mydatabase", "username", "password");
            conn.setAutoCommit(false); // disable auto-commit
            
            // Perform some operations
            Statement stmt = conn.createStatement();
            stmt.executeUpdate("INSERT INTO orders (customer_id, order_date, amount) VALUES (1, '2022-05-01', 100.00)");
            stmt.executeUpdate("UPDATE customers SET balance = balance - 100.00 WHERE customer_id = 1");
            
            // Commit the transaction
            conn.commit();
            
            System.out.println("Transaction committed successfully.");
        } catch (SQLException ex) {
            // Rollback the transaction if there's an exception
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex2) {
                    ex2.printStackTrace();
                }
            }
            ex.printStackTrace();
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        } finally {
            // Close the connection
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
        }
    }
}

In this program, we first disable the auto-commit mode using the setAutoCommit(false) method on the Connection object. This means that all changes to the database will be treated as part of a transaction until we explicitly commit the transaction.

We then perform some operations, including an INSERT and an UPDATE statement, which are both part of the same transaction. If any exception occurs, we catch it and rollback the transaction using the rollback() method on the Connection object.

If there are no exceptions, we commit the transaction using the commit() method on the Connection object.

Finally, we close the connection in the finally block.