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.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: 4066 $
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
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
172
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
182 try {
183 Sql.class.getClassLoader().loadClass(driverClassName);
184 }
185 catch (ClassNotFoundException e3) {
186 throw e;
187 }
188 }
189 }
190 }
191
192 public static final OutParameter ARRAY = new OutParameter(){ public int getType() { return Types.ARRAY; }};
193 public static final OutParameter BIGINT = new OutParameter(){ public int getType() { return Types.BIGINT; }};
194 public static final OutParameter BINARY = new OutParameter(){ public int getType() { return Types.BINARY; }};
195 public static final OutParameter BIT = new OutParameter(){ public int getType() { return Types.BIT; }};
196 public static final OutParameter BLOB = new OutParameter(){ public int getType() { return Types.BLOB; }};
197 public static final OutParameter BOOLEAN = new OutParameter(){ public int getType() { return Types.BOOLEAN; }};
198 public static final OutParameter CHAR = new OutParameter(){ public int getType() { return Types.CHAR; }};
199 public static final OutParameter CLOB = new OutParameter(){ public int getType() { return Types.CLOB; }};
200 public static final OutParameter DATALINK = new OutParameter(){ public int getType() { return Types.DATALINK; }};
201 public static final OutParameter DATE = new OutParameter(){ public int getType() { return Types.DATE; }};
202 public static final OutParameter DECIMAL = new OutParameter(){ public int getType() { return Types.DECIMAL; }};
203 public static final OutParameter DISTINCT = new OutParameter(){ public int getType() { return Types.DISTINCT; }};
204 public static final OutParameter DOUBLE = new OutParameter(){ public int getType() { return Types.DOUBLE; }};
205 public static final OutParameter FLOAT = new OutParameter(){ public int getType() { return Types.FLOAT; }};
206 public static final OutParameter INTEGER = new OutParameter(){ public int getType() { return Types.INTEGER; }};
207 public static final OutParameter JAVA_OBJECT = new OutParameter(){ public int getType() { return Types.JAVA_OBJECT; }};
208 public static final OutParameter LONGVARBINARY = new OutParameter(){ public int getType() { return Types.LONGVARBINARY; }};
209 public static final OutParameter LONGVARCHAR = new OutParameter(){ public int getType() { return Types.LONGVARCHAR; }};
210 public static final OutParameter NULL = new OutParameter(){ public int getType() { return Types.NULL; }};
211 public static final OutParameter NUMERIC = new OutParameter(){ public int getType() { return Types.NUMERIC; }};
212 public static final OutParameter OTHER = new OutParameter(){ public int getType() { return Types.OTHER; }};
213 public static final OutParameter REAL = new OutParameter(){ public int getType() { return Types.REAL; }};
214 public static final OutParameter REF = new OutParameter(){ public int getType() { return Types.REF; }};
215 public static final OutParameter SMALLINT = new OutParameter(){ public int getType() { return Types.SMALLINT; }};
216 public static final OutParameter STRUCT = new OutParameter(){ public int getType() { return Types.STRUCT; }};
217 public static final OutParameter TIME = new OutParameter(){ public int getType() { return Types.TIME; }};
218 public static final OutParameter TIMESTAMP = new OutParameter(){ public int getType() { return Types.TIMESTAMP; }};
219 public static final OutParameter TINYINT = new OutParameter(){ public int getType() { return Types.TINYINT; }};
220 public static final OutParameter VARBINARY = new OutParameter(){ public int getType() { return Types.VARBINARY; }};
221 public static final OutParameter VARCHAR = new OutParameter(){ public int getType() { return Types.VARCHAR; }};
222
223 public static InParameter ARRAY(Object value) { return in(Types.ARRAY, value); }
224 public static InParameter BIGINT(Object value) { return in(Types.BIGINT, value); }
225 public static InParameter BINARY(Object value) { return in(Types.BINARY, value); }
226 public static InParameter BIT(Object value) { return in(Types.BIT, value); }
227 public static InParameter BLOB(Object value) { return in(Types.BLOB, value); }
228 public static InParameter BOOLEAN(Object value) { return in(Types.BOOLEAN, value); }
229 public static InParameter CHAR(Object value) { return in(Types.CHAR, value); }
230 public static InParameter CLOB(Object value) { return in(Types.CLOB, value); }
231 public static InParameter DATALINK(Object value) { return in(Types.DATALINK, value); }
232 public static InParameter DATE(Object value) { return in(Types.DATE, value); }
233 public static InParameter DECIMAL(Object value) { return in(Types.DECIMAL, value); }
234 public static InParameter DISTINCT(Object value) { return in(Types.DISTINCT, value); }
235 public static InParameter DOUBLE(Object value) { return in(Types.DOUBLE, value); }
236 public static InParameter FLOAT(Object value) { return in(Types.FLOAT, value); }
237 public static InParameter INTEGER(Object value) { return in(Types.INTEGER, value); }
238 public static InParameter JAVA_OBJECT(Object value) { return in(Types.JAVA_OBJECT, value); }
239 public static InParameter LONGVARBINARY(Object value) { return in(Types.LONGVARBINARY, value); }
240 public static InParameter LONGVARCHAR(Object value) { return in(Types.LONGVARCHAR, value); }
241 public static InParameter NULL(Object value) { return in(Types.NULL, value); }
242 public static InParameter NUMERIC(Object value) { return in(Types.NUMERIC, value); }
243 public static InParameter OTHER(Object value) { return in(Types.OTHER, value); }
244 public static InParameter REAL(Object value) { return in(Types.REAL, value); }
245 public static InParameter REF(Object value) { return in(Types.REF, value); }
246 public static InParameter SMALLINT(Object value) { return in(Types.SMALLINT, value); }
247 public static InParameter STRUCT(Object value) { return in(Types.STRUCT, value); }
248 public static InParameter TIME(Object value) { return in(Types.TIME, value); }
249 public static InParameter TIMESTAMP(Object value) { return in(Types.TIMESTAMP, value); }
250 public static InParameter TINYINT(Object value) { return in(Types.TINYINT, value); }
251 public static InParameter VARBINARY(Object value) { return in(Types.VARBINARY, value); }
252 public static InParameter VARCHAR(Object value) { return in(Types.VARCHAR, value); }
253
254 /***
255 * Create a new InParameter
256 * @param type the JDBC data type
257 * @param value the object value
258 * @return an InParameter
259 */
260 public static InParameter in(final int type, final Object value) {
261 return new InParameter() {
262 public int getType() {
263 return type;
264 }
265 public Object getValue() {
266 return value;
267 }
268 };
269 }
270
271 /***
272 * Create a new OutParameter
273 * @param type the JDBC data type.
274 * @return an OutParameter
275 */
276 public static OutParameter out(final int type){
277 return new OutParameter(){
278 public int getType() {
279 return type;
280 }
281 };
282 }
283
284 /***
285 * Create an inout parameter using this in parameter.
286 * @param in
287 */
288 public static InOutParameter inout(final InParameter in){
289 return new InOutParameter(){
290 public int getType() {
291 return in.getType();
292 }
293 public Object getValue() {
294 return in.getValue();
295 }
296 };
297 }
298
299 /***
300 * Create a new ResultSetOutParameter
301 * @param type the JDBC data type.
302 * @return a ResultSetOutParameter
303 */
304 public static ResultSetOutParameter resultSet(final int type){
305 return new ResultSetOutParameter(){
306 public int getType() {
307 return type;
308 }
309 };
310 }
311
312 /***
313 * Creates a variable to be expanded in the Sql string rather
314 * than representing an sql parameter.
315 * @param object
316 */
317 public static ExpandedVariable expand(final Object object){
318 return new ExpandedVariable(){
319 public Object getObject() {
320 return object;
321 }};
322 }
323
324 /***
325 * Constructs an SQL instance using the given DataSource. Each operation
326 * will use a Connection from the DataSource pool and close it when the
327 * operation is completed putting it back into the pool.
328 *
329 * @param dataSource
330 */
331 public Sql(DataSource dataSource) {
332 this.dataSource = dataSource;
333 }
334
335 /***
336 * Construts an SQL instance using the given Connection. It is the callers
337 * responsibility to close the Connection after the Sql instance has been
338 * used. You can do this on the connection object directly or by calling the
339 * {@link java.sql.Connection#close()} method.
340 *
341 * @param connection
342 */
343 public Sql(Connection connection) {
344 if (connection == null) {
345 throw new NullPointerException("Must specify a non-null Connection");
346 }
347 this.useConnection = connection;
348 }
349
350 public Sql(Sql parent) {
351 this.dataSource = parent.dataSource;
352 this.useConnection = parent.useConnection;
353 }
354
355 public DataSet dataSet(String table) {
356 return new DataSet(this, table);
357 }
358
359 public DataSet dataSet(Class type) {
360 return new DataSet(this, type);
361 }
362
363 /***
364 * Performs the given SQL query calling the closure with the result set
365 */
366 public void query(String sql, Closure closure) throws SQLException {
367 Connection connection = createConnection();
368 Statement statement = connection.createStatement();
369 configure(statement);
370 ResultSet results = null;
371 try {
372 log.fine(sql);
373 results = statement.executeQuery(sql);
374 closure.call(results);
375 }
376 catch (SQLException e) {
377 log.log(Level.FINE, "Failed to execute: " + sql, e);
378 throw e;
379 }
380 finally {
381 closeResources(connection, statement, results);
382 }
383 }
384
385 /***
386 * Performs the given SQL query with parameters calling the closure with the
387 * result set
388 */
389 public void query(String sql, List params, Closure closure) throws SQLException {
390 Connection connection = createConnection();
391 PreparedStatement statement = null;
392 ResultSet results = null;
393 try {
394 log.fine(sql);
395 statement = connection.prepareStatement(sql);
396 setParameters(params, statement);
397 configure(statement);
398 results = statement.executeQuery();
399 closure.call(results);
400 }
401 catch (SQLException e) {
402 log.log(Level.FINE, "Failed to execute: " + sql, e);
403 throw e;
404 }
405 finally {
406 closeResources(connection, statement, results);
407 }
408 }
409
410 /***
411 * Performs the given SQL query calling the closure with the result set
412 */
413 public void query(GString gstring, Closure closure) throws SQLException {
414 List params = getParameters(gstring);
415 String sql = asSql(gstring, params);
416 query(sql, params, closure);
417 }
418
419 /***
420 * @deprecated please use eachRow instead
421 */
422 public void queryEach(String sql, Closure closure) throws SQLException {
423 warnDeprecated();
424 eachRow(sql, closure);
425 }
426
427 /***
428 * Performs the given SQL query calling the closure with each row of the
429 * result set
430 */
431 public void eachRow(String sql, Closure closure) throws SQLException {
432 Connection connection = createConnection();
433 Statement statement = connection.createStatement();
434 configure(statement);
435 ResultSet results = null;
436 try {
437 log.fine(sql);
438 results = statement.executeQuery(sql);
439
440 GroovyResultSet groovyRS = new GroovyResultSet(results);
441 while (groovyRS.next()) {
442 closure.call(groovyRS);
443 }
444 }
445 catch (SQLException e) {
446 log.log(Level.FINE, "Failed to execute: " + sql, e);
447 throw e;
448 }
449 finally {
450 closeResources(connection, statement, results);
451 }
452 }
453
454 /***
455 * @deprecated please use eachRow instead
456 */
457 public void queryEach(String sql, List params, Closure closure) throws SQLException {
458 warnDeprecated();
459 eachRow(sql, params, closure);
460 }
461
462 /***
463 * Performs the given SQL query calling the closure with the result set
464 */
465 public void eachRow(String sql, List params, Closure closure) throws SQLException {
466 Connection connection = createConnection();
467 PreparedStatement statement = null;
468 ResultSet results = null;
469 try {
470 log.fine(sql);
471 statement = connection.prepareStatement(sql);
472 setParameters(params, statement);
473 configure(statement);
474 results = statement.executeQuery();
475
476 GroovyResultSet groovyRS = new GroovyResultSet(results);
477 while (groovyRS.next()) {
478 closure.call(groovyRS);
479 }
480 }
481 catch (SQLException e) {
482 log.log(Level.FINE, "Failed to execute: " + sql, e);
483 throw e;
484 }
485 finally {
486 closeResources(connection, statement, results);
487 }
488 }
489
490 /***
491 * Performs the given SQL query calling the closure with the result set
492 */
493 public void eachRow(GString gstring, Closure closure) throws SQLException {
494 List params = getParameters(gstring);
495 String sql = asSql(gstring, params);
496 eachRow(sql, params, closure);
497 }
498
499 /***
500 * @deprecated please use eachRow instead
501 */
502 public void queryEach(GString gstring, Closure closure) throws SQLException {
503 warnDeprecated();
504 eachRow(gstring, closure);
505 }
506
507 /***
508 * Performs the given SQL query and return the rows of the result set
509 */
510 public List rows(String sql) throws SQLException {
511 List results = new ArrayList();
512 Connection connection = createConnection();
513 Statement statement = connection.createStatement();
514 configure(statement);
515 ResultSet rs = null;
516 try {
517 log.fine(sql);
518 rs = statement.executeQuery(sql);
519 while (rs.next()) {
520 ResultSetMetaData metadata = rs.getMetaData();
521 LinkedHashMap lhm = new LinkedHashMap(metadata.getColumnCount(),1,true);
522 for(int i=1 ; i<=metadata.getColumnCount() ; i++) {
523 lhm.put(metadata.getColumnName(i),rs.getObject(i));
524 }
525 GroovyRowResult row = new GroovyRowResult(lhm);
526 results.add(row);
527 }
528 return(results);
529 }
530 catch (SQLException e) {
531 log.log(Level.FINE, "Failed to execute: " + sql, e);
532 throw e;
533 }
534 finally {
535 closeResources(connection, statement, rs);
536 }
537 }
538
539 /***
540 * Performs the given SQL query and return the first row of the result set
541 */
542 public Object firstRow(String sql) throws SQLException {
543 List rows = rows(sql);
544 if (rows.isEmpty()) return null;
545 return(rows.get(0));
546 }
547
548 /***
549 * Performs the given SQL query with the list of params and return
550 * the rows of the result set
551 */
552 public List rows(String sql, List params) throws SQLException {
553 List results = new ArrayList();
554 Connection connection = createConnection();
555 PreparedStatement statement = null;
556 ResultSet rs = null;
557 try {
558 log.fine(sql);
559 statement = connection.prepareStatement(sql);
560 setParameters(params, statement);
561 configure(statement);
562 rs = statement.executeQuery();
563 while (rs.next()) {
564 ResultSetMetaData metadata = rs.getMetaData();
565 LinkedHashMap lhm = new LinkedHashMap(metadata.getColumnCount(),1,true);
566 for(int i=1 ; i<=metadata.getColumnCount() ; i++) {
567 lhm.put(metadata.getColumnName(i),rs.getObject(i));
568 }
569 GroovyRowResult row = new GroovyRowResult(lhm);
570 results.add(row);
571 }
572 return(results);
573 }
574 catch (SQLException e) {
575 log.log(Level.FINE, "Failed to execute: " + sql, e);
576 throw e;
577 }
578 finally {
579 closeResources(connection, statement, rs);
580 }
581 }
582
583 /***
584 * Performs the given SQL query with the list of params and return
585 * the first row of the result set
586 */
587 public Object firstRow(String sql, List params) throws SQLException {
588 List rows = rows(sql, params);
589 if (rows.isEmpty()) return null;
590 return rows.get(0);
591 }
592
593 /***
594 * Executes the given piece of SQL
595 */
596 public boolean execute(String sql) throws SQLException {
597 Connection connection = createConnection();
598 Statement statement = null;
599 try {
600 log.fine(sql);
601 statement = connection.createStatement();
602 configure(statement);
603 boolean isResultSet = statement.execute(sql);
604 this.updateCount = statement.getUpdateCount();
605 return isResultSet;
606 }
607 catch (SQLException e) {
608 log.log(Level.FINE, "Failed to execute: " + sql, e);
609 throw e;
610 }
611 finally {
612 closeResources(connection, statement);
613 }
614 }
615
616 /***
617 * Executes the given SQL update
618 *
619 * @return the number of rows updated
620 */
621 public int executeUpdate(String sql) throws SQLException {
622 Connection connection = createConnection();
623 Statement statement = null;
624 try {
625 log.fine(sql);
626 statement = connection.createStatement();
627 configure(statement);
628 this.updateCount = statement.executeUpdate(sql);
629 return this.updateCount;
630 }
631 catch (SQLException e) {
632 log.log(Level.FINE, "Failed to execute: " + sql, e);
633 throw e;
634 }
635 finally {
636 closeResources(connection, statement);
637 }
638 }
639
640 /***
641 * Executes the given SQL statement. See {@link #executeInsert(GString)}
642 * for more details.
643 * @param sql The SQL statement to execute.
644 * @return A list of the auto-generated column values for each
645 * inserted row.
646 */
647 public List executeInsert(String sql) throws SQLException {
648 Connection connection = createConnection();
649 Statement statement = null;
650 try {
651 log.fine(sql);
652 statement = connection.createStatement();
653 configure(statement);
654 boolean hasResultSet = statement.execute(sql, Statement.RETURN_GENERATED_KEYS);
655
656
657
658 List autoKeys = new ArrayList();
659 ResultSet keys = statement.getGeneratedKeys();
660 int count = keys.getMetaData().getColumnCount();
661
662
663 while (keys.next()) {
664 List rowKeys = new ArrayList(count);
665 for (int i = 1; i <= count; i++) {
666 rowKeys.add(keys.getObject(i));
667 }
668
669 autoKeys.add(rowKeys);
670 }
671
672
673
674
675 this.updateCount = statement.getUpdateCount();
676 return autoKeys;
677 }
678 catch (SQLException e) {
679 log.log(Level.FINE, "Failed to execute: " + sql, e);
680 throw e;
681 }
682 finally {
683 closeResources(connection, statement);
684 }
685 }
686
687 /***
688 * Executes the given piece of SQL with parameters
689 */
690 public boolean execute(String sql, List params) throws SQLException {
691 Connection connection = createConnection();
692 PreparedStatement statement = null;
693 try {
694 log.fine(sql);
695 statement = connection.prepareStatement(sql);
696 setParameters(params, statement);
697 configure(statement);
698 boolean isResultSet = statement.execute();
699 this.updateCount = statement.getUpdateCount();
700 return isResultSet;
701 }
702 catch (SQLException e) {
703 log.log(Level.FINE, "Failed to execute: " + sql, e);
704 throw e;
705 }
706 finally {
707 closeResources(connection, statement);
708 }
709 }
710
711 /***
712 * Executes the given SQL update with parameters
713 *
714 * @return the number of rows updated
715 */
716 public int executeUpdate(String sql, List params) throws SQLException {
717 Connection connection = createConnection();
718 PreparedStatement statement = null;
719 try {
720 log.fine(sql);
721 statement = connection.prepareStatement(sql);
722 setParameters(params, statement);
723 configure(statement);
724 this.updateCount = statement.executeUpdate();
725 return this.updateCount;
726 }
727 catch (SQLException e) {
728 log.log(Level.FINE, "Failed to execute: " + sql, e);
729 throw e;
730 }
731 finally {
732 closeResources(connection, statement);
733 }
734 }
735
736 /***
737 * Executes the given SQL statement with a particular list of
738 * parameter values. See {@link #executeInsert(GString)} for
739 * more details.
740 * @param sql The SQL statement to execute.
741 * @param params The parameter values that will be substituted
742 * into the SQL statement's parameter slots.
743 * @return A list of the auto-generated column values for each
744 * inserted row.
745 */
746 public List executeInsert(String sql, List params) throws SQLException {
747
748 Connection connection = createConnection();
749 PreparedStatement statement = null;
750 try {
751 log.fine(sql);
752
753
754 statement = connection.prepareStatement(sql);
755 setParameters(params, statement);
756 configure(statement);
757 boolean hasResultSet = statement.execute(sql, Statement.RETURN_GENERATED_KEYS);
758
759
760
761 List autoKeys = new ArrayList();
762 ResultSet keys = statement.getGeneratedKeys();
763 int count = keys.getMetaData().getColumnCount();
764
765
766 while (keys.next()) {
767 List rowKeys = new ArrayList(count);
768 for (int i = 1; i <= count; i++) {
769 rowKeys.add(keys.getObject(i));
770 }
771
772 autoKeys.add(rowKeys);
773 }
774
775
776
777
778 this.updateCount = statement.getUpdateCount();
779 return autoKeys;
780 }
781 catch (SQLException e) {
782 log.log(Level.FINE, "Failed to execute: " + sql, e);
783 throw e;
784 }
785 finally {
786 closeResources(connection, statement);
787 }
788 }
789
790 /***
791 * Executes the given SQL with embedded expressions inside
792 */
793 public boolean execute(GString gstring) throws SQLException {
794 List params = getParameters(gstring);
795 String sql = asSql(gstring, params);
796 return execute(sql, params);
797 }
798
799 /***
800 * Executes the given SQL update with embedded expressions inside
801 *
802 * @return the number of rows updated
803 */
804 public int executeUpdate(GString gstring) throws SQLException {
805 List params = getParameters(gstring);
806 String sql = asSql(gstring, params);
807 return executeUpdate(sql, params);
808 }
809
810 /***
811 * <p>Executes the given SQL with embedded expressions inside, and
812 * returns the values of any auto-generated colums, such as an
813 * autoincrement ID field. These values can be accessed using
814 * array notation. For example, to return the second auto-generated
815 * column value of the third row, use <code>keys[3][1]</code>. The
816 * method is designed to be used with SQL INSERT statements, but is
817 * not limited to them.</p>
818 * <p>The standard use for this method is when a table has an
819 * autoincrement ID column and you want to know what the ID is for
820 * a newly inserted row. In this example, we insert a single row
821 * into a table in which the first column contains the autoincrement
822 * ID:</p>
823 * <pre>
824 * def sql = Sql.newInstance("jdbc:mysql://localhost:3306/groovy",
825 * "user",
826 * "password",
827 * "com.mysql.jdbc.Driver")
828 *
829 * def keys = sql.insert("insert into test_table (INT_DATA, STRING_DATA) "
830 * + "VALUES (1, 'Key Largo')")
831 *
832 * def id = keys[0][0]
833 *
834 * // 'id' now contains the value of the new row's ID column.
835 * // It can be used to update an object representation's
836 * // id attribute for example.
837 * ...
838 * </pre>
839 * @return A list of column values representing each row's
840 * auto-generated keys.
841 */
842 public List executeInsert(GString gstring) throws SQLException {
843 List params = getParameters(gstring);
844 String sql = asSql(gstring, params);
845 return executeInsert(sql, params);
846 }
847
848 /***
849 * Performs a stored procedure call
850 */
851 public int call(String sql) throws Exception {
852 return call(sql, Collections.EMPTY_LIST);
853 }
854
855 /***
856 * Performs a stored procedure call with the given parameters
857 */
858 public int call(String sql, List params) throws Exception {
859 Connection connection = createConnection();
860 CallableStatement statement = connection.prepareCall(sql);
861 try {
862 log.fine(sql);
863 setParameters(params, statement);
864 configure(statement);
865 return statement.executeUpdate();
866 }
867 catch (SQLException e) {
868 log.log(Level.FINE, "Failed to execute: " + sql, e);
869 throw e;
870 }
871 finally {
872 closeResources(connection, statement);
873 }
874 }
875
876 /***
877 * Performs a stored procedure call with the given parameters. The closure
878 * is called once with all the out parameters.
879 */
880 public void call(String sql, List params, Closure closure) throws Exception {
881 Connection connection = createConnection();
882 CallableStatement statement = connection.prepareCall(sql);
883 try {
884 log.fine(sql);
885 setParameters(params, statement);
886 statement.execute();
887 List results = new ArrayList();
888 int indx = 0;
889 int inouts = 0;
890 for (Iterator iter = params.iterator(); iter.hasNext();) {
891 Object value = iter.next();
892 if(value instanceof OutParameter){
893 if(value instanceof ResultSetOutParameter){
894 results.add(new CallResultSet(statement,indx));
895 }else{
896 Object o = statement.getObject(indx+1);
897 if(o instanceof ResultSet){
898 results.add(new GroovyResultSet((ResultSet)o));
899 }else{
900 results.add(o);
901 }
902 }
903 inouts++;
904 }
905 indx++;
906 }
907 closure.call(results.toArray(new Object[inouts]));
908 } catch (SQLException e) {
909 log.log(Level.WARNING, "Failed to execute: " + sql, e);
910 throw e;
911 } finally {
912 closeResources(connection, statement);
913 }
914 }
915
916 /***
917 * Performs a stored procedure call with the given parameters
918 */
919 public int call(GString gstring) throws Exception {
920 List params = getParameters(gstring);
921 String sql = asSql(gstring, params);
922 return call(sql, params);
923 }
924
925
926 /***
927 * Performs a stored procedure call with the given parameters,
928 * calling the closure once with all result objects.
929 */
930 public void call(GString gstring, Closure closure) throws Exception {
931 List params = getParameters(gstring);
932 String sql = asSql(gstring,params);
933 call(sql, params,closure);
934 }
935
936 /***
937 * If this SQL object was created with a Connection then this method closes
938 * the connection. If this SQL object was created from a DataSource then
939 * this method does nothing.
940 *
941 * @throws SQLException
942 */
943 public void close() throws SQLException {
944 if (useConnection != null) {
945 useConnection.close();
946 }
947 }
948
949 public DataSource getDataSource() {
950 return dataSource;
951 }
952
953
954 public void commit() {
955 try {
956 this.useConnection.commit();
957 }
958 catch (SQLException e) {
959 log.log(Level.SEVERE, "Caught exception commiting connection: " + e, e);
960 }
961 }
962
963 public void rollback() {
964 try {
965 this.useConnection.rollback();
966 }
967 catch (SQLException e) {
968 log.log(Level.SEVERE, "Caught exception rollbacking connection: " + e, e);
969 }
970 }
971
972 /***
973 * @return Returns the updateCount.
974 */
975 public int getUpdateCount() {
976 return updateCount;
977 }
978
979 /***
980 * If this instance was created with a single Connection then the connection
981 * is returned. Otherwise if this instance was created with a DataSource
982 * then this method returns null
983 *
984 * @return the connection wired into this object, or null if this object
985 * uses a DataSource
986 */
987 public Connection getConnection() {
988 return useConnection;
989 }
990
991
992 /***
993 * Allows a closure to be passed in to configure the JDBC statements before they are executed
994 * to do things like set the query size etc.
995 *
996 * @param configureStatement
997 */
998 public void withStatement(Closure configureStatement) {
999 this.configureStatement = configureStatement;
1000 }
1001
1002
1003
1004
1005 /***
1006 * @return the SQL version of the given query using ? instead of any
1007 * parameter
1008 */
1009 protected String asSql(GString gstring, List values) {
1010 String[] strings = gstring.getStrings();
1011 if (strings.length <= 0) {
1012 throw new IllegalArgumentException("No SQL specified in GString: " + gstring);
1013 }
1014 boolean nulls = false;
1015 StringBuffer buffer = new StringBuffer();
1016 boolean warned = false;
1017 Iterator iter = values.iterator();
1018 for (int i = 0; i < strings.length; i++) {
1019 String text = strings[i];
1020 if (text != null) {
1021 buffer.append(text);
1022 }
1023 if (iter.hasNext()) {
1024 Object value = iter.next();
1025 if (value != null) {
1026 if(value instanceof ExpandedVariable){
1027 buffer.append(((ExpandedVariable)value).getObject());
1028 iter.remove();
1029 }else{
1030 boolean validBinding = true;
1031 if (i < strings.length - 1) {
1032 String nextText = strings[i + 1];
1033 if ((text.endsWith("\"") || text.endsWith("'")) && (nextText.startsWith("'") || nextText.startsWith("\""))) {
1034 if (!warned) {
1035 log.warning("In Groovy SQL please do not use quotes around dynamic expressions " +
1036 "(which start with $) as this means we cannot use a JDBC PreparedStatement " +
1037 "and so is a security hole. Groovy has worked around your mistake but the security hole is still there. " +
1038 "The expression so far is: " + buffer.toString() + "?" + nextText);
1039 warned = true;
1040 }
1041 buffer.append(value);
1042 iter.remove();
1043 validBinding = false;
1044 }
1045 }
1046 if (validBinding) {
1047 buffer.append("?");
1048 }
1049 }
1050 }
1051 else {
1052 nulls = true;
1053 buffer.append("?'\"?");
1054
1055 }
1056 }
1057 }
1058 String sql = buffer.toString();
1059 if (nulls) {
1060 sql = nullify(sql);
1061 }
1062 return sql;
1063 }
1064
1065 /***
1066 * replace ?'"? references with NULLish
1067 *
1068 * @param sql
1069 */
1070 protected String nullify(String sql) {
1071
1072
1073
1074
1075
1076
1077 int firstWhere = findWhereKeyword(sql);
1078 if (firstWhere >= 0) {
1079 Pattern[] patterns = { Pattern.compile("(?is)^(.{" + firstWhere + "}.*?)!=//s{0,1}(//s*)//?'\"//?(.*)"),
1080 Pattern.compile("(?is)^(.{" + firstWhere + "}.*?)<>//s{0,1}(//s*)//?'\"//?(.*)"),
1081 Pattern.compile("(?is)^(.{" + firstWhere + "}.*?[^<>])=//s{0,1}(//s*)//?'\"//?(.*)"), };
1082 String[] replacements = { "$1 is not $2null$3", "$1 is not $2null$3", "$1 is $2null$3", };
1083 for (int i = 0; i < patterns.length; i++) {
1084 Matcher matcher = patterns[i].matcher(sql);
1085 while (matcher.matches()) {
1086 sql = matcher.replaceAll(replacements[i]);
1087 matcher = patterns[i].matcher(sql);
1088 }
1089 }
1090 }
1091 return sql.replaceAll("//?'\"//?", "null");
1092 }
1093
1094 /***
1095 * Find the first 'where' keyword in the sql.
1096 *
1097 * @param sql
1098 */
1099 protected int findWhereKeyword(String sql) {
1100 char[] chars = sql.toLowerCase().toCharArray();
1101 char[] whereChars = "where".toCharArray();
1102 int i = 0;
1103 boolean inString = false;
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
1186
1187
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
1209
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 }