Clover coverage report - groovy - 1.0-beta-6
Coverage timestamp: Thu Jul 15 2004 13:18:22 BST
file stats: LOC: 788   Methods: 47
NCLOC: 501   Classes: 1
30 day Evaluation Version distributed via the Maven Jar Repository. Clover is not free. You have 30 days to evaluate it. Please visit http://www.thecortex.net/clover to obtain a licensed version of Clover
 
 Source file Conditionals Statements Methods TOTAL
Sql.java 0% 0% 0% 0%
coverage
 1   
 /*
 2   
  * $Id: Sql.java,v 1.15 2004/07/14 19:20:35 jstrachan Exp $
 3   
  * 
 4   
  * Copyright 2003 (C) James Strachan and Bob Mcwhirter. All Rights Reserved.
 5   
  * 
 6   
  * Redistribution and use of this software and associated documentation
 7   
  * ("Software"), with or without modification, are permitted provided that the
 8   
  * following conditions are met: 1. Redistributions of source code must retain
 9   
  * copyright statements and notices. Redistributions must also contain a copy
 10   
  * of this document. 2. Redistributions in binary form must reproduce the above
 11   
  * copyright notice, this list of conditions and the following disclaimer in
 12   
  * the documentation and/or other materials provided with the distribution. 3.
 13   
  * The name "groovy" must not be used to endorse or promote products derived
 14   
  * from this Software without prior written permission of The Codehaus. For
 15   
  * written permission, please contact info@codehaus.org. 4. Products derived
 16   
  * from this Software may not be called "groovy" nor may "groovy" appear in
 17   
  * their names without prior written permission of The Codehaus. "groovy" is a
 18   
  * registered trademark of The Codehaus. 5. Due credit should be given to The
 19   
  * Codehaus - http://groovy.codehaus.org/
 20   
  * 
 21   
  * THIS SOFTWARE IS PROVIDED BY THE CODEHAUS AND CONTRIBUTORS ``AS IS'' AND ANY
 22   
  * EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
 23   
  * WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
 24   
  * DISCLAIMED. IN NO EVENT SHALL THE CODEHAUS OR ITS CONTRIBUTORS BE LIABLE FOR
 25   
  * ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
 26   
  * DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
 27   
  * SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER
 28   
  * CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
 29   
  * LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY
 30   
  * OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH
 31   
  * DAMAGE.
 32   
  *  
 33   
  */
 34   
 package groovy.sql;
 35   
 
 36   
 import groovy.lang.Closure;
 37   
 import groovy.lang.GString;
 38   
 
 39   
 import java.security.AccessController;
 40   
 import java.security.PrivilegedActionException;
 41   
 import java.security.PrivilegedExceptionAction;
 42   
 import java.sql.CallableStatement;
 43   
 import java.sql.Connection;
 44   
 import java.sql.DriverManager;
 45   
 import java.sql.PreparedStatement;
 46   
 import java.sql.ResultSet;
 47   
 import java.sql.SQLException;
 48   
 import java.sql.Statement;
 49   
 import java.util.ArrayList;
 50   
 import java.util.Collections;
 51   
 import java.util.Iterator;
 52   
 import java.util.List;
 53   
 import java.util.Properties;
 54   
 import java.util.logging.Level;
 55   
 import java.util.logging.Logger;
 56   
 import java.util.regex.Matcher;
 57   
 import java.util.regex.Pattern;
 58   
 
 59   
 import javax.sql.DataSource;
 60   
 
 61   
 /**
 62   
  * Represents an extent of objects
 63   
  * 
 64   
  * @author Chris Stevenson
 65   
  * @author <a href="mailto:james@coredevelopers.net">James Strachan </a>
 66   
  * @version $Revision: 1.15 $
 67   
  */
 68   
 public class Sql {
 69   
 
 70   
     protected Logger log = Logger.getLogger(getClass().getName());
 71   
 
 72   
     private DataSource dataSource;
 73   
 
 74   
     private Connection useConnection;
 75   
 
 76   
     /** lets only warn of using deprecated methods once */
 77   
     private boolean warned;
 78   
 
 79   
     // store the last row count for executeUpdate
 80   
     int updateCount = 0;
 81   
 
 82   
     /**
 83   
      * A helper method which creates a new Sql instance from a JDBC connection
 84   
      * URL
 85   
      * 
 86   
      * @param url
 87   
      * @return a new Sql instance with a connection
 88   
      */
 89  0
     public static Sql newInstance(String url) throws SQLException {
 90  0
         Connection connection = DriverManager.getConnection(url);
 91  0
         return new Sql(connection);
 92   
     }
 93   
 
 94   
     /**
 95   
      * A helper method which creates a new Sql instance from a JDBC connection
 96   
      * URL
 97   
      * 
 98   
      * @param url
 99   
      * @return a new Sql instance with a connection
 100   
      */
 101  0
     public static Sql newInstance(String url, Properties properties) throws SQLException {
 102  0
         Connection connection = DriverManager.getConnection(url, properties);
 103  0
         return new Sql(connection);
 104   
     }
 105   
 
 106   
     /**
 107   
      * A helper method which creates a new Sql instance from a JDBC connection
 108   
      * URL and driver class name
 109   
      * 
 110   
      * @param url
 111   
      * @return a new Sql instance with a connection
 112   
      */
 113  0
     public static Sql newInstance(String url, Properties properties, String driverClassName) throws SQLException, ClassNotFoundException {
 114  0
         loadDriver(driverClassName);
 115  0
         return newInstance(url, properties);
 116   
     }
 117   
 
 118   
     /**
 119   
      * A helper method which creates a new Sql instance from a JDBC connection
 120   
      * URL, username and password
 121   
      * 
 122   
      * @param url
 123   
      * @return a new Sql instance with a connection
 124   
      */
 125  0
     public static Sql newInstance(String url, String user, String password) throws SQLException {
 126  0
         Connection connection = DriverManager.getConnection(url, user, password);
 127  0
         return new Sql(connection);
 128   
     }
 129   
 
 130   
     /**
 131   
      * A helper method which creates a new Sql instance from a JDBC connection
 132   
      * URL, username, password and driver class name
 133   
      * 
 134   
      * @param url
 135   
      * @return a new Sql instance with a connection
 136   
      */
 137  0
     public static Sql newInstance(String url, String user, String password, String driverClassName) throws SQLException,
 138   
             ClassNotFoundException {
 139  0
         loadDriver(driverClassName);
 140  0
         return newInstance(url, user, password);
 141   
     }
 142   
 
 143   
     /**
 144   
      * A helper method which creates a new Sql instance from a JDBC connection
 145   
      * URL and driver class name
 146   
      * 
 147   
      * @param url
 148   
      * @param driverClassName
 149   
      *            the class name of the driver
 150   
      * @return a new Sql instance with a connection
 151   
      */
 152  0
     public static Sql newInstance(String url, String driverClassName) throws SQLException, ClassNotFoundException {
 153  0
         loadDriver(driverClassName);
 154  0
         return newInstance(url);
 155   
     }
 156   
 
 157   
     /**
 158   
      * Attempts to load the JDBC driver on the thread, current or system class
 159   
      * loaders
 160   
      * 
 161   
      * @param driverClassName
 162   
      * @throws ClassNotFoundException
 163   
      */
 164  0
     public static void loadDriver(String driverClassName) throws ClassNotFoundException {
 165   
         // lets try the thread context class loader first
 166   
         // lets try to use the system class loader
 167  0
         try {
 168  0
             Class.forName(driverClassName);
 169   
         }
 170   
         catch (ClassNotFoundException e) {
 171  0
             try {
 172  0
                 Thread.currentThread().getContextClassLoader().loadClass(driverClassName);
 173   
             }
 174   
             catch (ClassNotFoundException e2) {
 175   
                 // now lets try the classloader which loaded us
 176  0
                 try {
 177  0
                     Sql.class.getClassLoader().loadClass(driverClassName);
 178   
                 }
 179   
                 catch (ClassNotFoundException e3) {
 180  0
                     throw e;
 181   
                 }
 182   
             }
 183   
         }
 184   
     }
 185   
 
 186   
     /**
 187   
      * Constructs an SQL instance using the given DataSource. Each operation
 188   
      * will use a Connection from the DataSource pool and close it when the
 189   
      * operation is completed putting it back into the pool.
 190   
      * 
 191   
      * @param dataSource
 192   
      */
 193  0
     public Sql(DataSource dataSource) {
 194  0
         this.dataSource = dataSource;
 195   
     }
 196   
 
 197   
     /**
 198   
      * Construts an SQL instance using the given Connection. It is the callers
 199   
      * responsibility to close the Connection after the Sql instance has been
 200   
      * used. You can do this on the connection object directly or by calling the
 201   
      * {@link close()}method.
 202   
      * 
 203   
      * @param connection
 204   
      */
 205  0
     public Sql(Connection connection) {
 206  0
         if (connection == null) {
 207  0
             throw new NullPointerException("Must specify a non-null Connection");
 208   
         }
 209  0
         this.useConnection = connection;
 210   
     }
 211   
 
 212  0
     public Sql(Sql parent) {
 213  0
         this.dataSource = parent.dataSource;
 214  0
         this.useConnection = parent.useConnection;
 215   
     }
 216   
 
 217  0
     public DataSet dataSet(String table) {
 218  0
         return new DataSet(this, table);
 219   
     }
 220   
 
 221  0
     public DataSet dataSet(Class type) {
 222  0
         return new DataSet(this, type);
 223   
     }
 224   
 
 225   
     /**
 226   
      * Performs the given SQL query calling the closure with the result set
 227   
      */
 228  0
     public void query(String sql, Closure closure) throws SQLException {
 229  0
         Connection connection = createConnection();
 230  0
         Statement statement = connection.createStatement();
 231  0
         ResultSet results = null;
 232  0
         try {
 233  0
             log.fine(sql);
 234  0
             results = statement.executeQuery(sql);
 235  0
             closure.call(results);
 236   
         }
 237   
         catch (SQLException e) {
 238  0
             log.log(Level.WARNING, "Failed to execute: " + sql, e);
 239  0
             throw e;
 240   
         }
 241   
         finally {
 242  0
             closeResources(connection, statement, results);
 243   
         }
 244   
     }
 245   
 
 246   
     /**
 247   
      * Performs the given SQL query with parameters calling the closure with the
 248   
      * result set
 249   
      */
 250  0
     public void query(String sql, List params, Closure closure) throws SQLException {
 251  0
         Connection connection = createConnection();
 252  0
         PreparedStatement statement = null;
 253  0
         ResultSet results = null;
 254  0
         try {
 255  0
             log.fine(sql);
 256  0
             statement = connection.prepareStatement(sql);
 257  0
             setParameters(params, statement);
 258  0
             results = statement.executeQuery();
 259  0
             closure.call(results);
 260   
         }
 261   
         catch (SQLException e) {
 262  0
             log.log(Level.WARNING, "Failed to execute: " + sql, e);
 263  0
             throw e;
 264   
         }
 265   
         finally {
 266  0
             closeResources(connection, statement, results);
 267   
         }
 268   
     }
 269   
 
 270   
     /**
 271   
      * Performs the given SQL query calling the closure with the result set
 272   
      */
 273  0
     public void query(GString gstring, Closure closure) throws SQLException {
 274  0
         List params = getParameters(gstring);
 275  0
         String sql = asSql(gstring, params);
 276  0
         query(sql, params, closure);
 277   
     }
 278   
 
 279   
     /**
 280   
      * @deprecated please use eachRow instead
 281   
      */
 282  0
     public void queryEach(String sql, Closure closure) throws SQLException {
 283  0
         warnDeprecated();
 284  0
         eachRow(sql, closure);
 285   
     }
 286   
 
 287   
     /**
 288   
      * Performs the given SQL query calling the closure with each row of the
 289   
      * result set
 290   
      */
 291  0
     public void eachRow(String sql, Closure closure) throws SQLException {
 292  0
         Connection connection = createConnection();
 293  0
         Statement statement = connection.createStatement();
 294  0
         ResultSet results = null;
 295  0
         try {
 296  0
             log.fine(sql);
 297  0
             results = statement.executeQuery(sql);
 298   
 
 299  0
             GroovyResultSet groovyRS = new GroovyResultSet(results);
 300  0
             while (groovyRS.next()) {
 301  0
                 closure.call(groovyRS);
 302   
             }
 303   
         }
 304   
         catch (SQLException e) {
 305  0
             log.log(Level.WARNING, "Failed to execute: " + sql, e);
 306  0
             throw e;
 307   
         }
 308   
         finally {
 309  0
             closeResources(connection, statement, results);
 310   
         }
 311   
     }
 312   
 
 313   
     /**
 314   
      * @deprecated please use eachRow instead
 315   
      */
 316  0
     public void queryEach(String sql, List params, Closure closure) throws SQLException {
 317  0
         warnDeprecated();
 318  0
         eachRow(sql, params, closure);
 319   
     }
 320   
 
 321   
     /**
 322   
      * Performs the given SQL query calling the closure with the result set
 323   
      */
 324  0
     public void eachRow(String sql, List params, Closure closure) throws SQLException {
 325  0
         Connection connection = createConnection();
 326  0
         PreparedStatement statement = null;
 327  0
         ResultSet results = null;
 328  0
         try {
 329  0
             log.fine(sql);
 330  0
             statement = connection.prepareStatement(sql);
 331  0
             setParameters(params, statement);
 332  0
             results = statement.executeQuery();
 333   
 
 334  0
             GroovyResultSet groovyRS = new GroovyResultSet(results);
 335  0
             while (groovyRS.next()) {
 336  0
                 closure.call(groovyRS);
 337   
             }
 338   
         }
 339   
         catch (SQLException e) {
 340  0
             log.log(Level.WARNING, "Failed to execute: " + sql, e);
 341  0
             throw e;
 342   
         }
 343   
         finally {
 344  0
             closeResources(connection, statement, results);
 345   
         }
 346   
     }
 347   
 
 348   
     /**
 349   
      * Performs the given SQL query calling the closure with the result set
 350   
      */
 351  0
     public void eachRow(GString gstring, Closure closure) throws SQLException {
 352  0
         List params = getParameters(gstring);
 353  0
         String sql = asSql(gstring, params);
 354  0
         eachRow(sql, params, closure);
 355   
     }
 356   
 
 357   
     /**
 358   
      * @deprecated please use eachRow instead
 359   
      */
 360  0
     public void queryEach(GString gstring, Closure closure) throws SQLException {
 361  0
         warnDeprecated();
 362  0
         eachRow(gstring, closure);
 363   
     }
 364   
 
 365   
     /**
 366   
      * Executes the given piece of SQL
 367   
      */
 368  0
     public boolean execute(String sql) throws SQLException {
 369  0
         Connection connection = createConnection();
 370  0
         Statement statement = null;
 371  0
         try {
 372  0
             log.fine(sql);
 373  0
             statement = connection.createStatement();
 374  0
             boolean isResultSet = statement.execute(sql);
 375  0
             this.updateCount = statement.getUpdateCount();
 376  0
             return isResultSet;
 377   
         }
 378   
         catch (SQLException e) {
 379  0
             log.log(Level.WARNING, "Failed to execute: " + sql, e);
 380  0
             throw e;
 381   
         }
 382   
         finally {
 383  0
             closeResources(connection, statement);
 384   
         }
 385   
     }
 386   
 
 387   
     /**
 388   
      * Executes the given SQL update
 389   
      * 
 390   
      * @return the number of rows updated
 391   
      */
 392  0
     public int executeUpdate(String sql) throws SQLException {
 393  0
         Connection connection = createConnection();
 394  0
         Statement statement = null;
 395  0
         try {
 396  0
             log.fine(sql);
 397  0
             statement = connection.createStatement();
 398  0
             this.updateCount = statement.executeUpdate(sql);
 399  0
             return this.updateCount;
 400   
         }
 401   
         catch (SQLException e) {
 402  0
             log.log(Level.WARNING, "Failed to execute: " + sql, e);
 403  0
             throw e;
 404   
         }
 405   
         finally {
 406  0
             closeResources(connection, statement);
 407   
         }
 408   
     }
 409   
 
 410   
     /**
 411   
      * Executes the given piece of SQL with parameters
 412   
      */
 413  0
     public boolean execute(String sql, List params) throws SQLException {
 414  0
         Connection connection = createConnection();
 415  0
         PreparedStatement statement = null;
 416  0
         try {
 417  0
             log.fine(sql);
 418  0
             statement = connection.prepareStatement(sql);
 419  0
             setParameters(params, statement);
 420  0
             boolean isResultSet = statement.execute();
 421  0
             this.updateCount = statement.getUpdateCount();
 422  0
             return isResultSet;
 423   
         }
 424   
         catch (SQLException e) {
 425  0
             log.log(Level.WARNING, "Failed to execute: " + sql, e);
 426  0
             throw e;
 427   
         }
 428   
         finally {
 429  0
             closeResources(connection, statement);
 430   
         }
 431   
     }
 432   
 
 433   
     /**
 434   
      * Executes the given SQL update with parameters
 435   
      * 
 436   
      * @return the number of rows updated
 437   
      */
 438  0
     public int executeUpdate(String sql, List params) throws SQLException {
 439  0
         Connection connection = createConnection();
 440  0
         PreparedStatement statement = null;
 441  0
         try {
 442  0
             log.fine(sql);
 443  0
             statement = connection.prepareStatement(sql);
 444  0
             setParameters(params, statement);
 445  0
             this.updateCount = statement.executeUpdate();
 446  0
             return this.updateCount;
 447   
         }
 448   
         catch (SQLException e) {
 449  0
             log.log(Level.WARNING, "Failed to execute: " + sql, e);
 450  0
             throw e;
 451   
         }
 452   
         finally {
 453  0
             closeResources(connection, statement);
 454   
         }
 455   
     }
 456   
 
 457   
     /**
 458   
      * Executes the given SQL with embedded expressions inside
 459   
      */
 460  0
     public boolean execute(GString gstring) throws SQLException {
 461  0
         List params = getParameters(gstring);
 462  0
         String sql = asSql(gstring, params);
 463  0
         return execute(sql, params);
 464   
     }
 465   
 
 466   
     /**
 467   
      * Executes the given SQL update with embedded expressions inside
 468   
      * 
 469   
      * @return the number of rows updated
 470   
      */
 471  0
     public int executeUpdate(GString gstring) throws SQLException {
 472  0
         List params = getParameters(gstring);
 473  0
         String sql = asSql(gstring, params);
 474  0
         return executeUpdate(sql, params);
 475   
     }
 476   
 
 477   
     /**
 478   
      * Performs a stored procedure call
 479   
      */
 480  0
     public int call(String sql) throws Exception {
 481  0
         return call(sql, Collections.EMPTY_LIST);
 482   
     }
 483   
 
 484   
     /**
 485   
      * Performs a stored procedure call with the given parameters
 486   
      */
 487  0
     public int call(String sql, List params) throws Exception {
 488  0
         Connection connection = createConnection();
 489  0
         CallableStatement statement = connection.prepareCall(sql);
 490  0
         try {
 491  0
             log.fine(sql);
 492  0
             setParameters(params, statement);
 493  0
             return statement.executeUpdate();
 494   
         }
 495   
         catch (SQLException e) {
 496  0
             log.log(Level.WARNING, "Failed to execute: " + sql, e);
 497  0
             throw e;
 498   
         }
 499   
         finally {
 500  0
             closeResources(connection, statement);
 501   
         }
 502   
     }
 503   
 
 504   
     /**
 505   
      * Performs a stored procedure call with the given parameters
 506   
      */
 507  0
     public int call(GString gstring) throws Exception {
 508  0
         List params = getParameters(gstring);
 509  0
         String sql = asSql(gstring, params);
 510  0
         return call(sql, params);
 511   
     }
 512   
 
 513   
     /**
 514   
      * If this SQL object was created with a Connection then this method closes
 515   
      * the connection. If this SQL object was created from a DataSource then
 516   
      * this method does nothing.
 517   
      * 
 518   
      * @throws SQLException
 519   
      */
 520  0
     public void close() throws SQLException {
 521  0
         if (useConnection != null) {
 522  0
             useConnection.close();
 523   
         }
 524   
     }
 525   
 
 526  0
     public DataSource getDataSource() {
 527  0
         return dataSource;
 528   
     }
 529   
 
 530   
     /**
 531   
      * @return the SQL version of the given query using ? instead of any
 532   
      *         parameter
 533   
      */
 534  0
     protected String asSql(GString gstring, List values) {
 535  0
         boolean nulls = false;
 536  0
         String[] strings = gstring.getStrings();
 537  0
         if (strings.length <= 0) {
 538  0
             throw new IllegalArgumentException("No SQL specified in GString: " + gstring);
 539   
         }
 540  0
         StringBuffer buffer = new StringBuffer();
 541  0
         boolean warned = false;
 542  0
         Iterator iter = values.iterator();
 543  0
         for (int i = 0; i < strings.length; i++) {
 544  0
             String text = strings[i];
 545  0
             if (text != null) {
 546  0
                 buffer.append(text);
 547   
             }
 548  0
             if (iter.hasNext()) {
 549  0
                 Object value = iter.next();
 550  0
                 if (value != null) {
 551  0
                     boolean validBinding = true;
 552  0
                     if (i < strings.length - 1) {
 553  0
                         String nextText = strings[i + 1];
 554  0
                         if ((text.endsWith("\"") || text.endsWith("'")) && (nextText.startsWith("'") || nextText.startsWith("\""))) {
 555  0
                             if (!warned) {
 556  0
                                 log.warning("In Groovy SQL please do not use quotes around dynamic expressions "
 557   
                                         + "(which start with $) as this means we cannot use a JDBC PreparedStatement "
 558   
                                         + "and so is a security hole. Groovy has worked around your mistake but the security hole is still there. The expression so far is: " + buffer.toString() + "?" + nextText);
 559  0
                                 warned = true;
 560   
                             }
 561  0
                             buffer.append(value);
 562  0
                             iter.remove();
 563  0
                             validBinding = false;
 564   
                         }
 565   
                     }
 566  0
                     if (validBinding) {
 567  0
                         buffer.append("?");
 568   
                     }
 569   
                 }
 570   
                 else {
 571  0
                     nulls = true;
 572  0
                     buffer.append("?'\"?"); // will replace these with nullish
 573   
                     // values
 574   
                 }
 575   
             }
 576   
         }
 577  0
         String sql = buffer.toString();
 578  0
         if (nulls) {
 579  0
             sql = nullify(sql);
 580   
         }
 581  0
         return sql;
 582   
     }
 583   
 
 584   
     /**
 585   
      * replace ?'"? references with NULLish
 586   
      * 
 587   
      * @param sql
 588   
      * @return
 589   
      */
 590  0
     protected String nullify(String sql) {
 591   
         /*
 592   
          * Some drivers (Oracle classes12.zip) have difficulty resolving data
 593   
          * type if setObject(null). We will modify the query to pass 'null', 'is
 594   
          * null', and 'is not null'
 595   
          */
 596   
         //could be more efficient by compiling expressions in advance.
 597  0
         int firstWhere = findWhereKeyword(sql);
 598  0
         if (firstWhere >= 0) {
 599  0
             Pattern[] patterns = { Pattern.compile("(?is)^(.{" + firstWhere + "}.*?)!=\\s{0,1}(\\s*)\\?'\"\\?(.*)"),
 600   
                     Pattern.compile("(?is)^(.{" + firstWhere + "}.*?)<>\\s{0,1}(\\s*)\\?'\"\\?(.*)"),
 601   
                     Pattern.compile("(?is)^(.{" + firstWhere + "}.*?[^<>])=\\s{0,1}(\\s*)\\?'\"\\?(.*)"), };
 602  0
             String[] replacements = { "$1 is not $2null$3", "$1 is not $2null$3", "$1 is $2null$3", };
 603  0
             for (int i = 0; i < patterns.length; i++) {
 604  0
                 Matcher matcher = patterns[i].matcher(sql);
 605  0
                 while (matcher.matches()) {
 606  0
                     sql = matcher.replaceAll(replacements[i]);
 607  0
                     matcher = patterns[i].matcher(sql);
 608   
                 }
 609   
             }
 610   
         }
 611  0
         return sql.replaceAll("\\?'\"\\?", "null");
 612   
     }
 613   
 
 614   
     /**
 615   
      * Find the first 'where' keyword in the sql.
 616   
      * 
 617   
      * @param sql
 618   
      * @return
 619   
      */
 620  0
     protected int findWhereKeyword(String sql) {
 621  0
         char[] chars = sql.toLowerCase().toCharArray();
 622  0
         char[] whereChars = "where".toCharArray();
 623  0
         int i = 0;
 624  0
         boolean inString = false; //TODO: Cater for comments?
 625  0
         boolean noWhere = true;
 626  0
         int inWhere = 0;
 627  0
         while (i < chars.length && noWhere) {
 628  0
             switch (chars[i]) {
 629   
                 case '\'':
 630  0
                     if (inString) {
 631  0
                         inString = false;
 632   
                     }
 633   
                     else {
 634  0
                         inString = true;
 635   
                     }
 636  0
                     break;
 637   
                 default:
 638  0
                     if (!inString && chars[i] == whereChars[inWhere]) {
 639  0
                         inWhere++;
 640  0
                         if (inWhere == whereChars.length) {
 641  0
                             return i;
 642   
                         }
 643   
                     }
 644   
             }
 645  0
             i++;
 646   
         }
 647  0
         return -1;
 648   
     }
 649   
 
 650   
     /**
 651   
      * @return extracts the parameters from the expression as a List
 652   
      */
 653  0
     protected List getParameters(GString gstring) {
 654  0
         Object[] values = gstring.getValues();
 655  0
         List answer = new ArrayList(values.length);
 656  0
         for (int i = 0; i < values.length; i++) {
 657  0
             if (values[i] != null) {
 658  0
                 answer.add(values[i]);
 659   
             }
 660   
         }
 661  0
         return answer;
 662   
     }
 663   
 
 664   
     /**
 665   
      * Appends the parameters to the given statement
 666   
      */
 667  0
     protected void setParameters(List params, PreparedStatement statement) throws SQLException {
 668  0
         int i = 1;
 669  0
         for (Iterator iter = params.iterator(); iter.hasNext();) {
 670  0
             Object value = iter.next();
 671  0
             setObject(statement, i++, value);
 672   
         }
 673   
     }
 674   
 
 675   
     /**
 676   
      * Strategy method allowing derived classes to handle types differently such
 677   
      * as for CLOBs etc.
 678   
      */
 679  0
     protected void setObject(PreparedStatement statement, int i, Object value) throws SQLException {
 680  0
         statement.setObject(i, value);
 681   
     }
 682   
 
 683  0
     protected Connection createConnection() throws SQLException {
 684  0
         if (dataSource != null) {
 685   
             //Use a doPrivileged here as many different properties need to be
 686   
             // read, and the policy
 687   
             //shouldn't have to list them all.
 688  0
             Connection con = null;
 689  0
             try {
 690  0
                 con = (Connection) AccessController.doPrivileged(new PrivilegedExceptionAction() {
 691  0
                     public Object run() throws SQLException {
 692  0
                         return dataSource.getConnection();
 693   
                     }
 694   
                 });
 695   
             }
 696   
             catch (PrivilegedActionException pae) {
 697  0
                 Exception e = pae.getException();
 698  0
                 if (e instanceof SQLException) {
 699  0
                     throw (SQLException) e;
 700   
                 }
 701   
                 else {
 702  0
                     throw (RuntimeException) e;
 703   
                 }
 704   
             }
 705  0
             return con;
 706   
         }
 707   
         else {
 708   
             //System.out.println("createConnection returning: " +
 709   
             // useConnection);
 710  0
             return useConnection;
 711   
         }
 712   
     }
 713   
 
 714  0
     protected void closeResources(Connection connection, Statement statement, ResultSet results) {
 715  0
         if (results != null) {
 716  0
             try {
 717  0
                 results.close();
 718   
             }
 719   
             catch (SQLException e) {
 720  0
                 log.log(Level.SEVERE, "Caught exception closing resultSet: " + e, e);
 721   
             }
 722   
         }
 723  0
         closeResources(connection, statement);
 724   
     }
 725   
 
 726  0
     protected void closeResources(Connection connection, Statement statement) {
 727  0
         if (statement != null) {
 728  0
             try {
 729  0
                 statement.close();
 730   
             }
 731   
             catch (SQLException e) {
 732  0
                 log.log(Level.SEVERE, "Caught exception closing statement: " + e, e);
 733   
             }
 734   
         }
 735  0
         if (dataSource != null) {
 736  0
             try {
 737  0
                 connection.close();
 738   
             }
 739   
             catch (SQLException e) {
 740  0
                 log.log(Level.SEVERE, "Caught exception closing connection: " + e, e);
 741   
             }
 742   
         }
 743   
     }
 744   
 
 745  0
     private void warnDeprecated() {
 746  0
         if (!warned) {
 747  0
             warned = true;
 748  0
             log.warning("queryEach() is deprecated, please use eachRow() instead");
 749   
         }
 750   
     }
 751   
 
 752  0
     public void commit() {
 753  0
         try {
 754  0
             this.useConnection.commit();
 755   
         }
 756   
         catch (SQLException e) {
 757  0
             log.log(Level.SEVERE, "Caught exception commiting connection: " + e, e);
 758   
         }
 759   
     }
 760   
 
 761  0
     public void rollback() {
 762  0
         try {
 763  0
             this.useConnection.rollback();
 764   
         }
 765   
         catch (SQLException e) {
 766  0
             log.log(Level.SEVERE, "Caught exception rollbacking connection: " + e, e);
 767   
         }
 768   
     }
 769   
 
 770   
     /**
 771   
      * @return Returns the updateCount.
 772   
      */
 773  0
     public int getUpdateCount() {
 774  0
         return updateCount;
 775   
     }
 776   
 
 777   
     /**
 778   
      * If this instance was created with a single Connection then the connection
 779   
      * is returned. Otherwise if this instance was created with a DataSource
 780   
      * then this method returns null
 781   
      * 
 782   
      * @return the connection wired into this object, or null if this object
 783   
      *         uses a DataSource
 784   
      */
 785  0
     public Connection getConnection() {
 786  0
         return useConnection;
 787   
     }
 788   
 }