Template method for database operations

Template methods :

• are used in most abstract base classes
• are perhaps the most commonly used of all design patterns
• define the general steps of a method, while deferring the implementation of at least one of the steps to a concrete subclass

ExampleTxTemplate is an abstract base class which defines a template method for executing multiple database operations within a transaction. It is useful to define these steps in one place. The alternative is to repeat the same structure every time a transaction is required. As usual, such code repetition should always be aggressively eliminated.
The executeTx method is the template method. It is final, and defines the general outline of how to execute a database transaction. The specific database actions to be taken are implemented by calling the abstract method executeMultipleSqls.

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 an example of using TxTemplate. It alters the set of roles attached to an end user, first by deleting all existing roles, and then by adding the new roles one at a time.

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;
  }
}