Architecture & DesignHow to Handle Transactions with the JDBC API

How to Handle Transactions with the JDBC API

Developer.com content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

There are occasions where we want a group of interrelated activities to either perform as a single unit or not at all. This collection of activities is referred to as a transaction. A transaction must fail or succeed as a group because a break in the individual units of the task may create havoc with respect to the maintenance of data integrity. In a real application, a transaction is so crucial that it is maintained in more than one layer or level. The Java library provides APIs to achieve many of these feats. The JDBC API library is one of them and here it is maintained in close association with the underlying database system. The API support is fairly exhaustive. This article attempts to show how transactions are handled with the JDBC API.

Potential Disaster in Transactions

When an application wants to make any changes in one or more table, it is crucial that either all the changes are reflected in the database or none of them do. One of the classical examples in this regard is the bank transaction or booking a ticket. Suppose one wants to transfer money from one account to another account, say from s current account to a savings account. Also, assume that two different types of accounts are actually maintained in a different table. Two UPDATE statements are fired to make the appropriate changes to two different tables. The amount of the appropriate balance is subtracted by one UPDATE statement, whereas another UPDATE statement adds the appropriate amount to the balance of another account. In essence, one update is the cause of another update. It is fairly easy to understand that both the update statements must occur as a single unit.

Following is a very crude way to represent a simple transaction process.

package transaction_demo;

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

public class TransactionDemo {

   private static final String URL =
      "jdbc:mysql://localhost:3306/mybank";
   private static final String USER = "root";
   private static final String PASSWORD = "secret";
   private static final String DRIVER =
      "com.mysql.jdbc.Driver";

   public void transferFund(String fromTable, String toTable,
      String ofAccNumber, int amount)
         throws ClassNotFoundException,
         UnsufficientFundException, SQLException {
            Class.forName(DRIVER);

      try (Connection connection =
         DriverManager.getConnection(URL, USER,
            PASSWORD)) {

         Statement statement = connection.createStatement();
         ResultSet resultSet = statement
            .executeQuery("SELECT balance FROM "
               + fromTable + " WHERE acc_no ="
               + ofAccNumber);
         resultSet.next();
         int balance1 = resultSet.getInt(1) - amount;
         if (balance1 < 0)
            throw new
               UnsufficientFundException("Unsufficient Fund.");
         resultSet.close();
         resultSet = statement.executeQuery("SELECT balance FROM "
            + toTable + " WHERE acc_no =" + ofAccNumber);
         resultSet.next();
         int balance2 = resultSet.getInt(1);
         statement.executeUpdate(
            "UPDATE " + fromTable + " SET balance=" + (balance1)
            + " WHERE acc_no=" + ofAccNumber);
         statement.executeUpdate(
            "UPDATE " + toTable + " SET balance="
            + (balance2 + amount)
            + " WHERE acc_no=" + ofAccNumber);
      }
   }

   public void showBalance(String table, String accno)
         throws ClassNotFoundException, SQLException {

      Class.forName(DRIVER);

      try (Connection connection =
            DriverManager.getConnection(URL, USER,
               PASSWORD)) {

         Statement statement = connection.createStatement();
         ResultSet resultSet =
            statement.executeQuery("SELECT balance FROM "
            + table + " WHERE acc_no =" + accno);
         while (resultSet.next())
            System.out.println(table + " " + accno
               + " " + resultSet.getInt(1));
      }
   }

   class UnsufficientFundException extends Exception {
      private static final long serialVersionUID = 1L;

      public UnsufficientFundException(String message) {
         super(message);
      }
   }

   public static void main(String[] args) throws Exception {

      TransactionDemo t = new TransactionDemo();

      t.showBalance("savings_acc", "1001");
      t.showBalance("current_acc", "1001");
      t.transferFund("savings_acc", "current_acc", "1001", 8000);
      t.showBalance("savings_acc", "1001");
      t.showBalance("current_acc", "1001");
   }
}

The code works, but is a potential for transaction problems because each unit of activity invokes a singularity of events and there is no mechanism applied to denote these individual activities as a single unit of work. During execution, it is quite possible that the application is interrupted in between the interweaving plot of updates. It may happen that the amount deducted is not reflected in the database or the amount is deducted from one account successfully but updating another account with same amount could not happen due to some interruption. The person who initiated the fund transfer had no idea where the deducted amount went. This is anarchy. There is no mechanism applied to the code to stop such a disaster.

So, our first concern is to apply some mechanism to group the activities as a single unit. Unfortunately, JDBC has no rule to define a batch-like activity. But, it does provide some methods which, when applied, enable us to demarcate the beginning and end of a transaction.

Transaction Demarcation

The methods supplied by JDBC can be used to define the start of a transaction, end of a transaction, and explicitly do the commit or roll back at the end of a transaction. Commit refers to actually writing the changes in the database table and roll back refers to aborting the transaction at any given point of time, respectively.

In JDBC, it is not always necessary to explicitly denote the start of a transaction because all updates are, by default, considered as a part of the transaction. A commit operation is also performed by default after each update. However, we can disable or enable this default behavior by invoking the Connection method setAutoCommit() with a true/false boolean value.

In relation to this the method, commit() and rollback() are used to end or abort the transaction, respectively.

Because only one transaction can be active at a given point in time, only one connection can be active. In case we want multiple transactions to be active, we also must obtain multiple connections for each transaction.

Now, armed with the preceding knowledge, let’s rewrite the earlier code and rectify the potential problem.

package transaction_demo;

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

public class TransactionDemo {

   private static final String URL =
      "jdbc:mysql://localhost:3306/mybank";
   private static final String USER = "root";
   private static final String PASSWORD = "secret";
   private static final String DRIVER =
      "com.mysql.jdbc.Driver";

   public void transferFund(String fromTable, String toTable,
         String ofAccNumber, int amount)
         throws ClassNotFoundException,
         UnsufficientFundException, SQLException{
      Class.forName(DRIVER);
      Statement statement = null;
      ResultSet resultSet = null;
      Connection connection = null;
      try {
         connection = DriverManager.getConnection(URL, USER,
            PASSWORD);
         connection.setAutoCommit(false);

         statement = connection.createStatement();
         resultSet = statement
            .executeQuery("SELECT balance FROM " + fromTable
               + " WHERE acc_no =" + ofAccNumber);
         resultSet.next();
         int balance1 = resultSet.getInt(1) - amount;
         if (balance1 < 0)
            throw new UnsufficientFundException("Unsufficient Fund.");
         resultSet.close();
         resultSet = statement.executeQuery("SELECT balance FROM "
            + toTable + " WHERE acc_no =" + ofAccNumber);
         resultSet.next();
         int balance2 = resultSet.getInt(1);
         statement.executeUpdate(
            "UPDATE " + fromTable + " SET balance=" + (balance1)
            + " WHERE acc_no=" + ofAccNumber);
         statement.executeUpdate(
            "UPDATE " + toTable + " SET balance="
            + (balance2 + amount)
            + " WHERE acc_no=" + ofAccNumber);
         connection.commit();
      }catch(SQLException ex){
         connection.rollback();
         throw ex;
      }finally{
         if (resultSet!= null)
            resultSet.close();
         if (statement != null)
            statement.close();
         connection.close();
      }
   }

   public void showBalance(String table, String accno)
         throws ClassNotFoundException, SQLException {
      Class.forName(DRIVER);

      try (Connection connection =
            DriverManager.getConnection(URL, USER,
               PASSWORD)) {

         Statement statement = connection.createStatement();
         ResultSet resultSet = statement.executeQuery("SELECT balance FROM "
            + table + " WHERE acc_no =" + accno);
         while (resultSet.next())
            System.out.println(table + " "
               + accno + " " + resultSet.getInt(1));
      }
   }

   class UnsufficientFundException extends Exception {
      private static final long serialVersionUID = 1L;

      public UnsufficientFundException(String message) {
         super(message);
      }
   }

   public static void main(String[] args) throws Exception {

      TransactionDemo t = new TransactionDemo();

      t.showBalance("savings_acc", "1001");
      t.showBalance("current_acc", "1001");
      t.transferFund("savings_acc", "current_acc", "1001", 8000);
      t.showBalance("savings_acc", "1001");
      t.showBalance("current_acc", "1001");
   }
}

Savepoints

The rollback() methods can be invoked at any time to abort the transaction from actually modifying the database. JDBC 3.0 introduced an interesting concept, called save points. The save points allow us to designate a subset of a transaction that can be rolled back without canceling all the changes made since the beginning of the transaction. A simple analogy to understand this is the save points we make while playing a game. We can start playing again from any of the saved positions. Save points, in a similar fashion, can save the point before the second update and carry on the next. At any later point in time a rollback occurs, the transaction can start from the last successful save point denoted. This leverages the efficiency of transaction handling because the transaction can always start from the last defined point rather then start again from the beginning. The following code snippet would perhaps illustrate it better.

Connection connection;
Savepoint savepoint = null;
// ...
try {
   updateTable1(connection);
   savepoint = connection.setSavepoint();
   updateTable2(connection);
   connection.commit();
}
catch (SQLException ex) {
   if (savepoint != null) {
      connection.rollback(savepoint);
   }
   else {
      connection.rollback();
   }
}

Points to be Noted

  • Transactions are not limited to UPDATE operations only. They can applied to multiple SELECT or INSERT statements or any CRUD combination of operations that have a potentiality to create a data integrity problem.
  • The disasters that are caused by unmanaged transactions have specific, names such as dirty reads, non-repeatable reads, phantom reads, and so forth. Any standard book on databases would give a fairly good description about them.
  • Regardless of the SQL statement used, actual transaction support is provided by the underlying database used and not by the JDBC driver. So, for example, if save points are not working in a Java code, blame it on the database system and not on the JDBC API.
  • What happens with the transaction where tables are distributed across multiple machines? This is called distributed transaction, something which is not dealt in this article. A couple of hints to read further on distributed transaction: Java handles distributed transaction through JTA and JTS, and connection pooling.

Conclusion

Transaction is a collective activity meant to be executed as a single unit of operation. JDBC provides certain rules to manage transactions in the form of methods. These APIs interact with the underlying database to bring the actual effect. Therefore, a transaction is actually the power of the database system and JDBC just provides the means of interaction with the database through Java code.

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Latest Posts

Related Stories