1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18 package com.finalist.tools.database;
19
20 import java.sql.Connection;
21 import java.sql.PreparedStatement;
22 import java.sql.ResultSet;
23 import java.sql.SQLException;
24 import java.util.ArrayList;
25 import java.util.HashMap;
26 import java.util.Iterator;
27 import java.util.Map;
28
29 import org.apache.commons.beanutils.BeanUtils;
30
31
32 /***
33 * <h1>StatementExecutor</h1>
34 *
35 *
36 * This class can be used to execute SQL statements against a database. It's main purpose is to
37 * speed up JDBC coding.
38 * It requires a java.sql.Connection object in the constructor and a RowMapper to be set.
39 * The main features are <ul>
40 * <li> named parameter binding and unbinding from and to HashMaps </li>
41 * <li> binding and undbinding from and to Java Beans </li>
42 * <li> correct binding and unbinding of null values. </li>
43 * <li> encapsulation of explicit JDBC programming </li>
44 * <li> caching of PreparedStatements </li>
45 * <li> enhanced error messaging using a runtime Exception </li>
46 * </ul>
47 *
48 * In the Constructor, the StatementExecutor takes a <code>java.sql.SQLConnection</code> as
49 * well as a <code>com.finalist.util.database.RowMapper</code> object. The <code>RowMapper</code> object
50 * does the binding of objects into PreparedStatements as well as the unbinding of ResultSets back
51 * into Java objects. Since this is rather database specific stuff, every database requires it's
52 * own extension of the abstract <code>RowMapper</code>.<br>
53 *
54 * Explanation of the workings will be done using code examples. First will be shown how
55 * parameter binding is done.
56 * <br>
57 * <h2>Binding</h2>
58 * Take a look at the following code snippet that presumes a <code>Connection</code> conn to be there. An
59 * <code>OracleRowMapper</code> is used:<br>
60 * <code><pre>
61 *
62 * StatementExecutor s = new StatementExecutor(conn, new OracleRowMapper()); //use the Oracle RowMapper
63 *
64 * s.prepareStatement("SELECT * FROM EMP WHERE MGR=:MANAGERNO AND SAL> :SALARY");
65 * s.setBindVar("MANAGERNO", new Integer(1343));
66 * s.setBindVar("SALARY", new Short(2000));
67 * </pre></code>
68 * The first thing that should be noted is the notation of the bind variable in the SQL statement. Rather than
69 * using the "?" as in traditional JDBC programming, <code>StatementExecutor</code> requires the bindvariables
70 * to be explicitly named using a colon as prefix. <br>
71 * The method <code>setBindVar(String bindName, Object bindVar)</code> binds an Integer object using
72 * the name MANAGERNO into the prepared statement. The code that does the actual binding into the JDBC
73 * prepared statement can be found in the RowMapper object. <br><br>
74 * Binding can also be done using an explicit HashMap with named bind variables and binding this HashMap
75 * in one time
76 * using the <code>setBindVars(HashMap bindVars)</code> method:<br>
77 * <code><pre>
78 *
79 * HashMap binds = new HashMap();
80 * binds.put("MANAGERNO",new Integer(1343));
81 * binds.put("SALARY", new Short((short)2000));
82 *
83 * s.prepareStatement("SELECT * SALARY FROM EMP WHERE MGR=:MANAGERNO AND SAL> :SALARY");
84 * s.setBindVars(binds);<br>
85 * </pre></code>
86 * Finally, the bindvariables can be set in the form of a Java Bean. Assume a bean <code>Employee</code>
87 * to be there with the properties <i>name</i>, <i>telephoneNumber</i> and <i>department</i> and the
88 * associated <code>getter</code> and <code>setter</code> methods:
89 * <pre><code>
90 * Employee emp = new Employee();
91 * emp.setName("Peter");
92 * emp.setTelephoneNumber(31104047395)
93 * emp.setDepartment("Development");
94 *
95 * s.prepareStatement("INSERT INTO EMP (NAME,TELNO,DEPT) VALUES(:NAME, :TELEPHONENUMBER, :DEPARTMENT)");
96 * s.setBindVar(emp);
97 *
98 * </pre></code>
99 * The method <code>setBindBean(Object bean)</code> derives the properties from the bean (using reflection) and
100 * binds them at the appropriate places into the prepared statement. The mapping between the property names and
101 * the bindvariables is case-insensitive. Furthermore, if the used RowMapper supports this, it does not matter
102 * whether the properties are primitives or Objects. <br>
103 * The three ways of binding that have been described can be used next to each other for the same statement
104 * execution. Look at the following code snippet that reuses the previously instantiated <code>Employee</code>
105 * object emp as well as the <code>HashMap</code> binds:
106 * <code><pre>
107 * s.prepareStatement("INSERT INTO EMP (NAME,TELNO,DEPT,SAL,MGR,STARTDATE) "
108 * + "VALUES(:NAME, :TELEPHONENUMBE, :DEPARTMENT)");
109 * s.setBindVars(emp);
110 * s.setBindVars(binds);
111 * s.setBindVar("STARTDATE", new java.util.Date());
112 * </pre></code><br>
113 * The following rules are used when bind variables with the same names are found:<ul>
114 * <li>Properties of bind beans take precedence over the other bind variables</li>
115 * <li><code>setBindVar(String name, Object object)</code> and <code>setBindVars(HashMap bind)</code> overwrite
116 * each other, so the most recently set bind variable takes precedence.
117 * </ul>
118 *
119 * <h2>DML</h2>
120 * Execution of DML (Data Modification Language, e.g. Insert, Update and Deletes) is equivalent to normal
121 * JDBC programming and performed by the method <code>executeDML()</code> that returns an int value identifying
122 * the amount of rows that were affected. A sequel of the previous code example could, for example, be:
123 * <code><pre>
124 * int nrOfRowsAffected = s.executeDML();
125 * </code></pre>
126 * Preconditions for this method to work are a valid SQL statement and the appropriate bind variables being set.<br>
127 * DDL (Data Definition Language, e.g. table creation, dropping etc.) can be performed with this method as well:<br>
128 * <code><pre>
129 * s.prepareStatement("CREATE TABLE TEST (KOLOM1 NUMBER(10))");
130 * s.executeDML();
131 * </code></pre>
132 *
133 * <h2>Querying (Unbinding)</h2>
134 * The resultset of a query is evaluated an put into an ArrayList. Evaluation can be done in two ways. First, into an
135 * ArrayList of HashMaps. Let's look at an example:
136 * <code><pre>
137 * s.prepareStatement("SELECT * FROM EMP");
138 * ArrayList results = s.executeIntoHashMap();
139 * </code></pre>
140 * After this piece of code is executed, an ArrayList is created that contains a HashMap for every row that is returned by
141 * the query. The HashMap is filled with keys of type String that are equivalent to the columnames of the queried table or
142 * to the column labels of the query, if specified. The values of the HashMap are the values returned by the query.
143 * The conversion of database data types to Java Objects is handled by the RowMapper. The reader is referred to
144 * RowMapper for details on the conversion. <br>
145 * A second option is to have the resultset evaluate into an ArrayList of Java Beans. Input for this method is one,
146 * Class class, that serves as a template:
147 * <code><pre>
148 * s.prepareStatement("SELECT NAME Name, TELNO TelephoneNumber, DEPTNO Department FROM EMP");
149 * ArrayList results = s.executeIntoBean(Employee.class);
150 * </code></pre>
151 * As a result, there will be an ArrayList containing a bean for every queried row with the properties set
152 * to the queried values. The conversion between the database datatypes returned by the query and the Java object and/or
153 * primitive attributes of the bean is done by the RowMapper.
154 * <br>
155 * <h2>Error Handling</h2>
156 * Only one type of Exception is thrown by StatementExecutor and this is <code>ExecutionException</code>. This exception
157 * extends from RuntimeException so it does not have to be explicitly caught. It is thrown upon three kind of
158 * error situations: <ul>
159 * <li> <i>SQLExceptions</i>. These exceptions are caught by StatementExecutor and the error information is
160 * being transferred to the ExecutionException. They can be caused by erroneous SQL statements, constraint violation etc.</li>
161 * <li> <i>Mapping Errors</i>. These exceptions are thrown by the RowMapper object when no mapping could
162 * be made in either the conversion from Java Objects to database datatypes or vice versa. </li>
163 * <li> <i>StatementExecutor errors</i>. These are thrown by StatementExecutor when it detects an erroneous
164 * situation. E.g. when a query is trying to be executed using the <code>executeDML()</code> method, or when there
165 * are not enough bindvariables set. </li>
166 * </ul>
167 * The <code>getMessage()</code> method of ExecutionException returns an elaborate error message containing a detailed
168 * report of the internal settings of the StatementExecutor. Too that, it tries to reconstruct the query string that
169 * is executed against the database for debugging purposes.
170 * <br>
171 * <h2>Partial result sets</h2>
172 * When dealing with large result sets, it is computationally expensive to create the correspondingly large
173 * collection of result beans. In practice, within an application such large result sets are often displayed to
174 * the user in 'chunks' using some sort of 'page through results' presentation layer device: e.g. a search results
175 * screen that shows 25 results at a time, giving the user the option of paging back and forth through the whole
176 * result set. In such cases it is unnecessary to create the entire result set in one go, and to do so would
177 * result in very poor application performance.<br><br>
178 * StatementExecutor provides methods to deal with <i>partial result sets</i>, whereby methods are called with a
179 * 'start index' and a 'maximum number of results'. Given a hypothetical result set with 100,000 results where only
180 * 25 are displayed to the user at a time, you could first call one of these methods to return results 0 - 24, then
181 * maybe later call again to return results 25 - 49, etc.
182 * <br>
183 * <h2>Prepared Statement caching</h2>
184 * StatementExecutor can run in two modes: <ol>
185 * <li>With Prepared Statement caching turned off. In this mode, every execution of a subsequent SQL statement
186 * by the same StatementExecutor instance, results in the statement being prepared again.</li>
187 * <li>In order to improve performance for situations where a couple of SQL statements need to be executed in a
188 * row from the same StatementExecutor instance, one can turn on Prepared Statement Caching by calling the method
189 * <code>enablePreparedStatementCaching()</code>. Running in this mode, StatementExecutor will cache all the prepared
190 * statements, which dramatically speeds up re-execution. In this mode, the developer has to explicitly call the
191 * <code>close()</code> method when finished, in order to close the prepared statements in the cache.
192 * </ol>
193 * Note that StatementExecutor does nothing with the nature of the Connection object reference that is passed in
194 * the constructor. Closure of the connection as well as determining the commit or rollback behaviour has still
195 * to be taken care of by the developer.
196 * <br>
197 * <h2>Usage</h2>
198 * <ul>
199 * <li><b>ID Generation</b>. The generation of an Id can be done quite easily by using freshly queried Beans
200 * as bind beans for the next statement execution. Look at the following example where, after an ID has been
201 * generated by the database, a new record is inserted into the EMP table:
202 * <pre><code>
203 * StatementExecutor s = new StatementExecutor(conn, new OracleRowMapper());
204 *
205 * //do a sequence query first
206 * s.preparesStatement("SELECT SEQUENCE.NEXTVAL ID FROM DUAL");
207 * Employee newEmp = (Employee)(s.executeQueryIntoBean(Employee.class).get(0));
208 *
209 * //enrich the employee with some more details
210 * newEmp.setName("Peter");
211 * newEmp.setTelephoneNumber(31104047395)
212 * newEmp.setDepartment("Development");
213 *
214 * System.out.println("New Employee ID :" + newEmp.getId());
215 *
216 * //and create a record in the database
217 * s.setBindBean(newEmp);
218 * s.prepareStatement("INSERT INTO EMP (ID,NAME,TELNO,DEPT) VALUES(:ID,:NAME,:TELEPHONENUMBER,:DEPARTMENT)");
219 *
220 * </pre></code>
221 * <li><b>Setting of NULL values</b></li>. Mapping the Java <code><b>null</b></code>
222 * to e.g. Oracle's <code><b>NULL</b></code> is done without pain if the RowMapper is correctly implemented.
223 * They can be bound using <code>setVindar(bindName,null);</code>, through a bind HashMap or having an null reference
224 * in a <i>Object</i> property of the bind bean. A property of <i>primitive type</i>, however will be assigned
225 * a value, most probably 0 or 0.0, upon instantiation of the bean, before being inserted. Consequently, querying
226 * NULL database values into <i>Object</i> properties of a bean will result in the null reference being
227 * assigned to this property.<br>
228 * <li><b>Stickiness of bind Variables and SQL statements</b> </li>
229 * Once set, the references to internal HashMap and bind bean, as well as the SQL statement
230 * definition, remain set after a statement execution. This allows for efficient programming:
231 * <code><pre>
232 * //query those employees that have manager Ids stored in ArrayList mgrIds,
233 * //update their salaries and so on.
234 *
235 * ArrayList emps = new ArrayList();
236 * StatementExecutor s = new StatementExecutor(conn, new OracleRowMapper());
237 * s.enablePreparedStatementCaching();
238 *
239 * //instead of using the IN clause...
240 * s.prepareStatement("SELECT * FROM EMP WHERE MGR_ID = :MGR_ID");
241 *
242 * for( Iterator i = mgrIds.iterator() ; i.hasNext(); ) {
243 * s.setBindVar("MGR_ID",i.next()); //let StatementExecutor do the typemapping
244 * emps.addCollection(s.executeQueryIntoBean(Employee.class));
245 * }
246 *
247 * //set fixed bindvars
248 * s.setBindVar("SAL",null);
249 * s.setBindVar("ENDDATE",java.util.Date());
250 * //ignore the property values of the bean by using different bind names in the statement
251 * s.prepareStatement("UPDATE EMP SET SAL = :SAL, END_EMPLOYMENT_DATE = :ENDDATE, NAME = :NAME WHERE ID = :ID");
252 * for( Iterator j = emps.iterator(); j.hasNext(); ) {
253 * Employee curEmp = (Employee)j.next();
254 * curEmp.setName(curEmp.getName+"*");
255 * s.setBindBean(curEmp);
256 * s.executeDML();
257 * }
258 *
259 * s.disablePreparedStatementCaching(); //close the cache
260 * </code></pre>
261 * <li><b>Database conversion. </b>Having multiple RowMappers in place for the different databases, it is possible to
262 * query from different databases into the same bean and vice versa. The flexibilty that exists in naming the
263 * bind variables in the statement definition can help in overcome the differences in the datamodel and naming.</li>
264 * </ul>
265 *
266 * <h2>Future Enhancements</h2>
267 * A few enhancements will be implemented in future releases: <ul>
268 * <li>Allowing the binding of ArrayLists of bind HashMaps and Beans. Internally this will result in re-execution
269 * of the statement for every set of bind variables inside the ArrayList.</li>
270 * <li>Build RowMapper extensions for MySQL and VisualFoxPro DBF and investigate whether it is possible to allow
271 * for more losely mapping of Java Objects to datatypes. Mapping of boolean to a CHAR(1) column e.g.</li>
272 * <li>Building in Locale dependency. In order to bridge the gap betweeen database language settings (think of
273 * Oracle NLS) and the Java Locales, a method can be made that sets the right language context for the database
274 * connection based on a Java Locale object. This can be useful for language dependend sorting, dateformats etc.</li>
275 * <li>Code generation. Using the RowMapper mechanism it is quite easy to generate so called Data Transfer Objects (beans)
276 * based on the output of a query. These objects can be used rightaway by StatementExecutor. Try the
277 * <code>generateBeanFromQuery()</code> method using a query that returns at least one row.
278 * Another option might be the generation of SQL statemenst and a CREATE TABLE statement
279 * taking Java Beans as an input. Generation of a BMP Entity Bean that relies on StatementExecutor for its database
280 * communication also belongs to the possibilities.</li>
281 * <li>Query externilization. In most database applications the queries used are tightly connected to the
282 * Model Objects (ValueObjects, BMP entity beans) that they operate on. This fact, taken together with the nuisance of maintaining
283 * and coding SQL statements inside your Java code,might proof it worthwile to centralize and aggregate the set
284 * of queries inside an XML file, a database table or just a Constants class and serve them to the application
285 * using a QueryPool implemented by a singleton or something alike.</li>
286 * </ul>
287 *
288 * @author P.S.D.Reitsma, Michael O'Connor - Finalist IT Group
289 */
290 public class StatementExecutor {
291
292 protected Connection conn;
293 private final Map preparedStatementCache = new HashMap();
294 private final Map queryCache = new HashMap();
295 protected final Map bindVars = new HashMap();
296 protected Object bindBean = null;
297 private boolean pstmtCaching = false;
298 private RowMapper rowMapper;
299 private String queryDefinition;
300 protected Query query = null;
301 private PreparedStatement pstmt = null;
302
303
304 /***
305 * Empty Constructor.
306 */
307 public StatementExecutor() {
308 }
309
310
311 /***
312 * Constructor.
313 *
314 * @param conn Connection connection used to execute the statements.
315 * @param rowMapper RowMapper object that contains the binding and unbinding functionality. Differs
316 * per database type.
317 */
318 public StatementExecutor(Connection conn, RowMapper rowMapper) {
319 this.conn = conn;
320 this.rowMapper = rowMapper;
321 }
322
323
324 /***
325 * Turns on Prepared Statement Caching. After this option has been turned
326 * on Prepared Statements, once prepared, will be cached and reused when the same query is
327 * being executed. This improves performance since since only variables binding needs to take
328 * place on re-execution.
329 * <b>Note: Having Prepared Statement Caching turned on, one needs to explicitly call the
330 * <code>close()</code> method when done StatementExecutor to close the Prepared Statements or
331 * call <code>disablePreparedStatementCaching()</code>.</b>
332 */
333 public void enablePreparedStatementCaching() {
334 pstmtCaching = true;
335 }
336
337
338 /***
339 * Turn Prepared Statement Caching off and implcitly close the Prepared Statement Cachne <br>
340 *
341 */
342 public void disablePreparedStatementCaching() {
343 pstmtCaching = false;
344 close();
345 }
346
347
348 /***
349 * Closes cached prepared statements.
350 *
351 */
352 public void close() {
353 Iterator it = preparedStatementCache.keySet().iterator();
354 try {
355 while (it.hasNext()) {
356 ((PreparedStatement) it.next()).close();
357 }
358 }
359 catch (SQLException Q) {
360 ExecutionException E = new ExecutionException(Q);
361 E.setPatient(this);
362 throw E;
363 }
364 }
365
366
367 /***
368 * Sets the statement definition.
369 *
370 * @param queryDefinition The definition of the query. Use colons to identiy bind variables by name.
371 */
372 public void prepareStatement(String queryDefinition) {
373 this.queryDefinition = queryDefinition;
374 }
375
376
377 /***
378 * Define one bindvariable.
379 *
380 * @param name the name of the bindvar
381 * @param bindVar the object that is bound
382 */
383 public void setBindVar(String name, Object bindVar) {
384 bindVars.put(name.toUpperCase(), bindVar);
385 }
386
387
388 /***
389 * Define the bindvariables a HashMap containing
390 * keys as bindvariable names and values as the actual bindVars
391 *
392 * @param binds
393 */
394 public void setBindVars(HashMap binds) {
395
396 Iterator keys = binds.keySet().iterator();
397 while (keys.hasNext()) {
398 String keyName = (String) keys.next();
399 bindVars.put(keyName.toUpperCase(), binds.get(keyName));
400 }
401 }
402
403
404 /***
405 * Define the bindvariables as properties of a bean
406 *
407 *
408 * @param bindBean the bean of which the properties should be used.
409 */
410 public void setBindVars(Object bindBean) {
411 this.bindBean = bindBean;
412 }
413
414
415 /***
416 * Executes DML statements and returns the amount of rows that are affected.
417 * Internally the following is done: <UL>
418 * <LI>parse the QueryDefinition </LI>
419 * <LI>bind the bindvars </LI>
420 * <LI>execute the statement </LI>
421 * <UL>
422 *
423 * @return the amount of rows affected
424 */
425 public int executeDML() {
426 prepare();
427 bind();
428 try {
429 if (query.givesResultSet) {
430 throw new SQLException("Query does not do any DML");
431 }
432 return pstmt.executeUpdate();
433 }
434 catch (SQLException Q) {
435 ExecutionException E = new ExecutionException(Q);
436 E.setPatient(this);
437 throw E;
438 }
439 finally {
440 if (pstmtCaching) {
441 preparedStatementCache.put(queryDefinition, pstmt);
442 }
443 else {
444 try {
445 if (pstmt != null) {
446 pstmt.close();
447 pstmt = null;
448 }
449 }
450 catch (SQLException q) {
451 ExecutionException e = new ExecutionException(q);
452 e.setPatient(this);
453 throw e;
454 }
455 }
456 }
457 }
458
459
460 /***
461 * Executes the previously set statement and returns the results as an ArrayList of HashMaps
462 *
463 * @return the results of the query
464 */
465 public ArrayList executeQueryIntoHashMap() {
466
467 return executeQuery(null, null, 0).getPartialResult();
468 }
469
470
471 /***
472 * Scrollable result set enabled version of {@link #executeQueryIntoHashMap()}, useful
473 * for dealing with large result sets.
474 *
475 * @param startIndex the place within the results to start from, where 0 is the first record.
476 * If startIndex is specified larger than the number of results in the result set, this method
477 * will return an empty result. A negative startIndex will be treated as 0.
478 * @param maxLength the maximum number of results required. Negative values are treated as zero.
479 * @return the results of the query, as a PartialResult.
480 */
481 public PartialResult executeQueryIntoHashMap(int startIndex, int maxLength) {
482
483 return executeQuery(null, new Integer(startIndex), maxLength);
484 }
485
486
487 /***
488 * Executes the previously set statement and returns the results as an ArrayList of Beans
489 * of which the type is specified by one input bean.
490 *
491 * @return the results of the query
492 * @param templateBean Bean that serves as a template to fit the results in.
493 */
494 public ArrayList executeQueryIntoBean(Object templateBean) {
495 return executeQuery(templateBean, null, 0).getPartialResult();
496 }
497
498
499 /***
500 * Executes the previously set statement and returns the results as an ArrayList of Beans
501 * of which the type is specified by templateClass.
502 *
503 * @return the results of the query
504 * @param templateBean Class that serves as a template to fit the results in.
505 */
506 public ArrayList executeQueryIntoBean(Class templateBean) {
507 return executeQuery(templateBean, null, 0).getPartialResult();
508 }
509
510
511 /***
512 * Scrollable result set enabled version of {@link #executeQueryIntoBean(Object)}, useful
513 * for dealing with large result sets.
514 * <p>
515 * Use this method to generate a partial result set by specifying a 'start index' and
516 * a 'maximum length'.
517 *
518 * @return the results of the query, as a PartialResult.
519 * @param templateBean Bean that serves as a template to fit the results in.
520 * @param startIndex the place within the results to start from, where 0 is the first record.
521 * If startIndex is specified larger than the number of results in the result set, this method
522 * will return an empty result. A negative startIndex will be treated as 0.
523 * @param maxLength the maximum number of results required. Negative values are treated as zero.
524 */
525 public PartialResult executeQueryIntoBean(Object templateBean, int startIndex, int maxLength) {
526 return executeQuery(templateBean, new Integer(startIndex), maxLength);
527 }
528
529
530 /***
531 * Scrollable result set enabled version of {@link #executeQueryIntoBean(Class)}, useful
532 * for dealing with large result sets.
533 * <p>
534 * Use this method to generate a partial result set by specifying a 'start index' and
535 * a 'maximum length'.
536 *
537 * @return the results of the query, as a PartialResult.
538 * @param templateBean Class that serves as a template to fit the results in.
539 * @param startIndex the place within the results to start from, where 0 is the first record.
540 * If startIndex is specified larger than the number of results in the result set, this method
541 * will return an empty result. A negative startIndex will be treated as 0.
542 * @param maxLength the maximum number of results required. Negative values are treated as zero.
543 */
544 public PartialResult executeQueryIntoBean(Class templateBean, int startIndex, int maxLength) {
545 return executeQuery(templateBean, new Integer(startIndex), maxLength);
546 }
547
548
549 /***
550 * This method wipes out any bindvariables
551 * that have been set before.
552 *
553 */
554 public void clearBindVars() {
555 bindVars.clear();
556 bindBean = null;
557 }
558
559
560 /***
561 * This method generates the code of a Value (DTO) Bean using the outputformat of a query.
562 *
563 * The query should return at least on row for this method to work.
564 *
565 * @return the String that makes up the Java code of the bean
566 * @param name the name of the bean
567 * @param packageName the package name it should be in
568 * @param queryDefinition the definition of the query that defines the object
569 */
570 publicng> String generateBeanFromQuery(String name, String packageName, String queryDefinition) {
571 HashMap template = null;
572
573 prepareStatement(queryDefinition);
574
575 ArrayList resultList = executeQueryIntoHashMap();
576
577 if (resultList.size() > 0) {
578 template = (HashMap) resultList.iterator().next();
579 }
580
581 returntrong> Helper.generateBeanFromHashMap(name, packageName, template);
582 }
583
584
585 /***
586 * This method returns the Connection that has
587 * been given in the constructor
588 *
589 */
590 public Connection getConnection() {
591 return conn;
592 }
593
594
595 private void prepare() {
596 try {
597 if (queryDefinition == null) {
598 throw new SQLException("no statement defined");
599 }
600
601 if (pstmtCaching) {
602 pstmt = (PreparedStatement) preparedStatementCache.get(queryDefinition);
603 query = (Query) queryCache.get(queryDefinition);
604 }
605
606 if (pstmt == null) {
607 query = (Query) queryCache.get(queryDefinition);
608 if (query == null) {
609 query = new Query(queryDefinition);
610 queryCache.put(queryDefinition, query);
611 }
612
613 pstmt = conn.prepareStatement(query.qString);
614
615 if (pstmtCaching) {
616 preparedStatementCache.put(queryDefinition, pstmt);
617 }
618 }
619 else {
620 pstmt.clearParameters();
621 }
622 }
623 catch (SQLException Q) {
624 ExecutionException E = new ExecutionException(Q);
625 E.setPatient(this);
626 throw E;
627 }
628 }
629
630
631 private void bind() {
632 Object bindVar = null;
633 String bindVarName = null;
634 String bindVarClassType = null;
635 int pos = 1;
636 try {
637
638 ArrayList bindVarNamesToBeBound = new ArrayList(query.BindIdentifiers);
639
640 for (Iterator i = query.BindIdentifiers.iterator(); i.hasNext();) {
641
642 bindVarName = (String) i.next();
643
644 if (bindVars != null && bindVars.containsKey(bindVarName.toUpperCase())) {
645 bindVar = this.bindVars.get(bindVarName.toUpperCase());
646
647 if (bindVar != null) {
648 bindVarClassType = bindVar.getClass().getName();
649 }
650 else {
651 bindVarClassType = "undetermined";
652 }
653 rowMapper.bind(pstmt, bindVar, bindVarClassType, pos++);
654 bindVarNamesToBeBound.remove(bindVarName);
655 }
656 else if (bindBean != null && MethodInvoker.getterExists(bindBean, bindVarName)) {
657 try {
658 bindVar = MethodInvoker.getProperty(bindBean, bindVarName);
659 bindVarClassType = MethodInvoker.getReturnType(bindBean, bindVarName);
660 rowMapper.bind(pstmt, bindVar, bindVarClassType, pos++);
661 bindVarNamesToBeBound.remove(bindVarName);
662
663 }
664 catch (Exception e) {
665 if (e instanceof NotMappableException) {
666 throw (NotMappableException) e;
667 }
668 }
669 }
670 }
671
672 if (bindVarNamesToBeBound.size() > 0) {
673 StringBuffer missingBinds = new StringBuffer();
674 for (Iterator i = bindVarNamesToBeBound.iterator(); i.hasNext();) {
675 missingBinds.append((String) i.next() + " ");
676 }
677
678 throw new SQLException("Bindvariables missing: " + missingBinds.toString());
679 }
680
681
682 }
683 catch (NotMappableException nme) {
684 nme.setTargetName(bindVarName);
685 ExecutionException e = new ExecutionException(nme);
686 e.setPatient(this);
687 throw e;
688
689 }
690 catch (SQLException q) {
691 ExecutionException e = new ExecutionException(q);
692 e.setPatient(this);
693 throw e;
694 }
695 }
696
697
698 private PartialResult executeQuery(Object templateBean, Integer startIndexInteger, int maxLength) {
699 prepare();
700 bind();
701 ResultSet rset = null;
702 Object newBeanDef = null;
703 ArrayList res = new ArrayList();
704 boolean partialResult = (startIndexInteger != null);
705 int index = 0;
706 try {
707 if (!query.givesResultSet) {
708 throw new SQLException("Query does not produce a resultset");
709 }
710
711 rset = pstmt.executeQuery();
712
713 while (rset.next()) {
714 if (partialResult &&
715 (index++ < startIndexInteger.intValue() || (index > (startIndexInteger.intValue() + maxLength)))) {
716 continue;
717 }
718
719 if (templateBean == null) {
720 HashMap r = new HashMap();
721 r = rowMapper.unBindIntoHashMap(rset, r);
722 res.add(r);
723 }
724 else if (templateBean instanceof Class) {
725 try {
726 newBeanDef = ((Class) templateBean).newInstance();
727 }
728 catch (Exception e) {
729 throw new SQLException("Bean could not be cloned through Class.newInstance()");
730 }
731 newBeanDef = rowMapper.unBindIntoBean(rset, newBeanDef);
732 res.add(newBeanDef);
733 }
734 else {
735 try {
736 newBeanDef = BeanUtils.cloneBean(templateBean);
737 }
738 catch (Exception e) {
739 throw new SQLException("Bean could not be cloned through BeanUtils.cloneBean");
740 }
741 newBeanDef = rowMapper.unBindIntoBean(rset, newBeanDef);
742 res.add(newBeanDef);
743 }
744 }
745
746 }
747 catch (NotMappableException nme) {
748 ExecutionException e = new ExecutionException(nme);
749 e.setPatient(this);
750 throw e;
751 }
752 catch (SQLException q) {
753 ExecutionException e = new ExecutionException(q);
754 e.setPatient(this);
755 throw e;
756 }
757 finally {
758 try {
759 if (rset != null)
760 rset.close();
761 if (pstmtCaching) {
762 preparedStatementCache.put(queryDefinition, pstmt);
763 }
764 else {
765 pstmt.close();
766 pstmt = null;
767 }
768 }
769 catch (SQLException q) {
770 ExecutionException e = new ExecutionException(q);
771 e.setPatient(this);
772 throw e;
773 }
774 }
775
776 return new PartialResult(res, index);
777 }
778
779
780 /*** Setter for property rowMapper.
781 * @param rowMapper New value of property rowMapper.
782 *
783 */
784 public void setRowMapper(RowMapper rowMapper) {
785 this.rowMapper = rowMapper;
786 }
787
788
789 /*** Setter for property conn.
790 * @param conn New value of property conn.
791 *
792 */
793 public void setConnection(Connection conn) {
794 this.conn = conn;
795 }
796
797 }
798