001    /*
002     * $Id: Sql.java 4066 2006-09-20 17:26:36Z glaforge $
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.sql.Types;
051    import java.util.ArrayList;
052    import java.util.Collections;
053    import java.util.Iterator;
054    import java.util.List;
055    import java.util.LinkedHashMap;
056    import java.util.Properties;
057    import java.util.logging.Level;
058    import java.util.logging.Logger;
059    import java.util.regex.Matcher;
060    import java.util.regex.Pattern;
061    
062    import javax.sql.DataSource;
063    
064    /**
065     * Represents an extent of objects
066     *
067     * @author Chris Stevenson
068     * @author <a href="mailto:james@coredevelopers.net">James Strachan </a>
069     * @version $Revision: 4066 $
070     */
071    public class Sql {
072    
073        protected Logger log = Logger.getLogger(getClass().getName());
074    
075        private DataSource dataSource;
076    
077        private Connection useConnection;
078    
079        /** lets only warn of using deprecated methods once */
080        private boolean warned;
081    
082        // store the last row count for executeUpdate
083        int updateCount = 0;
084    
085        /** allows a closure to be used to configure the statement before its use */
086        private Closure configureStatement;
087    
088        /**
089         * A helper method which creates a new Sql instance from a JDBC connection
090         * URL
091         *
092         * @param url
093         * @return a new Sql instance with a connection
094         */
095        public static Sql newInstance(String url) throws SQLException {
096            Connection connection = DriverManager.getConnection(url);
097            return new Sql(connection);
098        }
099    
100        /**
101         * A helper method which creates a new Sql instance from a JDBC connection
102         * URL
103         *
104         * @param url
105         * @return a new Sql instance with a connection
106         */
107        public static Sql newInstance(String url, Properties properties) throws SQLException {
108            Connection connection = DriverManager.getConnection(url, properties);
109            return new Sql(connection);
110        }
111    
112        /**
113         * A helper method which creates a new Sql instance from a JDBC connection
114         * URL and driver class name
115         *
116         * @param url
117         * @return a new Sql instance with a connection
118         */
119        public static Sql newInstance(String url, Properties properties, String driverClassName) throws SQLException, ClassNotFoundException {
120            loadDriver(driverClassName);
121            return newInstance(url, properties);
122        }
123    
124        /**
125         * A helper method which creates a new Sql instance from a JDBC connection
126         * URL, username and password
127         *
128         * @param url
129         * @return a new Sql instance with a connection
130         */
131        public static Sql newInstance(String url, String user, String password) throws SQLException {
132            Connection connection = DriverManager.getConnection(url, user, password);
133            return new Sql(connection);
134        }
135    
136        /**
137         * A helper method which creates a new Sql instance from a JDBC connection
138         * URL, username, password and driver class name
139         *
140         * @param url
141         * @return a new Sql instance with a connection
142         */
143        public static Sql newInstance(String url, String user, String password, String driverClassName) throws SQLException,
144                ClassNotFoundException {
145            loadDriver(driverClassName);
146            return newInstance(url, user, password);
147        }
148    
149        /**
150         * A helper method which creates a new Sql instance from a JDBC connection
151         * URL and driver class name
152         *
153         * @param url
154         * @param driverClassName
155         *            the class name of the driver
156         * @return a new Sql instance with a connection
157         */
158        public static Sql newInstance(String url, String driverClassName) throws SQLException, ClassNotFoundException {
159            loadDriver(driverClassName);
160            return newInstance(url);
161        }
162    
163        /**
164         * Attempts to load the JDBC driver on the thread, current or system class
165         * loaders
166         *
167         * @param driverClassName
168         * @throws ClassNotFoundException
169         */
170        public static void loadDriver(String driverClassName) throws ClassNotFoundException {
171            // lets try the thread context class loader first
172            // lets try to use the system class loader
173            try {
174                Class.forName(driverClassName);
175            }
176            catch (ClassNotFoundException e) {
177                try {
178                    Thread.currentThread().getContextClassLoader().loadClass(driverClassName);
179                }
180                catch (ClassNotFoundException e2) {
181                    // now lets try the classloader which loaded us
182                    try {
183                        Sql.class.getClassLoader().loadClass(driverClassName);
184                    }
185                    catch (ClassNotFoundException e3) {
186                        throw e;
187                    }
188                }
189            }
190        }
191    
192        public static final OutParameter ARRAY         = new OutParameter(){ public int getType() { return Types.ARRAY; }};
193        public static final OutParameter BIGINT        = new OutParameter(){ public int getType() { return Types.BIGINT; }};
194        public static final OutParameter BINARY        = new OutParameter(){ public int getType() { return Types.BINARY; }};
195        public static final OutParameter BIT           = new OutParameter(){ public int getType() { return Types.BIT; }};
196        public static final OutParameter BLOB          = new OutParameter(){ public int getType() { return Types.BLOB; }};
197        public static final OutParameter BOOLEAN       = new OutParameter(){ public int getType() { return Types.BOOLEAN; }};
198        public static final OutParameter CHAR          = new OutParameter(){ public int getType() { return Types.CHAR; }};
199        public static final OutParameter CLOB          = new OutParameter(){ public int getType() { return Types.CLOB; }};
200        public static final OutParameter DATALINK      = new OutParameter(){ public int getType() { return Types.DATALINK; }};
201        public static final OutParameter DATE          = new OutParameter(){ public int getType() { return Types.DATE; }};
202        public static final OutParameter DECIMAL       = new OutParameter(){ public int getType() { return Types.DECIMAL; }};
203        public static final OutParameter DISTINCT      = new OutParameter(){ public int getType() { return Types.DISTINCT; }};
204        public static final OutParameter DOUBLE        = new OutParameter(){ public int getType() { return Types.DOUBLE; }};
205        public static final OutParameter FLOAT         = new OutParameter(){ public int getType() { return Types.FLOAT; }};
206        public static final OutParameter INTEGER       = new OutParameter(){ public int getType() { return Types.INTEGER; }};
207        public static final OutParameter JAVA_OBJECT   = new OutParameter(){ public int getType() { return Types.JAVA_OBJECT; }};
208        public static final OutParameter LONGVARBINARY = new OutParameter(){ public int getType() { return Types.LONGVARBINARY; }};
209        public static final OutParameter LONGVARCHAR   = new OutParameter(){ public int getType() { return Types.LONGVARCHAR; }};
210        public static final OutParameter NULL          = new OutParameter(){ public int getType() { return Types.NULL; }};
211        public static final OutParameter NUMERIC       = new OutParameter(){ public int getType() { return Types.NUMERIC; }};
212        public static final OutParameter OTHER         = new OutParameter(){ public int getType() { return Types.OTHER; }};
213        public static final OutParameter REAL          = new OutParameter(){ public int getType() { return Types.REAL; }};
214        public static final OutParameter REF           = new OutParameter(){ public int getType() { return Types.REF; }};
215        public static final OutParameter SMALLINT      = new OutParameter(){ public int getType() { return Types.SMALLINT; }};
216        public static final OutParameter STRUCT        = new OutParameter(){ public int getType() { return Types.STRUCT; }};
217        public static final OutParameter TIME          = new OutParameter(){ public int getType() { return Types.TIME; }};
218        public static final OutParameter TIMESTAMP     = new OutParameter(){ public int getType() { return Types.TIMESTAMP; }};
219        public static final OutParameter TINYINT       = new OutParameter(){ public int getType() { return Types.TINYINT; }};
220        public static final OutParameter VARBINARY     = new OutParameter(){ public int getType() { return Types.VARBINARY; }};
221        public static final OutParameter VARCHAR       = new OutParameter(){ public int getType() { return Types.VARCHAR; }};
222    
223        public static InParameter ARRAY(Object value) { return in(Types.ARRAY, value); }
224        public static InParameter BIGINT(Object value) { return in(Types.BIGINT, value); }
225        public static InParameter BINARY(Object value) { return in(Types.BINARY, value); }
226        public static InParameter BIT(Object value) { return in(Types.BIT, value); }
227        public static InParameter BLOB(Object value) { return in(Types.BLOB, value); }
228        public static InParameter BOOLEAN(Object value) { return in(Types.BOOLEAN, value); }
229        public static InParameter CHAR(Object value) { return in(Types.CHAR, value); }
230        public static InParameter CLOB(Object value) { return in(Types.CLOB, value); }
231        public static InParameter DATALINK(Object value) { return in(Types.DATALINK, value); }
232        public static InParameter DATE(Object value) { return in(Types.DATE, value); }
233        public static InParameter DECIMAL(Object value) { return in(Types.DECIMAL, value); }
234        public static InParameter DISTINCT(Object value) { return in(Types.DISTINCT, value); }
235        public static InParameter DOUBLE(Object value) { return in(Types.DOUBLE, value); }
236        public static InParameter FLOAT(Object value) { return in(Types.FLOAT, value); }
237        public static InParameter INTEGER(Object value) { return in(Types.INTEGER, value); }
238        public static InParameter JAVA_OBJECT(Object value) { return in(Types.JAVA_OBJECT, value); }
239        public static InParameter LONGVARBINARY(Object value) { return in(Types.LONGVARBINARY, value); }
240        public static InParameter LONGVARCHAR(Object value) { return in(Types.LONGVARCHAR, value); }
241        public static InParameter NULL(Object value) { return in(Types.NULL, value); }
242        public static InParameter NUMERIC(Object value) { return in(Types.NUMERIC, value); }
243        public static InParameter OTHER(Object value) { return in(Types.OTHER, value); }
244        public static InParameter REAL(Object value) { return in(Types.REAL, value); }
245        public static InParameter REF(Object value) { return in(Types.REF, value); }
246        public static InParameter SMALLINT(Object value) { return in(Types.SMALLINT, value); }
247        public static InParameter STRUCT(Object value) { return in(Types.STRUCT, value); }
248        public static InParameter TIME(Object value) { return in(Types.TIME, value); }
249        public static InParameter TIMESTAMP(Object value) { return in(Types.TIMESTAMP, value); }
250        public static InParameter TINYINT(Object value) { return in(Types.TINYINT, value); }
251        public static InParameter VARBINARY(Object value) { return in(Types.VARBINARY, value); }
252        public static InParameter VARCHAR(Object value) { return in(Types.VARCHAR, value); }
253    
254        /**
255         * Create a new InParameter
256         * @param type the JDBC data type
257         * @param value the object value
258         * @return an InParameter
259         */
260        public static InParameter in(final int type, final Object value) {
261            return new InParameter() {
262                public int getType() {
263                    return type;
264                }
265                public Object getValue() {
266                    return value;
267                }
268            };
269        }
270        
271        /**
272         * Create a new OutParameter
273         * @param type the JDBC data type.
274         * @return an OutParameter
275         */
276        public static OutParameter out(final int type){
277            return new OutParameter(){
278                public int getType() {
279                    return type;
280                }
281            };
282        }
283        
284        /**
285         * Create an inout parameter using this in parameter.
286         * @param in
287         */
288        public static InOutParameter inout(final InParameter in){
289            return new InOutParameter(){
290                public int getType() {
291                    return in.getType();
292                }
293                public Object getValue() {
294                    return in.getValue();
295                }            
296            };
297        }
298        
299        /**
300         * Create a new ResultSetOutParameter
301         * @param type the JDBC data type.
302         * @return a ResultSetOutParameter
303         */
304        public static ResultSetOutParameter resultSet(final int type){
305            return new ResultSetOutParameter(){
306                public int getType() {
307                    return type;
308                }
309            };
310        }
311            
312        /**
313         * Creates a variable to be expanded in the Sql string rather
314         * than representing an sql parameter.
315         * @param object
316         */
317        public static ExpandedVariable expand(final Object object){
318            return new ExpandedVariable(){
319                public Object getObject() {
320                    return object;
321                }};
322        }
323        
324        /**
325         * Constructs an SQL instance using the given DataSource. Each operation
326         * will use a Connection from the DataSource pool and close it when the
327         * operation is completed putting it back into the pool.
328         *
329         * @param dataSource
330         */
331        public Sql(DataSource dataSource) {
332            this.dataSource = dataSource;
333        }
334    
335        /**
336         * Construts an SQL instance using the given Connection. It is the callers
337         * responsibility to close the Connection after the Sql instance has been
338         * used. You can do this on the connection object directly or by calling the
339         * {@link java.sql.Connection#close()}  method.
340         *
341         * @param connection
342         */
343        public Sql(Connection connection) {
344            if (connection == null) {
345                throw new NullPointerException("Must specify a non-null Connection");
346            }
347            this.useConnection = connection;
348        }
349    
350        public Sql(Sql parent) {
351            this.dataSource = parent.dataSource;
352            this.useConnection = parent.useConnection;
353        }
354    
355        public DataSet dataSet(String table) {
356            return new DataSet(this, table);
357        }
358    
359        public DataSet dataSet(Class type) {
360            return new DataSet(this, type);
361        }
362    
363        /**
364         * Performs the given SQL query calling the closure with the result set
365         */
366        public void query(String sql, Closure closure) throws SQLException {
367            Connection connection = createConnection();
368            Statement statement = connection.createStatement();
369            configure(statement);
370            ResultSet results = null;
371            try {
372                log.fine(sql);
373                results = statement.executeQuery(sql);
374                closure.call(results);
375            }
376            catch (SQLException e) {
377                log.log(Level.FINE, "Failed to execute: " + sql, e);
378                throw e;
379            }
380            finally {
381                closeResources(connection, statement, results);
382            }
383        }
384    
385        /**
386         * Performs the given SQL query with parameters calling the closure with the
387         * result set
388         */
389        public void query(String sql, List params, Closure closure) throws SQLException {
390            Connection connection = createConnection();
391            PreparedStatement statement = null;
392            ResultSet results = null;
393            try {
394                log.fine(sql);
395                statement = connection.prepareStatement(sql);
396                setParameters(params, statement);
397                configure(statement);
398                results = statement.executeQuery();
399                closure.call(results);
400            }
401            catch (SQLException e) {
402                log.log(Level.FINE, "Failed to execute: " + sql, e);
403                throw e;
404            }
405            finally {
406                closeResources(connection, statement, results);
407            }
408        }
409    
410        /**
411         * Performs the given SQL query calling the closure with the result set
412         */
413        public void query(GString gstring, Closure closure) throws SQLException {
414            List params = getParameters(gstring);
415            String sql = asSql(gstring, params);
416            query(sql, params, closure);
417        }
418    
419        /**
420         * @deprecated please use eachRow instead
421         */
422        public void queryEach(String sql, Closure closure) throws SQLException {
423            warnDeprecated();
424            eachRow(sql, closure);
425        }
426    
427        /**
428         * Performs the given SQL query calling the closure with each row of the
429         * result set
430         */
431        public void eachRow(String sql, Closure closure) throws SQLException {
432            Connection connection = createConnection();
433            Statement statement = connection.createStatement();
434            configure(statement);
435            ResultSet results = null;
436            try {
437                log.fine(sql);
438                results = statement.executeQuery(sql);
439    
440                GroovyResultSet groovyRS = new GroovyResultSet(results);
441                while (groovyRS.next()) {
442                    closure.call(groovyRS);
443                }
444            }
445            catch (SQLException e) {
446                log.log(Level.FINE, "Failed to execute: " + sql, e);
447                throw e;
448            }
449            finally {
450                closeResources(connection, statement, results);
451            }
452        }
453    
454        /**
455         * @deprecated please use eachRow instead
456         */
457        public void queryEach(String sql, List params, Closure closure) throws SQLException {
458            warnDeprecated();
459            eachRow(sql, params, closure);
460        }
461    
462        /**
463         * Performs the given SQL query calling the closure with the result set
464         */
465        public void eachRow(String sql, List params, Closure closure) throws SQLException {
466            Connection connection = createConnection();
467            PreparedStatement statement = null;
468            ResultSet results = null;
469            try {
470                log.fine(sql);
471                statement = connection.prepareStatement(sql);
472                setParameters(params, statement);
473                configure(statement);
474                results = statement.executeQuery();
475    
476                GroovyResultSet groovyRS = new GroovyResultSet(results);
477                while (groovyRS.next()) {
478                    closure.call(groovyRS);
479                }
480            }
481            catch (SQLException e) {
482                log.log(Level.FINE, "Failed to execute: " + sql, e);
483                throw e;
484            }
485            finally {
486                closeResources(connection, statement, results);
487            }
488        }
489    
490        /**
491         * Performs the given SQL query calling the closure with the result set
492         */
493        public void eachRow(GString gstring, Closure closure) throws SQLException {
494            List params = getParameters(gstring);
495            String sql = asSql(gstring, params);
496            eachRow(sql, params, closure);
497        }
498    
499        /**
500         * @deprecated please use eachRow instead
501         */
502        public void queryEach(GString gstring, Closure closure) throws SQLException {
503            warnDeprecated();
504            eachRow(gstring, closure);
505        }
506    
507        /**
508         * Performs the given SQL query and return the rows of the result set
509         */
510         public List rows(String sql) throws SQLException {
511            List results = new ArrayList();
512            Connection connection = createConnection();
513            Statement statement = connection.createStatement();
514            configure(statement);
515            ResultSet rs = null;
516            try {
517                log.fine(sql);
518                rs = statement.executeQuery(sql);
519                while (rs.next()) {
520                    ResultSetMetaData metadata = rs.getMetaData();
521                    LinkedHashMap lhm = new LinkedHashMap(metadata.getColumnCount(),1,true);
522                    for(int i=1 ; i<=metadata.getColumnCount() ; i++) {
523                          lhm.put(metadata.getColumnName(i),rs.getObject(i));
524                    }
525                    GroovyRowResult row = new GroovyRowResult(lhm);
526                    results.add(row);
527                }
528                return(results);
529            }
530            catch (SQLException e) {
531                log.log(Level.FINE, "Failed to execute: " + sql, e);
532                throw e;
533            }
534            finally {
535                closeResources(connection, statement, rs);
536            }
537        }
538    
539        /**
540         * Performs the given SQL query and return the first row of the result set
541         */
542        public Object firstRow(String sql) throws SQLException {
543            List rows = rows(sql);
544            if (rows.isEmpty()) return null;
545            return(rows.get(0));
546        }
547    
548        /**
549         * Performs the given SQL query with the list of params and return
550         * the rows of the result set
551         */
552        public List rows(String sql, List params) throws SQLException {
553            List results = new ArrayList();
554            Connection connection = createConnection();
555            PreparedStatement statement = null;
556            ResultSet rs = null;
557            try {
558                log.fine(sql);
559                statement = connection.prepareStatement(sql);
560                setParameters(params, statement);
561                configure(statement);
562                rs = statement.executeQuery();
563                while (rs.next()) {
564                    ResultSetMetaData metadata = rs.getMetaData();
565                    LinkedHashMap lhm = new LinkedHashMap(metadata.getColumnCount(),1,true);
566                    for(int i=1 ; i<=metadata.getColumnCount() ; i++) {
567                        lhm.put(metadata.getColumnName(i),rs.getObject(i));
568                    }
569                    GroovyRowResult row = new GroovyRowResult(lhm);
570                    results.add(row);
571                }
572                return(results);
573            }
574            catch (SQLException e) {
575                log.log(Level.FINE, "Failed to execute: " + sql, e);
576                throw e;
577            }
578            finally {
579                closeResources(connection, statement, rs);
580            }
581        }
582    
583         /**
584          * Performs the given SQL query with the list of params and return
585          * the first row of the result set
586          */
587        public Object firstRow(String sql, List params) throws SQLException {
588             List rows = rows(sql, params);
589             if (rows.isEmpty()) return null;
590             return rows.get(0);
591         }
592    
593        /**
594         * Executes the given piece of SQL
595         */
596        public boolean execute(String sql) throws SQLException {
597            Connection connection = createConnection();
598            Statement statement = null;
599            try {
600                log.fine(sql);
601                statement = connection.createStatement();
602                configure(statement);
603                boolean isResultSet = statement.execute(sql);
604                this.updateCount = statement.getUpdateCount();
605                return isResultSet;
606            }
607            catch (SQLException e) {
608                log.log(Level.FINE, "Failed to execute: " + sql, e);
609                throw e;
610            }
611            finally {
612                closeResources(connection, statement);
613            }
614        }
615    
616        /**
617         * Executes the given SQL update
618         * 
619         * @return the number of rows updated
620         */
621        public int executeUpdate(String sql) throws SQLException {
622            Connection connection = createConnection();
623            Statement statement = null;
624            try {
625                log.fine(sql);
626                statement = connection.createStatement();
627                configure(statement);
628                this.updateCount = statement.executeUpdate(sql);
629                return this.updateCount;
630            }
631            catch (SQLException e) {
632                log.log(Level.FINE, "Failed to execute: " + sql, e);
633                throw e;
634            }
635            finally {
636                closeResources(connection, statement);
637            }
638        }
639    
640        /**
641         * Executes the given SQL statement. See {@link #executeInsert(GString)}
642         * for more details. 
643         * @param sql The SQL statement to execute.
644         * @return A list of the auto-generated column values for each
645         * inserted row.
646         */
647        public List executeInsert(String sql) throws SQLException {
648            Connection connection = createConnection();
649            Statement statement = null;
650            try {
651                log.fine(sql);
652                statement = connection.createStatement();
653                configure(statement);
654                boolean hasResultSet = statement.execute(sql, Statement.RETURN_GENERATED_KEYS);
655    
656                // Prepare a list to contain the auto-generated column
657                // values, and then fetch them from the statement.
658                List autoKeys = new ArrayList();
659                    ResultSet keys = statement.getGeneratedKeys();
660                    int count = keys.getMetaData().getColumnCount();
661    
662                    // Copy the column values into a list of a list.
663                    while (keys.next()) {
664                            List rowKeys = new ArrayList(count);
665                            for (int i = 1; i <= count; i++) {
666                                    rowKeys.add(keys.getObject(i));
667                            }
668    
669                            autoKeys.add(rowKeys);
670                    }
671    
672                    // Store the update count so that it can be retrieved by
673                    // clients, and then return the list of auto-generated
674                    // values.
675                    this.updateCount = statement.getUpdateCount();
676                    return autoKeys;
677            }
678            catch (SQLException e) {
679                log.log(Level.FINE, "Failed to execute: " + sql, e);
680                throw e;
681            }
682            finally {
683                closeResources(connection, statement);
684            }
685        }
686    
687        /**
688         * Executes the given piece of SQL with parameters
689         */
690        public boolean execute(String sql, List params) throws SQLException {
691            Connection connection = createConnection();
692            PreparedStatement statement = null;
693            try {
694                log.fine(sql);
695                statement = connection.prepareStatement(sql);
696                setParameters(params, statement);
697                configure(statement);
698                boolean isResultSet = statement.execute();
699                this.updateCount = statement.getUpdateCount();
700                return isResultSet;
701            }
702            catch (SQLException e) {
703                log.log(Level.FINE, "Failed to execute: " + sql, e);
704                throw e;
705            }
706            finally {
707                closeResources(connection, statement);
708            }
709        }
710    
711        /**
712         * Executes the given SQL update with parameters
713         * 
714         * @return the number of rows updated
715         */
716        public int executeUpdate(String sql, List params) throws SQLException {
717            Connection connection = createConnection();
718            PreparedStatement statement = null;
719            try {
720                log.fine(sql);
721                statement = connection.prepareStatement(sql);
722                setParameters(params, statement);
723                configure(statement);
724                this.updateCount = statement.executeUpdate();
725                return this.updateCount;
726            }
727            catch (SQLException e) {
728                log.log(Level.FINE, "Failed to execute: " + sql, e);
729                throw e;
730            }
731            finally {
732                closeResources(connection, statement);
733            }
734        }
735    
736        /**
737         * Executes the given SQL statement with a particular list of
738         * parameter values. See {@link #executeInsert(GString)} for
739         * more details. 
740         * @param sql The SQL statement to execute.
741         * @param params The parameter values that will be substituted
742         * into the SQL statement's parameter slots.
743         * @return A list of the auto-generated column values for each
744         * inserted row.
745         */
746        public List executeInsert(String sql, List params) throws SQLException {
747            // Now send the SQL to the database.
748            Connection connection = createConnection();
749            PreparedStatement statement = null;
750            try {
751                log.fine(sql);
752    
753                // Prepare a statement for the SQL and then execute it.
754                statement = connection.prepareStatement(sql);
755                setParameters(params, statement);
756                configure(statement);
757                boolean hasResultSet = statement.execute(sql, Statement.RETURN_GENERATED_KEYS);
758    
759                // Prepare a list to contain the auto-generated column
760                // values, and then fetch them from the statement.
761                List autoKeys = new ArrayList();
762                    ResultSet keys = statement.getGeneratedKeys();
763                    int count = keys.getMetaData().getColumnCount();
764    
765                    // Copy the column values into a list of a list.
766                    while (keys.next()) {
767                            List rowKeys = new ArrayList(count);
768                            for (int i = 1; i <= count; i++) {
769                                    rowKeys.add(keys.getObject(i));
770                            }
771    
772                            autoKeys.add(rowKeys);
773                    }
774    
775                    // Store the update count so that it can be retrieved by
776                    // clients, and then return the list of auto-generated
777                    // values.
778                    this.updateCount = statement.getUpdateCount();
779                    return autoKeys;
780            }
781            catch (SQLException e) {
782                log.log(Level.FINE, "Failed to execute: " + sql, e);
783                throw e;
784            }
785            finally {
786                closeResources(connection, statement);
787            }
788        }
789    
790        /**
791         * Executes the given SQL with embedded expressions inside
792         */
793        public boolean execute(GString gstring) throws SQLException {
794            List params = getParameters(gstring);
795            String sql = asSql(gstring, params);
796            return execute(sql, params);
797        }
798    
799        /**
800         * Executes the given SQL update with embedded expressions inside
801         * 
802         * @return the number of rows updated
803         */
804        public int executeUpdate(GString gstring) throws SQLException {
805            List params = getParameters(gstring);
806            String sql = asSql(gstring, params);
807            return executeUpdate(sql, params);
808        }
809    
810        /**
811         * <p>Executes the given SQL with embedded expressions inside, and
812         * returns the values of any auto-generated colums, such as an
813         * autoincrement ID field. These values can be accessed using
814         * array notation. For example, to return the second auto-generated
815         * column value of the third row, use <code>keys[3][1]</code>. The
816         * method is designed to be used with SQL INSERT statements, but is
817         * not limited to them.</p>
818         * <p>The standard use for this method is when a table has an
819         * autoincrement ID column and you want to know what the ID is for
820         * a newly inserted row. In this example, we insert a single row
821         * into a table in which the first column contains the autoincrement
822         * ID:</p>
823         * <pre>
824         *     def sql = Sql.newInstance("jdbc:mysql://localhost:3306/groovy",
825         *                               "user", 
826         *                               "password",
827         *                               "com.mysql.jdbc.Driver")
828         *
829         *     def keys = sql.insert("insert into test_table (INT_DATA, STRING_DATA) "
830         *                           + "VALUES (1, 'Key Largo')")
831         *
832         *     def id = keys[0][0]
833         *
834         *     // 'id' now contains the value of the new row's ID column.
835         *     // It can be used to update an object representation's
836         *     // id attribute for example.
837         *     ...
838         * </pre>
839         * @return A list of column values representing each row's
840         * auto-generated keys.
841         */
842        public List executeInsert(GString gstring) throws SQLException {
843            List params = getParameters(gstring);
844            String sql = asSql(gstring, params);
845            return executeInsert(sql, params);
846        }
847    
848        /**
849         * Performs a stored procedure call
850         */
851        public int call(String sql) throws Exception {
852            return call(sql, Collections.EMPTY_LIST);
853        }
854    
855        /**
856         * Performs a stored procedure call with the given parameters
857         */
858        public int call(String sql, List params) throws Exception {
859            Connection connection = createConnection();
860            CallableStatement statement = connection.prepareCall(sql);
861            try {
862                log.fine(sql);
863                setParameters(params, statement);
864                configure(statement);
865                return statement.executeUpdate();
866            }
867            catch (SQLException e) {
868                log.log(Level.FINE, "Failed to execute: " + sql, e);
869                throw e;
870            }
871            finally {
872                closeResources(connection, statement);
873            }
874        }
875    
876        /**
877         * Performs a stored procedure call with the given parameters.  The closure
878         * is called once with all the out parameters.
879         */
880        public void call(String sql, List params, Closure closure) throws Exception {
881            Connection connection = createConnection();
882            CallableStatement statement = connection.prepareCall(sql);
883            try {
884                log.fine(sql);
885                setParameters(params, statement);
886                statement.execute();
887                List results = new ArrayList();
888                int indx = 0;
889                int inouts = 0;
890                for (Iterator iter = params.iterator(); iter.hasNext();) {
891                    Object value = iter.next();
892                    if(value instanceof OutParameter){
893                        if(value instanceof ResultSetOutParameter){
894                            results.add(new CallResultSet(statement,indx));
895                        }else{
896                            Object o = statement.getObject(indx+1);
897                            if(o instanceof ResultSet){
898                                results.add(new GroovyResultSet((ResultSet)o));
899                            }else{
900                                results.add(o);
901                            }
902                        }
903                        inouts++;
904                    }
905                    indx++;
906                }
907                closure.call(results.toArray(new Object[inouts]));
908            } catch (SQLException e) {
909                log.log(Level.WARNING, "Failed to execute: " + sql, e);
910                throw e;
911            } finally {
912                closeResources(connection, statement);
913            }
914        }
915        
916        /**
917         * Performs a stored procedure call with the given parameters
918         */
919        public int call(GString gstring) throws Exception {
920            List params = getParameters(gstring);
921            String sql = asSql(gstring, params);
922            return call(sql, params);
923        }
924    
925    
926        /**
927         * Performs a stored procedure call with the given parameters,
928         * calling the closure once with all result objects.
929         */
930        public void call(GString gstring, Closure closure) throws Exception {
931            List params = getParameters(gstring);
932            String sql = asSql(gstring,params);
933            call(sql, params,closure);
934        }
935        
936        /**
937         * If this SQL object was created with a Connection then this method closes
938         * the connection. If this SQL object was created from a DataSource then
939         * this method does nothing.
940         * 
941         * @throws SQLException
942         */
943        public void close() throws SQLException {
944            if (useConnection != null) {
945                useConnection.close();
946            }
947        }
948    
949        public DataSource getDataSource() {
950            return dataSource;
951        }
952    
953    
954        public void commit() {
955            try {
956                this.useConnection.commit();
957            }
958            catch (SQLException e) {
959                log.log(Level.SEVERE, "Caught exception commiting connection: " + e, e);
960            }
961        }
962    
963        public void rollback() {
964            try {
965                this.useConnection.rollback();
966            }
967            catch (SQLException e) {
968                log.log(Level.SEVERE, "Caught exception rollbacking connection: " + e, e);
969            }
970        }
971    
972        /**
973         * @return Returns the updateCount.
974         */
975        public int getUpdateCount() {
976            return updateCount;
977        }
978    
979        /**
980         * If this instance was created with a single Connection then the connection
981         * is returned. Otherwise if this instance was created with a DataSource
982         * then this method returns null
983         *
984         * @return the connection wired into this object, or null if this object
985         *         uses a DataSource
986         */
987        public Connection getConnection() {
988            return useConnection;
989        }
990    
991    
992        /**
993         * Allows a closure to be passed in to configure the JDBC statements before they are executed
994         * to do things like set the query size etc.
995         *
996         * @param configureStatement
997         */
998        public void withStatement(Closure configureStatement) {
999            this.configureStatement = configureStatement;
1000        }
1001    
1002        // Implementation methods
1003        //-------------------------------------------------------------------------
1004    
1005        /**
1006         * @return the SQL version of the given query using ? instead of any
1007         *         parameter
1008         */
1009        protected String asSql(GString gstring, List values) {
1010            String[] strings = gstring.getStrings();
1011            if (strings.length <= 0) {
1012                throw new IllegalArgumentException("No SQL specified in GString: " + gstring);
1013            }
1014            boolean nulls = false;
1015            StringBuffer buffer = new StringBuffer();
1016            boolean warned = false;
1017            Iterator iter = values.iterator();
1018            for (int i = 0; i < strings.length; i++) {
1019                String text = strings[i];
1020                if (text != null) {
1021                    buffer.append(text);
1022                }
1023                if (iter.hasNext()) {
1024                    Object value = iter.next();
1025                    if (value != null) {
1026                        if(value instanceof ExpandedVariable){
1027                            buffer.append(((ExpandedVariable)value).getObject());
1028                            iter.remove();
1029                        }else{
1030                            boolean validBinding = true;
1031                            if (i < strings.length - 1) {
1032                                String nextText = strings[i + 1];
1033                                if ((text.endsWith("\"") || text.endsWith("'")) && (nextText.startsWith("'") || nextText.startsWith("\""))) {
1034                                    if (!warned) {
1035                                        log.warning("In Groovy SQL please do not use quotes around dynamic expressions " +
1036                                                "(which start with $) as this means we cannot use a JDBC PreparedStatement " +
1037                                                "and so is a security hole. Groovy has worked around your mistake but the security hole is still there. " +
1038                                                "The expression so far is: " + buffer.toString() + "?" + nextText);
1039                                        warned = true;
1040                                    }
1041                                    buffer.append(value);
1042                                    iter.remove();
1043                                    validBinding = false;
1044                                }
1045                            }
1046                            if (validBinding) {
1047                                buffer.append("?");
1048                            }
1049                        }
1050                    }
1051                    else {
1052                        nulls = true;
1053                        buffer.append("?'\"?"); // will replace these with nullish
1054                        // values
1055                    }
1056                }
1057            }
1058            String sql = buffer.toString();
1059            if (nulls) {
1060                sql = nullify(sql);
1061            }
1062            return sql;
1063        }
1064    
1065        /**
1066         * replace ?'"? references with NULLish
1067         * 
1068         * @param sql
1069         */
1070        protected String nullify(String sql) {
1071            /*
1072             * Some drivers (Oracle classes12.zip) have difficulty resolving data
1073             * type if setObject(null). We will modify the query to pass 'null', 'is
1074             * null', and 'is not null'
1075             */
1076            //could be more efficient by compiling expressions in advance.
1077            int firstWhere = findWhereKeyword(sql);
1078            if (firstWhere >= 0) {
1079                Pattern[] patterns = { Pattern.compile("(?is)^(.{" + firstWhere + "}.*?)!=\\s{0,1}(\\s*)\\?'\"\\?(.*)"),
1080                        Pattern.compile("(?is)^(.{" + firstWhere + "}.*?)<>\\s{0,1}(\\s*)\\?'\"\\?(.*)"),
1081                        Pattern.compile("(?is)^(.{" + firstWhere + "}.*?[^<>])=\\s{0,1}(\\s*)\\?'\"\\?(.*)"), };
1082                String[] replacements = { "$1 is not $2null$3", "$1 is not $2null$3", "$1 is $2null$3", };
1083                for (int i = 0; i < patterns.length; i++) {
1084                    Matcher matcher = patterns[i].matcher(sql);
1085                    while (matcher.matches()) {
1086                        sql = matcher.replaceAll(replacements[i]);
1087                        matcher = patterns[i].matcher(sql);
1088                    }
1089                }
1090            }
1091            return sql.replaceAll("\\?'\"\\?", "null");
1092        }
1093    
1094        /**
1095         * Find the first 'where' keyword in the sql.
1096         * 
1097         * @param sql
1098         */
1099        protected int findWhereKeyword(String sql) {
1100            char[] chars = sql.toLowerCase().toCharArray();
1101            char[] whereChars = "where".toCharArray();
1102            int i = 0;
1103            boolean inString = false; //TODO: Cater for comments?
1104            boolean noWhere = true;
1105            int inWhere = 0;
1106            while (i < chars.length && noWhere) {
1107                switch (chars[i]) {
1108                    case '\'':
1109                        if (inString) {
1110                            inString = false;
1111                        }
1112                        else {
1113                            inString = true;
1114                        }
1115                        break;
1116                    default:
1117                        if (!inString && chars[i] == whereChars[inWhere]) {
1118                            inWhere++;
1119                            if (inWhere == whereChars.length) {
1120                                return i;
1121                            }
1122                        }
1123                }
1124                i++;
1125            }
1126            return -1;
1127        }
1128    
1129        /**
1130         * @return extracts the parameters from the expression as a List
1131         */
1132        protected List getParameters(GString gstring) {
1133            Object[] values = gstring.getValues();
1134            List answer = new ArrayList(values.length);
1135            for (int i = 0; i < values.length; i++) {
1136                if (values[i] != null) {
1137                    answer.add(values[i]);
1138                }
1139            }
1140            return answer;
1141        }
1142    
1143        /**
1144         * Appends the parameters to the given statement
1145         */
1146        protected void setParameters(List params, PreparedStatement statement) throws SQLException {
1147            int i = 1;
1148            for (Iterator iter = params.iterator(); iter.hasNext();) {
1149                Object value = iter.next();
1150                setObject(statement, i++, value);
1151            }
1152        }
1153    
1154        /**
1155         * Strategy method allowing derived classes to handle types differently
1156         * such as for CLOBs etc.
1157         */
1158        protected void setObject(PreparedStatement statement, int i, Object value)
1159            throws SQLException {
1160            if (value instanceof InParameter  || value instanceof OutParameter) {
1161                if(value instanceof InParameter){
1162                    InParameter in = (InParameter) value;
1163                    Object val = in.getValue();
1164                    if (null == val) {
1165                        statement.setNull(i, in.getType());
1166                    } else {
1167                        statement.setObject(i, val, in.getType());
1168                    }
1169                }
1170                if(value instanceof OutParameter){
1171                    try{
1172                        OutParameter out = (OutParameter)value;
1173                        ((CallableStatement)statement).registerOutParameter(i,out.getType());
1174                    }catch(ClassCastException e){
1175                        throw new SQLException("Cannot register out parameter.");
1176                    }
1177                }
1178            } else {
1179                statement.setObject(i, value);
1180            }
1181        }
1182    
1183        protected Connection createConnection() throws SQLException {
1184            if (dataSource != null) {
1185                //Use a doPrivileged here as many different properties need to be
1186                // read, and the policy
1187                //shouldn't have to list them all.
1188                Connection con = null;
1189                try {
1190                    con = (Connection) AccessController.doPrivileged(new PrivilegedExceptionAction() {
1191                        public Object run() throws SQLException {
1192                            return dataSource.getConnection();
1193                        }
1194                    });
1195                }
1196                catch (PrivilegedActionException pae) {
1197                    Exception e = pae.getException();
1198                    if (e instanceof SQLException) {
1199                        throw (SQLException) e;
1200                    }
1201                    else {
1202                        throw (RuntimeException) e;
1203                    }
1204                }
1205                return con;
1206            }
1207            else {
1208                //System.out.println("createConnection returning: " +
1209                // useConnection);
1210                return useConnection;
1211            }
1212        }
1213    
1214        protected void closeResources(Connection connection, Statement statement, ResultSet results) {
1215            if (results != null) {
1216                try {
1217                    results.close();
1218                }
1219                catch (SQLException e) {
1220                    log.log(Level.SEVERE, "Caught exception closing resultSet: " + e, e);
1221                }
1222            }
1223            closeResources(connection, statement);
1224        }
1225    
1226        protected void closeResources(Connection connection, Statement statement) {
1227            if (statement != null) {
1228                try {
1229                    statement.close();
1230                }
1231                catch (SQLException e) {
1232                    log.log(Level.SEVERE, "Caught exception closing statement: " + e, e);
1233                }
1234            }
1235            if (dataSource != null) {
1236                try {
1237                    connection.close();
1238                }
1239                catch (SQLException e) {
1240                    log.log(Level.SEVERE, "Caught exception closing connection: " + e, e);
1241                }
1242            }
1243        }
1244    
1245        private void warnDeprecated() {
1246            if (!warned) {
1247                warned = true;
1248                log.warning("queryEach() is deprecated, please use eachRow() instead");
1249            }
1250        }
1251    
1252        /**
1253         * Provides a hook to be able to configure JDBC statements, such as to configure
1254         *
1255         * @param statement
1256         */
1257        protected void configure(Statement statement) {
1258            if (configureStatement != null) {
1259                configureStatement.call(statement);
1260            }
1261        }
1262    }