View Javadoc

    1   /*
    2    * $Id: Sql.java,v 1.21 2006/05/30 17:27:29 blackdrag 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.ResultSetMetaData;
    48  import java.sql.SQLException;
    49  import java.sql.Statement;
    50  import java.sql.Types;
    51  import java.util.ArrayList;
    52  import java.util.Collections;
    53  import java.util.Iterator;
    54  import java.util.List;
    55  import java.util.LinkedHashMap;
    56  import java.util.Properties;
    57  import java.util.logging.Level;
    58  import java.util.logging.Logger;
    59  import java.util.regex.Matcher;
    60  import java.util.regex.Pattern;
    61
    62  import javax.sql.DataSource;
    63
    64  /***
    65   * Represents an extent of objects
    66   *
    67   * @author Chris Stevenson
    68   * @author <a href="mailto:james@coredevelopers.net">James Strachan </a>
    69   * @version $Revision: 1.21 $
    70   */
    71  public class Sql {
    72
    73      protected Logger log = Logger.getLogger(getClass().getName());
    74
    75      private DataSource dataSource;
    76
    77      private Connection useConnection;
    78
    79      /*** lets only warn of using deprecated methods once */
    80      private boolean warned;
    81
    82      // store the last row count for executeUpdate
    83      int updateCount = 0;
    84
    85      /*** allows a closure to be used to configure the statement before its use */
    86      private Closure configureStatement;
    87
    88      /***
    89       * A helper method which creates a new Sql instance from a JDBC connection
    90       * URL
    91       *
    92       * @param url
    93       * @return a new Sql instance with a connection
    94       */
    95      public static Sql newInstance(String url) throws SQLException {
    96          Connection connection = DriverManager.getConnection(url);
    97          return new Sql(connection);
    98      }
    99
    100     /***
    101      * A helper method which creates a new Sql instance from a JDBC connection
    102      * URL
    103      *
    104      * @param url
    105      * @return a new Sql instance with a connection
    106      */
    107     public static Sql newInstance(String url, Properties properties) throws SQLException {
    108         Connection connection = DriverManager.getConnection(url, properties);
    109         return new Sql(connection);
    110     }
    111
    112     /***
    113      * A helper method which creates a new Sql instance from a JDBC connection
    114      * URL and driver class name
    115      *
    116      * @param url
    117      * @return a new Sql instance with a connection
    118      */
    119     public static Sql newInstance(String url, Properties properties, String driverClassName) throws SQLException, ClassNotFoundException {
    120         loadDriver(driverClassName);
    121         return newInstance(url, properties);
    122     }
    123
    124     /***
    125      * A helper method which creates a new Sql instance from a JDBC connection
    126      * URL, username and password
    127      *
    128      * @param url
    129      * @return a new Sql instance with a connection
    130      */
    131     public static Sql newInstance(String url, String user, String password) throws SQLException {
    132         Connection connection = DriverManager.getConnection(url, user, password);
    133         return new Sql(connection);
    134     }
    135
    136     /***
    137      * A helper method which creates a new Sql instance from a JDBC connection
    138      * URL, username, password and driver class name
    139      *
    140      * @param url
    141      * @return a new Sql instance with a connection
    142      */
    143     public static Sql newInstance(String url, String user, String password, String driverClassName) throws SQLException,
    144             ClassNotFoundException {
    145         loadDriver(driverClassName);
    146         return newInstance(url, user, password);
    147     }
    148
    149     /***
    150      * A helper method which creates a new Sql instance from a JDBC connection
    151      * URL and driver class name
    152      *
    153      * @param url
    154      * @param driverClassName
    155      *            the class name of the driver
    156      * @return a new Sql instance with a connection
    157      */
    158     public static Sql newInstance(String url, String driverClassName) throws SQLException, ClassNotFoundException {
    159         loadDriver(driverClassName);
    160         return newInstance(url);
    161     }
    162
    163     /***
    164      * Attempts to load the JDBC driver on the thread, current or system class
    165      * loaders
    166      *
    167      * @param driverClassName
    168      * @throws ClassNotFoundException
    169      */
    170     public static void loadDriver(String driverClassName) throws ClassNotFoundException {
    171         // 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      * @return
    288      */
    289     public static InOutParameter inout(final InParameter in){
    290         return new InOutParameter(){
    291             public int getType() {
    292                 return in.getType();
    293             }
    294             public Object getValue() {
    295                 return in.getValue();
    296             }
    297         };
    298     }
    299
    300     /***
    301      * Create a new ResultSetOutParameter
    302      * @param type the JDBC data type.
    303      * @return a ResultSetOutParameter
    304      */
    305     public static ResultSetOutParameter resultSet(final int type){
    306         return new ResultSetOutParameter(){
    307             public int getType() {
    308                 return type;
    309             }
    310         };
    311     }
    312
    313     /***
    314      * Creates a variable to be expanded in the Sql string rather
    315      * than representing an sql parameter.
    316      * @param object
    317      * @return
    318      */
    319     public static ExpandedVariable expand(final Object object){
    320         return new ExpandedVariable(){
    321             public Object getObject() {
    322                 return object;
    323             }};
    324     }
    325
    326     /***
    327      * Constructs an SQL instance using the given DataSource. Each operation
    328      * will use a Connection from the DataSource pool and close it when the
    329      * operation is completed putting it back into the pool.
    330      *
    331      * @param dataSource
    332      */
    333     public Sql(DataSource dataSource) {
    334         this.dataSource = dataSource;
    335     }
    336
    337     /***
    338      * Construts an SQL instance using the given Connection. It is the callers
    339      * responsibility to close the Connection after the Sql instance has been
    340      * used. You can do this on the connection object directly or by calling the
    341      * {@link java.sql.Connection#close()}  method.
    342      *
    343      * @param connection
    344      */
    345     public Sql(Connection connection) {
    346         if (connection == null) {
    347             throw new NullPointerException("Must specify a non-null Connection");
    348         }
    349         this.useConnection = connection;
    350     }
    351
    352     public Sql(Sql parent) {
    353         this.dataSource = parent.dataSource;
    354         this.useConnection = parent.useConnection;
    355     }
    356
    357     public DataSet dataSet(String table) {
    358         return new DataSet(this, table);
    359     }
    360
    361     public DataSet dataSet(Class type) {
    362         return new DataSet(this, type);
    363     }
    364
    365     /***
    366      * Performs the given SQL query calling the closure with the result set
    367      */
    368     public void query(String sql, Closure closure) throws SQLException {
    369         Connection connection = createConnection();
    370         Statement statement = connection.createStatement();
    371         configure(statement);
    372         ResultSet results = null;
    373         try {
    374             log.fine(sql);
    375             results = statement.executeQuery(sql);
    376             closure.call(results);
    377         }
    378         catch (SQLException e) {
    379             log.log(Level.FINE, "Failed to execute: " + sql, e);
    380             throw e;
    381         }
    382         finally {
    383             closeResources(connection, statement, results);
    384         }
    385     }
    386
    387     /***
    388      * Performs the given SQL query with parameters calling the closure with the
    389      * result set
    390      */
    391     public void query(String sql, List params, Closure closure) throws SQLException {
    392         Connection connection = createConnection();
    393         PreparedStatement statement = null;
    394         ResultSet results = null;
    395         try {
    396             log.fine(sql);
    397             statement = connection.prepareStatement(sql);
    398             setParameters(params, statement);
    399             configure(statement);
    400             results = statement.executeQuery();
    401             closure.call(results);
    402         }
    403         catch (SQLException e) {
    404             log.log(Level.FINE, "Failed to execute: " + sql, e);
    405             throw e;
    406         }
    407         finally {
    408             closeResources(connection, statement, results);
    409         }
    410     }
    411
    412     /***
    413      * Performs the given SQL query calling the closure with the result set
    414      */
    415     public void query(GString gstring, Closure closure) throws SQLException {
    416         List params = getParameters(gstring);
    417         String sql = asSql(gstring, params);
    418         query(sql, params, closure);
    419     }
    420
    421     /***
    422      * @deprecated please use eachRow instead
    423      */
    424     public void queryEach(String sql, Closure closure) throws SQLException {
    425         warnDeprecated();
    426         eachRow(sql, closure);
    427     }
    428
    429     /***
    430      * Performs the given SQL query calling the closure with each row of the
    431      * result set
    432      */
    433     public void eachRow(String sql, Closure closure) throws SQLException {
    434         Connection connection = createConnection();
    435         Statement statement = connection.createStatement();
    436         configure(statement);
    437         ResultSet results = null;
    438         try {
    439             log.fine(sql);
    440             results = statement.executeQuery(sql);
    441
    442             GroovyResultSet groovyRS = new GroovyResultSet(results);
    443             while (groovyRS.next()) {
    444                 closure.call(groovyRS);
    445             }
    446         }
    447         catch (SQLException e) {
    448             log.log(Level.FINE, "Failed to execute: " + sql, e);
    449             throw e;
    450         }
    451         finally {
    452             closeResources(connection, statement, results);
    453         }
    454     }
    455
    456     /***
    457      * @deprecated please use eachRow instead
    458      */
    459     public void queryEach(String sql, List params, Closure closure) throws SQLException {
    460         warnDeprecated();
    461         eachRow(sql, params, closure);
    462     }
    463
    464     /***
    465      * Performs the given SQL query calling the closure with the result set
    466      */
    467     public void eachRow(String sql, List params, Closure closure) throws SQLException {
    468         Connection connection = createConnection();
    469         PreparedStatement statement = null;
    470         ResultSet results = null;
    471         try {
    472             log.fine(sql);
    473             statement = connection.prepareStatement(sql);
    474             setParameters(params, statement);
    475             configure(statement);
    476             results = statement.executeQuery();
    477
    478             GroovyResultSet groovyRS = new GroovyResultSet(results);
    479             while (groovyRS.next()) {
    480                 closure.call(groovyRS);
    481             }
    482         }
    483         catch (SQLException e) {
    484             log.log(Level.FINE, "Failed to execute: " + sql, e);
    485             throw e;
    486         }
    487         finally {
    488             closeResources(connection, statement, results);
    489         }
    490     }
    491
    492     /***
    493      * Performs the given SQL query calling the closure with the result set
    494      */
    495     public void eachRow(GString gstring, Closure closure) throws SQLException {
    496         List params = getParameters(gstring);
    497         String sql = asSql(gstring, params);
    498         eachRow(sql, params, closure);
    499     }
    500
    501     /***
    502      * @deprecated please use eachRow instead
    503      */
    504     public void queryEach(GString gstring, Closure closure) throws SQLException {
    505         warnDeprecated();
    506         eachRow(gstring, closure);
    507     }
    508
    509     /***
    510      * Performs the given SQL query and return the rows of the result set
    511      */
    512      public List rows(String sql) throws SQLException {
    513         List results = new ArrayList();
    514         Connection connection = createConnection();
    515         Statement statement = connection.createStatement();
    516         configure(statement);
    517         ResultSet rs = null;
    518         try {
    519             log.fine(sql);
    520             rs = statement.executeQuery(sql);
    521             while (rs.next()) {
    522                 ResultSetMetaData metadata = rs.getMetaData();
    523                 LinkedHashMap lhm = new LinkedHashMap(metadata.getColumnCount(),1,true);
    524                 for(int i=1 ; i<=metadata.getColumnCount() ; i++) {
    525                       lhm.put(metadata.getColumnName(i),rs.getObject(i));
    526                 }
    527                 GroovyRowResult row = new GroovyRowResult(lhm);
    528                 results.add(row);
    529             }
    530             return(results);
    531         }
    532         catch (SQLException e) {
    533             log.log(Level.FINE, "Failed to execute: " + sql, e);
    534             throw e;
    535         }
    536         finally {
    537             closeResources(connection, statement, rs);
    538         }
    539     }
    540
    541     /***
    542      * Performs the given SQL query and return the first row of the result set
    543      */
    544     public Object firstRow(String sql) throws SQLException {
    545         return( rows(sql).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          return( rows(sql, params).get(0));
    589      }
    590
    591     /***
    592      * Executes the given piece of SQL
    593      */
    594     public boolean execute(String sql) throws SQLException {
    595         Connection connection = createConnection();
    596         Statement statement = null;
    597         try {
    598             log.fine(sql);
    599             statement = connection.createStatement();
    600             configure(statement);
    601             boolean isResultSet = statement.execute(sql);
    602             this.updateCount = statement.getUpdateCount();
    603             return isResultSet;
    604         }
    605         catch (SQLException e) {
    606             log.log(Level.FINE, "Failed to execute: " + sql, e);
    607             throw e;
    608         }
    609         finally {
    610             closeResources(connection, statement);
    611         }
    612     }
    613
    614     /***
    615      * Executes the given SQL update
    616      *
    617      * @return the number of rows updated
    618      */
    619     public int executeUpdate(String sql) throws SQLException {
    620         Connection connection = createConnection();
    621         Statement statement = null;
    622         try {
    623             log.fine(sql);
    624             statement = connection.createStatement();
    625             configure(statement);
    626             this.updateCount = statement.executeUpdate(sql);
    627             return this.updateCount;
    628         }
    629         catch (SQLException e) {
    630             log.log(Level.FINE, "Failed to execute: " + sql, e);
    631             throw e;
    632         }
    633         finally {
    634             closeResources(connection, statement);
    635         }
    636     }
    637
    638     /***
    639      * Executes the given SQL statement. See {@link #executeInsert(GString)}
    640      * for more details.
    641      * @param sql The SQL statement to execute.
    642      * @return A list of the auto-generated column values for each
    643      * inserted row.
    644      */
    645     public List executeInsert(String sql) throws SQLException {
    646         Connection connection = createConnection();
    647         Statement statement = null;
    648         try {
    649             log.fine(sql);
    650             statement = connection.createStatement();
    651             configure(statement);
    652             boolean hasResultSet = statement.execute(sql, Statement.RETURN_GENERATED_KEYS);
    653
    654             // Prepare a list to contain the auto-generated column
    655             // values, and then fetch them from the statement.
    656             List autoKeys = new ArrayList();
    657         	ResultSet keys = statement.getGeneratedKeys();
    658         	int count = keys.getMetaData().getColumnCount();
    659
    660         	// Copy the column values into a list of a list.
    661         	while (keys.next()) {
    662         		List rowKeys = new ArrayList(count);
    663         		for (int i = 1; i <= count; i++) {
    664         			rowKeys.add(keys.getObject(i));
    665         		}
    666
    667         		autoKeys.add(rowKeys);
    668         	}
    669
    670         	// Store the update count so that it can be retrieved by
    671         	// clients, and then return the list of auto-generated
    672         	// values.
    673         	this.updateCount = statement.getUpdateCount();
    674         	return autoKeys;
    675         }
    676         catch (SQLException e) {
    677             log.log(Level.FINE, "Failed to execute: " + sql, e);
    678             throw e;
    679         }
    680         finally {
    681             closeResources(connection, statement);
    682         }
    683     }
    684
    685     /***
    686      * Executes the given piece of SQL with parameters
    687      */
    688     public boolean execute(String sql, List params) throws SQLException {
    689         Connection connection = createConnection();
    690         PreparedStatement statement = null;
    691         try {
    692             log.fine(sql);
    693             statement = connection.prepareStatement(sql);
    694             setParameters(params, statement);
    695             configure(statement);
    696             boolean isResultSet = statement.execute();
    697             this.updateCount = statement.getUpdateCount();
    698             return isResultSet;
    699         }
    700         catch (SQLException e) {
    701             log.log(Level.FINE, "Failed to execute: " + sql, e);
    702             throw e;
    703         }
    704         finally {
    705             closeResources(connection, statement);
    706         }
    707     }
    708
    709     /***
    710      * Executes the given SQL update with parameters
    711      *
    712      * @return the number of rows updated
    713      */
    714     public int executeUpdate(String sql, List params) throws SQLException {
    715         Connection connection = createConnection();
    716         PreparedStatement statement = null;
    717         try {
    718             log.fine(sql);
    719             statement = connection.prepareStatement(sql);
    720             setParameters(params, statement);
    721             configure(statement);
    722             this.updateCount = statement.executeUpdate();
    723             return this.updateCount;
    724         }
    725         catch (SQLException e) {
    726             log.log(Level.FINE, "Failed to execute: " + sql, e);
    727             throw e;
    728         }
    729         finally {
    730             closeResources(connection, statement);
    731         }
    732     }
    733
    734     /***
    735      * Executes the given SQL statement with a particular list of
    736      * parameter values. See {@link #executeInsert(GString)} for
    737      * more details.
    738      * @param sql The SQL statement to execute.
    739      * @param params The parameter values that will be substituted
    740      * into the SQL statement's parameter slots.
    741      * @return A list of the auto-generated column values for each
    742      * inserted row.
    743      */
    744     public List executeInsert(String sql, List params) throws SQLException {
    745         // Now send the SQL to the database.
    746         Connection connection = createConnection();
    747         PreparedStatement statement = null;
    748         try {
    749             log.fine(sql);
    750
    751             // Prepare a statement for the SQL and then execute it.
    752             statement = connection.prepareStatement(sql);
    753             setParameters(params, statement);
    754             configure(statement);
    755             boolean hasResultSet = statement.execute(sql, Statement.RETURN_GENERATED_KEYS);
    756
    757             // Prepare a list to contain the auto-generated column
    758             // values, and then fetch them from the statement.
    759             List autoKeys = new ArrayList();
    760         	ResultSet keys = statement.getGeneratedKeys();
    761         	int count = keys.getMetaData().getColumnCount();
    762
    763         	// Copy the column values into a list of a list.
    764         	while (keys.next()) {
    765         		List rowKeys = new ArrayList(count);
    766         		for (int i = 1; i <= count; i++) {
    767         			rowKeys.add(keys.getObject(i));
    768         		}
    769
    770         		autoKeys.add(rowKeys);
    771         	}
    772
    773         	// Store the update count so that it can be retrieved by
    774         	// clients, and then return the list of auto-generated
    775         	// values.
    776         	this.updateCount = statement.getUpdateCount();
    777         	return autoKeys;
    778         }
    779         catch (SQLException e) {
    780             log.log(Level.FINE, "Failed to execute: " + sql, e);
    781             throw e;
    782         }
    783         finally {
    784             closeResources(connection, statement);
    785         }
    786     }
    787
    788     /***
    789      * Executes the given SQL with embedded expressions inside
    790      */
    791     public boolean execute(GString gstring) throws SQLException {
    792         List params = getParameters(gstring);
    793         String sql = asSql(gstring, params);
    794         return execute(sql, params);
    795     }
    796
    797     /***
    798      * Executes the given SQL update with embedded expressions inside
    799      *
    800      * @return the number of rows updated
    801      */
    802     public int executeUpdate(GString gstring) throws SQLException {
    803         List params = getParameters(gstring);
    804         String sql = asSql(gstring, params);
    805         return executeUpdate(sql, params);
    806     }
    807
    808     /***
    809      * <p>Executes the given SQL with embedded expressions inside, and
    810      * returns the values of any auto-generated colums, such as an
    811      * autoincrement ID field. These values can be accessed using
    812      * array notation. For example, to return the second auto-generated
    813      * column value of the third row, use <code>keys[3][1]</code>. The
    814      * method is designed to be used with SQL INSERT statements, but is
    815      * not limited to them.</p>
    816      * <p>The standard use for this method is when a table has an
    817      * autoincrement ID column and you want to know what the ID is for
    818      * a newly inserted row. In this example, we insert a single row
    819      * into a table in which the first column contains the autoincrement
    820      * ID:</p>
    821      * <pre>
    822      *     def sql = Sql.newInstance("jdbc:mysql://localhost:3306/groovy",
    823      *                               "user",
    824      *                               "password",
    825      *                               "com.mysql.jdbc.Driver")
    826      *
    827      *     def keys = sql.insert("insert into test_table (INT_DATA, STRING_DATA) "
    828      *                           + "VALUES (1, 'Key Largo')")
    829      *
    830      *     def id = keys[0][0]
    831      *
    832      *     // 'id' now contains the value of the new row's ID column.
    833      *     // It can be used to update an object representation's
    834      *     // id attribute for example.
    835      *     ...
    836      * </pre>
    837      * @return A list of column values representing each row's
    838      * auto-generated keys.
    839      */
    840     public List executeInsert(GString gstring) throws SQLException {
    841         List params = getParameters(gstring);
    842         String sql = asSql(gstring, params);
    843         return executeInsert(sql, params);
    844     }
    845
    846     /***
    847      * Performs a stored procedure call
    848      */
    849     public int call(String sql) throws Exception {
    850         return call(sql, Collections.EMPTY_LIST);
    851     }
    852
    853     /***
    854      * Performs a stored procedure call with the given parameters
    855      */
    856     public int call(String sql, List params) throws Exception {
    857         Connection connection = createConnection();
    858         CallableStatement statement = connection.prepareCall(sql);
    859         try {
    860             log.fine(sql);
    861             setParameters(params, statement);
    862             configure(statement);
    863             return statement.executeUpdate();
    864         }
    865         catch (SQLException e) {
    866             log.log(Level.FINE, "Failed to execute: " + sql, e);
    867             throw e;
    868         }
    869         finally {
    870             closeResources(connection, statement);
    871         }
    872     }
    873
    874     /***
    875      * Performs a stored procedure call with the given parameters.  The closure
    876      * is called once with all the out parameters.
    877      */
    878     public void call(String sql, List params, Closure closure) throws Exception {
    879         Connection connection = createConnection();
    880         CallableStatement statement = connection.prepareCall(sql);
    881         try {
    882             log.fine(sql);
    883             setParameters(params, statement);
    884             statement.execute();
    885             List results = new ArrayList();
    886             int indx = 0;
    887             int inouts = 0;
    888             for (Iterator iter = params.iterator(); iter.hasNext();) {
    889                 Object value = iter.next();
    890                 if(value instanceof OutParameter){
    891                     if(value instanceof ResultSetOutParameter){
    892                         results.add(new CallResultSet(statement,indx));
    893                     }else{
    894                         Object o = statement.getObject(indx+1);
    895                         if(o instanceof ResultSet){
    896                             results.add(new GroovyResultSet((ResultSet)o));
    897                         }else{
    898                             results.add(o);
    899                         }
    900                     }
    901                     inouts++;
    902                 }
    903                 indx++;
    904             }
    905             closure.call(results.toArray(new Object[inouts]));
    906         } catch (SQLException e) {
    907             log.log(Level.WARNING, "Failed to execute: " + sql, e);
    908             throw e;
    909         } finally {
    910             closeResources(connection, statement);
    911         }
    912     }
    913
    914     /***
    915      * Performs a stored procedure call with the given parameters
    916      */
    917     public int call(GString gstring) throws Exception {
    918         List params = getParameters(gstring);
    919         String sql = asSql(gstring, params);
    920         return call(sql, params);
    921     }
    922
    923
    924     /***
    925      * Performs a stored procedure call with the given parameters,
    926      * calling the closure once with all result objects.
    927      */
    928     public void call(GString gstring, Closure closure) throws Exception {
    929         List params = getParameters(gstring);
    930         String sql = asSql(gstring,params);
    931         call(sql, params,closure);
    932     }
    933
    934     /***
    935      * If this SQL object was created with a Connection then this method closes
    936      * the connection. If this SQL object was created from a DataSource then
    937      * this method does nothing.
    938      *
    939      * @throws SQLException
    940      */
    941     public void close() throws SQLException {
    942         if (useConnection != null) {
    943             useConnection.close();
    944         }
    945     }
    946
    947     public DataSource getDataSource() {
    948         return dataSource;
    949     }
    950
    951
    952     public void commit() {
    953         try {
    954             this.useConnection.commit();
    955         }
    956         catch (SQLException e) {
    957             log.log(Level.SEVERE, "Caught exception commiting connection: " + e, e);
    958         }
    959     }
    960
    961     public void rollback() {
    962         try {
    963             this.useConnection.rollback();
    964         }
    965         catch (SQLException e) {
    966             log.log(Level.SEVERE, "Caught exception rollbacking connection: " + e, e);
    967         }
    968     }
    969
    970     /***
    971      * @return Returns the updateCount.
    972      */
    973     public int getUpdateCount() {
    974         return updateCount;
    975     }
    976
    977     /***
    978      * If this instance was created with a single Connection then the connection
    979      * is returned. Otherwise if this instance was created with a DataSource
    980      * then this method returns null
    981      *
    982      * @return the connection wired into this object, or null if this object
    983      *         uses a DataSource
    984      */
    985     public Connection getConnection() {
    986         return useConnection;
    987     }
    988
    989
    990     /***
    991      * Allows a closure to be passed in to configure the JDBC statements before they are executed
    992      * to do things like set the query size etc.
    993      *
    994      * @param configureStatement
    995      */
    996     public void withStatement(Closure configureStatement) {
    997         this.configureStatement = configureStatement;
    998     }
    999
    1000     // Implementation methods
    1001     //-------------------------------------------------------------------------
    1002
    1003     /***
    1004      * @return the SQL version of the given query using ? instead of any
    1005      *         parameter
    1006      */
    1007     protected String asSql(GString gstring, List values) {
    1008         String[] strings = gstring.getStrings();
    1009         if (strings.length <= 0) {
    1010             throw new IllegalArgumentException("No SQL specified in GString: " + gstring);
    1011         }
    1012         boolean nulls = false;
    1013         StringBuffer buffer = new StringBuffer();
    1014         boolean warned = false;
    1015         Iterator iter = values.iterator();
    1016         for (int i = 0; i < strings.length; i++) {
    1017             String text = strings[i];
    1018             if (text != null) {
    1019                 buffer.append(text);
    1020             }
    1021             if (iter.hasNext()) {
    1022                 Object value = iter.next();
    1023                 if (value != null) {
    1024                     if(value instanceof ExpandedVariable){
    1025                         buffer.append(((ExpandedVariable)value).getObject());
    1026                         iter.remove();
    1027                     }else{
    1028                         boolean validBinding = true;
    1029                         if (i < strings.length - 1) {
    1030                             String nextText = strings[i + 1];
    1031                             if ((text.endsWith("\"") || text.endsWith("'")) && (nextText.startsWith("'") || nextText.startsWith("\""))) {
    1032                                 if (!warned) {
    1033                                     log.warning("In Groovy SQL please do not use quotes around dynamic expressions " +
    1034                                             "(which start with $) as this means we cannot use a JDBC PreparedStatement " +
    1035                                             "and so is a security hole. Groovy has worked around your mistake but the security hole is still there. " +
    1036                                             "The expression so far is: " + buffer.toString() + "?" + nextText);
    1037                                     warned = true;
    1038                                 }
    1039                                 buffer.append(value);
    1040                                 iter.remove();
    1041                                 validBinding = false;
    1042                             }
    1043                         }
    1044                         if (validBinding) {
    1045                             buffer.append("?");
    1046                         }
    1047                     }
    1048                 }
    1049                 else {
    1050                     nulls = true;
    1051                     buffer.append("?'\"?"); // will replace these with nullish
    1052                     // values
    1053                 }
    1054             }
    1055         }
    1056         String sql = buffer.toString();
    1057         if (nulls) {
    1058             sql = nullify(sql);
    1059         }
    1060         return sql;
    1061     }
    1062
    1063     /***
    1064      * replace ?'"? references with NULLish
    1065      *
    1066      * @param sql
    1067      * @return
    1068      */
    1069     protected String nullify(String sql) {
    1070         /*
    1071          * Some drivers (Oracle classes12.zip) have difficulty resolving data
    1072          * type if setObject(null). We will modify the query to pass 'null', 'is
    1073          * null', and 'is not null'
    1074          */
    1075         //could be more efficient by compiling expressions in advance.
    1076         int firstWhere = findWhereKeyword(sql);
    1077         if (firstWhere >= 0) {
    1078             Pattern[] patterns = { Pattern.compile("(?is)^(.{" + firstWhere + "}.*?)!=//s{0,1}(//s*)//?'\"//?(.*)"),
    1079                     Pattern.compile("(?is)^(.{" + firstWhere + "}.*?)<>//s{0,1}(//s*)//?'\"//?(.*)"),
    1080                     Pattern.compile("(?is)^(.{" + firstWhere + "}.*?[^<>])=//s{0,1}(//s*)//?'\"//?(.*)"), };
    1081             String[] replacements = { "$1 is not $2null$3", "$1 is not $2null$3", "$1 is $2null$3", };
    1082             for (int i = 0; i < patterns.length; i++) {
    1083                 Matcher matcher = patterns[i].matcher(sql);
    1084                 while (matcher.matches()) {
    1085                     sql = matcher.replaceAll(replacements[i]);
    1086                     matcher = patterns[i].matcher(sql);
    1087                 }
    1088             }
    1089         }
    1090         return sql.replaceAll("//?'\"//?", "null");
    1091     }
    1092
    1093     /***
    1094      * Find the first 'where' keyword in the sql.
    1095      *
    1096      * @param sql
    1097      * @return
    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 }