Use template for transactions

Code related to transactions is bulky, and has significant logic attached to it. Such code should not be repeated every time a transaction is required. Instead, define a template method which handles most of the processing of the transaction.
Example
Tx is a generic interface which can be applied to both

• local transactions - attached to a single JDBC connection
• distributed transactions (UserTransaction) - not attached to a single connection, or even to a single database

package hirondelle.web4j.database;

/**
 Execute a database transaction.

 <P>Should be applied only to operations involving more than one SQL statement.
*/
public interface Tx {

  /**
   Execute a database transaction, and return the number of edited records.
  */
  int executeTx() throws DAOException;
  
}
 

TxTemplate is an implementation of Tx for local transactions. (An implementation for distributed transactions would be similar). It is an Abstract Base Class, and uses the template method design pattern. To implement a transaction, callers subclass TxTemplate, and provide a concrete implementation of executeMultipleSqls(Connection).

import java.sql.*;
import java.util.logging.*;

import hirondelle.web4j.BuildImpl;
import hirondelle.web4j.util.Util;
import hirondelle.web4j.util.Consts;

/** 
* Template for executing a local, non-distributed transaction versus a single database,  
* using a single connection.
*
* <P>This abstract base class implements the template method design pattern.  
*/
public abstract class TxTemplate implements Tx {
  
  //..elided
  
  /**
  * <b>Template</b> method calls the abstract method {@link #executeMultipleSqls}.
  * <P>Returns the same value as <tt>executeMultipleSqls</tt>.
  *
  * <P>A <tt>rollback</tt> is performed if <tt>executeMultipleSqls</tt> throws 
  * a {@link SQLException} or
  * {@link DAOException}, or if {@link #executeMultipleSqls(Connection)} returns 
  * {@link #BUSINESS_RULE_FAILURE}.  
  */
  public final int executeTx() throws DAOException {
    int result = 0;
    fLogger.fine(
      "Editing within a local transaction, with isolation level : " + fTxIsolationLevel
    );
    ConnectionSource connSource = BuildImpl.forConnectionSource();
    if(Util.textHasContent(fDatabaseName)){
      fConnection = connSource.getConnection(fDatabaseName);
    }
    else {
      fConnection = connSource.getConnection();
    }
    
    try {
      TxIsolationLevel.set(fTxIsolationLevel, fConnection);
      startTx();
      result = executeMultipleSqls(fConnection);
      endTx(result);
    }
    catch(SQLException rootCause){
      fLogger.fine("Transaction throws SQLException.");
      rollbackTx();
      String message = 
        "Cannot execute edit. ErrorId code : " +  rootCause.getErrorCode() + 
        Consts.SPACE + rootCause
      ;
      if (rootCause.getErrorCode() == DbConfig.getErrorCodeForDuplicateKey().intValue()){
        throw new DuplicateException(message, rootCause);
      }
      throw new DAOException(message, rootCause);
    }
    catch (DAOException ex){
      fLogger.fine("Transaction throws DAOException.");
      rollbackTx();
      throw ex;
    }
    finally {
      DbUtil.logWarnings(fConnection);
      DbUtil.close(fConnection);
    }
    fLogger.fine("Total number of edited records: " + result);
    return result;
  }

  /**
  * Execute multiple SQL operations in a single local transaction.
  *
  * <P>This method returns the number of records edited. If a business rule 
  * determines that a 
  * rollback should be performed, then it is recommended that the special value
  * {@link #BUSINESS_RULE_FAILURE} be returned by the implementation. This will
  *  signal to 
  * {@link #executeTx()} that a rollback must be performed. (Another option for 
  * signalling that a rollback is desired is to throw a checked exception.)  
  *
  * <P><em>Design Note</em>: allowing <tt>SQLException</tt> in the <tt>throws</tt> 
  * clause simplifies the implementor significantly, since no <tt>try-catch</tt> 
  * blocks are needed. Thus, the caller has simple, "straight-line" code.
  *
  * @param aConnection must be used by all SQL statements participating in this 
  * transaction
  * @return number of records edited by this operation. Implementations may return
  * {@link #BUSINESS_RULE_FAILURE} if there is a business rule failure. 
  */
  public abstract int executeMultipleSqls(
    Connection aConnection
  ) throws SQLException, DAOException;
  
  /**
  * Value {@value}. Special value returned by 
  * {@link #executeMultipleSqls(Connection)} to indicate that  
  * a business rule has been violated. Such a return value indicates to this class
  * that a rollback must be performed.
  */
  public static final int BUSINESS_RULE_FAILURE = -1;
  
  // PRIVATE //
  
  /**
  * The connection through which all SQL statements attached to this 
  * transaction are executed. This connection may be for the default 
  * database, or any other defined database. See {@link #fDatabaseName}.
  */
  private Connection fConnection;
  
  /** 
  * Identifier for a non-default database. The connection taken from the default
  * database only if this item has no content. 
  */
  private String fDatabaseName;
  
  /**
  * The transaction isolation level, defaults to level configured in 
  * <tt>web.xml</tt>.
  */
  private final TxIsolationLevel fTxIsolationLevel;
  
  private static final boolean fOFF = false;
  private static final boolean fON = true;
  
  private static final Logger fLogger = Util.getLogger(TxTemplate.class);  

  private void startTx() throws SQLException {
    fConnection.setAutoCommit(fOFF);
  }
  
  private void endTx(int aNumEdits) throws SQLException, DAOException {
    if ( BUSINESS_RULE_FAILURE == aNumEdits ) {
      fLogger.severe("Business rule failure occured. Cannot commit transaction.");
      rollbackTx();
    }
    else {
      fLogger.fine("Commiting transaction.");
      fConnection.commit();
      fConnection.setAutoCommit(fON);
    }
  }
  
  private void rollbackTx() throws DAOException {
    fLogger.severe("ROLLING BACK TRANSACTION.");
    try {
      fConnection.rollback();
    }
    catch(SQLException ex){
      throw new DAOException("Cannot rollback transaction", ex);
    }
  }
}
 

Here is a concrete implementation named RoleDAO. It has a change method which updates the roles attached to a user. The roles are stored in a cross-reference table. In this case, an update is implemented as 'delete all old, then add all new'. Note the lack of try..catch blocks in this class.

final class RoleDAO {

  //..elided  

  /**
  * Update all roles attached to a user.
  * 
  * <P>This implementation will treat all edits to user roles as 
  * '<tt>DELETE-ALL</tt>, then <tt>ADD-ALL</tt>' operations. 
  */
  boolean change(UserRole aUserRole) throws DAOException {
    Tx update = new UpdateTransaction(aUserRole);
    return Util.isSuccess(update.executeTx());
  }
  
  // PRIVATE //
  
  /** Cannot be a {@link hirondelle.web4j.database.TxSimple}, since there is looping. */
  private static final class UpdateTransaction extends TxTemplate {
    UpdateTransaction(UserRole aUserRole){
      super(ConnectionSrc.ACCESS_CONTROL);
      fUserRole = aUserRole;
    }
    public int executeMultipleSqls(
      Connection aConnection
    ) throws SQLException, DAOException {
      int result = 0;
      //perform edits using a shared connection
      result = result + DbTx.edit(aConnection, ROLES_DELETE, fUserRole.getUserName());
      for(Id roleId : fUserRole.getRoles()){
        result = result + DbTx.edit(aConnection,ROLES_ADD,fUserRole.getUserName(),roleId);
      }
      return result;
    }
    private UserRole fUserRole;
  }
}