001    /*
002     * $Id: Sql.java,v 1.19 2005/02/23 22:09:15 glaforge Exp $
003     * 
004     * Copyright 2003 (C) James Strachan and Bob Mcwhirter. All Rights Reserved.
005     * 
006     * Redistribution and use of this software and associated documentation
007     * ("Software"), with or without modification, are permitted provided that the
008     * following conditions are met: 1. Redistributions of source code must retain
009     * copyright statements and notices. Redistributions must also contain a copy
010     * of this document. 2. Redistributions in binary form must reproduce the above
011     * copyright notice, this list of conditions and the following disclaimer in
012     * the documentation and/or other materials provided with the distribution. 3.
013     * The name "groovy" must not be used to endorse or promote products derived
014     * from this Software without prior written permission of The Codehaus. For
015     * written permission, please contact info@codehaus.org. 4. Products derived
016     * from this Software may not be called "groovy" nor may "groovy" appear in
017     * their names without prior written permission of The Codehaus. "groovy" is a
018     * registered trademark of The Codehaus. 5. Due credit should be given to The
019     * Codehaus - http://groovy.codehaus.org/
020     * 
021     * THIS SOFTWARE IS PROVIDED BY THE CODEHAUS AND CONTRIBUTORS ``AS IS'' AND ANY
022     * EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
023     * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
024     * DISCLAIMED. IN NO EVENT SHALL THE CODEHAUS OR ITS CONTRIBUTORS BE LIABLE FOR
025     * ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
026     * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
027     * SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
028     * CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
029     * LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY
030     * OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH
031     * DAMAGE.
032     *  
033     */
034    package groovy.sql;
035    
036    import groovy.lang.Closure;
037    import groovy.lang.GString;
038    
039    import java.security.AccessController;
040    import java.security.PrivilegedActionException;
041    import java.security.PrivilegedExceptionAction;
042    import java.sql.CallableStatement;
043    import java.sql.Connection;
044    import java.sql.DriverManager;
045    import java.sql.PreparedStatement;
046    import java.sql.ResultSet;
047    import java.sql.ResultSetMetaData;
048    import java.sql.SQLException;
049    import java.sql.Statement;
050    import java.util.ArrayList;
051    import java.util.Collections;
052    import java.util.Iterator;
053    import java.util.List;
054    import java.util.LinkedHashMap;
055    import java.util.Properties;
056    import java.util.logging.Level;
057    import java.util.logging.Logger;
058    import java.util.regex.Matcher;
059    import java.util.regex.Pattern;
060    
061    import javax.sql.DataSource;
062    
063    /**
064     * Represents an extent of objects
065     *
066     * @author Chris Stevenson
067     * @author <a href="mailto:james@coredevelopers.net">James Strachan </a>
068     * @version $Revision: 1.19 $
069     */
070    public class Sql {
071    
072        protected Logger log = Logger.getLogger(getClass().getName());
073    
074        private DataSource dataSource;
075    
076        private Connection useConnection;
077    
078        /** lets only warn of using deprecated methods once */
079        private boolean warned;
080    
081        // store the last row count for executeUpdate
082        int updateCount = 0;
083    
084        /** allows a closure to be used to configure the statement before its use */
085        private Closure configureStatement;
086    
087        /**
088         * A helper method which creates a new Sql instance from a JDBC connection
089         * URL
090         *
091         * @param url
092         * @return a new Sql instance with a connection
093         */
094        public static Sql newInstance(String url) throws SQLException {
095            Connection connection = DriverManager.getConnection(url);
096            return new Sql(connection);
097        }
098    
099        /**
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    }