001 /* 002 * $Id: Sql.java,v 1.19 2005/02/23 22:09:15 glaforge Exp $ 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.util.ArrayList; 051 import java.util.Collections; 052 import java.util.Iterator; 053 import java.util.List; 054 import java.util.LinkedHashMap; 055 import java.util.Properties; 056 import java.util.logging.Level; 057 import java.util.logging.Logger; 058 import java.util.regex.Matcher; 059 import java.util.regex.Pattern; 060 061 import javax.sql.DataSource; 062 063 /** 064 * Represents an extent of objects 065 * 066 * @author Chris Stevenson 067 * @author <a href="mailto:james@coredevelopers.net">James Strachan </a> 068 * @version $Revision: 1.19 $ 069 */ 070 public class Sql { 071 072 protected Logger log = Logger.getLogger(getClass().getName()); 073 074 private DataSource dataSource; 075 076 private Connection useConnection; 077 078 /** lets only warn of using deprecated methods once */ 079 private boolean warned; 080 081 // store the last row count for executeUpdate 082 int updateCount = 0; 083 084 /** allows a closure to be used to configure the statement before its use */ 085 private Closure configureStatement; 086 087 /** 088 * A helper method which creates a new Sql instance from a JDBC connection 089 * URL 090 * 091 * @param url 092 * @return a new Sql instance with a connection 093 */ 094 public static Sql newInstance(String url) throws SQLException { 095 Connection connection = DriverManager.getConnection(url); 096 return new Sql(connection); 097 } 098 099 /** 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 // lets try the thread context class loader first 171 // lets try to use the system class loader 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 // now lets try the classloader which loaded us 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 // Implementation methods 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("?'\"?"); // will replace these with nullish 721 // values 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 * Some drivers (Oracle classes12.zip) have difficulty resolving data 741 * type if setObject(null). We will modify the query to pass 'null', 'is 742 * null', and 'is not null' 743 */ 744 //could be more efficient by compiling expressions in advance. 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; //TODO: Cater for comments? 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 //Use a doPrivileged here as many different properties need to be 834 // read, and the policy 835 //shouldn't have to list them all. 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 //System.out.println("createConnection returning: " + 857 // useConnection); 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 }