Java > Open Source Codes > groovy > sql > Sql


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 }

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags