001 /* 002 * $Id: Sql.java 4066 2006-09-20 17:26:36Z glaforge $ 003 * 004 * Copyright 2003 (C) James Strachan and Bob Mcwhirter. All Rights Reserved. 005 * 006 * Redistribution and use of this software and associated documentation 007 * ("Software"), with or without modification, are permitted provided that the 008 * following conditions are met: 1. Redistributions of source code must retain 009 * copyright statements and notices. Redistributions must also contain a copy 010 * of this document. 2. Redistributions in binary form must reproduce the above 011 * copyright notice, this list of conditions and the following disclaimer in 012 * the documentation and/or other materials provided with the distribution. 3. 013 * The name "groovy" must not be used to endorse or promote products derived 014 * from this Software without prior written permission of The Codehaus. For 015 * written permission, please contact info@codehaus.org. 4. Products derived 016 * from this Software may not be called "groovy" nor may "groovy" appear in 017 * their names without prior written permission of The Codehaus. "groovy" is a 018 * registered trademark of The Codehaus. 5. Due credit should be given to The 019 * Codehaus - http://groovy.codehaus.org/ 020 * 021 * THIS SOFTWARE IS PROVIDED BY THE CODEHAUS AND CONTRIBUTORS ``AS IS'' AND ANY 022 * EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED 023 * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE 024 * DISCLAIMED. IN NO EVENT SHALL THE CODEHAUS OR ITS CONTRIBUTORS BE LIABLE FOR 025 * ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL 026 * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR 027 * SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER 028 * CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT 029 * LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY 030 * OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH 031 * DAMAGE. 032 * 033 */ 034 package groovy.sql; 035 036 import groovy.lang.Closure; 037 import groovy.lang.GString; 038 039 import java.security.AccessController; 040 import java.security.PrivilegedActionException; 041 import java.security.PrivilegedExceptionAction; 042 import java.sql.CallableStatement; 043 import java.sql.Connection; 044 import java.sql.DriverManager; 045 import java.sql.PreparedStatement; 046 import java.sql.ResultSet; 047 import java.sql.ResultSetMetaData; 048 import java.sql.SQLException; 049 import java.sql.Statement; 050 import java.sql.Types; 051 import java.util.ArrayList; 052 import java.util.Collections; 053 import java.util.Iterator; 054 import java.util.List; 055 import java.util.LinkedHashMap; 056 import java.util.Properties; 057 import java.util.logging.Level; 058 import java.util.logging.Logger; 059 import java.util.regex.Matcher; 060 import java.util.regex.Pattern; 061 062 import javax.sql.DataSource; 063 064 /** 065 * Represents an extent of objects 066 * 067 * @author Chris Stevenson 068 * @author <a href="mailto:james@coredevelopers.net">James Strachan </a> 069 * @version $Revision: 4066 $ 070 */ 071 public class Sql { 072 073 protected Logger log = Logger.getLogger(getClass().getName()); 074 075 private DataSource dataSource; 076 077 private Connection useConnection; 078 079 /** lets only warn of using deprecated methods once */ 080 private boolean warned; 081 082 // store the last row count for executeUpdate 083 int updateCount = 0; 084 085 /** allows a closure to be used to configure the statement before its use */ 086 private Closure configureStatement; 087 088 /** 089 * A helper method which creates a new Sql instance from a JDBC connection 090 * URL 091 * 092 * @param url 093 * @return a new Sql instance with a connection 094 */ 095 public static Sql newInstance(String url) throws SQLException { 096 Connection connection = DriverManager.getConnection(url); 097 return new Sql(connection); 098 } 099 100 /** 101 * A helper method which creates a new Sql instance from a JDBC connection 102 * URL 103 * 104 * @param url 105 * @return a new Sql instance with a connection 106 */ 107 public static Sql newInstance(String url, Properties properties) throws SQLException { 108 Connection connection = DriverManager.getConnection(url, properties); 109 return new Sql(connection); 110 } 111 112 /** 113 * A helper method which creates a new Sql instance from a JDBC connection 114 * URL and driver class name 115 * 116 * @param url 117 * @return a new Sql instance with a connection 118 */ 119 public static Sql newInstance(String url, Properties properties, String driverClassName) throws SQLException, ClassNotFoundException { 120 loadDriver(driverClassName); 121 return newInstance(url, properties); 122 } 123 124 /** 125 * A helper method which creates a new Sql instance from a JDBC connection 126 * URL, username and password 127 * 128 * @param url 129 * @return a new Sql instance with a connection 130 */ 131 public static Sql newInstance(String url, String user, String password) throws SQLException { 132 Connection connection = DriverManager.getConnection(url, user, password); 133 return new Sql(connection); 134 } 135 136 /** 137 * A helper method which creates a new Sql instance from a JDBC connection 138 * URL, username, password and driver class name 139 * 140 * @param url 141 * @return a new Sql instance with a connection 142 */ 143 public static Sql newInstance(String url, String user, String password, String driverClassName) throws SQLException, 144 ClassNotFoundException { 145 loadDriver(driverClassName); 146 return newInstance(url, user, password); 147 } 148 149 /** 150 * A helper method which creates a new Sql instance from a JDBC connection 151 * URL and driver class name 152 * 153 * @param url 154 * @param driverClassName 155 * the class name of the driver 156 * @return a new Sql instance with a connection 157 */ 158 public static Sql newInstance(String url, String driverClassName) throws SQLException, ClassNotFoundException { 159 loadDriver(driverClassName); 160 return newInstance(url); 161 } 162 163 /** 164 * Attempts to load the JDBC driver on the thread, current or system class 165 * loaders 166 * 167 * @param driverClassName 168 * @throws ClassNotFoundException 169 */ 170 public static void loadDriver(String driverClassName) throws ClassNotFoundException { 171 // lets try the thread context class loader first 172 // lets try to use the system class loader 173 try { 174 Class.forName(driverClassName); 175 } 176 catch (ClassNotFoundException e) { 177 try { 178 Thread.currentThread().getContextClassLoader().loadClass(driverClassName); 179 } 180 catch (ClassNotFoundException e2) { 181 // now lets try the classloader which loaded us 182 try { 183 Sql.class.getClassLoader().loadClass(driverClassName); 184 } 185 catch (ClassNotFoundException e3) { 186 throw e; 187 } 188 } 189 } 190 } 191 192 public static final OutParameter ARRAY = new OutParameter(){ public int getType() { return Types.ARRAY; }}; 193 public static final OutParameter BIGINT = new OutParameter(){ public int getType() { return Types.BIGINT; }}; 194 public static final OutParameter BINARY = new OutParameter(){ public int getType() { return Types.BINARY; }}; 195 public static final OutParameter BIT = new OutParameter(){ public int getType() { return Types.BIT; }}; 196 public static final OutParameter BLOB = new OutParameter(){ public int getType() { return Types.BLOB; }}; 197 public static final OutParameter BOOLEAN = new OutParameter(){ public int getType() { return Types.BOOLEAN; }}; 198 public static final OutParameter CHAR = new OutParameter(){ public int getType() { return Types.CHAR; }}; 199 public static final OutParameter CLOB = new OutParameter(){ public int getType() { return Types.CLOB; }}; 200 public static final OutParameter DATALINK = new OutParameter(){ public int getType() { return Types.DATALINK; }}; 201 public static final OutParameter DATE = new OutParameter(){ public int getType() { return Types.DATE; }}; 202 public static final OutParameter DECIMAL = new OutParameter(){ public int getType() { return Types.DECIMAL; }}; 203 public static final OutParameter DISTINCT = new OutParameter(){ public int getType() { return Types.DISTINCT; }}; 204 public static final OutParameter DOUBLE = new OutParameter(){ public int getType() { return Types.DOUBLE; }}; 205 public static final OutParameter FLOAT = new OutParameter(){ public int getType() { return Types.FLOAT; }}; 206 public static final OutParameter INTEGER = new OutParameter(){ public int getType() { return Types.INTEGER; }}; 207 public static final OutParameter JAVA_OBJECT = new OutParameter(){ public int getType() { return Types.JAVA_OBJECT; }}; 208 public static final OutParameter LONGVARBINARY = new OutParameter(){ public int getType() { return Types.LONGVARBINARY; }}; 209 public static final OutParameter LONGVARCHAR = new OutParameter(){ public int getType() { return Types.LONGVARCHAR; }}; 210 public static final OutParameter NULL = new OutParameter(){ public int getType() { return Types.NULL; }}; 211 public static final OutParameter NUMERIC = new OutParameter(){ public int getType() { return Types.NUMERIC; }}; 212 public static final OutParameter OTHER = new OutParameter(){ public int getType() { return Types.OTHER; }}; 213 public static final OutParameter REAL = new OutParameter(){ public int getType() { return Types.REAL; }}; 214 public static final OutParameter REF = new OutParameter(){ public int getType() { return Types.REF; }}; 215 public static final OutParameter SMALLINT = new OutParameter(){ public int getType() { return Types.SMALLINT; }}; 216 public static final OutParameter STRUCT = new OutParameter(){ public int getType() { return Types.STRUCT; }}; 217 public static final OutParameter TIME = new OutParameter(){ public int getType() { return Types.TIME; }}; 218 public static final OutParameter TIMESTAMP = new OutParameter(){ public int getType() { return Types.TIMESTAMP; }}; 219 public static final OutParameter TINYINT = new OutParameter(){ public int getType() { return Types.TINYINT; }}; 220 public static final OutParameter VARBINARY = new OutParameter(){ public int getType() { return Types.VARBINARY; }}; 221 public static final OutParameter VARCHAR = new OutParameter(){ public int getType() { return Types.VARCHAR; }}; 222 223 public static InParameter ARRAY(Object value) { return in(Types.ARRAY, value); } 224 public static InParameter BIGINT(Object value) { return in(Types.BIGINT, value); } 225 public static InParameter BINARY(Object value) { return in(Types.BINARY, value); } 226 public static InParameter BIT(Object value) { return in(Types.BIT, value); } 227 public static InParameter BLOB(Object value) { return in(Types.BLOB, value); } 228 public static InParameter BOOLEAN(Object value) { return in(Types.BOOLEAN, value); } 229 public static InParameter CHAR(Object value) { return in(Types.CHAR, value); } 230 public static InParameter CLOB(Object value) { return in(Types.CLOB, value); } 231 public static InParameter DATALINK(Object value) { return in(Types.DATALINK, value); } 232 public static InParameter DATE(Object value) { return in(Types.DATE, value); } 233 public static InParameter DECIMAL(Object value) { return in(Types.DECIMAL, value); } 234 public static InParameter DISTINCT(Object value) { return in(Types.DISTINCT, value); } 235 public static InParameter DOUBLE(Object value) { return in(Types.DOUBLE, value); } 236 public static InParameter FLOAT(Object value) { return in(Types.FLOAT, value); } 237 public static InParameter INTEGER(Object value) { return in(Types.INTEGER, value); } 238 public static InParameter JAVA_OBJECT(Object value) { return in(Types.JAVA_OBJECT, value); } 239 public static InParameter LONGVARBINARY(Object value) { return in(Types.LONGVARBINARY, value); } 240 public static InParameter LONGVARCHAR(Object value) { return in(Types.LONGVARCHAR, value); } 241 public static InParameter NULL(Object value) { return in(Types.NULL, value); } 242 public static InParameter NUMERIC(Object value) { return in(Types.NUMERIC, value); } 243 public static InParameter OTHER(Object value) { return in(Types.OTHER, value); } 244 public static InParameter REAL(Object value) { return in(Types.REAL, value); } 245 public static InParameter REF(Object value) { return in(Types.REF, value); } 246 public static InParameter SMALLINT(Object value) { return in(Types.SMALLINT, value); } 247 public static InParameter STRUCT(Object value) { return in(Types.STRUCT, value); } 248 public static InParameter TIME(Object value) { return in(Types.TIME, value); } 249 public static InParameter TIMESTAMP(Object value) { return in(Types.TIMESTAMP, value); } 250 public static InParameter TINYINT(Object value) { return in(Types.TINYINT, value); } 251 public static InParameter VARBINARY(Object value) { return in(Types.VARBINARY, value); } 252 public static InParameter VARCHAR(Object value) { return in(Types.VARCHAR, value); } 253 254 /** 255 * Create a new InParameter 256 * @param type the JDBC data type 257 * @param value the object value 258 * @return an InParameter 259 */ 260 public static InParameter in(final int type, final Object value) { 261 return new InParameter() { 262 public int getType() { 263 return type; 264 } 265 public Object getValue() { 266 return value; 267 } 268 }; 269 } 270 271 /** 272 * Create a new OutParameter 273 * @param type the JDBC data type. 274 * @return an OutParameter 275 */ 276 public static OutParameter out(final int type){ 277 return new OutParameter(){ 278 public int getType() { 279 return type; 280 } 281 }; 282 } 283 284 /** 285 * Create an inout parameter using this in parameter. 286 * @param in 287 */ 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 // Prepare a list to contain the auto-generated column 657 // values, and then fetch them from the statement. 658 List autoKeys = new ArrayList(); 659 ResultSet keys = statement.getGeneratedKeys(); 660 int count = keys.getMetaData().getColumnCount(); 661 662 // Copy the column values into a list of a list. 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 // Store the update count so that it can be retrieved by 673 // clients, and then return the list of auto-generated 674 // values. 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 // Now send the SQL to the database. 748 Connection connection = createConnection(); 749 PreparedStatement statement = null; 750 try { 751 log.fine(sql); 752 753 // Prepare a statement for the SQL and then execute it. 754 statement = connection.prepareStatement(sql); 755 setParameters(params, statement); 756 configure(statement); 757 boolean hasResultSet = statement.execute(sql, Statement.RETURN_GENERATED_KEYS); 758 759 // Prepare a list to contain the auto-generated column 760 // values, and then fetch them from the statement. 761 List autoKeys = new ArrayList(); 762 ResultSet keys = statement.getGeneratedKeys(); 763 int count = keys.getMetaData().getColumnCount(); 764 765 // Copy the column values into a list of a list. 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 // Store the update count so that it can be retrieved by 776 // clients, and then return the list of auto-generated 777 // values. 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 // Implementation methods 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("?'\"?"); // will replace these with nullish 1054 // values 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 * Some drivers (Oracle classes12.zip) have difficulty resolving data 1073 * type if setObject(null). We will modify the query to pass 'null', 'is 1074 * null', and 'is not null' 1075 */ 1076 //could be more efficient by compiling expressions in advance. 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; //TODO: Cater for comments? 1104 boolean noWhere = true; 1105 int inWhere = 0; 1106 while (i < chars.length && noWhere) { 1107 switch (chars[i]) { 1108 case '\'': 1109 if (inString) { 1110 inString = false; 1111 } 1112 else { 1113 inString = true; 1114 } 1115 break; 1116 default: 1117 if (!inString && chars[i] == whereChars[inWhere]) { 1118 inWhere++; 1119 if (inWhere == whereChars.length) { 1120 return i; 1121 } 1122 } 1123 } 1124 i++; 1125 } 1126 return -1; 1127 } 1128 1129 /** 1130 * @return extracts the parameters from the expression as a List 1131 */ 1132 protected List getParameters(GString gstring) { 1133 Object[] values = gstring.getValues(); 1134 List answer = new ArrayList(values.length); 1135 for (int i = 0; i < values.length; i++) { 1136 if (values[i] != null) { 1137 answer.add(values[i]); 1138 } 1139 } 1140 return answer; 1141 } 1142 1143 /** 1144 * Appends the parameters to the given statement 1145 */ 1146 protected void setParameters(List params, PreparedStatement statement) throws SQLException { 1147 int i = 1; 1148 for (Iterator iter = params.iterator(); iter.hasNext();) { 1149 Object value = iter.next(); 1150 setObject(statement, i++, value); 1151 } 1152 } 1153 1154 /** 1155 * Strategy method allowing derived classes to handle types differently 1156 * such as for CLOBs etc. 1157 */ 1158 protected void setObject(PreparedStatement statement, int i, Object value) 1159 throws SQLException { 1160 if (value instanceof InParameter || value instanceof OutParameter) { 1161 if(value instanceof InParameter){ 1162 InParameter in = (InParameter) value; 1163 Object val = in.getValue(); 1164 if (null == val) { 1165 statement.setNull(i, in.getType()); 1166 } else { 1167 statement.setObject(i, val, in.getType()); 1168 } 1169 } 1170 if(value instanceof OutParameter){ 1171 try{ 1172 OutParameter out = (OutParameter)value; 1173 ((CallableStatement)statement).registerOutParameter(i,out.getType()); 1174 }catch(ClassCastException e){ 1175 throw new SQLException("Cannot register out parameter."); 1176 } 1177 } 1178 } else { 1179 statement.setObject(i, value); 1180 } 1181 } 1182 1183 protected Connection createConnection() throws SQLException { 1184 if (dataSource != null) { 1185 //Use a doPrivileged here as many different properties need to be 1186 // read, and the policy 1187 //shouldn't have to list them all. 1188 Connection con = null; 1189 try { 1190 con = (Connection) AccessController.doPrivileged(new PrivilegedExceptionAction() { 1191 public Object run() throws SQLException { 1192 return dataSource.getConnection(); 1193 } 1194 }); 1195 } 1196 catch (PrivilegedActionException pae) { 1197 Exception e = pae.getException(); 1198 if (e instanceof SQLException) { 1199 throw (SQLException) e; 1200 } 1201 else { 1202 throw (RuntimeException) e; 1203 } 1204 } 1205 return con; 1206 } 1207 else { 1208 //System.out.println("createConnection returning: " + 1209 // useConnection); 1210 return useConnection; 1211 } 1212 } 1213 1214 protected void closeResources(Connection connection, Statement statement, ResultSet results) { 1215 if (results != null) { 1216 try { 1217 results.close(); 1218 } 1219 catch (SQLException e) { 1220 log.log(Level.SEVERE, "Caught exception closing resultSet: " + e, e); 1221 } 1222 } 1223 closeResources(connection, statement); 1224 } 1225 1226 protected void closeResources(Connection connection, Statement statement) { 1227 if (statement != null) { 1228 try { 1229 statement.close(); 1230 } 1231 catch (SQLException e) { 1232 log.log(Level.SEVERE, "Caught exception closing statement: " + e, e); 1233 } 1234 } 1235 if (dataSource != null) { 1236 try { 1237 connection.close(); 1238 } 1239 catch (SQLException e) { 1240 log.log(Level.SEVERE, "Caught exception closing connection: " + e, e); 1241 } 1242 } 1243 } 1244 1245 private void warnDeprecated() { 1246 if (!warned) { 1247 warned = true; 1248 log.warning("queryEach() is deprecated, please use eachRow() instead"); 1249 } 1250 } 1251 1252 /** 1253 * Provides a hook to be able to configure JDBC statements, such as to configure 1254 * 1255 * @param statement 1256 */ 1257 protected void configure(Statement statement) { 1258 if (configureStatement != null) { 1259 configureStatement.call(statement); 1260 } 1261 } 1262 }