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 }