001    package hirondelle.web4j.database;
002    
003    import java.sql.Connection;
004    import java.sql.SQLException;
005    import java.sql.CallableStatement;
006    
007    import hirondelle.web4j.BuildImpl;
008    import hirondelle.web4j.util.Consts;
009    import hirondelle.web4j.util.Args;
010    import hirondelle.web4j.util.Util;
011    
012    /**
013     Template for using <tt>CallableStatement</tt>s.
014    
015     <P>The purpose of this class is to reduce code repetition related to 
016     <tt>CallableStatement</tt>s : getting a connection, catching and translating exceptions, 
017     and closing statements and connections. As a second benefit, concrete 
018     implementations of this class have simpler, "straight-line" code, 
019     which is easier to both read and write.
020      
021     <P>This abstract base class is an example of the template design pattern.
022    
023     <P>The two constructors of this class correspond to whether or not this task 
024     is being performed as part of a transaction. 
025    
026     <P>Use of this class requires creating a subclass. Typically, such a class would likely 
027     be nested within a Data Access Object. If an inner or local class is used, then input parameters
028     defined in the enclosing class (the DAO) can be referenced directly. For example : 
029    <PRE>
030     //defined in the body of some enclosing DAO  :
031     class DoPayRun extends StoredProcedureTemplate {
032       DoPayRun(){ 
033         super( "{call do_pay_run(?)}" );
034       }
035       void executeStoredProc(CallableStatement aCallableStatement) throws SQLException {
036         //set param values, register out params, 
037         //get results, etc.
038         //fBlah is defined in the enclosing class:
039         aCallableStatement.setInt(1, fBlah);
040         fResult = aCallableStatement.executeUpdate();
041       }
042       //one way of returning a result, but there are many others :
043       int getResult(){
044         return fResult;
045       }
046       private int fResult;
047     }
048     ...
049     //in the body of a DAO method, use a DoPayRun object
050     DoPayRun doPayRun = new DoPayRun();
051     doPayRun.execute();
052     int result = doPayRun.getResult(); 
053    </PRE>
054    
055    <P>There are many ways to retrieve data from a call to a stored procedure, <em>and 
056     this task is left entirely to subclasses of </em><tt>StoredProcedureTemplate</tt>.
057    
058     <P>In the rare cases where the default <tt>ResultSet</tt> properties are not adequate, 
059     the <tt>customizeResultSet</tt> methods may be used to alter them. 
060    
061    <P><em>Design Note :</em><br>
062     Although this class is still useful, it is not completely satisfactory for two 
063     reasons :
064    <ul>
065     <li>there are many different ways to return values from stored procedures :
066     a return value expliclty defined by the stored procedure itself, 
067     <tt>OUT</tt> parameters, <tt>INOUT</tt> parameters, the <tt>executeUpdate</tt> method,
068     and the <tt>executeQuery</tt> method. There is probably no simple technique for 
069     returning all of these possible return values in a generic way. That is, it does not
070     seem possible to create a reasonable method which will return all such values. 
071     <li>although this class does eliminate code repetition, the amount of code which the 
072     caller needs is still a bit large. 
073    </ul>
074    */
075    public abstract class StoredProcedureTemplate {
076      
077      /**
078       Constructor for case where this task is <em>not</em> part of a transaction.
079      
080       @param aTextForCallingStoredProc text such as <tt>'{call do_this(?,?)}'</tt> (for 
081       more information on valid values, see 
082       <a href="http://java.sun.com/j2se/1.5.0/docs/api/java/sql/CallableStatement.html">
083       CallableStatement</a>)
084      */
085      protected StoredProcedureTemplate(String aTextForCallingStoredProc) {
086        fTextForCallingStoredProc = aTextForCallingStoredProc;
087      }
088    
089      /**
090       Constructor for case where this task is part of a transaction.
091      
092       <P>The task performed by {@link #executeStoredProc} will use <tt>aConnection</tt>, 
093       and will thus participate in any associated transaction being used by the caller.
094      
095       @param aTextForCallingStoredProc text such as <tt>'{call do_this(?,?)}'</tt> (for 
096       more information on valid values, see 
097       <a href="http://java.sun.com/j2se/1.5.0/docs/api/java/sql/CallableStatement.html">
098       CallableStatement</a>).
099       @param aSharedConnection pre-existing connection created by the caller for including  
100       multiple operations in the same transaction.
101      */
102      protected StoredProcedureTemplate(String aTextForCallingStoredProc, Connection aSharedConnection) {
103        fTextForCallingStoredProc = aTextForCallingStoredProc;
104        fSharedConnection = aSharedConnection;
105      }
106      
107      /** <b>Template</b> method which calls {@link #executeStoredProc}.  */
108      public void execute() throws DAOException {
109        Connection connection = getConnection();
110        CallableStatement callableStatement = null;
111        try {
112          if ( isUncustomizedRS() ) {
113            callableStatement = connection.prepareCall(fTextForCallingStoredProc);
114          }
115          else if ( isPartiallyCustomizedRS() ) {
116            callableStatement = connection.prepareCall(
117              fTextForCallingStoredProc, 
118              fRSType, 
119              fRSConcurrency
120            );
121          }
122          else {
123            //fully customised ResultSet
124            callableStatement = connection.prepareCall(
125              fTextForCallingStoredProc, 
126              fRSType, 
127              fRSConcurrency, 
128              fRSHoldability
129            );
130          }
131          executeStoredProc(callableStatement);
132        }
133        catch (SQLException ex) {
134          throw new DAOException( getErrorMessage(ex) , ex);
135        }
136        finally {
137          close(callableStatement, connection);
138        }
139      }
140      
141      /**
142       Perform the core task.
143      
144       <P>Implementations of this method do not fetch a connection, catch exceptions, or 
145       call <tt>close</tt> methods. Those tasks are handled by this base class.
146      
147       <P>See class description for an example.
148      */
149      protected abstract void executeStoredProc(CallableStatement aCallableStatement) throws SQLException;
150    
151      /**
152       Change to a non-default database.
153        
154       <P>Use this method to force this class to use an 
155       <em>internal</em> connection a non-default database. It does not make sense to call this method when using 
156       an <em>external</em> {@link Connection} - that is, when using {@link StoredProcedureTemplate#StoredProcedureTemplate(String, Connection)}.
157       
158       <P>See {@link ConnectionSource} for more information on database names.
159       
160       @param aDatabaseName one of the values returned by {@link ConnectionSource#getDatabaseNames()}
161      */
162      protected final void setDatabaseName( String aDatabaseName ){
163        Args.checkForContent(aDatabaseName);
164        fDatabaseName = aDatabaseName;
165      }
166       
167      
168      /**
169       Change the properties of the default <tt>ResultSet</tt>, in exactly the same manner as
170       {@link java.sql.Connection#prepareCall(java.lang.String, int, int)}.
171      
172       <P>In the rare cases where this method is used, it must be called before
173       {@link #execute}.
174      */
175      protected final void customizeResultSet(int aResultSetType, int aResultSetConcurrency){
176        fRSType = aResultSetType;
177        fRSConcurrency = aResultSetConcurrency;
178      }
179      
180      /**
181       Change the properties of the default <tt>ResultSet</tt>, in exactly the same manner as
182       {@link java.sql.Connection#prepareCall(java.lang.String, int, int, int)}.
183      
184       <P>In the rare cases where this method is used, it must be called before {@link #execute}.
185      */
186      protected final void customizeResultSet(int aResultSetType, int aResultSetConcurrency, int aResultSetHoldability){
187        fRSType = aResultSetType;
188        fRSConcurrency = aResultSetConcurrency;
189        fRSHoldability = aResultSetHoldability;
190      }
191      
192      // PRIVATE //
193      
194      private final String fTextForCallingStoredProc;
195      private Connection fSharedConnection;
196      
197      /*
198       These three items are passed to the various overloads of Connection.prepareCall, and 
199       carry the same meaning as defined by those methods.
200      
201       A value of 0 for any of these items indicates that they have not been set by the 
202       caller.
203      */
204      private int fRSType;
205      private int fRSConcurrency;
206      private int fRSHoldability;
207      
208      private String fDatabaseName = Consts.EMPTY_STRING;
209      
210      private Connection getConnection() throws DAOException {
211        Connection result = null; 
212        if ( isSharedConnection() ) {
213          result = fSharedConnection;
214        }
215        else {
216          if ( Util.textHasContent(fDatabaseName) ){
217            result = BuildImpl.forConnectionSource().getConnection(fDatabaseName);
218          }
219          else {
220            result = BuildImpl.forConnectionSource().getConnection();
221          }
222        }
223        return result;
224      }
225    
226      private boolean isSharedConnection() {
227        return fSharedConnection != null;
228      }
229      
230      private String getErrorMessage(SQLException ex){
231        return 
232          "Cannot execute CallableStatement in the expected manner : " + 
233          fTextForCallingStoredProc + Consts.SPACE + 
234          ex.getMessage()
235        ;
236      }
237      
238      private void close(CallableStatement aStatement, Connection aConnection) throws DAOException {
239        if ( isSharedConnection() ) {
240          DbUtil.close(aStatement);
241        }
242        else {
243          DbUtil.close(aStatement, aConnection);
244        }
245      }
246      
247      private boolean isUncustomizedRS(){
248        return fRSType == 0;
249      }
250    
251      private boolean isPartiallyCustomizedRS(){
252        return fRSType != 0 && fRSHoldability == 0;
253      }
254    }