View Javadoc

    1   /*
    2    * $Id: Sql.java 4066 2006-09-20 17:26:36Z glaforge $
    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: 4066 $
    70   */
    71  public class Sql {
    72
    73      protected Logger log = Logger.getLogger(getClass().getName());
    74
    75      private DataSource dataSource;
    76
    77      private Connection useConnection;
    78
    79      /*** lets only warn of using deprecated methods once */
    80      private boolean warned;
    81
    82      // 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      */
    288     public static InOutParameter inout(final InParameter in){
    289         return new InOutParameter(){
    290             public int getType() {
    291                 return in.getType();
    292             }
    293             public Object getValue() {
    294                 return in.getValue();
    295             }
    296         };
    297     }
    298
    299     /***
    300      * Create a new ResultSetOutParameter
    301      * @param type the JDBC data type.
    302      * @return a ResultSetOutParameter
    303      */
    304     public static ResultSetOutParameter resultSet(final int type){
    305         return new ResultSetOutParameter(){
    306             public int getType() {
    307                 return type;
    308             }
    309         };
    310     }
    311
    312     /***
    313      * Creates a variable to be expanded in the Sql string rather
    314      * than representing an sql parameter.
    315      * @param object
    316      */
    317     public static ExpandedVariable expand(final Object object){
    318         return new ExpandedVariable(){
    319             public Object getObject() {
    320                 return object;
    321             }};
    322     }
    323
    324     /***
    325      * Constructs an SQL instance using the given DataSource. Each operation
    326      * will use a Connection from the DataSource pool and close it when the
    327      * operation is completed putting it back into the pool.
    328      *
    329      * @param dataSource
    330      */
    331     public Sql(DataSource dataSource) {
    332         this.dataSource = dataSource;
    333     }
    334
    335     /***
    336      * Construts an SQL instance using the given Connection. It is the callers
    337      * responsibility to close the Connection after the Sql instance has been
    338      * used. You can do this on the connection object directly or by calling the
    339      * {@link java.sql.Connection#close()}  method.
    340      *
    341      * @param connection
    342      */
    343     public Sql(Connection connection) {
    344         if (connection == null) {
    345             throw new NullPointerException("Must specify a non-null Connection");
    346         }
    347         this.useConnection = connection;
    348     }
    349
    350     public Sql(Sql parent) {
    351         this.dataSource = parent.dataSource;
    352         this.useConnection = parent.useConnection;
    353     }
    354
    355     public DataSet dataSet(String table) {
    356         return new DataSet(this, table);
    357     }
    358
    359     public DataSet dataSet(Class type) {
    360         return new DataSet(this, type);
    361     }
    362
    363     /***
    364      * Performs the given SQL query calling the closure with the result set
    365      */
    366     public void query(String sql, Closure closure) throws SQLException {
    367         Connection connection = createConnection();
    368         Statement statement = connection.createStatement();
    369         configure(statement);
    370         ResultSet results = null;
    371         try {
    372             log.fine(sql);
    373             results = statement.executeQuery(sql);
    374             closure.call(results);
    375         }
    376         catch (SQLException e) {
    377             log.log(Level.FINE, "Failed to execute: " + sql, e);
    378             throw e;
    379         }
    380         finally {
    381             closeResources(connection, statement, results);
    382         }
    383     }
    384
    385     /***
    386      * Performs the given SQL query with parameters calling the closure with the
    387      * result set
    388      */
    389     public void query(String sql, List params, Closure closure) throws SQLException {
    390         Connection connection = createConnection();
    391         PreparedStatement statement = null;
    392         ResultSet results = null;
    393         try {
    394             log.fine(sql);
    395             statement = connection.prepareStatement(sql);
    396             setParameters(params, statement);
    397             configure(statement);
    398             results = statement.executeQuery();
    399             closure.call(results);
    400         }
    401         catch (SQLException e) {
    402             log.log(Level.FINE, "Failed to execute: " + sql, e);
    403             throw e;
    404         }
    405         finally {
    406             closeResources(connection, statement, results);
    407         }
    408     }
    409
    410     /***
    411      * Performs the given SQL query calling the closure with the result set
    412      */
    413     public void query(GString gstring, Closure closure) throws SQLException {
    414         List params = getParameters(gstring);
    415         String sql = asSql(gstring, params);
    416         query(sql, params, closure);
    417     }
    418
    419     /***
    420      * @deprecated please use eachRow instead
    421      */
    422     public void queryEach(String sql, Closure closure) throws SQLException {
    423         warnDeprecated();
    424         eachRow(sql, closure);
    425     }
    426
    427     /***
    428      * Performs the given SQL query calling the closure with each row of the
    429      * result set
    430      */
    431     public void eachRow(String sql, Closure closure) throws SQLException {
    432         Connection connection = createConnection();
    433         Statement statement = connection.createStatement();
    434         configure(statement);
    435         ResultSet results = null;
    436         try {
    437             log.fine(sql);
    438             results = statement.executeQuery(sql);
    439
    440             GroovyResultSet groovyRS = new GroovyResultSet(results);
    441             while (groovyRS.next()) {
    442                 closure.call(groovyRS);
    443             }
    444         }
    445         catch (SQLException e) {
    446             log.log(Level.FINE, "Failed to execute: " + sql, e);
    447             throw e;
    448         }
    449         finally {
    450             closeResources(connection, statement, results);
    451         }
    452     }
    453
    454     /***
    455      * @deprecated please use eachRow instead
    456      */
    457     public void queryEach(String sql, List params, Closure closure) throws SQLException {
    458         warnDeprecated();
    459         eachRow(sql, params, closure);
    460     }
    461
    462     /***
    463      * Performs the given SQL query calling the closure with the result set
    464      */
    465     public void eachRow(String sql, List params, Closure closure) throws SQLException {
    466         Connection connection = createConnection();
    467         PreparedStatement statement = null;
    468         ResultSet results = null;
    469         try {
    470             log.fine(sql);
    471             statement = connection.prepareStatement(sql);
    472             setParameters(params, statement);
    473             configure(statement);
    474             results = statement.executeQuery();
    475
    476             GroovyResultSet groovyRS = new GroovyResultSet(results);
    477             while (groovyRS.next()) {
    478                 closure.call(groovyRS);
    479             }
    480         }
    481         catch (SQLException e) {
    482             log.log(Level.FINE, "Failed to execute: " + sql, e);
    483             throw e;
    484         }
    485         finally {
    486             closeResources(connection, statement, results);
    487         }
    488     }
    489
    490     /***
    491      * Performs the given SQL query calling the closure with the result set
    492      */
    493     public void eachRow(GString gstring, Closure closure) throws SQLException {
    494         List params = getParameters(gstring);
    495         String sql = asSql(gstring, params);
    496         eachRow(sql, params, closure);
    497     }
    498
    499     /***
    500      * @deprecated please use eachRow instead
    501      */
    502     public void queryEach(GString gstring, Closure closure) throws SQLException {
    503         warnDeprecated();
    504         eachRow(gstring, closure);
    505     }
    506
    507     /***
    508      * Performs the given SQL query and return the rows of the result set
    509      */
    510      public List rows(String sql) throws SQLException {
    511         List results = new ArrayList();
    512         Connection connection = createConnection();
    513         Statement statement = connection.createStatement();
    514         configure(statement);
    515         ResultSet rs = null;
    516         try {
    517             log.fine(sql);
    518             rs = statement.executeQuery(sql);
    519             while (rs.next()) {
    520                 ResultSetMetaData metadata = rs.getMetaData();
    521                 LinkedHashMap lhm = new LinkedHashMap(metadata.getColumnCount(),1,true);
    522                 for(int i=1 ; i<=metadata.getColumnCount() ; i++) {
    523                       lhm.put(metadata.getColumnName(i),rs.getObject(i));
    524                 }
    525                 GroovyRowResult row = new GroovyRowResult(lhm);
    526                 results.add(row);
    527             }
    528             return(results);
    529         }
    530         catch (SQLException e) {
    531             log.log(Level.FINE, "Failed to execute: " + sql, e);
    532             throw e;
    533         }
    534         finally {
    535             closeResources(connection, statement, rs);
    536         }
    537     }
    538
    539     /***
    540      * Performs the given SQL query and return the first row of the result set
    541      */
    542     public Object firstRow(String sql) throws SQLException {
    543         List rows = rows(sql);
    544         if (rows.isEmpty()) return null;
    545         return(rows.get(0));
    546     }
    547
    548     /***
    549      * Performs the given SQL query with the list of params and return
    550      * the rows of the result set
    551      */
    552     public List rows(String sql, List params) throws SQLException {
    553         List results = new ArrayList();
    554         Connection connection = createConnection();
    555         PreparedStatement statement = null;
    556         ResultSet rs = null;
    557         try {
    558             log.fine(sql);
    559             statement = connection.prepareStatement(sql);
    560             setParameters(params, statement);
    561             configure(statement);
    562             rs = statement.executeQuery();
    563             while (rs.next()) {
    564                 ResultSetMetaData metadata = rs.getMetaData();
    565                 LinkedHashMap lhm = new LinkedHashMap(metadata.getColumnCount(),1,true);
    566                 for(int i=1 ; i<=metadata.getColumnCount() ; i++) {
    567                     lhm.put(metadata.getColumnName(i),rs.getObject(i));
    568                 }
    569                 GroovyRowResult row = new GroovyRowResult(lhm);
    570                 results.add(row);
    571             }
    572             return(results);
    573         }
    574         catch (SQLException e) {
    575             log.log(Level.FINE, "Failed to execute: " + sql, e);
    576             throw e;
    577         }
    578         finally {
    579             closeResources(connection, statement, rs);
    580         }
    581     }
    582
    583      /***
    584       * Performs the given SQL query with the list of params and return
    585       * the first row of the result set
    586       */
    587     public Object firstRow(String sql, List params) throws SQLException {
    588          List rows = rows(sql, params);
    589          if (rows.isEmpty()) return null;
    590          return rows.get(0);
    591      }
    592
    593     /***
    594      * Executes the given piece of SQL
    595      */
    596     public boolean execute(String sql) throws SQLException {
    597         Connection connection = createConnection();
    598         Statement statement = null;
    599         try {
    600             log.fine(sql);
    601             statement = connection.createStatement();
    602             configure(statement);
    603             boolean isResultSet = statement.execute(sql);
    604             this.updateCount = statement.getUpdateCount();
    605             return isResultSet;
    606         }
    607         catch (SQLException e) {
    608             log.log(Level.FINE, "Failed to execute: " + sql, e);
    609             throw e;
    610         }
    611         finally {
    612             closeResources(connection, statement);
    613         }
    614     }
    615
    616     /***
    617      * Executes the given SQL update
    618      *
    619      * @return the number of rows updated
    620      */
    621     public int executeUpdate(String sql) throws SQLException {
    622         Connection connection = createConnection();
    623         Statement statement = null;
    624         try {
    625             log.fine(sql);
    626             statement = connection.createStatement();
    627             configure(statement);
    628             this.updateCount = statement.executeUpdate(sql);
    629             return this.updateCount;
    630         }
    631         catch (SQLException e) {
    632             log.log(Level.FINE, "Failed to execute: " + sql, e);
    633             throw e;
    634         }
    635         finally {
    636             closeResources(connection, statement);
    637         }
    638     }
    639
    640     /***
    641      * Executes the given SQL statement. See {@link #executeInsert(GString)}
    642      * for more details.
    643      * @param sql The SQL statement to execute.
    644      * @return A list of the auto-generated column values for each
    645      * inserted row.
    646      */
    647     public List executeInsert(String sql) throws SQLException {
    648         Connection connection = createConnection();
    649         Statement statement = null;
    650         try {
    651             log.fine(sql);
    652             statement = connection.createStatement();
    653             configure(statement);
    654             boolean hasResultSet = statement.execute(sql, Statement.RETURN_GENERATED_KEYS);
    655
    656             // Prepare a list to contain the auto-generated column
    657             // values, and then fetch them from the statement.
    658             List autoKeys = new ArrayList();
    659         	ResultSet keys = statement.getGeneratedKeys();
    660         	int count = keys.getMetaData().getColumnCount();
    661
    662         	// Copy the column values into a list of a list.
    663         	while (keys.next()) {
    664         		List rowKeys = new ArrayList(count);
    665         		for (int i = 1; i <= count; i++) {
    666         			rowKeys.add(keys.getObject(i));
    667         		}
    668
    669         		autoKeys.add(rowKeys);
    670         	}
    671
    672         	// Store the update count so that it can be retrieved by
    673         	// clients, and then return the list of auto-generated
    674         	// values.
    675         	this.updateCount = statement.getUpdateCount();
    676         	return autoKeys;
    677         }
    678         catch (SQLException e) {
    679             log.log(Level.FINE, "Failed to execute: " + sql, e);
    680             throw e;
    681         }
    682         finally {
    683             closeResources(connection, statement);
    684         }
    685     }
    686
    687     /***
    688      * Executes the given piece of SQL with parameters
    689      */
    690     public boolean execute(String sql, List params) throws SQLException {
    691         Connection connection = createConnection();
    692         PreparedStatement statement = null;
    693         try {
    694             log.fine(sql);
    695             statement = connection.prepareStatement(sql);
    696             setParameters(params, statement);
    697             configure(statement);
    698             boolean isResultSet = statement.execute();
    699             this.updateCount = statement.getUpdateCount();
    700             return isResultSet;
    701         }
    702         catch (SQLException e) {
    703             log.log(Level.FINE, "Failed to execute: " + sql, e);
    704             throw e;
    705         }
    706         finally {
    707             closeResources(connection, statement);
    708         }
    709     }
    710
    711     /***
    712      * Executes the given SQL update with parameters
    713      *
    714      * @return the number of rows updated
    715      */
    716     public int executeUpdate(String sql, List params) throws SQLException {
    717         Connection connection = createConnection();
    718         PreparedStatement statement = null;
    719         try {
    720             log.fine(sql);
    721             statement = connection.prepareStatement(sql);
    722             setParameters(params, statement);
    723             configure(statement);
    724             this.updateCount = statement.executeUpdate();
    725             return this.updateCount;
    726         }
    727         catch (SQLException e) {
    728             log.log(Level.FINE, "Failed to execute: " + sql, e);
    729             throw e;
    730         }
    731         finally {
    732             closeResources(connection, statement);
    733         }
    734     }
    735
    736     /***
    737      * Executes the given SQL statement with a particular list of
    738      * parameter values. See {@link #executeInsert(GString)} for
    739      * more details.
    740      * @param sql The SQL statement to execute.
    741      * @param params The parameter values that will be substituted
    742      * into the SQL statement's parameter slots.
    743      * @return A list of the auto-generated column values for each
    744      * inserted row.
    745      */
    746     public List executeInsert(String sql, List params) throws SQLException {
    747         // Now send the SQL to the database.
    748         Connection connection = createConnection();
    749         PreparedStatement statement = null;
    750         try {
    751             log.fine(sql);
    752
    753             // Prepare a statement for the SQL and then execute it.
    754             statement = connection.prepareStatement(sql);
    755             setParameters(params, statement);
    756             configure(statement);
    757             boolean hasResultSet = statement.execute(sql, Statement.RETURN_GENERATED_KEYS);
    758
    759             // Prepare a list to contain the auto-generated column
    760             // values, and then fetch them from the statement.
    761             List autoKeys = new ArrayList();
    762         	ResultSet keys = statement.getGeneratedKeys();
    763         	int count = keys.getMetaData().getColumnCount();
    764
    765         	// Copy the column values into a list of a list.
    766         	while (keys.next()) {
    767         		List rowKeys = new ArrayList(count);
    768         		for (int i = 1; i <= count; i++) {
    769         			rowKeys.add(keys.getObject(i));
    770         		}
    771
    772         		autoKeys.add(rowKeys);
    773         	}
    774
    775         	// Store the update count so that it can be retrieved by
    776         	// clients, and then return the list of auto-generated
    777         	// values.
    778         	this.updateCount = statement.getUpdateCount();
    779         	return autoKeys;
    780         }
    781         catch (SQLException e) {
    782             log.log(Level.FINE, "Failed to execute: " + sql, e);
    783             throw e;
    784         }
    785         finally {
    786             closeResources(connection, statement);
    787         }
    788     }
    789
    790     /***
    791      * Executes the given SQL with embedded expressions inside
    792      */
    793     public boolean execute(GString gstring) throws SQLException {
    794         List params = getParameters(gstring);
    795         String sql = asSql(gstring, params);
    796         return execute(sql, params);
    797     }
    798
    799     /***
    800      * Executes the given SQL update with embedded expressions inside
    801      *
    802      * @return the number of rows updated
    803      */
    804     public int executeUpdate(GString gstring) throws SQLException {
    805         List params = getParameters(gstring);
    806         String sql = asSql(gstring, params);
    807         return executeUpdate(sql, params);
    808     }
    809
    810     /***
    811      * <p>Executes the given SQL with embedded expressions inside, and
    812      * returns the values of any auto-generated colums, such as an
    813      * autoincrement ID field. These values can be accessed using
    814      * array notation. For example, to return the second auto-generated
    815      * column value of the third row, use <code>keys[3][1]</code>. The
    816      * method is designed to be used with SQL INSERT statements, but is
    817      * not limited to them.</p>
    818      * <p>The standard use for this method is when a table has an
    819      * autoincrement ID column and you want to know what the ID is for
    820      * a newly inserted row. In this example, we insert a single row
    821      * into a table in which the first column contains the autoincrement
    822      * ID:</p>
    823      * <pre>
    824      *     def sql = Sql.newInstance("jdbc:mysql://localhost:3306/groovy",
    825      *                               "user",
    826      *                               "password",
    827      *                               "com.mysql.jdbc.Driver")
    828      *
    829      *     def keys = sql.insert("insert into test_table (INT_DATA, STRING_DATA) "
    830      *                           + "VALUES (1, 'Key Largo')")
    831      *
    832      *     def id = keys[0][0]
    833      *
    834      *     // 'id' now contains the value of the new row's ID column.
    835      *     // It can be used to update an object representation's
    836      *     // id attribute for example.
    837      *     ...
    838      * </pre>
    839      * @return A list of column values representing each row's
    840      * auto-generated keys.
    841      */
    842     public List executeInsert(GString gstring) throws SQLException {
    843         List params = getParameters(gstring);
    844         String sql = asSql(gstring, params);
    845         return executeInsert(sql, params);
    846     }
    847
    848     /***
    849      * Performs a stored procedure call
    850      */
    851     public int call(String sql) throws Exception {
    852         return call(sql, Collections.EMPTY_LIST);
    853     }
    854
    855     /***
    856      * Performs a stored procedure call with the given parameters
    857      */
    858     public int call(String sql, List params) throws Exception {
    859         Connection connection = createConnection();
    860         CallableStatement statement = connection.prepareCall(sql);
    861         try {
    862             log.fine(sql);
    863             setParameters(params, statement);
    864             configure(statement);
    865             return statement.executeUpdate();
    866         }
    867         catch (SQLException e) {
    868             log.log(Level.FINE, "Failed to execute: " + sql, e);
    869             throw e;
    870         }
    871         finally {
    872             closeResources(connection, statement);
    873         }
    874     }
    875
    876     /***
    877      * Performs a stored procedure call with the given parameters.  The closure
    878      * is called once with all the out parameters.
    879      */
    880     public void call(String sql, List params, Closure closure) throws Exception {
    881         Connection connection = createConnection();
    882         CallableStatement statement = connection.prepareCall(sql);
    883         try {
    884             log.fine(sql);
    885             setParameters(params, statement);
    886             statement.execute();
    887             List results = new ArrayList();
    888             int indx = 0;
    889             int inouts = 0;
    890             for (Iterator iter = params.iterator(); iter.hasNext();) {
    891                 Object value = iter.next();
    892                 if(value instanceof OutParameter){
    893                     if(value instanceof ResultSetOutParameter){
    894                         results.add(new CallResultSet(statement,indx));
    895                     }else{
    896                         Object o = statement.getObject(indx+1);
    897                         if(o instanceof ResultSet){
    898                             results.add(new GroovyResultSet((ResultSet)o));
    899                         }else{
    900                             results.add(o);
    901                         }
    902                     }
    903                     inouts++;
    904                 }
    905                 indx++;
    906             }
    907             closure.call(results.toArray(new Object[inouts]));
    908         } catch (SQLException e) {
    909             log.log(Level.WARNING, "Failed to execute: " + sql, e);
    910             throw e;
    911         } finally {
    912             closeResources(connection, statement);
    913         }
    914     }
    915
    916     /***
    917      * Performs a stored procedure call with the given parameters
    918      */
    919     public int call(GString gstring) throws Exception {
    920         List params = getParameters(gstring);
    921         String sql = asSql(gstring, params);
    922         return call(sql, params);
    923     }
    924
    925
    926     /***
    927      * Performs a stored procedure call with the given parameters,
    928      * calling the closure once with all result objects.
    929      */
    930     public void call(GString gstring, Closure closure) throws Exception {
    931         List params = getParameters(gstring);
    932         String sql = asSql(gstring,params);
    933         call(sql, params,closure);
    934     }
    935
    936     /***
    937      * If this SQL object was created with a Connection then this method closes
    938      * the connection. If this SQL object was created from a DataSource then
    939      * this method does nothing.
    940      *
    941      * @throws SQLException
    942      */
    943     public void close() throws SQLException {
    944         if (useConnection != null) {
    945             useConnection.close();
    946         }
    947     }
    948
    949     public DataSource getDataSource() {
    950         return dataSource;
    951     }
    952
    953
    954     public void commit() {
    955         try {
    956             this.useConnection.commit();
    957         }
    958         catch (SQLException e) {
    959             log.log(Level.SEVERE, "Caught exception commiting connection: " + e, e);
    960         }
    961     }
    962
    963     public void rollback() {
    964         try {
    965             this.useConnection.rollback();
    966         }
    967         catch (SQLException e) {
    968             log.log(Level.SEVERE, "Caught exception rollbacking connection: " + e, e);
    969         }
    970     }
    971
    972     /***
    973      * @return Returns the updateCount.
    974      */
    975     public int getUpdateCount() {
    976         return updateCount;
    977     }
    978
    979     /***
    980      * If this instance was created with a single Connection then the connection
    981      * is returned. Otherwise if this instance was created with a DataSource
    982      * then this method returns null
    983      *
    984      * @return the connection wired into this object, or null if this object
    985      *         uses a DataSource
    986      */
    987     public Connection getConnection() {
    988         return useConnection;
    989     }
    990
    991
    992     /***
    993      * Allows a closure to be passed in to configure the JDBC statements before they are executed
    994      * to do things like set the query size etc.
    995      *
    996      * @param configureStatement
    997      */
    998     public void withStatement(Closure configureStatement) {
    999         this.configureStatement = configureStatement;
    1000     }
    1001
    1002     // Implementation methods
    1003     //-------------------------------------------------------------------------
    1004
    1005     /***
    1006      * @return the SQL version of the given query using ? instead of any
    1007      *         parameter
    1008      */
    1009     protected String asSql(GString gstring, List values) {
    1010         String[] strings = gstring.getStrings();
    1011         if (strings.length <= 0) {
    1012             throw new IllegalArgumentException("No SQL specified in GString: " + gstring);
    1013         }
    1014         boolean nulls = false;
    1015         StringBuffer buffer = new StringBuffer();
    1016         boolean warned = false;
    1017         Iterator iter = values.iterator();
    1018         for (int i = 0; i < strings.length; i++) {
    1019             String text = strings[i];
    1020             if (text != null) {
    1021                 buffer.append(text);
    1022             }
    1023             if (iter.hasNext()) {
    1024                 Object value = iter.next();
    1025                 if (value != null) {
    1026                     if(value instanceof ExpandedVariable){
    1027                         buffer.append(((ExpandedVariable)value).getObject());
    1028                         iter.remove();
    1029                     }else{
    1030                         boolean validBinding = true;
    1031                         if (i < strings.length - 1) {
    1032                             String nextText = strings[i + 1];
    1033                             if ((text.endsWith("\"") || text.endsWith("'")) && (nextText.startsWith("'") || nextText.startsWith("\""))) {
    1034                                 if (!warned) {
    1035                                     log.warning("In Groovy SQL please do not use quotes around dynamic expressions " +
    1036                                             "(which start with $) as this means we cannot use a JDBC PreparedStatement " +
    1037                                             "and so is a security hole. Groovy has worked around your mistake but the security hole is still there. " +
    1038                                             "The expression so far is: " + buffer.toString() + "?" + nextText);
    1039                                     warned = true;
    1040                                 }
    1041                                 buffer.append(value);
    1042                                 iter.remove();
    1043                                 validBinding = false;
    1044                             }
    1045                         }
    1046                         if (validBinding) {
    1047                             buffer.append("?");
    1048                         }
    1049                     }
    1050                 }
    1051                 else {
    1052                     nulls = true;
    1053                     buffer.append("?'\"?"); // will replace these with nullish
    1054                     // values
    1055                 }
    1056             }
    1057         }
    1058         String sql = buffer.toString();
    1059         if (nulls) {
    1060             sql = nullify(sql);
    1061         }
    1062         return sql;
    1063     }
    1064
    1065     /***
    1066      * replace ?'"? references with NULLish
    1067      *
    1068      * @param sql
    1069      */
    1070     protected String nullify(String sql) {
    1071         /*
    1072          * Some drivers (Oracle classes12.zip) have difficulty resolving data
    1073          * type if setObject(null). We will modify the query to pass 'null', 'is
    1074          * null', and 'is not null'
    1075          */
    1076         //could be more efficient by compiling expressions in advance.
    1077         int firstWhere = findWhereKeyword(sql);
    1078         if (firstWhere >= 0) {
    1079             Pattern[] patterns = { Pattern.compile("(?is)^(.{" + firstWhere + "}.*?)!=//s{0,1}(//s*)//?'\"//?(.*)"),
    1080                     Pattern.compile("(?is)^(.{" + firstWhere + "}.*?)<>//s{0,1}(//s*)//?'\"//?(.*)"),
    1081                     Pattern.compile("(?is)^(.{" + firstWhere + "}.*?[^<>])=//s{0,1}(//s*)//?'\"//?(.*)"), };
    1082             String[] replacements = { "$1 is not $2null$3", "$1 is not $2null$3", "$1 is $2null$3", };
    1083             for (int i = 0; i < patterns.length; i++) {
    1084                 Matcher matcher = patterns[i].matcher(sql);
    1085                 while (matcher.matches()) {
    1086                     sql = matcher.replaceAll(replacements[i]);
    1087                     matcher = patterns[i].matcher(sql);
    1088                 }
    1089             }
    1090         }
    1091         return sql.replaceAll("//?'\"//?", "null");
    1092     }
    1093
    1094     /***
    1095      * Find the first 'where' keyword in the sql.
    1096      *
    1097      * @param sql
    1098      */
    1099     protected int findWhereKeyword(String sql) {
    1100         char[] chars = sql.toLowerCase().toCharArray();
    1101         char[] whereChars = "where".toCharArray();
    1102         int i = 0;
    1103         boolean inString = false; //TODO: Cater for comments?
    1104         boolean noWhere = true;
    1105         int inWhere = 0;
    1106         while (i < chars.length && noWhere) {
    1107             switch (chars[i]) {
    1108                 case '\'':
    1109                     if (inString) {
    1110                         inString = false;
    1111                     }
    1112                     else {
    1113                         inString = true;
    1114                     }
    1115                     break;
    1116                 default:
    1117                     if (!inString && chars[i] == whereChars[inWhere]) {
    1118                         inWhere++;
    1119                         if (inWhere == whereChars.length) {
    1120                             return i;
    1121                         }
    1122                     }
    1123             }
    1124             i++;
    1125         }
    1126         return -1;
    1127     }
    1128
    1129     /***
    1130      * @return extracts the parameters from the expression as a List
    1131      */
    1132     protected List getParameters(GString gstring) {
    1133         Object[] values = gstring.getValues();
    1134         List answer = new ArrayList(values.length);
    1135         for (int i = 0; i < values.length; i++) {
    1136             if (values[i] != null) {
    1137                 answer.add(values[i]);
    1138             }
    1139         }
    1140         return answer;
    1141     }
    1142
    1143     /***
    1144      * Appends the parameters to the given statement
    1145      */
    1146     protected void setParameters(List params, PreparedStatement statement) throws SQLException {
    1147         int i = 1;
    1148         for (Iterator iter = params.iterator(); iter.hasNext();) {
    1149             Object value = iter.next();
    1150             setObject(statement, i++, value);
    1151         }
    1152     }
    1153
    1154     /***
    1155      * Strategy method allowing derived classes to handle types differently
    1156      * such as for CLOBs etc.
    1157      */
    1158     protected void setObject(PreparedStatement statement, int i, Object value)
    1159         throws SQLException {
    1160         if (value instanceof InParameter  || value instanceof OutParameter) {
    1161             if(value instanceof InParameter){
    1162                 InParameter in = (InParameter) value;
    1163                 Object val = in.getValue();
    1164                 if (null == val) {
    1165                     statement.setNull(i, in.getType());
    1166                 } else {
    1167                     statement.setObject(i, val, in.getType());
    1168                 }
    1169             }
    1170             if(value instanceof OutParameter){
    1171                 try{
    1172                     OutParameter out = (OutParameter)value;
    1173                     ((CallableStatement)statement).registerOutParameter(i,out.getType());
    1174                 }catch(ClassCastException e){
    1175                     throw new SQLException("Cannot register out parameter.");
    1176                 }
    1177             }
    1178         } else {
    1179             statement.setObject(i, value);
    1180         }
    1181     }
    1182
    1183     protected Connection createConnection() throws SQLException {
    1184         if (dataSource != null) {
    1185             //Use a doPrivileged here as many different properties need to be
    1186             // read, and the policy
    1187             //shouldn't have to list them all.
    1188             Connection con = null;
    1189             try {
    1190                 con = (Connection) AccessController.doPrivileged(new PrivilegedExceptionAction() {
    1191                     public Object run() throws SQLException {
    1192                         return dataSource.getConnection();
    1193                     }
    1194                 });
    1195             }
    1196             catch (PrivilegedActionException pae) {
    1197                 Exception e = pae.getException();
    1198                 if (e instanceof SQLException) {
    1199                     throw (SQLException) e;
    1200                 }
    1201                 else {
    1202                     throw (RuntimeException) e;
    1203                 }
    1204             }
    1205             return con;
    1206         }
    1207         else {
    1208             //System.out.println("createConnection returning: " +
    1209             // useConnection);
    1210             return useConnection;
    1211         }
    1212     }
    1213
    1214     protected void closeResources(Connection connection, Statement statement, ResultSet results) {
    1215         if (results != null) {
    1216             try {
    1217                 results.close();
    1218             }
    1219             catch (SQLException e) {
    1220                 log.log(Level.SEVERE, "Caught exception closing resultSet: " + e, e);
    1221             }
    1222         }
    1223         closeResources(connection, statement);
    1224     }
    1225
    1226     protected void closeResources(Connection connection, Statement statement) {
    1227         if (statement != null) {
    1228             try {
    1229                 statement.close();
    1230             }
    1231             catch (SQLException e) {
    1232                 log.log(Level.SEVERE, "Caught exception closing statement: " + e, e);
    1233             }
    1234         }
    1235         if (dataSource != null) {
    1236             try {
    1237                 connection.close();
    1238             }
    1239             catch (SQLException e) {
    1240                 log.log(Level.SEVERE, "Caught exception closing connection: " + e, e);
    1241             }
    1242         }
    1243     }
    1244
    1245     private void warnDeprecated() {
    1246         if (!warned) {
    1247             warned = true;
    1248             log.warning("queryEach() is deprecated, please use eachRow() instead");
    1249         }
    1250     }
    1251
    1252     /***
    1253      * Provides a hook to be able to configure JDBC statements, such as to configure
    1254      *
    1255      * @param statement
    1256      */
    1257     protected void configure(Statement statement) {
    1258         if (configureStatement != null) {
    1259             configureStatement.call(statement);
    1260         }
    1261     }
    1262 }