View Javadoc

1   /*
2    * $Id: Sql.java,v 1.21 2006/05/30 17:27:29 blackdrag Exp $
3    * 
4    * Copyright 2003 (C) James Strachan and Bob Mcwhirter. All Rights Reserved.
5    * 
6    * Redistribution and use of this software and associated documentation
7    * ("Software"), with or without modification, are permitted provided that the
8    * following conditions are met: 1. Redistributions of source code must retain
9    * copyright statements and notices. Redistributions must also contain a copy
10   * of this document. 2. Redistributions in binary form must reproduce the above
11   * copyright notice, this list of conditions and the following disclaimer in
12   * the documentation and/or other materials provided with the distribution. 3.
13   * The name "groovy" must not be used to endorse or promote products derived
14   * from this Software without prior written permission of The Codehaus. For
15   * written permission, please contact info@codehaus.org. 4. Products derived
16   * from this Software may not be called "groovy" nor may "groovy" appear in
17   * their names without prior written permission of The Codehaus. "groovy" is a
18   * registered trademark of The Codehaus. 5. Due credit should be given to The
19   * Codehaus - http://groovy.codehaus.org/
20   * 
21   * THIS SOFTWARE IS PROVIDED BY THE CODEHAUS AND CONTRIBUTORS ``AS IS'' AND ANY
22   * EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
23   * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
24   * DISCLAIMED. IN NO EVENT SHALL THE CODEHAUS OR ITS CONTRIBUTORS BE LIABLE FOR
25   * ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
26   * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
27   * SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
28   * CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
29   * LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY
30   * OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH
31   * DAMAGE.
32   *  
33   */
34  package groovy.sql;
35  
36  import groovy.lang.Closure;
37  import groovy.lang.GString;
38  
39  import java.security.AccessController;
40  import java.security.PrivilegedActionException;
41  import java.security.PrivilegedExceptionAction;
42  import java.sql.CallableStatement;
43  import java.sql.Connection;
44  import java.sql.DriverManager;
45  import java.sql.PreparedStatement;
46  import java.sql.ResultSet;
47  import java.sql.ResultSetMetaData;
48  import java.sql.SQLException;
49  import java.sql.Statement;
50  import java.sql.Types;
51  import java.util.ArrayList;
52  import java.util.Collections;
53  import java.util.Iterator;
54  import java.util.List;
55  import java.util.LinkedHashMap;
56  import java.util.Properties;
57  import java.util.logging.Level;
58  import java.util.logging.Logger;
59  import java.util.regex.Matcher;
60  import java.util.regex.Pattern;
61  
62  import javax.sql.DataSource;
63  
64  /***
65   * Represents an extent of objects
66   *
67   * @author Chris Stevenson
68   * @author <a href="mailto:james@coredevelopers.net">James Strachan </a>
69   * @version $Revision: 1.21 $
70   */
71  public class Sql {
72  
73      protected Logger log = Logger.getLogger(getClass().getName());
74  
75      private DataSource dataSource;
76  
77      private Connection useConnection;
78  
79      /*** lets only warn of using deprecated methods once */
80      private boolean warned;
81  
82      // store the last row count for executeUpdate
83      int updateCount = 0;
84  
85      /*** allows a closure to be used to configure the statement before its use */
86      private Closure configureStatement;
87  
88      /***
89       * A helper method which creates a new Sql instance from a JDBC connection
90       * URL
91       *
92       * @param url
93       * @return a new Sql instance with a connection
94       */
95      public static Sql newInstance(String url) throws SQLException {
96          Connection connection = DriverManager.getConnection(url);
97          return new Sql(connection);
98      }
99  
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      * @return
288      */
289     public static InOutParameter inout(final InParameter in){
290         return new InOutParameter(){
291             public int getType() {
292                 return in.getType();
293             }
294             public Object getValue() {
295                 return in.getValue();
296             }            
297         };
298     }
299     
300     /***
301      * Create a new ResultSetOutParameter
302      * @param type the JDBC data type.
303      * @return a ResultSetOutParameter
304      */
305     public static ResultSetOutParameter resultSet(final int type){
306         return new ResultSetOutParameter(){
307             public int getType() {
308                 return type;
309             }
310         };
311     }
312         
313     /***
314      * Creates a variable to be expanded in the Sql string rather
315      * than representing an sql parameter.
316      * @param object
317      * @return
318      */
319     public static ExpandedVariable expand(final Object object){
320         return new ExpandedVariable(){
321             public Object getObject() {
322                 return object;
323             }};
324     }
325     
326     /***
327      * Constructs an SQL instance using the given DataSource. Each operation
328      * will use a Connection from the DataSource pool and close it when the
329      * operation is completed putting it back into the pool.
330      *
331      * @param dataSource
332      */
333     public Sql(DataSource dataSource) {
334         this.dataSource = dataSource;
335     }
336 
337     /***
338      * Construts an SQL instance using the given Connection. It is the callers
339      * responsibility to close the Connection after the Sql instance has been
340      * used. You can do this on the connection object directly or by calling the
341      * {@link java.sql.Connection#close()}  method.
342      *
343      * @param connection
344      */
345     public Sql(Connection connection) {
346         if (connection == null) {
347             throw new NullPointerException("Must specify a non-null Connection");
348         }
349         this.useConnection = connection;
350     }
351 
352     public Sql(Sql parent) {
353         this.dataSource = parent.dataSource;
354         this.useConnection = parent.useConnection;
355     }
356 
357     public DataSet dataSet(String table) {
358         return new DataSet(this, table);
359     }
360 
361     public DataSet dataSet(Class type) {
362         return new DataSet(this, type);
363     }
364 
365     /***
366      * Performs the given SQL query calling the closure with the result set
367      */
368     public void query(String sql, Closure closure) throws SQLException {
369         Connection connection = createConnection();
370         Statement statement = connection.createStatement();
371         configure(statement);
372         ResultSet results = null;
373         try {
374             log.fine(sql);
375             results = statement.executeQuery(sql);
376             closure.call(results);
377         }
378         catch (SQLException e) {
379             log.log(Level.FINE, "Failed to execute: " + sql, e);
380             throw e;
381         }
382         finally {
383             closeResources(connection, statement, results);
384         }
385     }
386 
387     /***
388      * Performs the given SQL query with parameters calling the closure with the
389      * result set
390      */
391     public void query(String sql, List params, Closure closure) throws SQLException {
392         Connection connection = createConnection();
393         PreparedStatement statement = null;
394         ResultSet results = null;
395         try {
396             log.fine(sql);
397             statement = connection.prepareStatement(sql);
398             setParameters(params, statement);
399             configure(statement);
400             results = statement.executeQuery();
401             closure.call(results);
402         }
403         catch (SQLException e) {
404             log.log(Level.FINE, "Failed to execute: " + sql, e);
405             throw e;
406         }
407         finally {
408             closeResources(connection, statement, results);
409         }
410     }
411 
412     /***
413      * Performs the given SQL query calling the closure with the result set
414      */
415     public void query(GString gstring, Closure closure) throws SQLException {
416         List params = getParameters(gstring);
417         String sql = asSql(gstring, params);
418         query(sql, params, closure);
419     }
420 
421     /***
422      * @deprecated please use eachRow instead
423      */
424     public void queryEach(String sql, Closure closure) throws SQLException {
425         warnDeprecated();
426         eachRow(sql, closure);
427     }
428 
429     /***
430      * Performs the given SQL query calling the closure with each row of the
431      * result set
432      */
433     public void eachRow(String sql, Closure closure) throws SQLException {
434         Connection connection = createConnection();
435         Statement statement = connection.createStatement();
436         configure(statement);
437         ResultSet results = null;
438         try {
439             log.fine(sql);
440             results = statement.executeQuery(sql);
441 
442             GroovyResultSet groovyRS = new GroovyResultSet(results);
443             while (groovyRS.next()) {
444                 closure.call(groovyRS);
445             }
446         }
447         catch (SQLException e) {
448             log.log(Level.FINE, "Failed to execute: " + sql, e);
449             throw e;
450         }
451         finally {
452             closeResources(connection, statement, results);
453         }
454     }
455 
456     /***
457      * @deprecated please use eachRow instead
458      */
459     public void queryEach(String sql, List params, Closure closure) throws SQLException {
460         warnDeprecated();
461         eachRow(sql, params, closure);
462     }
463 
464     /***
465      * Performs the given SQL query calling the closure with the result set
466      */
467     public void eachRow(String sql, List params, Closure closure) throws SQLException {
468         Connection connection = createConnection();
469         PreparedStatement statement = null;
470         ResultSet results = null;
471         try {
472             log.fine(sql);
473             statement = connection.prepareStatement(sql);
474             setParameters(params, statement);
475             configure(statement);
476             results = statement.executeQuery();
477 
478             GroovyResultSet groovyRS = new GroovyResultSet(results);
479             while (groovyRS.next()) {
480                 closure.call(groovyRS);
481             }
482         }
483         catch (SQLException e) {
484             log.log(Level.FINE, "Failed to execute: " + sql, e);
485             throw e;
486         }
487         finally {
488             closeResources(connection, statement, results);
489         }
490     }
491 
492     /***
493      * Performs the given SQL query calling the closure with the result set
494      */
495     public void eachRow(GString gstring, Closure closure) throws SQLException {
496         List params = getParameters(gstring);
497         String sql = asSql(gstring, params);
498         eachRow(sql, params, closure);
499     }
500 
501     /***
502      * @deprecated please use eachRow instead
503      */
504     public void queryEach(GString gstring, Closure closure) throws SQLException {
505         warnDeprecated();
506         eachRow(gstring, closure);
507     }
508 
509     /***
510      * Performs the given SQL query and return the rows of the result set
511      */
512      public List rows(String sql) throws SQLException {
513         List results = new ArrayList();
514         Connection connection = createConnection();
515         Statement statement = connection.createStatement();
516         configure(statement);
517         ResultSet rs = null;
518         try {
519             log.fine(sql);
520             rs = statement.executeQuery(sql);
521             while (rs.next()) {
522                 ResultSetMetaData metadata = rs.getMetaData();
523                 LinkedHashMap lhm = new LinkedHashMap(metadata.getColumnCount(),1,true);
524                 for(int i=1 ; i<=metadata.getColumnCount() ; i++) {
525                       lhm.put(metadata.getColumnName(i),rs.getObject(i));
526                 }
527                 GroovyRowResult row = new GroovyRowResult(lhm);
528                 results.add(row);
529             }
530             return(results);
531         }
532         catch (SQLException e) {
533             log.log(Level.FINE, "Failed to execute: " + sql, e);
534             throw e;
535         }
536         finally {
537             closeResources(connection, statement, rs);
538         }
539     }
540 
541     /***
542      * Performs the given SQL query and return the first row of the result set
543      */
544     public Object firstRow(String sql) throws SQLException {
545         return( rows(sql).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          return( rows(sql, params).get(0));
589      }
590 
591     /***
592      * Executes the given piece of SQL
593      */
594     public boolean execute(String sql) throws SQLException {
595         Connection connection = createConnection();
596         Statement statement = null;
597         try {
598             log.fine(sql);
599             statement = connection.createStatement();
600             configure(statement);
601             boolean isResultSet = statement.execute(sql);
602             this.updateCount = statement.getUpdateCount();
603             return isResultSet;
604         }
605         catch (SQLException e) {
606             log.log(Level.FINE, "Failed to execute: " + sql, e);
607             throw e;
608         }
609         finally {
610             closeResources(connection, statement);
611         }
612     }
613 
614     /***
615      * Executes the given SQL update
616      * 
617      * @return the number of rows updated
618      */
619     public int executeUpdate(String sql) throws SQLException {
620         Connection connection = createConnection();
621         Statement statement = null;
622         try {
623             log.fine(sql);
624             statement = connection.createStatement();
625             configure(statement);
626             this.updateCount = statement.executeUpdate(sql);
627             return this.updateCount;
628         }
629         catch (SQLException e) {
630             log.log(Level.FINE, "Failed to execute: " + sql, e);
631             throw e;
632         }
633         finally {
634             closeResources(connection, statement);
635         }
636     }
637 
638     /***
639      * Executes the given SQL statement. See {@link #executeInsert(GString)}
640      * for more details. 
641      * @param sql The SQL statement to execute.
642      * @return A list of the auto-generated column values for each
643      * inserted row.
644      */
645     public List executeInsert(String sql) throws SQLException {
646         Connection connection = createConnection();
647         Statement statement = null;
648         try {
649             log.fine(sql);
650             statement = connection.createStatement();
651             configure(statement);
652             boolean hasResultSet = statement.execute(sql, Statement.RETURN_GENERATED_KEYS);
653 
654             // Prepare a list to contain the auto-generated column
655             // values, and then fetch them from the statement.
656             List autoKeys = new ArrayList();
657         	ResultSet keys = statement.getGeneratedKeys();
658         	int count = keys.getMetaData().getColumnCount();
659 
660         	// Copy the column values into a list of a list.
661         	while (keys.next()) {
662         		List rowKeys = new ArrayList(count);
663         		for (int i = 1; i <= count; i++) {
664         			rowKeys.add(keys.getObject(i));
665         		}
666 
667         		autoKeys.add(rowKeys);
668         	}
669 
670         	// Store the update count so that it can be retrieved by
671         	// clients, and then return the list of auto-generated
672         	// values.
673         	this.updateCount = statement.getUpdateCount();
674         	return autoKeys;
675         }
676         catch (SQLException e) {
677             log.log(Level.FINE, "Failed to execute: " + sql, e);
678             throw e;
679         }
680         finally {
681             closeResources(connection, statement);
682         }
683     }
684 
685     /***
686      * Executes the given piece of SQL with parameters
687      */
688     public boolean execute(String sql, List params) throws SQLException {
689         Connection connection = createConnection();
690         PreparedStatement statement = null;
691         try {
692             log.fine(sql);
693             statement = connection.prepareStatement(sql);
694             setParameters(params, statement);
695             configure(statement);
696             boolean isResultSet = statement.execute();
697             this.updateCount = statement.getUpdateCount();
698             return isResultSet;
699         }
700         catch (SQLException e) {
701             log.log(Level.FINE, "Failed to execute: " + sql, e);
702             throw e;
703         }
704         finally {
705             closeResources(connection, statement);
706         }
707     }
708 
709     /***
710      * Executes the given SQL update with parameters
711      * 
712      * @return the number of rows updated
713      */
714     public int executeUpdate(String sql, List params) throws SQLException {
715         Connection connection = createConnection();
716         PreparedStatement statement = null;
717         try {
718             log.fine(sql);
719             statement = connection.prepareStatement(sql);
720             setParameters(params, statement);
721             configure(statement);
722             this.updateCount = statement.executeUpdate();
723             return this.updateCount;
724         }
725         catch (SQLException e) {
726             log.log(Level.FINE, "Failed to execute: " + sql, e);
727             throw e;
728         }
729         finally {
730             closeResources(connection, statement);
731         }
732     }
733 
734     /***
735      * Executes the given SQL statement with a particular list of
736      * parameter values. See {@link #executeInsert(GString)} for
737      * more details. 
738      * @param sql The SQL statement to execute.
739      * @param params The parameter values that will be substituted
740      * into the SQL statement's parameter slots.
741      * @return A list of the auto-generated column values for each
742      * inserted row.
743      */
744     public List executeInsert(String sql, List params) throws SQLException {
745         // Now send the SQL to the database.
746         Connection connection = createConnection();
747         PreparedStatement statement = null;
748         try {
749             log.fine(sql);
750 
751             // Prepare a statement for the SQL and then execute it.
752             statement = connection.prepareStatement(sql);
753             setParameters(params, statement);
754             configure(statement);
755             boolean hasResultSet = statement.execute(sql, Statement.RETURN_GENERATED_KEYS);
756 
757             // Prepare a list to contain the auto-generated column
758             // values, and then fetch them from the statement.
759             List autoKeys = new ArrayList();
760         	ResultSet keys = statement.getGeneratedKeys();
761         	int count = keys.getMetaData().getColumnCount();
762 
763         	// Copy the column values into a list of a list.
764         	while (keys.next()) {
765         		List rowKeys = new ArrayList(count);
766         		for (int i = 1; i <= count; i++) {
767         			rowKeys.add(keys.getObject(i));
768         		}
769 
770         		autoKeys.add(rowKeys);
771         	}
772 
773         	// Store the update count so that it can be retrieved by
774         	// clients, and then return the list of auto-generated
775         	// values.
776         	this.updateCount = statement.getUpdateCount();
777         	return autoKeys;
778         }
779         catch (SQLException e) {
780             log.log(Level.FINE, "Failed to execute: " + sql, e);
781             throw e;
782         }
783         finally {
784             closeResources(connection, statement);
785         }
786     }
787 
788     /***
789      * Executes the given SQL with embedded expressions inside
790      */
791     public boolean execute(GString gstring) throws SQLException {
792         List params = getParameters(gstring);
793         String sql = asSql(gstring, params);
794         return execute(sql, params);
795     }
796 
797     /***
798      * Executes the given SQL update with embedded expressions inside
799      * 
800      * @return the number of rows updated
801      */
802     public int executeUpdate(GString gstring) throws SQLException {
803         List params = getParameters(gstring);
804         String sql = asSql(gstring, params);
805         return executeUpdate(sql, params);
806     }
807 
808     /***
809      * <p>Executes the given SQL with embedded expressions inside, and
810      * returns the values of any auto-generated colums, such as an
811      * autoincrement ID field. These values can be accessed using
812      * array notation. For example, to return the second auto-generated
813      * column value of the third row, use <code>keys[3][1]</code>. The
814      * method is designed to be used with SQL INSERT statements, but is
815      * not limited to them.</p>
816      * <p>The standard use for this method is when a table has an
817      * autoincrement ID column and you want to know what the ID is for
818      * a newly inserted row. In this example, we insert a single row
819      * into a table in which the first column contains the autoincrement
820      * ID:</p>
821      * <pre>
822      *     def sql = Sql.newInstance("jdbc:mysql://localhost:3306/groovy",
823      *                               "user", 
824      *                               "password",
825      *                               "com.mysql.jdbc.Driver")
826      *
827      *     def keys = sql.insert("insert into test_table (INT_DATA, STRING_DATA) "
828      *                           + "VALUES (1, 'Key Largo')")
829      *
830      *     def id = keys[0][0]
831      *
832      *     // 'id' now contains the value of the new row's ID column.
833      *     // It can be used to update an object representation's
834      *     // id attribute for example.
835      *     ...
836      * </pre>
837      * @return A list of column values representing each row's
838      * auto-generated keys.
839      */
840     public List executeInsert(GString gstring) throws SQLException {
841         List params = getParameters(gstring);
842         String sql = asSql(gstring, params);
843         return executeInsert(sql, params);
844     }
845 
846     /***
847      * Performs a stored procedure call
848      */
849     public int call(String sql) throws Exception {
850         return call(sql, Collections.EMPTY_LIST);
851     }
852 
853     /***
854      * Performs a stored procedure call with the given parameters
855      */
856     public int call(String sql, List params) throws Exception {
857         Connection connection = createConnection();
858         CallableStatement statement = connection.prepareCall(sql);
859         try {
860             log.fine(sql);
861             setParameters(params, statement);
862             configure(statement);
863             return statement.executeUpdate();
864         }
865         catch (SQLException e) {
866             log.log(Level.FINE, "Failed to execute: " + sql, e);
867             throw e;
868         }
869         finally {
870             closeResources(connection, statement);
871         }
872     }
873 
874     /***
875      * Performs a stored procedure call with the given parameters.  The closure
876      * is called once with all the out parameters.
877      */
878     public void call(String sql, List params, Closure closure) throws Exception {
879         Connection connection = createConnection();
880         CallableStatement statement = connection.prepareCall(sql);
881         try {
882             log.fine(sql);
883             setParameters(params, statement);
884             statement.execute();
885             List results = new ArrayList();
886             int indx = 0;
887             int inouts = 0;
888             for (Iterator iter = params.iterator(); iter.hasNext();) {
889                 Object value = iter.next();
890                 if(value instanceof OutParameter){
891                     if(value instanceof ResultSetOutParameter){
892                         results.add(new CallResultSet(statement,indx));
893                     }else{
894                         Object o = statement.getObject(indx+1);
895                         if(o instanceof ResultSet){
896                             results.add(new GroovyResultSet((ResultSet)o));
897                         }else{
898                             results.add(o);
899                         }
900                     }
901                     inouts++;
902                 }
903                 indx++;
904             }
905             closure.call(results.toArray(new Object[inouts]));
906         } catch (SQLException e) {
907             log.log(Level.WARNING, "Failed to execute: " + sql, e);
908             throw e;
909         } finally {
910             closeResources(connection, statement);
911         }
912     }
913     
914     /***
915      * Performs a stored procedure call with the given parameters
916      */
917     public int call(GString gstring) throws Exception {
918         List params = getParameters(gstring);
919         String sql = asSql(gstring, params);
920         return call(sql, params);
921     }
922 
923 
924     /***
925      * Performs a stored procedure call with the given parameters,
926      * calling the closure once with all result objects.
927      */
928     public void call(GString gstring, Closure closure) throws Exception {
929         List params = getParameters(gstring);
930         String sql = asSql(gstring,params);
931         call(sql, params,closure);
932     }
933     
934     /***
935      * If this SQL object was created with a Connection then this method closes
936      * the connection. If this SQL object was created from a DataSource then
937      * this method does nothing.
938      * 
939      * @throws SQLException
940      */
941     public void close() throws SQLException {
942         if (useConnection != null) {
943             useConnection.close();
944         }
945     }
946 
947     public DataSource getDataSource() {
948         return dataSource;
949     }
950 
951 
952     public void commit() {
953         try {
954             this.useConnection.commit();
955         }
956         catch (SQLException e) {
957             log.log(Level.SEVERE, "Caught exception commiting connection: " + e, e);
958         }
959     }
960 
961     public void rollback() {
962         try {
963             this.useConnection.rollback();
964         }
965         catch (SQLException e) {
966             log.log(Level.SEVERE, "Caught exception rollbacking connection: " + e, e);
967         }
968     }
969 
970     /***
971      * @return Returns the updateCount.
972      */
973     public int getUpdateCount() {
974         return updateCount;
975     }
976 
977     /***
978      * If this instance was created with a single Connection then the connection
979      * is returned. Otherwise if this instance was created with a DataSource
980      * then this method returns null
981      *
982      * @return the connection wired into this object, or null if this object
983      *         uses a DataSource
984      */
985     public Connection getConnection() {
986         return useConnection;
987     }
988 
989 
990     /***
991      * Allows a closure to be passed in to configure the JDBC statements before they are executed
992      * to do things like set the query size etc.
993      *
994      * @param configureStatement
995      */
996     public void withStatement(Closure configureStatement) {
997         this.configureStatement = configureStatement;
998     }
999 
1000     // Implementation methods
1001     //-------------------------------------------------------------------------
1002 
1003     /***
1004      * @return the SQL version of the given query using ? instead of any
1005      *         parameter
1006      */
1007     protected String asSql(GString gstring, List values) {
1008         String[] strings = gstring.getStrings();
1009         if (strings.length <= 0) {
1010             throw new IllegalArgumentException("No SQL specified in GString: " + gstring);
1011         }
1012         boolean nulls = false;
1013         StringBuffer buffer = new StringBuffer();
1014         boolean warned = false;
1015         Iterator iter = values.iterator();
1016         for (int i = 0; i < strings.length; i++) {
1017             String text = strings[i];
1018             if (text != null) {
1019                 buffer.append(text);
1020             }
1021             if (iter.hasNext()) {
1022                 Object value = iter.next();
1023                 if (value != null) {
1024                     if(value instanceof ExpandedVariable){
1025                         buffer.append(((ExpandedVariable)value).getObject());
1026                         iter.remove();
1027                     }else{
1028                         boolean validBinding = true;
1029                         if (i < strings.length - 1) {
1030                             String nextText = strings[i + 1];
1031                             if ((text.endsWith("\"") || text.endsWith("'")) && (nextText.startsWith("'") || nextText.startsWith("\""))) {
1032                                 if (!warned) {
1033                                     log.warning("In Groovy SQL please do not use quotes around dynamic expressions " +
1034                                             "(which start with $) as this means we cannot use a JDBC PreparedStatement " +
1035                                             "and so is a security hole. Groovy has worked around your mistake but the security hole is still there. " +
1036                                             "The expression so far is: " + buffer.toString() + "?" + nextText);
1037                                     warned = true;
1038                                 }
1039                                 buffer.append(value);
1040                                 iter.remove();
1041                                 validBinding = false;
1042                             }
1043                         }
1044                         if (validBinding) {
1045                             buffer.append("?");
1046                         }
1047                     }
1048                 }
1049                 else {
1050                     nulls = true;
1051                     buffer.append("?'\"?"); // will replace these with nullish
1052                     // values
1053                 }
1054             }
1055         }
1056         String sql = buffer.toString();
1057         if (nulls) {
1058             sql = nullify(sql);
1059         }
1060         return sql;
1061     }
1062 
1063     /***
1064      * replace ?'"? references with NULLish
1065      * 
1066      * @param sql
1067      * @return
1068      */
1069     protected String nullify(String sql) {
1070         /*
1071          * Some drivers (Oracle classes12.zip) have difficulty resolving data
1072          * type if setObject(null). We will modify the query to pass 'null', 'is
1073          * null', and 'is not null'
1074          */
1075         //could be more efficient by compiling expressions in advance.
1076         int firstWhere = findWhereKeyword(sql);
1077         if (firstWhere >= 0) {
1078             Pattern[] patterns = { Pattern.compile("(?is)^(.{" + firstWhere + "}.*?)!=//s{0,1}(//s*)//?'\"//?(.*)"),
1079                     Pattern.compile("(?is)^(.{" + firstWhere + "}.*?)<>//s{0,1}(//s*)//?'\"//?(.*)"),
1080                     Pattern.compile("(?is)^(.{" + firstWhere + "}.*?[^<>])=//s{0,1}(//s*)//?'\"//?(.*)"), };
1081             String[] replacements = { "$1 is not $2null$3", "$1 is not $2null$3", "$1 is $2null$3", };
1082             for (int i = 0; i < patterns.length; i++) {
1083                 Matcher matcher = patterns[i].matcher(sql);
1084                 while (matcher.matches()) {
1085                     sql = matcher.replaceAll(replacements[i]);
1086                     matcher = patterns[i].matcher(sql);
1087                 }
1088             }
1089         }
1090         return sql.replaceAll("//?'\"//?", "null");
1091     }
1092 
1093     /***
1094      * Find the first 'where' keyword in the sql.
1095      * 
1096      * @param sql
1097      * @return
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 }