JDBC handling transactions

Transaction is a set of one or more statements executed as a unit. The intent is that multiple statements which are part of a unit are executed together to make it meaningful. This article provides a quick introduction to handling transactions in JDBC.

Here are some key points to remember when dealing with transactions using JDBC.

• When a DB connection is created it is by default put in auto commit mode. The key to handle transactions is to disable auto commit mode so that the application can have better control when to commit.

connection.setAutoCommit(false);

• Since auto commit is disabled when working with transactions it is necessary to perform an explicit commit when all the statements that are part of a transaction are completed.

connect.commit();

• To undo the effects of a transaction the rollback method could be used to restore the values before the attempted transaction.

connection.rollback();

• JDBC also supports to set save points and then rollback to the specified save point. The following method could be used to define save points.

SavePoint savePoint1 = connection.setSavePoint();

• Rollback a transaction to an already defined save point using rollback call with an argument.

connection.rollback(savePoint1);

JDBC Transaction Example

Let us consider a simple example of a stock trading application where we have to update two tables one for individual transactions and another for total transactions in a day.

We are using a PostgreSQL database here. Refer to these tutorials to get started. JDBC Quick Start with PostgreSQL and JDBC Database Access Examples

Table1: Stock Trades

CREATE TABLE stock_trades
(
  trade_id numeric(6,0) NOT NULL,
  script character(10),
  num_units integer,
  CONSTRAINT stock_trades_pkey PRIMARY KEY (trade_id)
)

Table2: Total Trades

CREATE TABLE total_trades
(
  script character(10) NOT NULL,
  total_units integer,
  CONSTRAINT total_trades_pkey PRIMARY KEY (script)
)

Now let us perform a transaction where we need to update both the stock trades and total trades table.

public static void performTransaction(Connection connection, int id, String script, int units) {
  PreparedStatement insertStockTrades = null;
  PreparedStatement updateTotalTrades = null;
  
  String insertStockSql = "INSERT INTO stock_trades VALUES(?, ?, ?)";
  String updateStockSql = "UPDATE total_trades set total_units = total_units + ? where script = ?";
  
  try {
    connection.setAutoCommit(false);
    
    insertStockTrades = connection.prepareStatement(insertStockSql);
    insertStockTrades.setInt(1, id);
    insertStockTrades.setString(2, script);
    insertStockTrades.setInt(3, units);
    insertStockTrades.executeUpdate();
    
    updateTotalTrades = connection.prepareStatement(updateStockSql);
    updateTotalTrades.setInt(1, units);
    updateTotalTrades.setString(2, script);
    updateTotalTrades.executeUpdate();
    
    connection.commit();
  } catch ( SQLException  e ) {
    e.printStackTrace();
    try {
      connection.rollback();
    } catch (SQLException e1) {
      e1.printStackTrace();
    }
  } finally {
    try {
      connection.setAutoCommit(true);
    } catch (SQLException e2) {
      e2.printStackTrace();
    }
  }
}