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