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 }