View Javadoc

1   /*
2    * $Id: Sql.java,v 1.16 2004/12/27 23:38:27 spullara 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.SQLException;
48  import java.sql.Statement;
49  import java.util.ArrayList;
50  import java.util.Collections;
51  import java.util.Iterator;
52  import java.util.List;
53  import java.util.Properties;
54  import java.util.logging.Level;
55  import java.util.logging.Logger;
56  import java.util.regex.Matcher;
57  import java.util.regex.Pattern;
58  
59  import javax.sql.DataSource;
60  
61  /***
62   * Represents an extent of objects
63   * 
64   * @author Chris Stevenson
65   * @author <a href="mailto:james@coredevelopers.net">James Strachan </a>
66   * @version $Revision: 1.16 $
67   */
68  public class Sql {
69  
70      protected Logger log = Logger.getLogger(getClass().getName());
71  
72      private DataSource dataSource;
73  
74      private Connection useConnection;
75  
76      /*** lets only warn of using deprecated methods once */
77      private boolean warned;
78  
79      // store the last row count for executeUpdate
80      int updateCount = 0;
81  
82      /***
83       * A helper method which creates a new Sql instance from a JDBC connection
84       * URL
85       * 
86       * @param url
87       * @return a new Sql instance with a connection
88       */
89      public static Sql newInstance(String url) throws SQLException {
90          Connection connection = DriverManager.getConnection(url);
91          return new Sql(connection);
92      }
93  
94      /***
95       * A helper method which creates a new Sql instance from a JDBC connection
96       * URL
97       * 
98       * @param url
99       * @return a new Sql instance with a connection
100      */
101     public static Sql newInstance(String url, Properties properties) throws SQLException {
102         Connection connection = DriverManager.getConnection(url, properties);
103         return new Sql(connection);
104     }
105 
106     /***
107      * A helper method which creates a new Sql instance from a JDBC connection
108      * URL and driver class name
109      * 
110      * @param url
111      * @return a new Sql instance with a connection
112      */
113     public static Sql newInstance(String url, Properties properties, String driverClassName) throws SQLException, ClassNotFoundException {
114         loadDriver(driverClassName);
115         return newInstance(url, properties);
116     }
117 
118     /***
119      * A helper method which creates a new Sql instance from a JDBC connection
120      * URL, username and password
121      * 
122      * @param url
123      * @return a new Sql instance with a connection
124      */
125     public static Sql newInstance(String url, String user, String password) throws SQLException {
126         Connection connection = DriverManager.getConnection(url, user, password);
127         return new Sql(connection);
128     }
129 
130     /***
131      * A helper method which creates a new Sql instance from a JDBC connection
132      * URL, username, password and driver class name
133      * 
134      * @param url
135      * @return a new Sql instance with a connection
136      */
137     public static Sql newInstance(String url, String user, String password, String driverClassName) throws SQLException,
138             ClassNotFoundException {
139         loadDriver(driverClassName);
140         return newInstance(url, user, password);
141     }
142 
143     /***
144      * A helper method which creates a new Sql instance from a JDBC connection
145      * URL and driver class name
146      * 
147      * @param url
148      * @param driverClassName
149      *            the class name of the driver
150      * @return a new Sql instance with a connection
151      */
152     public static Sql newInstance(String url, String driverClassName) throws SQLException, ClassNotFoundException {
153         loadDriver(driverClassName);
154         return newInstance(url);
155     }
156 
157     /***
158      * Attempts to load the JDBC driver on the thread, current or system class
159      * loaders
160      * 
161      * @param driverClassName
162      * @throws ClassNotFoundException
163      */
164     public static void loadDriver(String driverClassName) throws ClassNotFoundException {
165         // lets try the thread context class loader first
166         // lets try to use the system class loader
167         try {
168             Class.forName(driverClassName);
169         }
170         catch (ClassNotFoundException e) {
171             try {
172                 Thread.currentThread().getContextClassLoader().loadClass(driverClassName);
173             }
174             catch (ClassNotFoundException e2) {
175                 // now lets try the classloader which loaded us
176                 try {
177                     Sql.class.getClassLoader().loadClass(driverClassName);
178                 }
179                 catch (ClassNotFoundException e3) {
180                     throw e;
181                 }
182             }
183         }
184     }
185 
186     /***
187      * Constructs an SQL instance using the given DataSource. Each operation
188      * will use a Connection from the DataSource pool and close it when the
189      * operation is completed putting it back into the pool.
190      * 
191      * @param dataSource
192      */
193     public Sql(DataSource dataSource) {
194         this.dataSource = dataSource;
195     }
196 
197     /***
198      * Construts an SQL instance using the given Connection. It is the callers
199      * responsibility to close the Connection after the Sql instance has been
200      * used. You can do this on the connection object directly or by calling the
201      * {@link close()}method.
202      * 
203      * @param connection
204      */
205     public Sql(Connection connection) {
206         if (connection == null) {
207             throw new NullPointerException("Must specify a non-null Connection");
208         }
209         this.useConnection = connection;
210     }
211 
212     public Sql(Sql parent) {
213         this.dataSource = parent.dataSource;
214         this.useConnection = parent.useConnection;
215     }
216 
217     public DataSet dataSet(String table) {
218         return new DataSet(this, table);
219     }
220 
221     public DataSet dataSet(Class type) {
222         return new DataSet(this, type);
223     }
224 
225     /***
226      * Performs the given SQL query calling the closure with the result set
227      */
228     public void query(String sql, Closure closure) throws SQLException {
229         Connection connection = createConnection();
230         Statement statement = connection.createStatement();
231         ResultSet results = null;
232         try {
233             log.fine(sql);
234             results = statement.executeQuery(sql);
235             closure.call(results);
236         }
237         catch (SQLException e) {
238             log.log(Level.FINE, "Failed to execute: " + sql, e);
239             throw e;
240         }
241         finally {
242             closeResources(connection, statement, results);
243         }
244     }
245 
246     /***
247      * Performs the given SQL query with parameters calling the closure with the
248      * result set
249      */
250     public void query(String sql, List params, Closure closure) throws SQLException {
251         Connection connection = createConnection();
252         PreparedStatement statement = null;
253         ResultSet results = null;
254         try {
255             log.fine(sql);
256             statement = connection.prepareStatement(sql);
257             setParameters(params, statement);
258             results = statement.executeQuery();
259             closure.call(results);
260         }
261         catch (SQLException e) {
262             log.log(Level.FINE, "Failed to execute: " + sql, e);
263             throw e;
264         }
265         finally {
266             closeResources(connection, statement, results);
267         }
268     }
269 
270     /***
271      * Performs the given SQL query calling the closure with the result set
272      */
273     public void query(GString gstring, Closure closure) throws SQLException {
274         List params = getParameters(gstring);
275         String sql = asSql(gstring, params);
276         query(sql, params, closure);
277     }
278 
279     /***
280      * @deprecated please use eachRow instead
281      */
282     public void queryEach(String sql, Closure closure) throws SQLException {
283         warnDeprecated();
284         eachRow(sql, closure);
285     }
286 
287     /***
288      * Performs the given SQL query calling the closure with each row of the
289      * result set
290      */
291     public void eachRow(String sql, Closure closure) throws SQLException {
292         Connection connection = createConnection();
293         Statement statement = connection.createStatement();
294         ResultSet results = null;
295         try {
296             log.fine(sql);
297             results = statement.executeQuery(sql);
298 
299             GroovyResultSet groovyRS = new GroovyResultSet(results);
300             while (groovyRS.next()) {
301                 closure.call(groovyRS);
302             }
303         }
304         catch (SQLException e) {
305             log.log(Level.FINE, "Failed to execute: " + sql, e);
306             throw e;
307         }
308         finally {
309             closeResources(connection, statement, results);
310         }
311     }
312 
313     /***
314      * @deprecated please use eachRow instead
315      */
316     public void queryEach(String sql, List params, Closure closure) throws SQLException {
317         warnDeprecated();
318         eachRow(sql, params, closure);
319     }
320 
321     /***
322      * Performs the given SQL query calling the closure with the result set
323      */
324     public void eachRow(String sql, List params, Closure closure) throws SQLException {
325         Connection connection = createConnection();
326         PreparedStatement statement = null;
327         ResultSet results = null;
328         try {
329             log.fine(sql);
330             statement = connection.prepareStatement(sql);
331             setParameters(params, statement);
332             results = statement.executeQuery();
333 
334             GroovyResultSet groovyRS = new GroovyResultSet(results);
335             while (groovyRS.next()) {
336                 closure.call(groovyRS);
337             }
338         }
339         catch (SQLException e) {
340             log.log(Level.FINE, "Failed to execute: " + sql, e);
341             throw e;
342         }
343         finally {
344             closeResources(connection, statement, results);
345         }
346     }
347 
348     /***
349      * Performs the given SQL query calling the closure with the result set
350      */
351     public void eachRow(GString gstring, Closure closure) throws SQLException {
352         List params = getParameters(gstring);
353         String sql = asSql(gstring, params);
354         eachRow(sql, params, closure);
355     }
356 
357     /***
358      * @deprecated please use eachRow instead
359      */
360     public void queryEach(GString gstring, Closure closure) throws SQLException {
361         warnDeprecated();
362         eachRow(gstring, closure);
363     }
364 
365     /***
366      * Executes the given piece of SQL
367      */
368     public boolean execute(String sql) throws SQLException {
369         Connection connection = createConnection();
370         Statement statement = null;
371         try {
372             log.fine(sql);
373             statement = connection.createStatement();
374             boolean isResultSet = statement.execute(sql);
375             this.updateCount = statement.getUpdateCount();
376             return isResultSet;
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);
384         }
385     }
386 
387     /***
388      * Executes the given SQL update
389      * 
390      * @return the number of rows updated
391      */
392     public int executeUpdate(String sql) throws SQLException {
393         Connection connection = createConnection();
394         Statement statement = null;
395         try {
396             log.fine(sql);
397             statement = connection.createStatement();
398             this.updateCount = statement.executeUpdate(sql);
399             return this.updateCount;
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);
407         }
408     }
409 
410     /***
411      * Executes the given piece of SQL with parameters
412      */
413     public boolean execute(String sql, List params) throws SQLException {
414         Connection connection = createConnection();
415         PreparedStatement statement = null;
416         try {
417             log.fine(sql);
418             statement = connection.prepareStatement(sql);
419             setParameters(params, statement);
420             boolean isResultSet = statement.execute();
421             this.updateCount = statement.getUpdateCount();
422             return isResultSet;
423         }
424         catch (SQLException e) {
425             log.log(Level.FINE, "Failed to execute: " + sql, e);
426             throw e;
427         }
428         finally {
429             closeResources(connection, statement);
430         }
431     }
432 
433     /***
434      * Executes the given SQL update with parameters
435      * 
436      * @return the number of rows updated
437      */
438     public int executeUpdate(String sql, List params) throws SQLException {
439         Connection connection = createConnection();
440         PreparedStatement statement = null;
441         try {
442             log.fine(sql);
443             statement = connection.prepareStatement(sql);
444             setParameters(params, statement);
445             this.updateCount = statement.executeUpdate();
446             return this.updateCount;
447         }
448         catch (SQLException e) {
449             log.log(Level.FINE, "Failed to execute: " + sql, e);
450             throw e;
451         }
452         finally {
453             closeResources(connection, statement);
454         }
455     }
456 
457     /***
458      * Executes the given SQL with embedded expressions inside
459      */
460     public boolean execute(GString gstring) throws SQLException {
461         List params = getParameters(gstring);
462         String sql = asSql(gstring, params);
463         return execute(sql, params);
464     }
465 
466     /***
467      * Executes the given SQL update with embedded expressions inside
468      * 
469      * @return the number of rows updated
470      */
471     public int executeUpdate(GString gstring) throws SQLException {
472         List params = getParameters(gstring);
473         String sql = asSql(gstring, params);
474         return executeUpdate(sql, params);
475     }
476 
477     /***
478      * Performs a stored procedure call
479      */
480     public int call(String sql) throws Exception {
481         return call(sql, Collections.EMPTY_LIST);
482     }
483 
484     /***
485      * Performs a stored procedure call with the given parameters
486      */
487     public int call(String sql, List params) throws Exception {
488         Connection connection = createConnection();
489         CallableStatement statement = connection.prepareCall(sql);
490         try {
491             log.fine(sql);
492             setParameters(params, statement);
493             return statement.executeUpdate();
494         }
495         catch (SQLException e) {
496             log.log(Level.FINE, "Failed to execute: " + sql, e);
497             throw e;
498         }
499         finally {
500             closeResources(connection, statement);
501         }
502     }
503 
504     /***
505      * Performs a stored procedure call with the given parameters
506      */
507     public int call(GString gstring) throws Exception {
508         List params = getParameters(gstring);
509         String sql = asSql(gstring, params);
510         return call(sql, params);
511     }
512 
513     /***
514      * If this SQL object was created with a Connection then this method closes
515      * the connection. If this SQL object was created from a DataSource then
516      * this method does nothing.
517      * 
518      * @throws SQLException
519      */
520     public void close() throws SQLException {
521         if (useConnection != null) {
522             useConnection.close();
523         }
524     }
525 
526     public DataSource getDataSource() {
527         return dataSource;
528     }
529 
530     /***
531      * @return the SQL version of the given query using ? instead of any
532      *         parameter
533      */
534     protected String asSql(GString gstring, List values) {
535         boolean nulls = false;
536         String[] strings = gstring.getStrings();
537         if (strings.length <= 0) {
538             throw new IllegalArgumentException("No SQL specified in GString: " + gstring);
539         }
540         StringBuffer buffer = new StringBuffer();
541         boolean warned = false;
542         Iterator iter = values.iterator();
543         for (int i = 0; i < strings.length; i++) {
544             String text = strings[i];
545             if (text != null) {
546                 buffer.append(text);
547             }
548             if (iter.hasNext()) {
549                 Object value = iter.next();
550                 if (value != null) {
551                     boolean validBinding = true;
552                     if (i < strings.length - 1) {
553                         String nextText = strings[i + 1];
554                         if ((text.endsWith("\"") || text.endsWith("'")) && (nextText.startsWith("'") || nextText.startsWith("\""))) {
555                             if (!warned) {
556                                 log.warning("In Groovy SQL please do not use quotes around dynamic expressions "
557                                         + "(which start with $) as this means we cannot use a JDBC PreparedStatement "
558                                         + "and so is a security hole. Groovy has worked around your mistake but the security hole is still there. The expression so far is: " + buffer.toString() + "?" + nextText);
559                                 warned = true;
560                             }
561                             buffer.append(value);
562                             iter.remove();
563                             validBinding = false;
564                         }
565                     }
566                     if (validBinding) {
567                         buffer.append("?");
568                     }
569                 }
570                 else {
571                     nulls = true;
572                     buffer.append("?'\"?"); // will replace these with nullish
573                     // values
574                 }
575             }
576         }
577         String sql = buffer.toString();
578         if (nulls) {
579             sql = nullify(sql);
580         }
581         return sql;
582     }
583 
584     /***
585      * replace ?'"? references with NULLish
586      * 
587      * @param sql
588      * @return
589      */
590     protected String nullify(String sql) {
591         /*
592          * Some drivers (Oracle classes12.zip) have difficulty resolving data
593          * type if setObject(null). We will modify the query to pass 'null', 'is
594          * null', and 'is not null'
595          */
596         //could be more efficient by compiling expressions in advance.
597         int firstWhere = findWhereKeyword(sql);
598         if (firstWhere >= 0) {
599             Pattern[] patterns = { Pattern.compile("(?is)^(.{" + firstWhere + "}.*?)!=//s{0,1}(//s*)//?'\"//?(.*)"),
600                     Pattern.compile("(?is)^(.{" + firstWhere + "}.*?)<>//s{0,1}(//s*)//?'\"//?(.*)"),
601                     Pattern.compile("(?is)^(.{" + firstWhere + "}.*?[^<>])=//s{0,1}(//s*)//?'\"//?(.*)"), };
602             String[] replacements = { "$1 is not $2null$3", "$1 is not $2null$3", "$1 is $2null$3", };
603             for (int i = 0; i < patterns.length; i++) {
604                 Matcher matcher = patterns[i].matcher(sql);
605                 while (matcher.matches()) {
606                     sql = matcher.replaceAll(replacements[i]);
607                     matcher = patterns[i].matcher(sql);
608                 }
609             }
610         }
611         return sql.replaceAll("//?'\"//?", "null");
612     }
613 
614     /***
615      * Find the first 'where' keyword in the sql.
616      * 
617      * @param sql
618      * @return
619      */
620     protected int findWhereKeyword(String sql) {
621         char[] chars = sql.toLowerCase().toCharArray();
622         char[] whereChars = "where".toCharArray();
623         int i = 0;
624         boolean inString = false; //TODO: Cater for comments?
625         boolean noWhere = true;
626         int inWhere = 0;
627         while (i < chars.length && noWhere) {
628             switch (chars[i]) {
629                 case '\'':
630                     if (inString) {
631                         inString = false;
632                     }
633                     else {
634                         inString = true;
635                     }
636                     break;
637                 default:
638                     if (!inString && chars[i] == whereChars[inWhere]) {
639                         inWhere++;
640                         if (inWhere == whereChars.length) {
641                             return i;
642                         }
643                     }
644             }
645             i++;
646         }
647         return -1;
648     }
649 
650     /***
651      * @return extracts the parameters from the expression as a List
652      */
653     protected List getParameters(GString gstring) {
654         Object[] values = gstring.getValues();
655         List answer = new ArrayList(values.length);
656         for (int i = 0; i < values.length; i++) {
657             if (values[i] != null) {
658                 answer.add(values[i]);
659             }
660         }
661         return answer;
662     }
663 
664     /***
665      * Appends the parameters to the given statement
666      */
667     protected void setParameters(List params, PreparedStatement statement) throws SQLException {
668         int i = 1;
669         for (Iterator iter = params.iterator(); iter.hasNext();) {
670             Object value = iter.next();
671             setObject(statement, i++, value);
672         }
673     }
674 
675     /***
676      * Strategy method allowing derived classes to handle types differently such
677      * as for CLOBs etc.
678      */
679     protected void setObject(PreparedStatement statement, int i, Object value) throws SQLException {
680         statement.setObject(i, value);
681     }
682 
683     protected Connection createConnection() throws SQLException {
684         if (dataSource != null) {
685             //Use a doPrivileged here as many different properties need to be
686             // read, and the policy
687             //shouldn't have to list them all.
688             Connection con = null;
689             try {
690                 con = (Connection) AccessController.doPrivileged(new PrivilegedExceptionAction() {
691                     public Object run() throws SQLException {
692                         return dataSource.getConnection();
693                     }
694                 });
695             }
696             catch (PrivilegedActionException pae) {
697                 Exception e = pae.getException();
698                 if (e instanceof SQLException) {
699                     throw (SQLException) e;
700                 }
701                 else {
702                     throw (RuntimeException) e;
703                 }
704             }
705             return con;
706         }
707         else {
708             //System.out.println("createConnection returning: " +
709             // useConnection);
710             return useConnection;
711         }
712     }
713 
714     protected void closeResources(Connection connection, Statement statement, ResultSet results) {
715         if (results != null) {
716             try {
717                 results.close();
718             }
719             catch (SQLException e) {
720                 log.log(Level.SEVERE, "Caught exception closing resultSet: " + e, e);
721             }
722         }
723         closeResources(connection, statement);
724     }
725 
726     protected void closeResources(Connection connection, Statement statement) {
727         if (statement != null) {
728             try {
729                 statement.close();
730             }
731             catch (SQLException e) {
732                 log.log(Level.SEVERE, "Caught exception closing statement: " + e, e);
733             }
734         }
735         if (dataSource != null) {
736             try {
737                 connection.close();
738             }
739             catch (SQLException e) {
740                 log.log(Level.SEVERE, "Caught exception closing connection: " + e, e);
741             }
742         }
743     }
744 
745     private void warnDeprecated() {
746         if (!warned) {
747             warned = true;
748             log.warning("queryEach() is deprecated, please use eachRow() instead");
749         }
750     }
751 
752     public void commit() {
753         try {
754             this.useConnection.commit();
755         }
756         catch (SQLException e) {
757             log.log(Level.SEVERE, "Caught exception commiting connection: " + e, e);
758         }
759     }
760 
761     public void rollback() {
762         try {
763             this.useConnection.rollback();
764         }
765         catch (SQLException e) {
766             log.log(Level.SEVERE, "Caught exception rollbacking connection: " + e, e);
767         }
768     }
769 
770     /***
771      * @return Returns the updateCount.
772      */
773     public int getUpdateCount() {
774         return updateCount;
775     }
776 
777     /***
778      * If this instance was created with a single Connection then the connection
779      * is returned. Otherwise if this instance was created with a DataSource
780      * then this method returns null
781      * 
782      * @return the connection wired into this object, or null if this object
783      *         uses a DataSource
784      */
785     public Connection getConnection() {
786         return useConnection;
787     }
788 }