View Javadoc

1   /*
2    * $Id: Sql.java,v 1.19 2005/02/23 22:09:15 glaforge Exp $
3    * 
4    * Copyright 2003 (C) James Strachan and Bob Mcwhirter. All Rights Reserved.
5    * 
6    * Redistribution and use of this software and associated documentation
7    * ("Software"), with or without modification, are permitted provided that the
8    * following conditions are met: 1. Redistributions of source code must retain
9    * copyright statements and notices. Redistributions must also contain a copy
10   * of this document. 2. Redistributions in binary form must reproduce the above
11   * copyright notice, this list of conditions and the following disclaimer in
12   * the documentation and/or other materials provided with the distribution. 3.
13   * The name "groovy" must not be used to endorse or promote products derived
14   * from this Software without prior written permission of The Codehaus. For
15   * written permission, please contact info@codehaus.org. 4. Products derived
16   * from this Software may not be called "groovy" nor may "groovy" appear in
17   * their names without prior written permission of The Codehaus. "groovy" is a
18   * registered trademark of The Codehaus. 5. Due credit should be given to The
19   * Codehaus - http://groovy.codehaus.org/
20   * 
21   * THIS SOFTWARE IS PROVIDED BY THE CODEHAUS AND CONTRIBUTORS ``AS IS'' AND ANY
22   * EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
23   * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
24   * DISCLAIMED. IN NO EVENT SHALL THE CODEHAUS OR ITS CONTRIBUTORS BE LIABLE FOR
25   * ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
26   * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
27   * SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
28   * CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
29   * LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY
30   * OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH
31   * DAMAGE.
32   *  
33   */
34  package groovy.sql;
35  
36  import groovy.lang.Closure;
37  import groovy.lang.GString;
38  
39  import java.security.AccessController;
40  import java.security.PrivilegedActionException;
41  import java.security.PrivilegedExceptionAction;
42  import java.sql.CallableStatement;
43  import java.sql.Connection;
44  import java.sql.DriverManager;
45  import java.sql.PreparedStatement;
46  import java.sql.ResultSet;
47  import java.sql.ResultSetMetaData;
48  import java.sql.SQLException;
49  import java.sql.Statement;
50  import java.util.ArrayList;
51  import java.util.Collections;
52  import java.util.Iterator;
53  import java.util.List;
54  import java.util.LinkedHashMap;
55  import java.util.Properties;
56  import java.util.logging.Level;
57  import java.util.logging.Logger;
58  import java.util.regex.Matcher;
59  import java.util.regex.Pattern;
60  
61  import javax.sql.DataSource;
62  
63  /***
64   * Represents an extent of objects
65   *
66   * @author Chris Stevenson
67   * @author <a href="mailto:james@coredevelopers.net">James Strachan </a>
68   * @version $Revision: 1.19 $
69   */
70  public class Sql {
71  
72      protected Logger log = Logger.getLogger(getClass().getName());
73  
74      private DataSource dataSource;
75  
76      private Connection useConnection;
77  
78      /*** lets only warn of using deprecated methods once */
79      private boolean warned;
80  
81      // store the last row count for executeUpdate
82      int updateCount = 0;
83  
84      /*** allows a closure to be used to configure the statement before its use */
85      private Closure configureStatement;
86  
87      /***
88       * A helper method which creates a new Sql instance from a JDBC connection
89       * URL
90       *
91       * @param url
92       * @return a new Sql instance with a connection
93       */
94      public static Sql newInstance(String url) throws SQLException {
95          Connection connection = DriverManager.getConnection(url);
96          return new Sql(connection);
97      }
98  
99      /***
100      * A helper method which creates a new Sql instance from a JDBC connection
101      * URL
102      *
103      * @param url
104      * @return a new Sql instance with a connection
105      */
106     public static Sql newInstance(String url, Properties properties) throws SQLException {
107         Connection connection = DriverManager.getConnection(url, properties);
108         return new Sql(connection);
109     }
110 
111     /***
112      * A helper method which creates a new Sql instance from a JDBC connection
113      * URL and driver class name
114      *
115      * @param url
116      * @return a new Sql instance with a connection
117      */
118     public static Sql newInstance(String url, Properties properties, String driverClassName) throws SQLException, ClassNotFoundException {
119         loadDriver(driverClassName);
120         return newInstance(url, properties);
121     }
122 
123     /***
124      * A helper method which creates a new Sql instance from a JDBC connection
125      * URL, username and password
126      *
127      * @param url
128      * @return a new Sql instance with a connection
129      */
130     public static Sql newInstance(String url, String user, String password) throws SQLException {
131         Connection connection = DriverManager.getConnection(url, user, password);
132         return new Sql(connection);
133     }
134 
135     /***
136      * A helper method which creates a new Sql instance from a JDBC connection
137      * URL, username, password and driver class name
138      *
139      * @param url
140      * @return a new Sql instance with a connection
141      */
142     public static Sql newInstance(String url, String user, String password, String driverClassName) throws SQLException,
143             ClassNotFoundException {
144         loadDriver(driverClassName);
145         return newInstance(url, user, password);
146     }
147 
148     /***
149      * A helper method which creates a new Sql instance from a JDBC connection
150      * URL and driver class name
151      *
152      * @param url
153      * @param driverClassName
154      *            the class name of the driver
155      * @return a new Sql instance with a connection
156      */
157     public static Sql newInstance(String url, String driverClassName) throws SQLException, ClassNotFoundException {
158         loadDriver(driverClassName);
159         return newInstance(url);
160     }
161 
162     /***
163      * Attempts to load the JDBC driver on the thread, current or system class
164      * loaders
165      *
166      * @param driverClassName
167      * @throws ClassNotFoundException
168      */
169     public static void loadDriver(String driverClassName) throws ClassNotFoundException {
170         // lets try the thread context class loader first
171         // lets try to use the system class loader
172         try {
173             Class.forName(driverClassName);
174         }
175         catch (ClassNotFoundException e) {
176             try {
177                 Thread.currentThread().getContextClassLoader().loadClass(driverClassName);
178             }
179             catch (ClassNotFoundException e2) {
180                 // now lets try the classloader which loaded us
181                 try {
182                     Sql.class.getClassLoader().loadClass(driverClassName);
183                 }
184                 catch (ClassNotFoundException e3) {
185                     throw e;
186                 }
187             }
188         }
189     }
190 
191     /***
192      * Constructs an SQL instance using the given DataSource. Each operation
193      * will use a Connection from the DataSource pool and close it when the
194      * operation is completed putting it back into the pool.
195      *
196      * @param dataSource
197      */
198     public Sql(DataSource dataSource) {
199         this.dataSource = dataSource;
200     }
201 
202     /***
203      * Construts an SQL instance using the given Connection. It is the callers
204      * responsibility to close the Connection after the Sql instance has been
205      * used. You can do this on the connection object directly or by calling the
206      * {@link java.sql.Connection#close()}  method.
207      *
208      * @param connection
209      */
210     public Sql(Connection connection) {
211         if (connection == null) {
212             throw new NullPointerException("Must specify a non-null Connection");
213         }
214         this.useConnection = connection;
215     }
216 
217     public Sql(Sql parent) {
218         this.dataSource = parent.dataSource;
219         this.useConnection = parent.useConnection;
220     }
221 
222     public DataSet dataSet(String table) {
223         return new DataSet(this, table);
224     }
225 
226     public DataSet dataSet(Class type) {
227         return new DataSet(this, type);
228     }
229 
230     /***
231      * Performs the given SQL query calling the closure with the result set
232      */
233     public void query(String sql, Closure closure) throws SQLException {
234         Connection connection = createConnection();
235         Statement statement = connection.createStatement();
236         configure(statement);
237         ResultSet results = null;
238         try {
239             log.fine(sql);
240             results = statement.executeQuery(sql);
241             closure.call(results);
242         }
243         catch (SQLException e) {
244             log.log(Level.FINE, "Failed to execute: " + sql, e);
245             throw e;
246         }
247         finally {
248             closeResources(connection, statement, results);
249         }
250     }
251 
252     /***
253      * Performs the given SQL query with parameters calling the closure with the
254      * result set
255      */
256     public void query(String sql, List params, Closure closure) throws SQLException {
257         Connection connection = createConnection();
258         PreparedStatement statement = null;
259         ResultSet results = null;
260         try {
261             log.fine(sql);
262             statement = connection.prepareStatement(sql);
263             setParameters(params, statement);
264             configure(statement);
265             results = statement.executeQuery();
266             closure.call(results);
267         }
268         catch (SQLException e) {
269             log.log(Level.FINE, "Failed to execute: " + sql, e);
270             throw e;
271         }
272         finally {
273             closeResources(connection, statement, results);
274         }
275     }
276 
277     /***
278      * Performs the given SQL query calling the closure with the result set
279      */
280     public void query(GString gstring, Closure closure) throws SQLException {
281         List params = getParameters(gstring);
282         String sql = asSql(gstring, params);
283         query(sql, params, closure);
284     }
285 
286     /***
287      * @deprecated please use eachRow instead
288      */
289     public void queryEach(String sql, Closure closure) throws SQLException {
290         warnDeprecated();
291         eachRow(sql, closure);
292     }
293 
294     /***
295      * Performs the given SQL query calling the closure with each row of the
296      * result set
297      */
298     public void eachRow(String sql, Closure closure) throws SQLException {
299         Connection connection = createConnection();
300         Statement statement = connection.createStatement();
301         configure(statement);
302         ResultSet results = null;
303         try {
304             log.fine(sql);
305             results = statement.executeQuery(sql);
306 
307             GroovyResultSet groovyRS = new GroovyResultSet(results);
308             while (groovyRS.next()) {
309                 closure.call(groovyRS);
310             }
311         }
312         catch (SQLException e) {
313             log.log(Level.FINE, "Failed to execute: " + sql, e);
314             throw e;
315         }
316         finally {
317             closeResources(connection, statement, results);
318         }
319     }
320 
321     /***
322      * @deprecated please use eachRow instead
323      */
324     public void queryEach(String sql, List params, Closure closure) throws SQLException {
325         warnDeprecated();
326         eachRow(sql, params, closure);
327     }
328 
329     /***
330      * Performs the given SQL query calling the closure with the result set
331      */
332     public void eachRow(String sql, List params, Closure closure) throws SQLException {
333         Connection connection = createConnection();
334         PreparedStatement statement = null;
335         ResultSet results = null;
336         try {
337             log.fine(sql);
338             statement = connection.prepareStatement(sql);
339             setParameters(params, statement);
340             configure(statement);
341             results = statement.executeQuery();
342 
343             GroovyResultSet groovyRS = new GroovyResultSet(results);
344             while (groovyRS.next()) {
345                 closure.call(groovyRS);
346             }
347         }
348         catch (SQLException e) {
349             log.log(Level.FINE, "Failed to execute: " + sql, e);
350             throw e;
351         }
352         finally {
353             closeResources(connection, statement, results);
354         }
355     }
356 
357     /***
358      * Performs the given SQL query calling the closure with the result set
359      */
360     public void eachRow(GString gstring, Closure closure) throws SQLException {
361         List params = getParameters(gstring);
362         String sql = asSql(gstring, params);
363         eachRow(sql, params, closure);
364     }
365 
366     /***
367      * @deprecated please use eachRow instead
368      */
369     public void queryEach(GString gstring, Closure closure) throws SQLException {
370         warnDeprecated();
371         eachRow(gstring, closure);
372     }
373 
374     /***
375      * Performs the given SQL query and return the rows of the result set
376      */
377      public List rows(String sql) throws SQLException {
378     	List results = new ArrayList();
379         Connection connection = createConnection();
380         Statement statement = connection.createStatement();
381         configure(statement);
382         ResultSet rs = null;
383         try {
384             log.fine(sql);
385             rs = statement.executeQuery(sql);
386             while (rs.next()) {
387             	ResultSetMetaData metadata = rs.getMetaData();
388                 LinkedHashMap lhm = new LinkedHashMap(metadata.getColumnCount(),1,true);
389             	for(int i=1 ; i<=metadata.getColumnCount() ; i++) {
390             	      lhm.put(metadata.getColumnName(i),rs.getObject(i));
391                 }
392                 GroovyRowResult row = new GroovyRowResult(lhm);
393             	results.add(row);
394             }
395             return(results);
396         }
397         catch (SQLException e) {
398             log.log(Level.FINE, "Failed to execute: " + sql, e);
399             throw e;
400         }
401         finally {
402             closeResources(connection, statement, rs);
403         }
404     }
405 
406     /***
407      * Performs the given SQL query and return the first row of the result set
408      */
409     public Object firstRow(String sql) throws SQLException {
410     	return( rows(sql).get(0));
411     }
412 
413     /***
414      * Performs the given SQL query with the list of params and return
415      * the rows of the result set
416      */
417      public List rows(String sql, List params) throws SQLException {
418     	List results = new ArrayList();
419         Connection connection = createConnection();
420         PreparedStatement statement = null;
421         ResultSet rs = null;
422         try {
423             log.fine(sql);
424             statement = connection.prepareStatement(sql);
425             setParameters(params, statement);
426             configure(statement);
427             rs = statement.executeQuery();
428             while (rs.next()) {
429             	ResultSetMetaData metadata = rs.getMetaData();
430             	LinkedHashMap lhm = new LinkedHashMap(metadata.getColumnCount(),1,true);
431             	for(int i=1 ; i<=metadata.getColumnCount() ; i++) {
432             	      lhm.put(metadata.getColumnName(i),rs.getObject(i));
433                 }
434                 GroovyRowResult row = new GroovyRowResult(lhm);
435             	results.add(row);
436             }
437             return(results);
438         }
439         catch (SQLException e) {
440             log.log(Level.FINE, "Failed to execute: " + sql, e);
441             throw e;
442         }
443         finally {
444             closeResources(connection, statement, rs);
445         }
446     }
447 
448      /***
449       * Performs the given SQL query with the list of params and return
450       * the first row of the result set
451       */
452     public Object firstRow(String sql, List params) throws SQLException {
453     	return( rows(sql, params).get(0));
454     }
455 
456     /***
457      * Executes the given piece of SQL
458      */
459     public boolean execute(String sql) throws SQLException {
460         Connection connection = createConnection();
461         Statement statement = null;
462         try {
463             log.fine(sql);
464             statement = connection.createStatement();
465             configure(statement);
466             boolean isResultSet = statement.execute(sql);
467             this.updateCount = statement.getUpdateCount();
468             return isResultSet;
469         }
470         catch (SQLException e) {
471             log.log(Level.FINE, "Failed to execute: " + sql, e);
472             throw e;
473         }
474         finally {
475             closeResources(connection, statement);
476         }
477     }
478 
479     /***
480      * Executes the given SQL update
481      * 
482      * @return the number of rows updated
483      */
484     public int executeUpdate(String sql) throws SQLException {
485         Connection connection = createConnection();
486         Statement statement = null;
487         try {
488             log.fine(sql);
489             statement = connection.createStatement();
490             configure(statement);
491             this.updateCount = statement.executeUpdate(sql);
492             return this.updateCount;
493         }
494         catch (SQLException e) {
495             log.log(Level.FINE, "Failed to execute: " + sql, e);
496             throw e;
497         }
498         finally {
499             closeResources(connection, statement);
500         }
501     }
502 
503     /***
504      * Executes the given piece of SQL with parameters
505      */
506     public boolean execute(String sql, List params) throws SQLException {
507         Connection connection = createConnection();
508         PreparedStatement statement = null;
509         try {
510             log.fine(sql);
511             statement = connection.prepareStatement(sql);
512             setParameters(params, statement);
513             configure(statement);
514             boolean isResultSet = statement.execute();
515             this.updateCount = statement.getUpdateCount();
516             return isResultSet;
517         }
518         catch (SQLException e) {
519             log.log(Level.FINE, "Failed to execute: " + sql, e);
520             throw e;
521         }
522         finally {
523             closeResources(connection, statement);
524         }
525     }
526 
527     /***
528      * Executes the given SQL update with parameters
529      * 
530      * @return the number of rows updated
531      */
532     public int executeUpdate(String sql, List params) throws SQLException {
533         Connection connection = createConnection();
534         PreparedStatement statement = null;
535         try {
536             log.fine(sql);
537             statement = connection.prepareStatement(sql);
538             setParameters(params, statement);
539             configure(statement);
540             this.updateCount = statement.executeUpdate();
541             return this.updateCount;
542         }
543         catch (SQLException e) {
544             log.log(Level.FINE, "Failed to execute: " + sql, e);
545             throw e;
546         }
547         finally {
548             closeResources(connection, statement);
549         }
550     }
551 
552     /***
553      * Executes the given SQL with embedded expressions inside
554      */
555     public boolean execute(GString gstring) throws SQLException {
556         List params = getParameters(gstring);
557         String sql = asSql(gstring, params);
558         return execute(sql, params);
559     }
560 
561     /***
562      * Executes the given SQL update with embedded expressions inside
563      * 
564      * @return the number of rows updated
565      */
566     public int executeUpdate(GString gstring) throws SQLException {
567         List params = getParameters(gstring);
568         String sql = asSql(gstring, params);
569         return executeUpdate(sql, params);
570     }
571 
572     /***
573      * Performs a stored procedure call
574      */
575     public int call(String sql) throws Exception {
576         return call(sql, Collections.EMPTY_LIST);
577     }
578 
579     /***
580      * Performs a stored procedure call with the given parameters
581      */
582     public int call(String sql, List params) throws Exception {
583         Connection connection = createConnection();
584         CallableStatement statement = connection.prepareCall(sql);
585         try {
586             log.fine(sql);
587             setParameters(params, statement);
588             configure(statement);
589             return statement.executeUpdate();
590         }
591         catch (SQLException e) {
592             log.log(Level.FINE, "Failed to execute: " + sql, e);
593             throw e;
594         }
595         finally {
596             closeResources(connection, statement);
597         }
598     }
599 
600     /***
601      * Performs a stored procedure call with the given parameters
602      */
603     public int call(GString gstring) throws Exception {
604         List params = getParameters(gstring);
605         String sql = asSql(gstring, params);
606         return call(sql, params);
607     }
608 
609     /***
610      * If this SQL object was created with a Connection then this method closes
611      * the connection. If this SQL object was created from a DataSource then
612      * this method does nothing.
613      * 
614      * @throws SQLException
615      */
616     public void close() throws SQLException {
617         if (useConnection != null) {
618             useConnection.close();
619         }
620     }
621 
622     public DataSource getDataSource() {
623         return dataSource;
624     }
625 
626 
627     public void commit() {
628         try {
629             this.useConnection.commit();
630         }
631         catch (SQLException e) {
632             log.log(Level.SEVERE, "Caught exception commiting connection: " + e, e);
633         }
634     }
635 
636     public void rollback() {
637         try {
638             this.useConnection.rollback();
639         }
640         catch (SQLException e) {
641             log.log(Level.SEVERE, "Caught exception rollbacking connection: " + e, e);
642         }
643     }
644 
645     /***
646      * @return Returns the updateCount.
647      */
648     public int getUpdateCount() {
649         return updateCount;
650     }
651 
652     /***
653      * If this instance was created with a single Connection then the connection
654      * is returned. Otherwise if this instance was created with a DataSource
655      * then this method returns null
656      *
657      * @return the connection wired into this object, or null if this object
658      *         uses a DataSource
659      */
660     public Connection getConnection() {
661         return useConnection;
662     }
663 
664 
665     /***
666      * Allows a closure to be passed in to configure the JDBC statements before they are executed
667      * to do things like set the query size etc.
668      *
669      * @param configureStatement
670      */
671     public void withStatement(Closure configureStatement) {
672         this.configureStatement = configureStatement;
673     }
674 
675     // Implementation methods
676     //-------------------------------------------------------------------------
677 
678     /***
679      * @return the SQL version of the given query using ? instead of any
680      *         parameter
681      */
682     protected String asSql(GString gstring, List values) {
683         boolean nulls = false;
684         String[] strings = gstring.getStrings();
685         if (strings.length <= 0) {
686             throw new IllegalArgumentException("No SQL specified in GString: " + gstring);
687         }
688         StringBuffer buffer = new StringBuffer();
689         boolean warned = false;
690         Iterator iter = values.iterator();
691         for (int i = 0; i < strings.length; i++) {
692             String text = strings[i];
693             if (text != null) {
694                 buffer.append(text);
695             }
696             if (iter.hasNext()) {
697                 Object value = iter.next();
698                 if (value != null) {
699                     boolean validBinding = true;
700                     if (i < strings.length - 1) {
701                         String nextText = strings[i + 1];
702                         if ((text.endsWith("\"") || text.endsWith("'")) && (nextText.startsWith("'") || nextText.startsWith("\""))) {
703                             if (!warned) {
704                                 log.warning("In Groovy SQL please do not use quotes around dynamic expressions "
705                                         + "(which start with $) as this means we cannot use a JDBC PreparedStatement "
706                                         + "and so is a security hole. Groovy has worked around your mistake but the security hole is still there. The expression so far is: " + buffer.toString() + "?" + nextText);
707                                 warned = true;
708                             }
709                             buffer.append(value);
710                             iter.remove();
711                             validBinding = false;
712                         }
713                     }
714                     if (validBinding) {
715                         buffer.append("?");
716                     }
717                 }
718                 else {
719                     nulls = true;
720                     buffer.append("?'\"?"); // will replace these with nullish
721                     // values
722                 }
723             }
724         }
725         String sql = buffer.toString();
726         if (nulls) {
727             sql = nullify(sql);
728         }
729         return sql;
730     }
731 
732     /***
733      * replace ?'"? references with NULLish
734      * 
735      * @param sql
736      * @return
737      */
738     protected String nullify(String sql) {
739         /*
740          * Some drivers (Oracle classes12.zip) have difficulty resolving data
741          * type if setObject(null). We will modify the query to pass 'null', 'is
742          * null', and 'is not null'
743          */
744         //could be more efficient by compiling expressions in advance.
745         int firstWhere = findWhereKeyword(sql);
746         if (firstWhere >= 0) {
747             Pattern[] patterns = { Pattern.compile("(?is)^(.{" + firstWhere + "}.*?)!=//s{0,1}(//s*)//?'\"//?(.*)"),
748                     Pattern.compile("(?is)^(.{" + firstWhere + "}.*?)<>//s{0,1}(//s*)//?'\"//?(.*)"),
749                     Pattern.compile("(?is)^(.{" + firstWhere + "}.*?[^<>])=//s{0,1}(//s*)//?'\"//?(.*)"), };
750             String[] replacements = { "$1 is not $2null$3", "$1 is not $2null$3", "$1 is $2null$3", };
751             for (int i = 0; i < patterns.length; i++) {
752                 Matcher matcher = patterns[i].matcher(sql);
753                 while (matcher.matches()) {
754                     sql = matcher.replaceAll(replacements[i]);
755                     matcher = patterns[i].matcher(sql);
756                 }
757             }
758         }
759         return sql.replaceAll("//?'\"//?", "null");
760     }
761 
762     /***
763      * Find the first 'where' keyword in the sql.
764      * 
765      * @param sql
766      * @return
767      */
768     protected int findWhereKeyword(String sql) {
769         char[] chars = sql.toLowerCase().toCharArray();
770         char[] whereChars = "where".toCharArray();
771         int i = 0;
772         boolean inString = false; //TODO: Cater for comments?
773         boolean noWhere = true;
774         int inWhere = 0;
775         while (i < chars.length && noWhere) {
776             switch (chars[i]) {
777                 case '\'':
778                     if (inString) {
779                         inString = false;
780                     }
781                     else {
782                         inString = true;
783                     }
784                     break;
785                 default:
786                     if (!inString && chars[i] == whereChars[inWhere]) {
787                         inWhere++;
788                         if (inWhere == whereChars.length) {
789                             return i;
790                         }
791                     }
792             }
793             i++;
794         }
795         return -1;
796     }
797 
798     /***
799      * @return extracts the parameters from the expression as a List
800      */
801     protected List getParameters(GString gstring) {
802         Object[] values = gstring.getValues();
803         List answer = new ArrayList(values.length);
804         for (int i = 0; i < values.length; i++) {
805             if (values[i] != null) {
806                 answer.add(values[i]);
807             }
808         }
809         return answer;
810     }
811 
812     /***
813      * Appends the parameters to the given statement
814      */
815     protected void setParameters(List params, PreparedStatement statement) throws SQLException {
816         int i = 1;
817         for (Iterator iter = params.iterator(); iter.hasNext();) {
818             Object value = iter.next();
819             setObject(statement, i++, value);
820         }
821     }
822 
823     /***
824      * Strategy method allowing derived classes to handle types differently such
825      * as for CLOBs etc.
826      */
827     protected void setObject(PreparedStatement statement, int i, Object value) throws SQLException {
828         statement.setObject(i, value);
829     }
830 
831     protected Connection createConnection() throws SQLException {
832         if (dataSource != null) {
833             //Use a doPrivileged here as many different properties need to be
834             // read, and the policy
835             //shouldn't have to list them all.
836             Connection con = null;
837             try {
838                 con = (Connection) AccessController.doPrivileged(new PrivilegedExceptionAction() {
839                     public Object run() throws SQLException {
840                         return dataSource.getConnection();
841                     }
842                 });
843             }
844             catch (PrivilegedActionException pae) {
845                 Exception e = pae.getException();
846                 if (e instanceof SQLException) {
847                     throw (SQLException) e;
848                 }
849                 else {
850                     throw (RuntimeException) e;
851                 }
852             }
853             return con;
854         }
855         else {
856             //System.out.println("createConnection returning: " +
857             // useConnection);
858             return useConnection;
859         }
860     }
861 
862     protected void closeResources(Connection connection, Statement statement, ResultSet results) {
863         if (results != null) {
864             try {
865                 results.close();
866             }
867             catch (SQLException e) {
868                 log.log(Level.SEVERE, "Caught exception closing resultSet: " + e, e);
869             }
870         }
871         closeResources(connection, statement);
872     }
873 
874     protected void closeResources(Connection connection, Statement statement) {
875         if (statement != null) {
876             try {
877                 statement.close();
878             }
879             catch (SQLException e) {
880                 log.log(Level.SEVERE, "Caught exception closing statement: " + e, e);
881             }
882         }
883         if (dataSource != null) {
884             try {
885                 connection.close();
886             }
887             catch (SQLException e) {
888                 log.log(Level.SEVERE, "Caught exception closing connection: " + e, e);
889             }
890         }
891     }
892 
893     private void warnDeprecated() {
894         if (!warned) {
895             warned = true;
896             log.warning("queryEach() is deprecated, please use eachRow() instead");
897         }
898     }
899 
900     /***
901      * Provides a hook to be able to configure JDBC statements, such as to configure
902      *
903      * @param statement
904      */
905     protected void configure(Statement statement) {
906         if (configureStatement != null) {
907             configureStatement.call(statement);
908         }
909     }
910 }