package hirondelle.web4j.database;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.CallableStatement;

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

/**
 Template for using <tt>CallableStatement</tt>s.

 <P>The purpose of this class is to reduce code repetition related to 
 <tt>CallableStatement</tt>s : getting a connection, catching and translating exceptions, 
 and closing statements and connections. As a second benefit, concrete 
 implementations of this class have simpler, "straight-line" code, 
 which is easier to both read and write.
  
 <P>This abstract base class is an example of the template design pattern.

 <P>The two constructors of this class correspond to whether or not this task 
 is being performed as part of a transaction. 

 <P>Use of this class requires creating a subclass. Typically, such a class would likely 
 be nested within a Data Access Object. If an inner or local class is used, then input parameters
 defined in the enclosing class (the DAO) can be referenced directly. For example : 
<PRE>
 //defined in the body of some enclosing DAO  :
 class DoPayRun extends StoredProcedureTemplate {
   DoPayRun(){ 
     super( "{call do_pay_run(?)}" );
   }
   void executeStoredProc(CallableStatement aCallableStatement) throws SQLException {
     //set param values, register out params, 
     //get results, etc.
     //fBlah is defined in the enclosing class:
     aCallableStatement.setInt(1, fBlah);
     fResult = aCallableStatement.executeUpdate();
   }
   //one way of returning a result, but there are many others :
   int getResult(){
     return fResult;
   }
   private int fResult;
 }
 ...
 //in the body of a DAO method, use a DoPayRun object
 DoPayRun doPayRun = new DoPayRun();
 doPayRun.execute();
 int result = doPayRun.getResult(); 
</PRE>

<P>There are many ways to retrieve data from a call to a stored procedure, <em>and 
 this task is left entirely to subclasses of </em><tt>StoredProcedureTemplate</tt>.

 <P>In the rare cases where the default <tt>ResultSet</tt> properties are not adequate, 
 the <tt>customizeResultSet</tt> methods may be used to alter them. 

<P><em>Design Note :</em><br>
 Although this class is still useful, it is not completely satisfactory for two 
 reasons :
<ul>
 <li>there are many different ways to return values from stored procedures :
 a return value expliclty defined by the stored procedure itself, 
 <tt>OUT</tt> parameters, <tt>INOUT</tt> parameters, the <tt>executeUpdate</tt> method,
 and the <tt>executeQuery</tt> method. There is probably no simple technique for 
 returning all of these possible return values in a generic way. That is, it does not
 seem possible to create a reasonable method which will return all such values. 
 <li>although this class does eliminate code repetition, the amount of code which the 
 caller needs is still a bit large. 
</ul>
*/
public abstract class StoredProcedureTemplate {
  
  /**
   Constructor for case where this task is <em>not</em> part of a transaction.
  
   @param aTextForCallingStoredProc text such as <tt>'{call do_this(?,?)}'</tt> (for 
   more information on valid values, see 
   <a href="http://java.sun.com/j2se/1.5.0/docs/api/java/sql/CallableStatement.html">
   CallableStatement</a>)
  */
  protected StoredProcedureTemplate(String aTextForCallingStoredProc) {
    fTextForCallingStoredProc = aTextForCallingStoredProc;
  }

  /**
   Constructor for case where this task is part of a transaction.
  
   <P>The task performed by {@link #executeStoredProc} will use <tt>aConnection</tt>, 
   and will thus participate in any associated transaction being used by the caller.
  
   @param aTextForCallingStoredProc text such as <tt>'{call do_this(?,?)}'</tt> (for 
   more information on valid values, see 
   <a href="http://java.sun.com/j2se/1.5.0/docs/api/java/sql/CallableStatement.html">
   CallableStatement</a>).
   @param aSharedConnection pre-existing connection created by the caller for including  
   multiple operations in the same transaction.
  */
  protected StoredProcedureTemplate(String aTextForCallingStoredProc, Connection aSharedConnection) {
    fTextForCallingStoredProc = aTextForCallingStoredProc;
    fSharedConnection = aSharedConnection;
  }
  
  /** <b>Template</b> method which calls {@link #executeStoredProc}.  */
  public void execute() throws DAOException {
    Connection connection = getConnection();
    CallableStatement callableStatement = null;
    try {
      if ( isUncustomizedRS() ) {
        callableStatement = connection.prepareCall(fTextForCallingStoredProc);
      }
      else if ( isPartiallyCustomizedRS() ) {
        callableStatement = connection.prepareCall(
          fTextForCallingStoredProc, 
          fRSType, 
          fRSConcurrency
        );
      }
      else {
        //fully customised ResultSet
        callableStatement = connection.prepareCall(
          fTextForCallingStoredProc, 
          fRSType, 
          fRSConcurrency, 
          fRSHoldability
        );
      }
      executeStoredProc(callableStatement);
    }
    catch (SQLException ex) {
      throw new DAOException( getErrorMessage(ex) , ex);
    }
    finally {
      close(callableStatement, connection);
    }
  }
  
  /**
   Perform the core task.
  
   <P>Implementations of this method do not fetch a connection, catch exceptions, or 
   call <tt>close</tt> methods. Those tasks are handled by this base class.
  
   <P>See class description for an example.
  */
  protected abstract void executeStoredProc(CallableStatement aCallableStatement) throws SQLException;

  /**
   Change to a non-default database.
    
   <P>Use this method to force this class to use an 
   <em>internal</em> connection a non-default database. It does not make sense to call this method when using 
   an <em>external</em> {@link Connection} - that is, when using {@link StoredProcedureTemplate#StoredProcedureTemplate(String, Connection)}.
   
   <P>See {@link ConnectionSource} for more information on database names.
   
   @param aDatabaseName one of the values returned by {@link ConnectionSource#getDatabaseNames()}
  */
  protected final void setDatabaseName( String aDatabaseName ){
    Args.checkForContent(aDatabaseName);
    fDatabaseName = aDatabaseName;
  }
   
  
  /**
   Change the properties of the default <tt>ResultSet</tt>, in exactly the same manner as
   {@link java.sql.Connection#prepareCall(java.lang.String, int, int)}.
  
   <P>In the rare cases where this method is used, it must be called before
   {@link #execute}.
  */
  protected final void customizeResultSet(int aResultSetType, int aResultSetConcurrency){
    fRSType = aResultSetType;
    fRSConcurrency = aResultSetConcurrency;
  }
  
  /**
   Change the properties of the default <tt>ResultSet</tt>, in exactly the same manner as
   {@link java.sql.Connection#prepareCall(java.lang.String, int, int, int)}.
  
   <P>In the rare cases where this method is used, it must be called before {@link #execute}.
  */
  protected final void customizeResultSet(int aResultSetType, int aResultSetConcurrency, int aResultSetHoldability){
    fRSType = aResultSetType;
    fRSConcurrency = aResultSetConcurrency;
    fRSHoldability = aResultSetHoldability;
  }
  
  // PRIVATE //
  
  private final String fTextForCallingStoredProc;
  private Connection fSharedConnection;
  
  /*
   These three items are passed to the various overloads of Connection.prepareCall, and 
   carry the same meaning as defined by those methods.
  
   A value of 0 for any of these items indicates that they have not been set by the 
   caller.
  */
  private int fRSType;
  private int fRSConcurrency;
  private int fRSHoldability;
  
  private String fDatabaseName = Consts.EMPTY_STRING;
  
  private Connection getConnection() throws DAOException {
    Connection result = null; 
    if ( isSharedConnection() ) {
      result = fSharedConnection;
    }
    else {
      if ( Util.textHasContent(fDatabaseName) ){
        result = BuildImpl.forConnectionSource().getConnection(fDatabaseName);
      }
      else {
        result = BuildImpl.forConnectionSource().getConnection();
      }
    }
    return result;
  }

  private boolean isSharedConnection() {
    return fSharedConnection != null;
  }
  
  private String getErrorMessage(SQLException ex){
    return 
      "Cannot execute CallableStatement in the expected manner : " + 
      fTextForCallingStoredProc + Consts.SPACE + 
      ex.getMessage()
    ;
  }
  
  private void close(CallableStatement aStatement, Connection aConnection) throws DAOException {
    if ( isSharedConnection() ) {
      DbUtil.close(aStatement);
    }
    else {
      DbUtil.close(aStatement, aConnection);
    }
  }
  
  private boolean isUncustomizedRS(){
    return fRSType == 0;
  }

  private boolean isPartiallyCustomizedRS(){
    return fRSType != 0 && fRSHoldability == 0;
  }
}
