All Downloads are FREE. Search and download functionalities are using the official Maven repository.

groovy.sql.Sql Maven / Gradle / Ivy

There is a newer version: 3.0.23
Show newest version
/*
 *  Licensed to the Apache Software Foundation (ASF) under one
 *  or more contributor license agreements.  See the NOTICE file
 *  distributed with this work for additional information
 *  regarding copyright ownership.  The ASF licenses this file
 *  to you under the Apache License, Version 2.0 (the
 *  "License"); you may not use this file except in compliance
 *  with the License.  You may obtain a copy of the License at
 *
 *    http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing,
 *  software distributed under the License is distributed on an
 *  "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 *  KIND, either express or implied.  See the License for the
 *  specific language governing permissions and limitations
 *  under the License.
 */
package groovy.sql;

import groovy.lang.Closure;
import groovy.lang.GString;
import groovy.lang.MissingPropertyException;
import groovy.lang.Tuple;
import groovy.transform.NamedParam;
import groovy.transform.stc.ClosureParams;
import groovy.transform.stc.SimpleType;
import org.codehaus.groovy.runtime.InvokerHelper;

import javax.sql.DataSource;

import java.security.AccessController;
import java.security.PrivilegedActionException;
import java.security.PrivilegedExceptionAction;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import static org.apache.groovy.sql.extensions.SqlExtensions.toRowResult;

/**
 * A facade over Java's normal JDBC APIs providing greatly simplified
 * resource management and result set handling. Under the covers the
 * facade hides away details associated with getting connections,
 * constructing and configuring statements, interacting with the
 * connection, closing resources and logging errors. Special
 * features of the facade include using closures to iterate
 * through result sets, a special GString syntax for representing
 * prepared statements and treating result sets like collections
 * of maps with the normal Groovy collection methods available.
 *
 * 

Typical usage

* * First you need to set up your sql instance. There are several constructors * and a few newInstance factory methods available to do this. * In simple cases, you can just provide * the necessary details to set up a connection (e.g. for hsqldb): *
 * def db = [url:'jdbc:hsqldb:mem:testDB', user:'sa', password:'', driver:'org.hsqldb.jdbc.JDBCDriver']
 * def sql = Sql.newInstance(db.url, db.user, db.password, db.driver)
 * 
* or if you have an existing connection (perhaps from a connection pool) or a * datasource use one of the constructors: *
 * def sql = new Sql(datasource)
 * 
* Now you can invoke sql, e.g. to create a table: *
 * sql.execute '''
 *     create table PROJECT (
 *         id integer not null,
 *         name varchar(50),
 *         url varchar(100),
 *     )
 * '''
 * 
* Or insert a row using JDBC PreparedStatement inspired syntax: *
 * def params = [10, 'Groovy', 'http://groovy.codehaus.org']
 * sql.execute 'insert into PROJECT (id, name, url) values (?, ?, ?)', params
 * 
* Or insert a row using GString syntax: *
 * def map = [id:20, name:'Grails', url:'http://grails.codehaus.org']
 * sql.execute "insert into PROJECT (id, name, url) values ($map.id, $map.name, $map.url)"
 * 
* Or a row update: *
 * def newUrl = 'http://grails.org'
 * def project = 'Grails'
 * sql.executeUpdate "update PROJECT set url=$newUrl where name=$project"
 * 
* Now try a query using eachRow: *
 * println 'Some GR8 projects:'
 * sql.eachRow('select * from PROJECT') { row {@code ->}
 *     println "${row.name.padRight(10)} ($row.url)"
 * }
 * 
* Which will produce something like this: *
 * Some GR8 projects:
 * Groovy     (http://groovy.codehaus.org)
 * Grails     (http://grails.org)
 * Griffon    (http://griffon.codehaus.org)
 * Gradle     (http://gradle.org)
 * 
* Now try a query using rows: *
 * def rows = sql.rows("select * from PROJECT where name like 'Gra%'")
 * assert rows.size() == 2
 * println rows.join('\n')
 * 
* with output like this: *
 * [ID:20, NAME:Grails, URL:http://grails.org]
 * [ID:40, NAME:Gradle, URL:http://gradle.org]
 * 
* Also, eachRow and rows support paging. Here's an example: *
 * sql.eachRow('select * from PROJECT', 2, 2) { row {@code ->}
 *     println "${row.name.padRight(10)} ($row.url)"
 * }
 * 
* Which will start at the second row and return a maximum of 2 rows. Here's an example result: *
 * Grails     (http://grails.org)
 * Griffon    (http://griffon.codehaus.org)
 * 
* * Finally, we should clean up: *
 * sql.close()
 * 
* If we are using a DataSource and we haven't enabled statement caching, then * strictly speaking the final close() method isn't required - as all connection * handling is performed transparently on our behalf; however, it doesn't hurt to * have it there as it will return silently in that case. *

* If instead of newInstance you use withInstance, then * close() will be called automatically for you. * *

Avoiding SQL injection

* * If you find yourself creating queries based on any kind of input from the user or * a 3rd party application you might wish to avoid the pure string method variants in this class. * While this is safe: * sql.firstRow('select * from PersonTable') * This example is potentially at risk of SQL injection: * sql.firstRow('select * from PersonTable where SurnameColumn = ' + userInput) * This in turn will be fine if 'userInput' is something like 'Smith' but maybe * not so fine if 'userInput' is something like 'Smith; DROP table PersonTable'. * Instead, use one of the variants with parameters and placeholders: * sql.firstRow("select * from PersonTable where SurnameColumn = ?", [userInput]) * or the GString variants which will be converted to the placeholder variants under the covers: * sql.firstRow("select * from PersonTable where SurnameColumn = $userInput") * or the named parameter variants discussed next. * *

Named and named ordinal parameters

* * Several of the methods in this class (ones which have a String-based sql query and params in * a List<Object> or Object[] or Map) support named or named ordinal parameters. * These methods are useful for queries with large numbers of parameters - though the GString * variations are often preferred in such cases too. Reminder: when you see a variant with Object[] as * the type of the last parameter, Groovy allows vararg style parameters so you don't explicitly need to * create an Object[] and if the first parameter is of type Map, Groovy supports named arguments - examples * of both are contained in the examples below. *

* Named parameter queries use placeholder values in the query String. Two forms are supported * ':propname1' and '?.propname2'. For these variations, a single model object is * supplied in the parameter list/array/map. The propname refers to a property of that model object. * The model object could be a map, Expando or domain class instance. Here are some examples: *

 * // using rows() with a named parameter with the parameter supplied in a map
 * println sql.rows('select * from PROJECT where name=:foo', [foo:'Gradle'])
 * // as above for eachRow()
 * sql.eachRow('select * from PROJECT where name=:foo', [foo:'Gradle']) {
 *     // process row
 * }
 *
 * // an example using both the ':' and '?.' variants of the notation
 * println sql.rows('select * from PROJECT where name=:foo and id=?.bar', [foo:'Gradle', bar:40])
 * // as above but using Groovy's named arguments instead of an explicit map
 * println sql.rows('select * from PROJECT where name=:foo and id=?.bar', foo:'Gradle', bar:40)
 *
 * // an example showing rows() with a domain object instead of a map
 * class MyDomainClass { def baz = 'Griffon' }
 * println sql.rows('select * from PROJECT where name=?.baz', new MyDomainClass())
 * // as above for eachRow() with the domain object supplied in a list
 * sql.eachRow('select * from PROJECT where name=?.baz', [new MyDomainClass()]) {
 *     // process row
 * }
 * 
* Named ordinal parameter queries have multiple model objects with the index number (starting * at 1) also supplied in the placeholder. Only the question mark variation of placeholder is supported. * Here are some examples: *
 * // an example showing the model objects as vararg style parameters (since rows() has an Object[] variant)
 * println sql.rows("select * from PROJECT where name=?1.baz and id=?2.num", new MyDomainClass(), [num:30])
 *
 * // an example showing the model objects (one domain class and one map) provided in a list
 * sql.eachRow("select * from PROJECT where name=?1.baz and id=?2.num", [new MyDomainClass(), [num:30]]) {
 *     // do something with row
 * }
 * 
* *

More details

* * See the method and constructor JavaDoc for more details. *

* For advanced usage, the class provides numerous extension points for overriding the * facade behavior associated with the various aspects of managing * the interaction with the underlying database. *

* This class is not thread-safe. */ public class Sql implements AutoCloseable { /** * Hook to allow derived classes to access the log */ protected static final Logger LOG = Logger.getLogger(Sql.class.getName()); private static final List EMPTY_LIST = Collections.emptyList(); private static final int USE_COLUMN_NAMES = -1; private static final String[] EMPTY_STRING_ARRAY = new String[0]; private DataSource dataSource; private Connection useConnection; private int resultSetType = ResultSet.TYPE_FORWARD_ONLY; private int resultSetConcurrency = ResultSet.CONCUR_READ_ONLY; private int resultSetHoldability = -1; // store last row update count for executeUpdate, executeInsert and execute private int updateCount = 0; // allows a closure to be used to configure Statement objects before its use private Closure configureStatement; private boolean cacheConnection; private boolean cacheStatements; private boolean cacheNamedQueries = true; private boolean enableNamedQueries = true; private boolean withinBatch; private final Map statementCache = new HashMap(); private final Map namedParamSqlCache = new HashMap(); private final Map> namedParamIndexPropCache = new HashMap>(); private List keyColumnNames; /** * Creates a new Sql instance given a JDBC connection URL. * * @param url a database url of the form * jdbc:subprotocol:subname * @return a new Sql instance with a connection * @throws SQLException if a database access error occurs */ public static Sql newInstance(String url) throws SQLException { Connection connection = DriverManager.getConnection(url); return new Sql(connection); } /** * Invokes a closure passing it a new Sql instance created from the given JDBC connection URL. * The created connection will be closed if required. * * @param url a database url of the form * jdbc:subprotocol:subname * @param c the Closure to call * @see #newInstance(String) * @throws SQLException if a database access error occurs */ public static void withInstance(String url, @ClosureParams(value=SimpleType.class, options="groovy.sql.Sql") Closure c) throws SQLException { try (Sql sql = newInstance(url)) { c.call(sql); } } /** * Creates a new Sql instance given a JDBC connection URL * and some properties. * * @param url a database url of the form * jdbc:subprotocol:subname * @param properties a list of arbitrary string tag/value pairs * as connection arguments; normally at least a "user" and * "password" property should be included * @return a new Sql instance with a connection * @throws SQLException if a database access error occurs */ public static Sql newInstance(String url, Properties properties) throws SQLException { Connection connection = DriverManager.getConnection(url, properties); return new Sql(connection); } /** * Invokes a closure passing it a new Sql instance created from the given JDBC connection URL and properties. * The created connection will be closed if required. * * @param url a database url of the form * jdbc:subprotocol:subname * @param properties a list of arbitrary string tag/value pairs * as connection arguments; normally at least a "user" and * "password" property should be included * @param c the Closure to call * @see #newInstance(String, java.util.Properties) * @throws SQLException if a database access error occurs */ public static void withInstance(String url, Properties properties, @ClosureParams(value=SimpleType.class, options="groovy.sql.Sql") Closure c) throws SQLException { try (Sql sql = newInstance(url, properties)) { c.call(sql); } } /** * Creates a new Sql instance given a JDBC connection URL, * some properties and a driver class name. * * @param url a database url of the form * jdbc:subprotocol:subname * @param properties a list of arbitrary string tag/value pairs * as connection arguments; normally at least a "user" and * "password" property should be included * @param driverClassName the fully qualified class name of the driver class * @return a new Sql instance with a connection * @throws SQLException if a database access error occurs * @throws ClassNotFoundException if the driver class cannot be found or loaded */ public static Sql newInstance(String url, Properties properties, String driverClassName) throws SQLException, ClassNotFoundException { loadDriver(driverClassName); return newInstance(url, properties); } /** * Invokes a closure passing it a new Sql instance created from the given JDBC connection URL, * properties and driver classname. The created connection will be closed if required. * * @param url a database url of the form * jdbc:subprotocol:subname * @param properties a list of arbitrary string tag/value pairs * as connection arguments; normally at least a "user" and * "password" property should be included * @param driverClassName the fully qualified class name of the driver class * @param c the Closure to call * @see #newInstance(String, java.util.Properties, String) * @throws SQLException if a database access error occurs * @throws ClassNotFoundException if the driver class cannot be found or loaded */ public static void withInstance(String url, Properties properties, String driverClassName, @ClosureParams(value=SimpleType.class, options="groovy.sql.Sql") Closure c) throws SQLException, ClassNotFoundException { try (Sql sql = newInstance(url, properties, driverClassName)) { c.call(sql); } } /** * Creates a new Sql instance given a JDBC connection URL, * a username and a password. * * @param url a database url of the form * jdbc:subprotocol:subname * @param user the database user on whose behalf the connection * is being made * @param password the user's password * @return a new Sql instance with a connection * @throws SQLException if a database access error occurs */ public static Sql newInstance(String url, String user, String password) throws SQLException { Connection connection = DriverManager.getConnection(url, user, password); return new Sql(connection); } /** * Invokes a closure passing it a new Sql instance created from the given JDBC connection URL, user and password. * The created connection will be closed if required. * * @param url a database url of the form * jdbc:subprotocol:subname * @param user the database user on whose behalf the connection * is being made * @param password the user's password * @param c the Closure to call * @see #newInstance(String, String, String) * @throws SQLException if a database access error occurs */ public static void withInstance(String url, String user, String password, @ClosureParams(value=SimpleType.class, options="groovy.sql.Sql") Closure c) throws SQLException { try (Sql sql = newInstance(url, user, password)) { c.call(sql); } } /** * Creates a new Sql instance given a JDBC connection URL, * a username, a password and a driver class name. * * @param url a database url of the form * jdbc:subprotocol:subname * @param user the database user on whose behalf the connection * is being made * @param password the user's password * @param driverClassName the fully qualified class name of the driver class * @return a new Sql instance with a connection * @throws SQLException if a database access error occurs * @throws ClassNotFoundException if the driver class cannot be found or loaded */ public static Sql newInstance(String url, String user, String password, String driverClassName) throws SQLException, ClassNotFoundException { loadDriver(driverClassName); return newInstance(url, user, password); } /** * Invokes a closure passing it a new Sql instance created from the given JDBC connection URL. * The created connection will be closed if required. * * @param url a database url of the form * jdbc:subprotocol:subname * @param user the database user on whose behalf the connection * is being made * @param password the user's password * @param driverClassName the fully qualified class name of the driver class * @param c the Closure to call * @see #newInstance(String, String, String, String) * @throws SQLException if a database access error occurs * @throws ClassNotFoundException if the driver class cannot be found or loaded */ public static void withInstance(String url, String user, String password, String driverClassName, @ClosureParams(value=SimpleType.class, options="groovy.sql.Sql") Closure c) throws SQLException, ClassNotFoundException { try (Sql sql = newInstance(url, user, password, driverClassName)) { c.call(sql); } } /** * Creates a new Sql instance given a JDBC connection URL * and a driver class name. * * @param url a database url of the form * jdbc:subprotocol:subname * @param driverClassName the fully qualified class name of the driver class * @return a new Sql instance with a connection * @throws SQLException if a database access error occurs * @throws ClassNotFoundException if the driver class cannot be found or loaded */ public static Sql newInstance(String url, String driverClassName) throws SQLException, ClassNotFoundException { loadDriver(driverClassName); return newInstance(url); } /** * Invokes a closure passing it a new Sql instance created from the given JDBC connection URL. * The created connection will be closed if required. * * @param url a database url of the form * jdbc:subprotocol:subname * @param driverClassName the fully qualified class name of the driver class * @param c the Closure to call * @see #newInstance(String, String) * @throws SQLException if a database access error occurs * @throws ClassNotFoundException if the driver class cannot be found or loaded */ public static void withInstance(String url, String driverClassName, @ClosureParams(value=SimpleType.class, options="groovy.sql.Sql") Closure c) throws SQLException, ClassNotFoundException { try (Sql sql = newInstance(url, driverClassName)) { c.call(sql); } } /** * Creates a new Sql instance given parameters in a Map. * Recognized keys for the Map include: *
     * driverClassName the fully qualified class name of the driver class
     * driver          a synonym for driverClassName
     * url             a database url of the form: jdbc:subprotocol:subname
     * user            the database user on whose behalf the connection is being made
     * password        the user's password
     * properties      a list of arbitrary string tag/value pairs as connection arguments;
     *                 normally at least a "user" and "password" property should be included
     * other           any of the public setter methods of this class may be used with property notation
     *                 e.g. cacheStatements: true, resultSetConcurrency: ResultSet.CONCUR_READ_ONLY
     * 
* Of these, 'url' is required. Others may be needed depending on your database.
* If 'properties' is supplied, neither 'user' nor 'password' should be supplied.
* If one of 'user' or 'password' is supplied, both should be supplied. *

* Example usage: *

     * import groovy.sql.Sql
     * import static java.sql.ResultSet.*
     *
     * def sql = Sql.newInstance(
     *     url:'jdbc:hsqldb:mem:testDB',
     *     user:'sa',
     *     password:'',
     *     driver:'org.hsqldb.jdbc.JDBCDriver',
     *     cacheStatements: true,
     *     resultSetConcurrency: CONCUR_READ_ONLY
     * )
     * 
* * @param args a Map contain further arguments * @return a new Sql instance with a connection * @throws SQLException if a database access error occurs * @throws ClassNotFoundException if the driver class cannot be found or loaded */ public static Sql newInstance( @NamedParam(value = "url", type = String.class, required = true) @NamedParam(value = "properties", type = Properties.class) @NamedParam(value = "driverClassName", type = String.class) @NamedParam(value = "driver", type = String.class) @NamedParam(value = "user", type = String.class) @NamedParam(value = "password", type = String.class) @NamedParam(value = "cacheNamedQueries", type = Boolean.class) @NamedParam(value = "cacheStatements", type = Boolean.class) @NamedParam(value = "enableNamedQueries", type = Boolean.class) @NamedParam(value = "resultSetConcurrency", type = Integer.class) @NamedParam(value = "resultSetHoldability", type = Integer.class) @NamedParam(value = "resultSetType", type = Integer.class) // TODO below will be deleted once we fix type checker to understand // readonly Map otherwise seen as Map @NamedParam(value = "unused", type = Object.class) Map args) throws SQLException, ClassNotFoundException { if (!args.containsKey("url")) throw new IllegalArgumentException("Argument 'url' is required"); if (args.get("url") == null) throw new IllegalArgumentException("Argument 'url' must not be null"); if (args.containsKey("driverClassName") && args.containsKey("driver")) throw new IllegalArgumentException("Only one of 'driverClassName' and 'driver' should be provided"); // Make a copy so destructive operations will not affect the caller Map sqlArgs = new HashMap(args); sqlArgs.remove("unused"); // TODO remove Object driverClassName = sqlArgs.remove("driverClassName"); if (driverClassName == null) driverClassName = sqlArgs.remove("driver"); if (driverClassName != null) loadDriver(driverClassName.toString()); Properties props = (Properties) sqlArgs.remove("properties"); if (props != null && sqlArgs.containsKey("user")) throw new IllegalArgumentException("Only one of 'properties' and 'user' should be supplied"); if (props != null && sqlArgs.containsKey("password")) throw new IllegalArgumentException("Only one of 'properties' and 'password' should be supplied"); if (sqlArgs.containsKey("user") ^ sqlArgs.containsKey("password")) throw new IllegalArgumentException("Found one but not both of 'user' and 'password'"); Object url = sqlArgs.remove("url"); Connection connection; LOG.fine("url = " + url); if (props != null) { connection = DriverManager.getConnection(url.toString(), props); if (LOG.isLoggable(Level.FINE)) { if (!props.containsKey("password")) { LOG.fine("props = " + props); } else { // don't log the password Properties propsCopy = new Properties(); propsCopy.putAll(props); propsCopy.setProperty("password", "***"); LOG.fine("props = " + propsCopy); } } } else if (sqlArgs.containsKey("user")) { Object user = sqlArgs.remove("user"); LOG.fine("user = " + user); Object password = sqlArgs.remove("password"); LOG.fine("password = " + (password == null ? "null" : "***")); connection = DriverManager.getConnection(url.toString(), (user == null ? null : user.toString()), (password == null ? null : password.toString())); } else { LOG.fine("No user/password specified"); connection = DriverManager.getConnection(url.toString()); } Sql result = (Sql) InvokerHelper.invokeConstructorOf(Sql.class, sqlArgs); result.setConnection(connection); return result; } /** * Invokes a closure passing it a new Sql instance created from the given map of arguments. * The created connection will be closed if required. * * @param args a Map contain further arguments * @param c the Closure to call * @see #newInstance(java.util.Map) * @throws SQLException if a database access error occurs * @throws ClassNotFoundException if the driver class cannot be found or loaded */ public static void withInstance( @NamedParam(value = "url", type = String.class, required = true) @NamedParam(value = "properties", type = Properties.class) @NamedParam(value = "driverClassName", type = String.class) @NamedParam(value = "driver", type = String.class) @NamedParam(value = "user", type = String.class) @NamedParam(value = "password", type = String.class) @NamedParam(value = "cacheNamedQueries", type = Boolean.class) @NamedParam(value = "cacheStatements", type = Boolean.class) @NamedParam(value = "enableNamedQueries", type = Boolean.class) @NamedParam(value = "resultSetConcurrency", type = Integer.class) @NamedParam(value = "resultSetHoldability", type = Integer.class) @NamedParam(value = "resultSetType", type = Integer.class) // TODO below will be deleted once we fix type checker to understand // readonly Map otherwise seen as Map @NamedParam(value = "unused", type = Object.class) Map args, @ClosureParams(value=SimpleType.class, options="groovy.sql.Sql") Closure c) throws SQLException, ClassNotFoundException { try (Sql sql = newInstance(args)) { c.call(sql); } } /** * Gets the resultSetType for statements created using the connection. * * @return the current resultSetType value * @since 1.5.2 */ public int getResultSetType() { return resultSetType; } /** * Sets the resultSetType for statements created using the connection. * May cause SQLFeatureNotSupportedException exceptions to occur if the * underlying database doesn't support the requested type value. * * @param resultSetType one of the following ResultSet * constants: * ResultSet.TYPE_FORWARD_ONLY, * ResultSet.TYPE_SCROLL_INSENSITIVE, or * ResultSet.TYPE_SCROLL_SENSITIVE * @since 1.5.2 */ public void setResultSetType(int resultSetType) { this.resultSetType = resultSetType; } /** * Gets the resultSetConcurrency for statements created using the connection. * * @return the current resultSetConcurrency value * @since 1.5.2 */ public int getResultSetConcurrency() { return resultSetConcurrency; } /** * Sets the resultSetConcurrency for statements created using the connection. * May cause SQLFeatureNotSupportedException exceptions to occur if the * underlying database doesn't support the requested concurrency value. * * @param resultSetConcurrency one of the following ResultSet * constants: * ResultSet.CONCUR_READ_ONLY or * ResultSet.CONCUR_UPDATABLE * @since 1.5.2 */ public void setResultSetConcurrency(int resultSetConcurrency) { this.resultSetConcurrency = resultSetConcurrency; } /** * Gets the resultSetHoldability for statements created using the connection. * * @return the current resultSetHoldability value or -1 if not set * @since 1.5.2 */ public int getResultSetHoldability() { return resultSetHoldability; } /** * Sets the resultSetHoldability for statements created using the connection. * May cause SQLFeatureNotSupportedException exceptions to occur if the * underlying database doesn't support the requested holdability value. * * @param resultSetHoldability one of the following ResultSet * constants: * ResultSet.HOLD_CURSORS_OVER_COMMIT or * ResultSet.CLOSE_CURSORS_AT_COMMIT * @since 1.5.2 */ public void setResultSetHoldability(int resultSetHoldability) { this.resultSetHoldability = resultSetHoldability; } /** * Attempts to load the JDBC driver on the thread, current or system class * loaders * * @param driverClassName the fully qualified class name of the driver class * @throws ClassNotFoundException if the class cannot be found or loaded */ public static void loadDriver(String driverClassName) throws ClassNotFoundException { // let's try the thread context class loader first // let's try to use the system class loader try { Class.forName(driverClassName); } catch (ClassNotFoundException e) { try { Thread.currentThread().getContextClassLoader().loadClass(driverClassName); } catch (ClassNotFoundException e2) { // now let's try the classloader which loaded us try { Sql.class.getClassLoader().loadClass(driverClassName); } catch (ClassNotFoundException e3) { throw e; } } } } public static final OutParameter ARRAY = new OutParameter(){ public int getType() { return Types.ARRAY; }}; public static final OutParameter BIGINT = new OutParameter(){ public int getType() { return Types.BIGINT; }}; public static final OutParameter BINARY = new OutParameter(){ public int getType() { return Types.BINARY; }}; public static final OutParameter BIT = new OutParameter(){ public int getType() { return Types.BIT; }}; public static final OutParameter BLOB = new OutParameter(){ public int getType() { return Types.BLOB; }}; public static final OutParameter BOOLEAN = new OutParameter(){ public int getType() { return Types.BOOLEAN; }}; public static final OutParameter CHAR = new OutParameter(){ public int getType() { return Types.CHAR; }}; public static final OutParameter CLOB = new OutParameter(){ public int getType() { return Types.CLOB; }}; public static final OutParameter DATALINK = new OutParameter(){ public int getType() { return Types.DATALINK; }}; public static final OutParameter DATE = new OutParameter(){ public int getType() { return Types.DATE; }}; public static final OutParameter DECIMAL = new OutParameter(){ public int getType() { return Types.DECIMAL; }}; public static final OutParameter DISTINCT = new OutParameter(){ public int getType() { return Types.DISTINCT; }}; public static final OutParameter DOUBLE = new OutParameter(){ public int getType() { return Types.DOUBLE; }}; public static final OutParameter FLOAT = new OutParameter(){ public int getType() { return Types.FLOAT; }}; public static final OutParameter INTEGER = new OutParameter(){ public int getType() { return Types.INTEGER; }}; public static final OutParameter JAVA_OBJECT = new OutParameter(){ public int getType() { return Types.JAVA_OBJECT; }}; public static final OutParameter LONGVARBINARY = new OutParameter(){ public int getType() { return Types.LONGVARBINARY; }}; public static final OutParameter LONGVARCHAR = new OutParameter(){ public int getType() { return Types.LONGVARCHAR; }}; public static final OutParameter NULL = new OutParameter(){ public int getType() { return Types.NULL; }}; public static final OutParameter NUMERIC = new OutParameter(){ public int getType() { return Types.NUMERIC; }}; public static final OutParameter OTHER = new OutParameter(){ public int getType() { return Types.OTHER; }}; public static final OutParameter REAL = new OutParameter(){ public int getType() { return Types.REAL; }}; public static final OutParameter REF = new OutParameter(){ public int getType() { return Types.REF; }}; public static final OutParameter SMALLINT = new OutParameter(){ public int getType() { return Types.SMALLINT; }}; public static final OutParameter STRUCT = new OutParameter(){ public int getType() { return Types.STRUCT; }}; public static final OutParameter TIME = new OutParameter(){ public int getType() { return Types.TIME; }}; public static final OutParameter TIMESTAMP = new OutParameter(){ public int getType() { return Types.TIMESTAMP; }}; public static final OutParameter TINYINT = new OutParameter(){ public int getType() { return Types.TINYINT; }}; public static final OutParameter VARBINARY = new OutParameter(){ public int getType() { return Types.VARBINARY; }}; public static final OutParameter VARCHAR = new OutParameter(){ public int getType() { return Types.VARCHAR; }}; public static InParameter ARRAY(Object value) { return in(Types.ARRAY, value); } public static InParameter BIGINT(Object value) { return in(Types.BIGINT, value); } public static InParameter BINARY(Object value) { return in(Types.BINARY, value); } public static InParameter BIT(Object value) { return in(Types.BIT, value); } public static InParameter BLOB(Object value) { return in(Types.BLOB, value); } public static InParameter BOOLEAN(Object value) { return in(Types.BOOLEAN, value); } public static InParameter CHAR(Object value) { return in(Types.CHAR, value); } public static InParameter CLOB(Object value) { return in(Types.CLOB, value); } public static InParameter DATALINK(Object value) { return in(Types.DATALINK, value); } public static InParameter DATE(Object value) { return in(Types.DATE, value); } public static InParameter DECIMAL(Object value) { return in(Types.DECIMAL, value); } public static InParameter DISTINCT(Object value) { return in(Types.DISTINCT, value); } public static InParameter DOUBLE(Object value) { return in(Types.DOUBLE, value); } public static InParameter FLOAT(Object value) { return in(Types.FLOAT, value); } public static InParameter INTEGER(Object value) { return in(Types.INTEGER, value); } public static InParameter JAVA_OBJECT(Object value) { return in(Types.JAVA_OBJECT, value); } public static InParameter LONGVARBINARY(Object value) { return in(Types.LONGVARBINARY, value); } public static InParameter LONGVARCHAR(Object value) { return in(Types.LONGVARCHAR, value); } public static InParameter NULL(Object value) { return in(Types.NULL, value); } public static InParameter NUMERIC(Object value) { return in(Types.NUMERIC, value); } public static InParameter OTHER(Object value) { return in(Types.OTHER, value); } public static InParameter REAL(Object value) { return in(Types.REAL, value); } public static InParameter REF(Object value) { return in(Types.REF, value); } public static InParameter SMALLINT(Object value) { return in(Types.SMALLINT, value); } public static InParameter STRUCT(Object value) { return in(Types.STRUCT, value); } public static InParameter TIME(Object value) { return in(Types.TIME, value); } public static InParameter TIMESTAMP(Object value) { return in(Types.TIMESTAMP, value); } public static InParameter TINYINT(Object value) { return in(Types.TINYINT, value); } public static InParameter VARBINARY(Object value) { return in(Types.VARBINARY, value); } public static InParameter VARCHAR(Object value) { return in(Types.VARCHAR, value); } public static final int NO_RESULT_SETS = 0; public static final int FIRST_RESULT_SET = 1; public static final int ALL_RESULT_SETS = 2; /** * Create a new InParameter * * @param type the JDBC data type * @param value the object value * @return an InParameter */ public static InParameter in(final int type, final Object value) { return new InParameter() { public int getType() { return type; } public Object getValue() { return value; } }; } /** * Create a new OutParameter * * @param type the JDBC data type. * @return an OutParameter */ public static OutParameter out(final int type) { return new OutParameter() { public int getType() { return type; } }; } /** * Create an inout parameter using this in parameter. * * @param in the InParameter of interest * @return the resulting InOutParameter */ public static InOutParameter inout(final InParameter in) { return new InOutParameter() { public int getType() { return in.getType(); } public Object getValue() { return in.getValue(); } }; } /** * Create a new ResultSetOutParameter * * @param type the JDBC data type. * @return a ResultSetOutParameter */ public static ResultSetOutParameter resultSet(final int type) { return new ResultSetOutParameter() { public int getType() { return type; } }; } /** * When using GString SQL queries, allows a variable to be expanded * in the Sql string rather than representing an sql parameter. *

* Example usage: *

     * def fieldName = 'firstname'
     * def fieldOp = Sql.expand('like')
     * def fieldVal = '%a%'
     * sql.query "select * from PERSON where ${Sql.expand(fieldName)} $fieldOp ${fieldVal}", { ResultSet rs {@code ->}
     *     while (rs.next()) println rs.getString('firstname')
     * }
     * // query will be 'select * from PERSON where firstname like ?'
     * // params will be [fieldVal]
     * 
* * @param object the object of interest * @return the expanded variable * @see #expand(Object) */ public static ExpandedVariable expand(final Object object) { return new ExpandedVariable() { public Object getObject() { return object; } }; } /** * Constructs an SQL instance using the given DataSource. Each operation * will use a Connection from the DataSource pool and close it when the * operation is completed putting it back into the pool. * * @param dataSource the DataSource to use */ public Sql(DataSource dataSource) { this.dataSource = dataSource; } /** * Constructs an SQL instance using the given Connection. It is the caller's * responsibility to close the Connection after the Sql instance has been * used. Depending on which features you are using, you may be able to do * this on the connection object directly but the preferred approach is to * call the {@link #close()} method which will close the connection but also * free any cached resources. * * @param connection the Connection to use */ public Sql(Connection connection) { if (connection == null) { throw new NullPointerException("Must specify a non-null Connection"); } this.useConnection = connection; } public Sql(Sql parent) { this.dataSource = parent.dataSource; this.useConnection = parent.useConnection; } private Sql() { // supports Map style newInstance method } public DataSet dataSet(String table) { return new DataSet(this, table); } public DataSet dataSet(Class type) { return new DataSet(this, type); } /** * Performs the given SQL query, which should return a single * ResultSet object. The given closure is called * with the ResultSet as its argument. *

* Example usages: *

     * sql.query("select * from PERSON where firstname like 'S%'") { ResultSet rs {@code ->}
     *     while (rs.next()) println rs.getString('firstname') + ' ' + rs.getString(3)
     * }
     *
     * sql.query("call get_people_places()") { ResultSet rs {@code ->}
     *     while (rs.next()) println rs.toRowResult().firstname
     * }
     * 
*

* All resources including the ResultSet are closed automatically * after the closure is called. * * @param sql the sql statement * @param closure called for each row with a ResultSet * @throws SQLException if a database access error occurs */ public void query(String sql, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSet") Closure closure) throws SQLException { Connection connection = createConnection(); Statement statement = null; ResultSet results = null; try { statement = getStatement(connection, sql); results = statement.executeQuery(sql); closure.call(results); } catch (SQLException e) { LOG.warning("Failed to execute: " + sql + " because: " + e.getMessage()); throw e; } finally { closeResources(connection, statement, results); } } /** * Performs the given SQL query, which should return a single * ResultSet object. The given closure is called * with the ResultSet as its argument. * The query may contain placeholder question marks which match the given list of parameters. *

* Example usage: *

     * sql.query('select * from PERSON where lastname like ?', ['%a%']) { ResultSet rs {@code ->}
     *     while (rs.next()) println rs.getString('lastname')
     * }
     * 
*

* This method supports named and named ordinal parameters. * See the class Javadoc for more details. *

* All resources including the ResultSet are closed automatically * after the closure is called. * * @param sql the sql statement * @param params a list of parameters * @param closure called for each row with a ResultSet * @throws SQLException if a database access error occurs */ public void query(String sql, List params, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSet") Closure closure) throws SQLException { Connection connection = createConnection(); PreparedStatement statement = null; ResultSet results = null; try { statement = getPreparedStatement(connection, sql, params); results = statement.executeQuery(); closure.call(results); } catch (SQLException e) { LOG.warning("Failed to execute: " + sql + " because: " + e.getMessage()); throw e; } finally { closeResources(connection, statement, results); } } /** * A variant of {@link #query(String, java.util.List, groovy.lang.Closure)} * useful when providing the named parameters as a map. * * @param sql the sql statement * @param map a map containing the named parameters * @param closure called for each row with a ResultSet * @throws SQLException if a database access error occurs * @since 1.8.7 */ public void query(String sql, Map map, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSet") Closure closure) throws SQLException { query(sql, singletonList(map), closure); } /** * A variant of {@link #query(String, java.util.List, groovy.lang.Closure)} * useful when providing the named parameters as named arguments. * * @param map a map containing the named parameters * @param sql the sql statement * @param closure called for each row with a ResultSet * @throws SQLException if a database access error occurs * @since 1.8.7 */ public void query(Map map, String sql, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSet") Closure closure) throws SQLException { query(sql, singletonList(map), closure); } private static ArrayList singletonList(Object item) { ArrayList params = new ArrayList(); params.add(item); return params; } /** * Performs the given SQL query, which should return a single * ResultSet object. The given closure is called * with the ResultSet as its argument. * The query may contain GString expressions. *

* Example usage: *

     * def location = 25
     * sql.query "select * from PERSON where location_id {@code <} $location", { ResultSet rs {@code ->}
     *     while (rs.next()) println rs.getString('firstname')
     * }
     * 
*

* All resources including the ResultSet are closed automatically * after the closure is called. * * @param gstring a GString containing the SQL query with embedded params * @param closure called for each row with a ResultSet * @throws SQLException if a database access error occurs * @see #expand(Object) */ public void query(GString gstring, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSet") Closure closure) throws SQLException { List params = getParameters(gstring); String sql = asSql(gstring, params); query(sql, params, closure); } /** * Performs the given SQL query calling the given Closure with each row of the result set. * The row will be a GroovyResultSet which is a ResultSet * that supports accessing the fields using property style notation and ordinal index values. *

* Example usages: *

     * sql.eachRow("select * from PERSON where firstname like 'S%'") { row {@code ->}
     *    println "$row.firstname ${row[2]}}"
     * }
     *
     * sql.eachRow "call my_stored_proc_returning_resultset()", {
     *     println it.firstname
     * }
     * 
*

* Resource handling is performed automatically where appropriate. * * @param sql the sql statement * @param closure called for each row with a GroovyResultSet * @throws SQLException if a database access error occurs */ public void eachRow(String sql, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure closure) throws SQLException { eachRow(sql, (Closure) null, closure); } /** * Performs the given SQL query calling the given closure with each row of the result set starting at * the provided offset, and including up to maxRows number of rows. * The row will be a GroovyResultSet which is a ResultSet * that supports accessing the fields using property style notation and ordinal index values. *

* Note that the underlying implementation is based on either invoking ResultSet.absolute(), * or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY, the ResultSet.next() method * is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect * on the initial positioning within the result set. *

* Note that different database and JDBC driver implementations may work differently with respect to this method. * Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY may be less efficient than a * "scrollable" type. *

* Resource handling is performed automatically where appropriate. * * @param sql the sql statement * @param offset the 1-based offset for the first row to be processed * @param maxRows the maximum number of rows to be processed * @param closure called for each row with a GroovyResultSet * @throws SQLException if a database access error occurs */ public void eachRow(String sql, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure closure) throws SQLException { eachRow(sql, (Closure) null, offset, maxRows, closure); } /** * Performs the given SQL query calling the given rowClosure with each row of the * result set. * The row will be a GroovyResultSet which is a ResultSet * that supports accessing the fields using property style notation and ordinal index values. * In addition, the metaClosure will be called once passing in the * ResultSetMetaData as argument. *

* Example usage: *

     * def printColNames = { meta {@code ->}
     *     (1..meta.columnCount).each {
     *         print meta.getColumnLabel(it).padRight(20)
     *     }
     *     println()
     * }
     * def printRow = { row {@code ->}
     *     row.toRowResult().values().each{ print it.toString().padRight(20) }
     *     println()
     * }
     * sql.eachRow("select * from PERSON", printColNames, printRow)
     * 
*

* Resource handling is performed automatically where appropriate. * * @param sql the sql statement * @param metaClosure called for meta data (only once after sql execution) * @param rowClosure called for each row with a GroovyResultSet * @throws SQLException if a database access error occurs */ public void eachRow(String sql, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure rowClosure) throws SQLException { eachRow(sql, metaClosure, 0, 0, rowClosure); } /** * Performs the given SQL query calling the given rowClosure with each row of the result set starting at * the provided offset, and including up to maxRows number of rows. * The row will be a GroovyResultSet which is a ResultSet * that supports accessing the fields using property style notation and ordinal index values. *

* In addition, the metaClosure will be called once passing in the * ResultSetMetaData as argument. *

* Note that the underlying implementation is based on either invoking ResultSet.absolute(), * or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY, the ResultSet.next() method * is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect * on the initial positioning within the result set. *

* Note that different database and JDBC driver implementations may work differently with respect to this method. * Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY may be less efficient than a * "scrollable" type. *

* Resource handling is performed automatically where appropriate. * * @param sql the sql statement * @param offset the 1-based offset for the first row to be processed * @param maxRows the maximum number of rows to be processed * @param metaClosure called for meta data (only once after sql execution) * @param rowClosure called for each row with a GroovyResultSet * @throws SQLException if a database access error occurs */ public void eachRow(String sql, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure rowClosure) throws SQLException { Connection connection = createConnection(); Statement statement = null; ResultSet results = null; try { statement = getStatement(connection, sql); results = statement.executeQuery(sql); if (metaClosure != null) metaClosure.call(results.getMetaData()); boolean cursorAtRow = moveCursor(results, offset); if (!cursorAtRow) return; GroovyResultSet groovyRS = new GroovyResultSetProxy(results).getImpl(); int i = 0; while ((maxRows <= 0 || i++ < maxRows) && groovyRS.next()) { rowClosure.call(groovyRS); } } catch (SQLException e) { LOG.warning("Failed to execute: " + sql + " because: " + e.getMessage()); throw e; } finally { closeResources(connection, statement, results); } } private static boolean moveCursor(ResultSet results, int offset) throws SQLException { boolean cursorAtRow = true; if (results.getType() == ResultSet.TYPE_FORWARD_ONLY) { int i = 1; while (i++ < offset && cursorAtRow) { cursorAtRow = results.next(); } } else if (offset > 1) { cursorAtRow = results.absolute(offset - 1); } return cursorAtRow; } /** * Performs the given SQL query calling the given rowClosure with each row of the result set starting at * the provided offset, and including up to maxRows number of rows. * The row will be a GroovyResultSet which is a ResultSet * that supports accessing the fields using property style notation and ordinal index values. *

* In addition, the metaClosure will be called once passing in the * ResultSetMetaData as argument. * The query may contain placeholder question marks which match the given list of parameters. *

* Note that the underlying implementation is based on either invoking ResultSet.absolute(), * or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY, the ResultSet.next() method * is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect * on the initial positioning within the result set. *

* Note that different database and JDBC driver implementations may work differently with respect to this method. * Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY may be less efficient than a * "scrollable" type. * * @param sql the sql statement * @param params a list of parameters * @param offset the 1-based offset for the first row to be processed * @param maxRows the maximum number of rows to be processed * @param metaClosure called for meta data (only once after sql execution) * @param rowClosure called for each row with a GroovyResultSet * @throws SQLException if a database access error occurs */ public void eachRow(String sql, List params, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure rowClosure) throws SQLException { Connection connection = createConnection(); PreparedStatement statement = null; ResultSet results = null; try { statement = getPreparedStatement(connection, sql, params); results = statement.executeQuery(); if (metaClosure != null) metaClosure.call(results.getMetaData()); boolean cursorAtRow = moveCursor(results, offset); if (!cursorAtRow) return; GroovyResultSet groovyRS = new GroovyResultSetProxy(results).getImpl(); int i = 0; while ((maxRows <= 0 || i++ < maxRows) && groovyRS.next()) { rowClosure.call(groovyRS); } } catch (SQLException e) { LOG.warning("Failed to execute: " + sql + " because: " + e.getMessage()); throw e; } finally { closeResources(connection, statement, results); } } /** * A variant of {@link #eachRow(String, java.util.List, groovy.lang.Closure, int, int, groovy.lang.Closure)} * allowing the named parameters to be supplied in a map. * * @param sql the sql statement * @param map a map containing the named parameters * @param offset the 1-based offset for the first row to be processed * @param maxRows the maximum number of rows to be processed * @param metaClosure called for meta data (only once after sql execution) * @param rowClosure called for each row with a GroovyResultSet * @throws SQLException if a database access error occurs * @since 1.8.7 */ public void eachRow(String sql, Map map, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure rowClosure) throws SQLException { eachRow(sql, singletonList(map), metaClosure, offset, maxRows, rowClosure); } /** * A variant of {@link #eachRow(String, java.util.List, groovy.lang.Closure, int, int, groovy.lang.Closure)} * allowing the named parameters to be supplied as named arguments. * * @param map a map containing the named parameters * @param sql the sql statement * @param offset the 1-based offset for the first row to be processed * @param maxRows the maximum number of rows to be processed * @param metaClosure called for meta data (only once after sql execution) * @param rowClosure called for each row with a GroovyResultSet * @throws SQLException if a database access error occurs * @since 1.8.7 */ public void eachRow(Map map, String sql, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure rowClosure) throws SQLException { eachRow(sql, singletonList(map), metaClosure, offset, maxRows, rowClosure); } /** * Performs the given SQL query calling the given Closure with each row of the result set. * The row will be a GroovyResultSet which is a ResultSet * that supports accessing the fields using property style notation and ordinal index values. * In addition, the metaClosure will be called once passing in the * ResultSetMetaData as argument. * The query may contain placeholder question marks which match the given list of parameters. *

* Example usage: *

     * def printColNames = { meta {@code ->}
     *     (1..meta.columnCount).each {
     *         print meta.getColumnLabel(it).padRight(20)
     *     }
     *     println()
     * }
     * def printRow = { row {@code ->}
     *     row.toRowResult().values().each{ print it.toString().padRight(20) }
     *     println()
     * }
     * sql.eachRow("select * from PERSON where lastname like ?", ['%a%'], printColNames, printRow)
     * 
*

* This method supports named and named ordinal parameters. * See the class Javadoc for more details. *

* Resource handling is performed automatically where appropriate. * * @param sql the sql statement * @param params a list of parameters * @param metaClosure called for meta data (only once after sql execution) * @param rowClosure called for each row with a GroovyResultSet * @throws SQLException if a database access error occurs */ public void eachRow(String sql, List params, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure rowClosure) throws SQLException { eachRow(sql, params, metaClosure, 0, 0, rowClosure); } /** * A variant of {@link #eachRow(String, java.util.List, groovy.lang.Closure, groovy.lang.Closure)} * useful when providing the named parameters as a map. * * @param sql the sql statement * @param params a map of named parameters * @param metaClosure called for meta data (only once after sql execution) * @param rowClosure called for each row with a GroovyResultSet * @throws SQLException if a database access error occurs * @since 1.8.7 */ public void eachRow(String sql, Map params, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure rowClosure) throws SQLException { eachRow(sql, singletonList(params), metaClosure, rowClosure); } /** * A variant of {@link #eachRow(String, java.util.List, groovy.lang.Closure, groovy.lang.Closure)} * useful when providing the named parameters as named arguments. * * @param params a map of named parameters * @param sql the sql statement * @param metaClosure called for meta data (only once after sql execution) * @param rowClosure called for each row with a GroovyResultSet * @throws SQLException if a database access error occurs * @since 1.8.7 */ public void eachRow(Map params, String sql, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure rowClosure) throws SQLException { eachRow(sql, singletonList(params), metaClosure, rowClosure); } /** * Performs the given SQL query calling the given Closure with each row of the result set. * The row will be a GroovyResultSet which is a ResultSet * that supports accessing the fields using property style notation and ordinal index values. * The query may contain placeholder question marks which match the given list of parameters. *

* Example usage: *

     * sql.eachRow("select * from PERSON where lastname like ?", ['%a%']) { row {@code ->}
     *     println "${row[1]} $row.lastname"
     * }
     * 
*

* Resource handling is performed automatically where appropriate. * * @param sql the sql statement * @param params a list of parameters * @param closure called for each row with a GroovyResultSet * @throws SQLException if a database access error occurs */ public void eachRow(String sql, List params, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure closure) throws SQLException { eachRow(sql, params, null, closure); } /** * A variant of {@link #eachRow(String, java.util.List, groovy.lang.Closure)} * useful when providing the named parameters as a map. * * @param sql the sql statement * @param params a map of named parameters * @param closure called for each row with a GroovyResultSet * @throws SQLException if a database access error occurs * @since 1.8.7 */ public void eachRow(String sql, Map params, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure closure) throws SQLException { eachRow(sql, singletonList(params), closure); } /** * A variant of {@link #eachRow(String, java.util.List, groovy.lang.Closure)} * useful when providing the named parameters as named arguments. * * @param params a map of named parameters * @param sql the sql statement * @param closure called for each row with a GroovyResultSet * @throws SQLException if a database access error occurs * @since 1.8.7 */ public void eachRow(Map params, String sql, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure closure) throws SQLException { eachRow(sql, singletonList(params), closure); } /** * Performs the given SQL query calling the given closure with each row of the result set starting at * the provided offset, and including up to maxRows number of rows. * The row will be a GroovyResultSet which is a ResultSet * that supports accessing the fields using property style notation and ordinal index values. * The query may contain placeholder question marks which match the given list of parameters. *

* Note that the underlying implementation is based on either invoking ResultSet.absolute(), * or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY, the ResultSet.next() method * is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect * on the initial positioning within the result set. *

* Note that different database and JDBC driver implementations may work differently with respect to this method. * Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY may be less efficient than a * "scrollable" type. * * @param sql the sql statement * @param params a list of parameters * @param offset the 1-based offset for the first row to be processed * @param maxRows the maximum number of rows to be processed * @param closure called for each row with a GroovyResultSet * @throws SQLException if a database access error occurs */ public void eachRow(String sql, List params, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure closure) throws SQLException { eachRow(sql, params, null, offset, maxRows, closure); } /** * A variant of {@link #eachRow(String, java.util.List, int, int, groovy.lang.Closure)} * useful when providing the named parameters as a map. * * @param sql the sql statement * @param params a map of named parameters * @param offset the 1-based offset for the first row to be processed * @param maxRows the maximum number of rows to be processed * @param closure called for each row with a GroovyResultSet * @throws SQLException if a database access error occurs * @since 1.8.7 */ public void eachRow(String sql, Map params, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure closure) throws SQLException { eachRow(sql, singletonList(params), offset, maxRows, closure); } /** * A variant of {@link #eachRow(String, java.util.List, int, int, groovy.lang.Closure)} * useful when providing the named parameters as named arguments. * * @param params a map of named parameters * @param sql the sql statement * @param offset the 1-based offset for the first row to be processed * @param maxRows the maximum number of rows to be processed * @param closure called for each row with a GroovyResultSet * @throws SQLException if a database access error occurs * @since 1.8.7 */ public void eachRow(Map params, String sql, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure closure) throws SQLException { eachRow(sql, singletonList(params), offset, maxRows, closure); } /** * Performs the given SQL query calling the given Closure with each row of the result set. * The row will be a GroovyResultSet which is a ResultSet * that supports accessing the fields using property style notation and ordinal index values. *

* In addition, the metaClosure will be called once passing in the * ResultSetMetaData as argument. * The query may contain GString expressions. *

* Example usage: *

     * def location = 25
     * def printColNames = { meta {@code ->}
     *     (1..meta.columnCount).each {
     *         print meta.getColumnLabel(it).padRight(20)
     *     }
     *     println()
     * }
     * def printRow = { row {@code ->}
     *     row.toRowResult().values().each{ print it.toString().padRight(20) }
     *     println()
     * }
     * sql.eachRow("select * from PERSON where location_id {@code <} $location", printColNames, printRow)
     * 
*

* Resource handling is performed automatically where appropriate. * * @param gstring a GString containing the SQL query with embedded params * @param metaClosure called for meta data (only once after sql execution) * @param rowClosure called for each row with a GroovyResultSet * @throws SQLException if a database access error occurs * @see #expand(Object) */ public void eachRow(GString gstring, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure rowClosure) throws SQLException { List params = getParameters(gstring); String sql = asSql(gstring, params); eachRow(sql, params, metaClosure, rowClosure); } /** * Performs the given SQL query calling the given closure with each row of the result set starting at * the provided offset, and including up to maxRows number of rows. * The row will be a GroovyResultSet which is a ResultSet * that supports accessing the fields using property style notation and ordinal index values. * In addition, the metaClosure will be called once passing in the * ResultSetMetaData as argument. * The query may contain GString expressions. *

* Note that the underlying implementation is based on either invoking ResultSet.absolute(), * or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY, the ResultSet.next() method * is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect * on the initial positioning within the result set. *

* Note that different database and JDBC driver implementations may work differently with respect to this method. * Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY may be less efficient than a * "scrollable" type. * * @param gstring a GString containing the SQL query with embedded params * @param metaClosure called for meta data (only once after sql execution) * @param offset the 1-based offset for the first row to be processed * @param maxRows the maximum number of rows to be processed * @param rowClosure called for each row with a GroovyResultSet * @throws SQLException if a database access error occurs */ public void eachRow(GString gstring, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure rowClosure) throws SQLException { List params = getParameters(gstring); String sql = asSql(gstring, params); eachRow(sql, params, metaClosure, offset, maxRows, rowClosure); } /** * Performs the given SQL query calling the given closure with each row of the result set starting at * the provided offset, and including up to maxRows number of rows. * The row will be a GroovyResultSet which is a ResultSet * that supports accessing the fields using property style notation and ordinal index values. * The query may contain GString expressions. *

* Note that the underlying implementation is based on either invoking ResultSet.absolute(), * or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY, the ResultSet.next() method * is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect * on the initial positioning within the result set. *

* Note that different database and JDBC driver implementations may work differently with respect to this method. * Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY may be less efficient than a * "scrollable" type. * * @param gstring a GString containing the SQL query with embedded params * @param offset the 1-based offset for the first row to be processed * @param maxRows the maximum number of rows to be processed * @param closure called for each row with a GroovyResultSet * @throws SQLException if a database access error occurs */ public void eachRow(GString gstring, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure closure) throws SQLException { List params = getParameters(gstring); String sql = asSql(gstring, params); eachRow(sql, params, offset, maxRows, closure); } /** * Performs the given SQL query calling the given Closure with each row of the result set. * The row will be a GroovyResultSet which is a ResultSet * that supports accessing the fields using property style notation and ordinal index values. * The query may contain GString expressions. *

* Example usage: *

     * def location = 25
     * sql.eachRow("select * from PERSON where location_id {@code <} $location") { row {@code ->}
     *     println row.firstname
     * }
     * 
*

* Resource handling is performed automatically where appropriate. * * @param gstring a GString containing the SQL query with embedded params * @param closure called for each row with a GroovyResultSet * @throws SQLException if a database access error occurs * @see #expand(Object) */ public void eachRow(GString gstring, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet") Closure closure) throws SQLException { eachRow(gstring, null, closure); } /** * Performs the given SQL query and return the rows of the result set. *

* Example usage: *

     * def ans = sql.rows("select * from PERSON where firstname like 'S%'")
     * println "Found ${ans.size()} rows"
     * 
*

* Resource handling is performed automatically where appropriate. * * @param sql the SQL statement * @return a list of GroovyRowResult objects * @throws SQLException if a database access error occurs */ public List rows(String sql) throws SQLException { return rows(sql, 0, 0, null); } /** * Performs the given SQL query and return a "page" of rows from the result set. A page is defined as starting at * a 1-based offset, and containing a maximum number of rows. *

* Note that the underlying implementation is based on either invoking ResultSet.absolute(), * or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY, the ResultSet.next() method * is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect * on the initial positioning within the result set. *

* Note that different database and JDBC driver implementations may work differently with respect to this method. * Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY may be less efficient than a * "scrollable" type. *

* Resource handling is performed automatically where appropriate. * * @param sql the SQL statement * @param offset the 1-based offset for the first row to be processed * @param maxRows the maximum number of rows to be processed * @return a list of GroovyRowResult objects * @throws SQLException if a database access error occurs */ public List rows(String sql, int offset, int maxRows) throws SQLException { return rows(sql, offset, maxRows, null); } /** * Performs the given SQL query and return the rows of the result set. * In addition, the metaClosure will be called once passing in the * ResultSetMetaData as argument. *

* Example usage: *

     * def printNumCols = { meta {@code ->} println "Found $meta.columnCount columns" }
     * def ans = sql.rows("select * from PERSON", printNumCols)
     * println "Found ${ans.size()} rows"
     * 
*

* Resource handling is performed automatically where appropriate. * * @param sql the SQL statement * @param metaClosure called with meta data of the ResultSet * @return a list of GroovyRowResult objects * @throws SQLException if a database access error occurs */ public List rows(String sql, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure) throws SQLException { return rows(sql, 0, 0, metaClosure); } /** * Performs the given SQL query and return a "page" of rows from the result set. A page is defined as starting at * a 1-based offset, and containing a maximum number of rows. * In addition, the metaClosure will be called once passing in the * ResultSetMetaData as argument. *

* Note that the underlying implementation is based on either invoking ResultSet.absolute(), * or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY, the ResultSet.next() method * is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect * on the initial positioning within the result set. *

* Note that different database and JDBC driver implementations may work differently with respect to this method. * Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY may be less efficient than a * "scrollable" type. *

* Resource handling is performed automatically where appropriate. * * @param sql the SQL statement * @param offset the 1-based offset for the first row to be processed * @param maxRows the maximum number of rows to be processed * @param metaClosure called for meta data (only once after sql execution) * @return a list of GroovyRowResult objects * @throws SQLException if a database access error occurs */ public List rows(String sql, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure) throws SQLException { AbstractQueryCommand command = createQueryCommand(sql); // for efficiency set maxRows (adjusted for the first offset rows we are going to skip the cursor over) command.setMaxRows(offset + maxRows); ResultSet rs = null; try { rs = command.execute(); List result = asList(sql, rs, offset, maxRows, metaClosure); rs = null; return result; } finally { command.closeResources(rs); } } /** * Performs the given SQL query and return the rows of the result set. * The query may contain placeholder question marks which match the given list of parameters. *

* Example usage: *

     * def ans = sql.rows("select * from PERSON where lastname like ?", ['%a%'])
     * println "Found ${ans.size()} rows"
     * 
*

* This method supports named and named ordinal parameters by supplying such * parameters in the params list. See the class Javadoc for more details. *

* Resource handling is performed automatically where appropriate. * * @param sql the SQL statement * @param params a list of parameters * @return a list of GroovyRowResult objects * @throws SQLException if a database access error occurs */ public List rows(String sql, List params) throws SQLException { return rows(sql, params, null); } /** * A variant of {@link #rows(String, java.util.List)} * useful when providing the named parameters as named arguments. * * @param params a map containing the named parameters * @param sql the SQL statement * @return a list of GroovyRowResult objects * @throws SQLException if a database access error occurs * @since 1.8.7 */ public List rows(Map params, String sql) throws SQLException { return rows(sql, singletonList(params)); } /** * Performs the given SQL query and return a "page" of rows from the result set. A page is defined as starting at * a 1-based offset, and containing a maximum number of rows. * The query may contain placeholder question marks which match the given list of parameters. *

* Note that the underlying implementation is based on either invoking ResultSet.absolute(), * or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY, the ResultSet.next() method * is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect * on the initial positioning within the result set. *

* Note that different database and JDBC driver implementations may work differently with respect to this method. * Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY may be less efficient than a * "scrollable" type. *

* This method supports named and named ordinal parameters by supplying such * parameters in the params list. See the class Javadoc for more details. *

* Resource handling is performed automatically where appropriate. * * @param sql the SQL statement * @param params a list of parameters * @param offset the 1-based offset for the first row to be processed * @param maxRows the maximum number of rows to be processed * @return a list of GroovyRowResult objects * @throws SQLException if a database access error occurs */ public List rows(String sql, List params, int offset, int maxRows) throws SQLException { return rows(sql, params, offset, maxRows, null); } /** * A variant of {@link #rows(String, java.util.List, int, int)} * useful when providing the named parameters as a map. * * @param sql the SQL statement * @param params a map of named parameters * @param offset the 1-based offset for the first row to be processed * @param maxRows the maximum number of rows to be processed * @return a list of GroovyRowResult objects * @throws SQLException if a database access error occurs * @since 1.8.7 */ public List rows(String sql, Map params, int offset, int maxRows) throws SQLException { return rows(sql, singletonList(params), offset, maxRows); } /** * A variant of {@link #rows(String, java.util.List, int, int)} * useful when providing the named parameters as named arguments. * * @param params a map of named parameters * @param sql the SQL statement * @param offset the 1-based offset for the first row to be processed * @param maxRows the maximum number of rows to be processed * @return a list of GroovyRowResult objects * @throws SQLException if a database access error occurs * @since 1.8.7 */ public List rows(Map params, String sql, int offset, int maxRows) throws SQLException { return rows(sql, singletonList(params), offset, maxRows); } /** * Performs the given SQL query and return the rows of the result set. *

* This method supports named and named ordinal parameters by supplying such * parameters in the params array. See the class Javadoc for more details. *

* An Object array variant of {@link #rows(String, List)}. * * @param sql the SQL statement * @param params an array of parameters * @return a list of GroovyRowResult objects * @throws SQLException if a database access error occurs */ public List rows(String sql, Object[] params) throws SQLException { return rows(sql, params, 0, 0); } /** * Performs the given SQL query and return the rows of the result set. *

* This method supports named and named ordinal parameters by supplying such * parameters in the params array. See the class Javadoc for more details. *

* An Object array variant of {@link #rows(String, List, int, int)}. * * @param sql the SQL statement * @param params an array of parameters * @param offset the 1-based offset for the first row to be processed * @param maxRows the maximum number of rows to be processed * @return a list of GroovyRowResult objects * @throws SQLException if a database access error occurs */ public List rows(String sql, Object[] params, int offset, int maxRows) throws SQLException { return rows(sql, Arrays.asList(params), offset, maxRows, null); } /** * Performs the given SQL query and return the rows of the result set. * In addition, the metaClosure will be called once passing in the * ResultSetMetaData as argument. * The query may contain placeholder question marks which match the given list of parameters. *

* Example usage: *

     * def printNumCols = { meta {@code ->} println "Found $meta.columnCount columns" }
     * def ans = sql.rows("select * from PERSON where lastname like ?", ['%a%'], printNumCols)
     * println "Found ${ans.size()} rows"
     * 
*

* This method supports named and named ordinal parameters by supplying such * parameters in the params list. Here is an example: *

     * def printNumCols = { meta {@code ->} println "Found $meta.columnCount columns" }
     *
     * def mapParam = [foo: 'Smith']
     * def domainParam = new MyDomainClass(bar: 'John')
     * def qry = 'select * from PERSON where lastname=?1.foo and firstname=?2.bar'
     * def ans = sql.rows(qry, [mapParam, domainParam], printNumCols)
     * println "Found ${ans.size()} rows"
     *
     * def qry2 = 'select * from PERSON where firstname=:first and lastname=:last'
     * def ans2 = sql.rows(qry2, [[last:'Smith', first:'John']], printNumCols)
     * println "Found ${ans2.size()} rows"
     * 
* See the class Javadoc for more details. *

* Resource handling is performed automatically where appropriate. * * @param sql the SQL statement * @param params a list of parameters * @param metaClosure called for meta data (only once after sql execution) * @return a list of GroovyRowResult objects * @throws SQLException if a database access error occurs */ public List rows(String sql, List params, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure) throws SQLException { return rows(sql, params, 0, 0, metaClosure); } /** * A variant of {@link #rows(String, java.util.List, groovy.lang.Closure)} * useful when providing the named parameters as a map. * * @param sql the SQL statement * @param params a map of named parameters * @param metaClosure called for meta data (only once after sql execution) * @return a list of GroovyRowResult objects * @throws SQLException if a database access error occurs * @since 1.8.7 */ public List rows(String sql, Map params, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure) throws SQLException { return rows(sql, singletonList(params), metaClosure); } /** * A variant of {@link #rows(String, java.util.List, groovy.lang.Closure)} * useful when providing the named parameters as named arguments. * * @param params a map of named parameters * @param sql the SQL statement * @param metaClosure called for meta data (only once after sql execution) * @return a list of GroovyRowResult objects * @throws SQLException if a database access error occurs * @since 1.8.7 */ public List rows(Map params, String sql, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure) throws SQLException { return rows(sql, singletonList(params), metaClosure); } /** * Performs the given SQL query and return a "page" of rows from the result set. A page is defined as starting at * a 1-based offset, and containing a maximum number of rows. * In addition, the metaClosure will be called once passing in the * ResultSetMetaData as argument. * The query may contain placeholder question marks which match the given list of parameters. *

* Note that the underlying implementation is based on either invoking ResultSet.absolute(), * or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY, the ResultSet.next() method * is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect * on the initial positioning within the result set. *

* Note that different database and JDBC driver implementations may work differently with respect to this method. * Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY may be less efficient than a * "scrollable" type. *

* This method supports named and named ordinal parameters by supplying such * parameters in the params list. See the class Javadoc for more details. *

* Resource handling is performed automatically where appropriate. * * @param sql the SQL statement * @param params a list of parameters * @param offset the 1-based offset for the first row to be processed * @param maxRows the maximum number of rows to be processed * @param metaClosure called for meta data (only once after sql execution) * @return a list of GroovyRowResult objects * @throws SQLException if a database access error occurs */ public List rows(String sql, List params, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure) throws SQLException { AbstractQueryCommand command = createPreparedQueryCommand(sql, params); // for efficiency set maxRows (adjusted for the first offset rows we are going to skip the cursor over) command.setMaxRows(offset + maxRows); try { return asList(sql, command.execute(), offset, maxRows, metaClosure); } finally { command.closeResources(); } } /** * A variant of {@link #rows(String, java.util.List, int, int, groovy.lang.Closure)} * useful when providing the named parameters as a map. * * @param sql the SQL statement * @param params a map of named parameters * @param offset the 1-based offset for the first row to be processed * @param maxRows the maximum number of rows to be processed * @param metaClosure called for meta data (only once after sql execution) * @return a list of GroovyRowResult objects * @throws SQLException if a database access error occurs * @since 1.8.7 */ public List rows(String sql, Map params, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure) throws SQLException { return rows(sql, singletonList(params), offset, maxRows, metaClosure); } /** * A variant of {@link #rows(String, java.util.List, int, int, groovy.lang.Closure)} * useful when providing the named parameters as named arguments. * * @param params a map of named parameters * @param sql the SQL statement * @param offset the 1-based offset for the first row to be processed * @param maxRows the maximum number of rows to be processed * @param metaClosure called for meta data (only once after sql execution) * @return a list of GroovyRowResult objects * @throws SQLException if a database access error occurs * @since 1.8.7 */ public List rows(Map params, String sql, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure) throws SQLException { return rows(sql, singletonList(params), offset, maxRows, metaClosure); } /** * Performs the given SQL query and return a "page" of rows from the result set. A page is defined as starting at * a 1-based offset, and containing a maximum number of rows. * The query may contain GString expressions. *

* Note that the underlying implementation is based on either invoking ResultSet.absolute(), * or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY, the ResultSet.next() method * is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect * on the initial positioning within the result set. *

* Note that different database and JDBC driver implementations may work differently with respect to this method. * Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY may be less efficient than a * "scrollable" type. *

* Resource handling is performed automatically where appropriate. * * @param sql the SQL statement * @param offset the 1-based offset for the first row to be processed * @param maxRows the maximum number of rows to be processed * @return a list of GroovyRowResult objects * @throws SQLException if a database access error occurs */ public List rows(GString sql, int offset, int maxRows) throws SQLException { return rows(sql, offset, maxRows, null); } /** * Performs the given SQL query and return the rows of the result set. * The query may contain GString expressions. *

* Example usage: *

     * def location = 25
     * def ans = sql.rows("select * from PERSON where location_id {@code <} $location")
     * println "Found ${ans.size()} rows"
     * 
*

* Resource handling is performed automatically where appropriate. * * @param gstring a GString containing the SQL query with embedded params * @return a list of GroovyRowResult objects * @throws SQLException if a database access error occurs * @see #expand(Object) */ public List rows(GString gstring) throws SQLException { return rows(gstring, null); } /** * Performs the given SQL query and return the rows of the result set. * In addition, the metaClosure will be called once passing in the * ResultSetMetaData as argument. * The query may contain GString expressions. *

* Example usage: *

     * def location = 25
     * def printNumCols = { meta {@code ->} println "Found $meta.columnCount columns" }
     * def ans = sql.rows("select * from PERSON where location_id {@code <} $location", printNumCols)
     * println "Found ${ans.size()} rows"
     * 
*

* Resource handling is performed automatically where appropriate. * * @param gstring a GString containing the SQL query with embedded params * @param metaClosure called with meta data of the ResultSet * @return a list of GroovyRowResult objects * @throws SQLException if a database access error occurs * @see #expand(Object) */ public List rows(GString gstring, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure) throws SQLException { List params = getParameters(gstring); String sql = asSql(gstring, params); return rows(sql, params, metaClosure); } /** * Performs the given SQL query and return a "page" of rows from the result set. A page is defined as starting at * a 1-based offset, and containing a maximum number of rows. * In addition, the metaClosure will be called once passing in the * ResultSetMetaData as argument. * The query may contain GString expressions. *

* Note that the underlying implementation is based on either invoking ResultSet.absolute(), * or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY, the ResultSet.next() method * is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect * on the initial positioning within the result set. *

* Note that different database and JDBC driver implementations may work differently with respect to this method. * Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY may be less efficient than a * "scrollable" type. *

* Resource handling is performed automatically where appropriate. * * @param gstring the SQL statement * @param offset the 1-based offset for the first row to be processed * @param maxRows the maximum number of rows to be processed * @param metaClosure called for meta data (only once after sql execution) * @return a list of GroovyRowResult objects * @throws SQLException if a database access error occurs */ public List rows(GString gstring, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure) throws SQLException { List params = getParameters(gstring); String sql = asSql(gstring, params); return rows(sql, params, offset, maxRows, metaClosure); } /** * Performs the given SQL query and return the first row of the result set. *

* Example usage: *

     * def ans = sql.firstRow("select * from PERSON where firstname like 'S%'")
     * println ans.firstname
     * 
*

* Resource handling is performed automatically where appropriate. * * @param sql the SQL statement * @return a GroovyRowResult object or null if no row is found * @throws SQLException if a database access error occurs */ public GroovyRowResult firstRow(String sql) throws SQLException { List rows = null; try { rows = rows(sql, 1, 1, null); } //should be SQLFeatureNotSupportedException instead once we move to Java 1.6 catch (SQLException featureNotSupportedException) { rows = rows(sql); } if (rows.isEmpty()) return null; return rows.get(0); } /** * Performs the given SQL query and return * the first row of the result set. * The query may contain GString expressions. *

* Example usage: *

     * def location = 25
     * def ans = sql.firstRow("select * from PERSON where location_id {@code <} $location")
     * println ans.firstname
     * 
*

* Resource handling is performed automatically where appropriate. * * @param gstring a GString containing the SQL query with embedded params * @return a GroovyRowResult object or null if no row is found * @throws SQLException if a database access error occurs * @see #expand(Object) */ public GroovyRowResult firstRow(GString gstring) throws SQLException { List params = getParameters(gstring); String sql = asSql(gstring, params); return firstRow(sql, params); } /** * Performs the given SQL query and return the first row of the result set. * The query may contain placeholder question marks which match the given list of parameters. *

* Example usages: *

     * def ans = sql.firstRow("select * from PERSON where lastname like ?", ['%a%'])
     * println ans.firstname
     * 
* If your database returns scalar functions as ResultSets, you can also use firstRow * to gain access to stored procedure results, e.g. using hsqldb 1.9 RC4: *
     * sql.execute """
     *     create function FullName(p_firstname VARCHAR(40)) returns VARCHAR(80)
     *     BEGIN atomic
     *     DECLARE ans VARCHAR(80);
     *     SET ans = (SELECT firstname || ' ' || lastname FROM PERSON WHERE firstname = p_firstname);
     *     RETURN ans;
     *     END
     * """
     *
     * assert sql.firstRow("{call FullName(?)}", ['Sam'])[0] == 'Sam Pullara'
     * 
*

* This method supports named and named ordinal parameters by supplying such * parameters in the params list. See the class Javadoc for more details. *

* Resource handling is performed automatically where appropriate. * * @param sql the SQL statement * @param params a list of parameters * @return a GroovyRowResult object or null if no row is found * @throws SQLException if a database access error occurs */ public GroovyRowResult firstRow(String sql, List params) throws SQLException { List rows = null; try { rows = rows(sql, params, 1, 1, null); } //should be SQLFeatureNotSupportedException instead once we move to Java 1.6 catch (SQLException featureNotSupportedException) { rows = rows(sql, params); } if (rows.isEmpty()) return null; return rows.get(0); } /** * A variant of {@link #firstRow(String, java.util.List)} * useful when providing the named parameters as named arguments. * * @param params a map containing the named parameters * @param sql the SQL statement * @return a GroovyRowResult object or null if no row is found * @throws SQLException if a database access error occurs * @since 1.8.7 */ public GroovyRowResult firstRow(Map params, String sql) throws SQLException { return firstRow(sql, singletonList(params)); } /** * Performs the given SQL query and return the first row of the result set. *

* An Object array variant of {@link #firstRow(String, List)}. *

* This method supports named and named ordinal parameters by supplying such * parameters in the params array. See the class Javadoc for more details. * * @param sql the SQL statement * @param params an array of parameters * @return a GroovyRowResult object or null if no row is found * @throws SQLException if a database access error occurs */ public GroovyRowResult firstRow(String sql, Object[] params) throws SQLException { return firstRow(sql, Arrays.asList(params)); } /** * Executes the given piece of SQL. * Also saves the updateCount, if any, for subsequent examination. *

* Example usages: *

     * sql.execute "DROP TABLE IF EXISTS person"
     *
     * sql.execute """
     *     CREATE TABLE person (
     *         id INTEGER NOT NULL,
     *         firstname VARCHAR(100),
     *         lastname VARCHAR(100),
     *         location_id INTEGER
     *     )
     * """
     *
     * sql.execute """
     *     INSERT INTO person (id, firstname, lastname, location_id) VALUES (4, 'Paul', 'King', 40)
     * """
     * assert sql.updateCount == 1
     * 
*

* Resource handling is performed automatically where appropriate. * * @param sql the SQL to execute * @return true if the first result is a ResultSet * object; false if it is an update count or there are * no results * @throws SQLException if a database access error occurs */ public boolean execute(String sql) throws SQLException { Connection connection = createConnection(); Statement statement = null; try { statement = getStatement(connection, sql); boolean isResultSet = statement.execute(sql); this.updateCount = statement.getUpdateCount(); return isResultSet; } catch (SQLException e) { LOG.warning("Failed to execute: " + sql + " because: " + e.getMessage()); throw e; } finally { closeResources(connection, statement); } } /** * Executes the given piece of SQL. * Also calls the provided processResults Closure to process any ResultSet or UpdateCount results that executing the SQL might produce. *

* Example usages: *

     * boolean first = true
     * sql.execute "{call FindAllByFirst('J')}", { isResultSet, result {@code ->}
     *   if (first) {
     *     first = false
     *     assert !isResultSet {@code &&} result == 0
     *   } else {
     *     assert isResultSet {@code &&} result == [[ID:1, FIRSTNAME:'James', LASTNAME:'Strachan'], [ID:4, FIRSTNAME:'Jean', LASTNAME:'Gabin']]
     *   }
     * }
     * 
*

* Resource handling is performed automatically where appropriate. * * @param sql the SQL to execute * @param processResults a Closure which will be passed two parameters: either {@code true} plus a list of GroovyRowResult values * derived from {@code statement.getResultSet()} or {@code false} plus the update count from {@code statement.getUpdateCount()}. * The closure will be called for each result produced from executing the SQL. * @throws SQLException if a database access error occurs * @since 2.3.2 */ public void execute(String sql, @ClosureParams(value=SimpleType.class, options={"boolean,java.util.List", "boolean,int"}) Closure processResults) throws SQLException { Connection connection = createConnection(); Statement statement = null; try { statement = getStatement(connection, sql); boolean isResultSet = statement.execute(sql); int updateCount = statement.getUpdateCount(); while(isResultSet || updateCount != -1) { if (processResults.getMaximumNumberOfParameters() != 2) { throw new SQLException("Incorrect number of parameters for processResults Closure"); } if (isResultSet) { ResultSet resultSet = statement.getResultSet(); List rowResult = resultSet == null ? null : asList(sql, resultSet); processResults.call(isResultSet, rowResult); } else { processResults.call(isResultSet, updateCount); } isResultSet = statement.getMoreResults(); updateCount = statement.getUpdateCount(); } } catch (SQLException e) { LOG.warning("Failed to execute: " + sql + " because: " + e.getMessage()); throw e; } finally { closeResources(connection, statement); } } /** * Executes the given piece of SQL with parameters. * Also saves the updateCount, if any, for subsequent examination. *

* Example usage: *

     * sql.execute """
     *     insert into PERSON (id, firstname, lastname, location_id) values (?, ?, ?, ?)
     * """, [1, "Guillaume", "Laforge", 10]
     * assert sql.updateCount == 1
     * 
*

* This method supports named and named ordinal parameters. * See the class Javadoc for more details. *

* Resource handling is performed automatically where appropriate. * * @param sql the SQL statement * @param params a list of parameters * @return true if the first result is a ResultSet * object; false if it is an update count or there are * no results * @throws SQLException if a database access error occurs */ public boolean execute(String sql, List params) throws SQLException { Connection connection = createConnection(); PreparedStatement statement = null; try { statement = getPreparedStatement(connection, sql, params); boolean isResultSet = statement.execute(); this.updateCount = statement.getUpdateCount(); return isResultSet; } catch (SQLException e) { LOG.warning("Failed to execute: " + sql + " because: " + e.getMessage()); throw e; } finally { closeResources(connection, statement); } } /** * Executes the given piece of SQL with parameters. * Also calls the provided processResults Closure to process any ResultSet or UpdateCount results that executing the SQL might produce. *

* This method supports named and named ordinal parameters. * See the class Javadoc for more details. *

* Resource handling is performed automatically where appropriate. * * @param sql the SQL statement * @param params a list of parameters * @param processResults a Closure which will be passed two parameters: either {@code true} plus a list of GroovyRowResult values * derived from {@code statement.getResultSet()} or {@code false} plus the update count from {@code statement.getUpdateCount()}. * The closure will be called for each result produced from executing the SQL. * @throws SQLException if a database access error occurs * @see #execute(String, Closure) * @since 2.3.2 */ public void execute(String sql, List params, @ClosureParams(value=SimpleType.class, options={"boolean,java.util.List", "boolean,int"}) Closure processResults) throws SQLException { Connection connection = createConnection(); PreparedStatement statement = null; try { statement = getPreparedStatement(connection, sql, params); boolean isResultSet = statement.execute(); int updateCount = statement.getUpdateCount(); while(isResultSet || updateCount != -1) { if (processResults.getMaximumNumberOfParameters() != 2) { throw new SQLException("Incorrect number of parameters for processResults Closure"); } if (isResultSet) { ResultSet resultSet = statement.getResultSet(); List rowResult = resultSet == null ? null : asList(sql, resultSet); processResults.call(isResultSet, rowResult); } else { processResults.call(isResultSet, updateCount); } isResultSet = statement.getMoreResults(); updateCount = statement.getUpdateCount(); } } catch (SQLException e) { LOG.warning("Failed to execute: " + sql + " because: " + e.getMessage()); throw e; } finally { closeResources(connection, statement); } } /** * A variant of {@link #execute(String, java.util.List)} * useful when providing the named parameters as named arguments. * * @param params a map containing the named parameters * @param sql the SQL statement * @return true if the first result is a ResultSet * object; false if it is an update count or there are * no results * @throws SQLException if a database access error occurs * @since 1.8.7 */ public boolean execute(Map params, String sql) throws SQLException { return execute(sql, singletonList(params)); } /** * A variant of {@link #execute(String, java.util.List, Closure)} * useful when providing the named parameters as named arguments. * * @param params a map containing the named parameters * @param sql the SQL statement * @param processResults a Closure which will be passed two parameters: either {@code true} plus a list of GroovyRowResult values * derived from {@code statement.getResultSet()} or {@code false} plus the update count from {@code statement.getUpdateCount()}. * The closure will be called for each result produced from executing the SQL. * @throws SQLException if a database access error occurs * @since 2.3.2 */ public void execute(Map params, String sql, @ClosureParams(value=SimpleType.class, options={"boolean,java.util.List", "boolean,int"}) Closure processResults) throws SQLException { execute(sql, singletonList(params), processResults); } /** * Executes the given piece of SQL with parameters. *

* An Object array variant of {@link #execute(String, List)}. *

* This method supports named and named ordinal parameters by supplying such * parameters in the params array. See the class Javadoc for more details. * * @param sql the SQL statement * @param params an array of parameters * @return true if the first result is a ResultSet * object; false if it is an update count or there are * no results * @throws SQLException if a database access error occurs */ public boolean execute(String sql, Object[] params) throws SQLException { return execute(sql, Arrays.asList(params)); } /** * Executes the given piece of SQL with parameters. *

* An Object array variant of {@link #execute(String, List, Closure)}. *

* This method supports named and named ordinal parameters by supplying such * parameters in the params array. See the class Javadoc for more details. * * @param sql the SQL statement * @param params an array of parameters * @param processResults a Closure which will be passed two parameters: either {@code true} plus a list of GroovyRowResult values * derived from {@code statement.getResultSet()} or {@code false} plus the update count from {@code statement.getUpdateCount()}. * The closure will be called for each result produced from executing the SQL. * @throws SQLException if a database access error occurs * @see #execute(String, List, Closure) * @since 2.3.2 */ public void execute(String sql, Object[] params, @ClosureParams(value=SimpleType.class, options={"boolean,java.util.List", "boolean,int"}) Closure processResults) throws SQLException { execute(sql, Arrays.asList(params), processResults); } /** * Executes the given SQL with embedded expressions inside. * Also saves the updateCount, if any, for subsequent examination. *

* Example usage: *

     * def scott = [firstname: "Scott", lastname: "Davis", id: 5, location_id: 50]
     * sql.execute """
     *     insert into PERSON (id, firstname, lastname, location_id) values ($scott.id, $scott.firstname, $scott.lastname, $scott.location_id)
     * """
     * assert sql.updateCount == 1
     * 
*

* Resource handling is performed automatically where appropriate. * * @param gstring a GString containing the SQL query with embedded params * @return true if the first result is a ResultSet * object; false if it is an update count or there are * no results * @throws SQLException if a database access error occurs * @see #expand(Object) */ public boolean execute(GString gstring) throws SQLException { List params = getParameters(gstring); String sql = asSql(gstring, params); return execute(sql, params); } /** * Executes the given SQL with embedded expressions inside. * Also calls the provided processResults Closure to process any ResultSet or UpdateCount results that executing the SQL might produce. * Resource handling is performed automatically where appropriate. * * @param gstring a GString containing the SQL query with embedded params * @param processResults a Closure which will be passed two parameters: either {@code true} plus a list of GroovyRowResult values * derived from {@code statement.getResultSet()} or {@code false} plus the update count from {@code statement.getUpdateCount()}. * The closure will be called for each result produced from executing the SQL. * @throws SQLException if a database access error occurs * @see #expand(Object) * @see #execute(String, List, Closure) * @since 2.3.2 */ public void execute(GString gstring, @ClosureParams(value=SimpleType.class, options={"boolean,java.util.List", "boolean,int"}) Closure processResults) throws SQLException { List params = getParameters(gstring); String sql = asSql(gstring, params); execute(sql, params, processResults); } /** * Executes the given SQL statement (typically an INSERT statement). * Use this variant when you want to receive the values of any * auto-generated columns, such as an autoincrement ID field. * See {@link #executeInsert(GString)} for more details. *

* Resource handling is performed automatically where appropriate. * * @param sql The SQL statement to execute * @return A list of the auto-generated column values for each * inserted row (typically auto-generated keys) * @throws SQLException if a database access error occurs */ public List> executeInsert(String sql) throws SQLException { Connection connection = createConnection(); Statement statement = null; try { statement = getStatement(connection, sql); this.updateCount = statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS); ResultSet keys = statement.getGeneratedKeys(); return calculateKeys(keys); } catch (SQLException e) { LOG.warning("Failed to execute: " + sql + " because: " + e.getMessage()); throw e; } finally { closeResources(connection, statement); } } /** * Executes the given SQL statement (typically an INSERT statement). * Use this variant when you want to receive the values of any * auto-generated columns, such as an autoincrement ID field. * The query may contain placeholder question marks which match the given list of parameters. * See {@link #executeInsert(GString)} for more details. *

* This method supports named and named ordinal parameters. * See the class Javadoc for more details. *

* Resource handling is performed automatically where appropriate. * * @param sql The SQL statement to execute * @param params The parameter values that will be substituted * into the SQL statement's parameter slots * @return A list of the auto-generated column values for each * inserted row (typically auto-generated keys) * @throws SQLException if a database access error occurs */ public List> executeInsert(String sql, List params) throws SQLException { Connection connection = createConnection(); PreparedStatement statement = null; try { statement = getPreparedStatement(connection, sql, params, Statement.RETURN_GENERATED_KEYS); this.updateCount = statement.executeUpdate(); ResultSet keys = statement.getGeneratedKeys(); return calculateKeys(keys); } catch (SQLException e) { LOG.warning("Failed to execute: " + sql + " because: " + e.getMessage()); throw e; } finally { closeResources(connection, statement); } } /** * Executes the given SQL statement (typically an INSERT statement). * Use this variant when you want to receive the values of any auto-generated columns, * such as an autoincrement ID field (or fields) and you know the column name(s) of the ID field(s). * The query may contain placeholder question marks which match the given list of parameters. * See {@link #executeInsert(GString)} for more details. *

* This method supports named and named ordinal parameters. * See the class Javadoc for more details. *

* Resource handling is performed automatically where appropriate. * * @param sql The SQL statement to execute * @param params The parameter values that will be substituted * into the SQL statement's parameter slots * @param keyColumnNames a list of column names indicating the columns that should be returned from the * inserted row or rows (some drivers may be case sensitive, e.g. may require uppercase names) * @return A list of the auto-generated row results for each inserted row (typically auto-generated keys) * @throws SQLException if a database access error occurs * @see Connection#prepareStatement(String, String[]) * @since 2.3.2 */ public List executeInsert(String sql, List params, List keyColumnNames) throws SQLException { Connection connection = createConnection(); PreparedStatement statement = null; try { this.keyColumnNames = keyColumnNames; statement = getPreparedStatement(connection, sql, params, USE_COLUMN_NAMES); this.keyColumnNames = null; this.updateCount = statement.executeUpdate(); ResultSet keys = statement.getGeneratedKeys(); return asList(sql, keys); } catch (SQLException e) { LOG.warning("Failed to execute: " + sql + " because: " + e.getMessage()); throw e; } finally { closeResources(connection, statement); } } /** * A variant of {@link #executeInsert(String, java.util.List)} * useful when providing the named parameters as named arguments. * * @param params a map containing the named parameters * @param sql The SQL statement to execute * @return A list of the auto-generated column values for each * inserted row (typically auto-generated keys) * @throws SQLException if a database access error occurs * @since 1.8.7 */ public List> executeInsert(Map params, String sql) throws SQLException { return executeInsert(sql, singletonList(params)); } /** * A variant of {@link #executeInsert(String, List, List)} * useful when providing the named parameters as named arguments. * This variant allows you to receive the values of any auto-generated columns, * such as an autoincrement ID field (or fields) when you know the column name(s) of the ID field(s). * * @param params a map containing the named parameters * @param sql The SQL statement to execute * @param keyColumnNames a list of column names indicating the columns that should be returned from the * inserted row or rows (some drivers may be case sensitive, e.g. may require uppercase names) * @return A list of the auto-generated row results for each inserted row (typically auto-generated keys) * @throws SQLException if a database access error occurs * @see Connection#prepareStatement(String, String[]) * @since 2.3.2 */ public List executeInsert(Map params, String sql, List keyColumnNames) throws SQLException { return executeInsert(sql, singletonList(params), keyColumnNames); } /** * Executes the given SQL statement (typically an INSERT statement). *

* An Object array variant of {@link #executeInsert(String, List)}. *

* This method supports named and named ordinal parameters by supplying such * parameters in the params array. See the class Javadoc for more details. * * @param sql The SQL statement to execute * @param params The parameter values that will be substituted * into the SQL statement's parameter slots * @return A list of the auto-generated column values for each * inserted row (typically auto-generated keys) * @throws SQLException if a database access error occurs */ public List> executeInsert(String sql, Object[] params) throws SQLException { return executeInsert(sql, Arrays.asList(params)); } /** * Executes the given SQL statement (typically an INSERT statement). * This variant allows you to receive the values of any auto-generated columns, * such as an autoincrement ID field (or fields) when you know the column name(s) of the ID field(s). *

* This method supports named and named ordinal parameters by supplying such * parameters in the params array. See the class Javadoc for more details. * * @param sql The SQL statement to execute * @param keyColumnNames an array of column names indicating the columns that should be returned from the * inserted row or rows (some drivers may be case sensitive, e.g. may require uppercase names) * @return A list of the auto-generated row results for each inserted row (typically auto-generated keys) * @throws SQLException if a database access error occurs * @since 2.3.2 */ public List executeInsert(String sql, String[] keyColumnNames) throws SQLException { Connection connection = createConnection(); Statement statement = null; try { statement = getStatement(connection, sql); this.updateCount = statement.executeUpdate(sql, keyColumnNames); ResultSet keys = statement.getGeneratedKeys(); return asList(sql, keys); } catch (SQLException e) { LOG.warning("Failed to execute: " + sql + " because: " + e.getMessage()); throw e; } finally { closeResources(connection, statement); } } /** * Executes the given SQL statement (typically an INSERT statement). * This variant allows you to receive the values of any auto-generated columns, * such as an autoincrement ID field (or fields) when you know the column name(s) of the ID field(s). *

* An array variant of {@link #executeInsert(String, List, List)}. *

* This method supports named and named ordinal parameters by supplying such * parameters in the params array. See the class Javadoc for more details. * * @param sql The SQL statement to execute * @param keyColumnNames an array of column names indicating the columns that should be returned from the * inserted row or rows (some drivers may be case sensitive, e.g. may require uppercase names) * @param params The parameter values that will be substituted * into the SQL statement's parameter slots * @return A list of the auto-generated row results for each inserted row (typically auto-generated keys) * @throws SQLException if a database access error occurs * @since 2.3.2 */ public List executeInsert(String sql, String[] keyColumnNames, Object[] params) throws SQLException { return executeInsert(sql, Arrays.asList(params), Arrays.asList(keyColumnNames)); } /** * Executes the given SQL statement (typically an INSERT statement). * Use this variant when you want to receive the values of any * auto-generated columns, such as an autoincrement ID field. * The query may contain GString expressions. *

* Generated key values can be accessed using * array notation. For example, to return the second auto-generated * column value of the third row, use keys[3][1]. The * method is designed to be used with SQL INSERT statements, but is * not limited to them. *

* The standard use for this method is when a table has an * autoincrement ID column and you want to know what the ID is for * a newly inserted row. In this example, we insert a single row * into a table in which the first column contains the autoincrement ID: *

     * def sql = Sql.newInstance("jdbc:mysql://localhost:3306/groovy",
     *                           "user",
     *                           "password",
     *                           "com.mysql.jdbc.Driver")
     *
     * def keys = sql.executeInsert("insert into test_table (INT_DATA, STRING_DATA) "
     *                       + "VALUES (1, 'Key Largo')")
     *
     * def id = keys[0][0]
     *
     * // 'id' now contains the value of the new row's ID column.
     * // It can be used to update an object representation's
     * // id attribute for example.
     * ...
     * 
*

* Resource handling is performed automatically where appropriate. * * @param gstring a GString containing the SQL query with embedded params * @return A list of the auto-generated column values for each * inserted row (typically auto-generated keys) * @throws SQLException if a database access error occurs * @see #expand(Object) */ public List> executeInsert(GString gstring) throws SQLException { List params = getParameters(gstring); String sql = asSql(gstring, params); return executeInsert(sql, params); } /** * Executes the given SQL statement (typically an INSERT statement). * Use this variant when you want to receive the values of any auto-generated columns, * such as an autoincrement ID field (or fields) and you know the column name(s) of the ID field(s). *

* Resource handling is performed automatically where appropriate. * * @param gstring a GString containing the SQL query with embedded params * @param keyColumnNames a list of column names indicating the columns that should be returned from the * inserted row or rows (some drivers may be case sensitive, e.g. may require uppercase names) * @return A list of the auto-generated row results for each inserted row (typically auto-generated keys) * @throws SQLException if a database access error occurs * @see Connection#prepareStatement(String, String[]) * @see #expand(Object) * @since 2.3.2 */ public List executeInsert(GString gstring, List keyColumnNames) throws SQLException { List params = getParameters(gstring); String sql = asSql(gstring, params); return executeInsert(sql, params, keyColumnNames); } /** * Executes the given SQL update. *

* Resource handling is performed automatically where appropriate. * * @param sql the SQL to execute * @return the number of rows updated or 0 for SQL statements that return nothing * @throws SQLException if a database access error occurs */ public int executeUpdate(String sql) throws SQLException { Connection connection = createConnection(); Statement statement = null; try { statement = getStatement(connection, sql); this.updateCount = statement.executeUpdate(sql); return this.updateCount; } catch (SQLException e) { LOG.warning("Failed to execute: " + sql + " because: " + e.getMessage()); throw e; } finally { closeResources(connection, statement); } } /** * Executes the given SQL update with parameters. *

* This method supports named and named ordinal parameters. * See the class Javadoc for more details. *

* Resource handling is performed automatically where appropriate. * * @param sql the SQL statement * @param params a list of parameters * @return the number of rows updated or 0 for SQL statements that return nothing * @throws SQLException if a database access error occurs */ public int executeUpdate(String sql, List params) throws SQLException { Connection connection = createConnection(); PreparedStatement statement = null; try { statement = getPreparedStatement(connection, sql, params); this.updateCount = statement.executeUpdate(); return this.updateCount; } catch (SQLException e) { LOG.warning("Failed to execute: " + sql + " because: " + e.getMessage()); throw e; } finally { closeResources(connection, statement); } } /** * A variant of {@link #executeUpdate(String, java.util.List)} * useful when providing the named parameters as named arguments. * * @param params a map containing the named parameters * @param sql the SQL statement * @return the number of rows updated or 0 for SQL statements that return nothing * @throws SQLException if a database access error occurs * @since 1.8.7 */ public int executeUpdate(Map params, String sql) throws SQLException { return executeUpdate(sql, singletonList(params)); } /** * Executes the given SQL update with parameters. *

* An Object array variant of {@link #executeUpdate(String, List)}. * * @param sql the SQL statement * @param params an array of parameters * @return the number of rows updated or 0 for SQL statements that return nothing * @throws SQLException if a database access error occurs */ public int executeUpdate(String sql, Object[] params) throws SQLException { return executeUpdate(sql, Arrays.asList(params)); } /** * Executes the given SQL update with embedded expressions inside. *

* Resource handling is performed automatically where appropriate. * * @param gstring a GString containing the SQL query with embedded params * @return the number of rows updated or 0 for SQL statements that return nothing * @throws SQLException if a database access error occurs * @see #expand(Object) */ public int executeUpdate(GString gstring) throws SQLException { List params = getParameters(gstring); String sql = asSql(gstring, params); return executeUpdate(sql, params); } /** * Performs a stored procedure call. *

* Example usage (tested with MySQL) - suppose we have the following stored procedure: *

     * sql.execute """
     *     CREATE PROCEDURE HouseSwap(_first1 VARCHAR(50), _first2 VARCHAR(50))
     *     BEGIN
     *         DECLARE _loc1 INT;
     *         DECLARE _loc2 INT;
     *         SELECT location_id into _loc1 FROM PERSON where firstname = _first1;
     *         SELECT location_id into _loc2 FROM PERSON where firstname = _first2;
     *         UPDATE PERSON
     *         set location_id = case firstname
     *             when _first1 then _loc2
     *             when _first2 then _loc1
     *         end
     *         where (firstname = _first1 OR firstname = _first2);
     *     END
     * """
     * 
* then you can invoke the procedure as follows: *
     * def rowsChanged = sql.call("{call HouseSwap('Guillaume', 'Paul')}")
     * assert rowsChanged == 2
     * 
* * @param sql the SQL statement * @return the number of rows updated or 0 for SQL statements that return nothing * @throws SQLException if a database access error occurs */ public int call(String sql) throws Exception { return call(sql, EMPTY_LIST); } /** * Performs a stored procedure call with the given embedded parameters. *

* Example usage - see {@link #call(String)} for more details about * creating a HouseSwap(IN name1, IN name2) stored procedure. * Once created, it can be called like this: *

     * def p1 = 'Paul'
     * def p2 = 'Guillaume'
     * def rowsChanged = sql.call("{call HouseSwap($p1, $p2)}")
     * assert rowsChanged == 2
     * 
*

* Resource handling is performed automatically where appropriate. * * @param gstring a GString containing the SQL query with embedded params * @return the number of rows updated or 0 for SQL statements that return nothing * @throws SQLException if a database access error occurs * @see #expand(Object) * @see #call(String) */ public int call(GString gstring) throws Exception { List params = getParameters(gstring); String sql = asSql(gstring, params); return call(sql, params); } /** * Performs a stored procedure call with the given parameters. *

* Example usage - see {@link #call(String)} for more details about * creating a HouseSwap(IN name1, IN name2) stored procedure. * Once created, it can be called like this: *

     * def rowsChanged = sql.call("{call HouseSwap(?, ?)}", ['Guillaume', 'Paul'])
     * assert rowsChanged == 2
     * 
*

* Resource handling is performed automatically where appropriate. * * @param sql the SQL statement * @param params a list of parameters * @return the number of rows updated or 0 for SQL statements that return nothing * @throws SQLException if a database access error occurs * @see #call(String) */ public int call(String sql, List params) throws Exception { Connection connection = createConnection(); CallableStatement statement = null; try { statement = getCallableStatement(connection, sql, params); return statement.executeUpdate(); } catch (SQLException e) { LOG.warning("Failed to execute: " + sql + " because: " + e.getMessage()); throw e; } finally { closeResources(connection, statement); } } /** * Performs a stored procedure call with the given parameters. *

* An Object array variant of {@link #call(String, List)}. * * @param sql the SQL statement * @param params an array of parameters * @return the number of rows updated or 0 for SQL statements that return nothing * @throws SQLException if a database access error occurs * @see #call(String) */ public int call(String sql, Object[] params) throws Exception { return call(sql, Arrays.asList(params)); } /** * Performs a stored procedure call with the given parameters. The closure * is called once with all the out parameters. *

* Example usage - suppose we create a stored procedure (ignore its simplistic implementation): *

     * // Tested with MySql 5.0.75
     * sql.execute """
     *     CREATE PROCEDURE Hemisphere(
     *         IN p_firstname VARCHAR(50),
     *         IN p_lastname VARCHAR(50),
     *         OUT ans VARCHAR(50))
     *     BEGIN
     *     DECLARE loc INT;
     *     SELECT location_id into loc FROM PERSON where firstname = p_firstname and lastname = p_lastname;
     *     CASE loc
     *         WHEN 40 THEN
     *             SET ans = 'Southern Hemisphere';
     *         ELSE
     *             SET ans = 'Northern Hemisphere';
     *     END CASE;
     *     END;
     * """
     * 
* we can now call the stored procedure as follows: *
     * sql.call '{call Hemisphere(?, ?, ?)}', ['Guillaume', 'Laforge', Sql.VARCHAR], { dwells {@code ->}
     *     println dwells
     * }
     * 
* which will output 'Northern Hemisphere'. *

* We can also access stored functions with scalar return values where the return value * will be treated as an OUT parameter. Here are examples for various databases for * creating such a procedure: *

     * // Tested with MySql 5.0.75
     * sql.execute """
     *     create function FullName(p_firstname VARCHAR(40)) returns VARCHAR(80)
     *     begin
     *         declare ans VARCHAR(80);
     *         SELECT CONCAT(firstname, ' ', lastname) INTO ans FROM PERSON WHERE firstname = p_firstname;
     *         return ans;
     *     end
     * """
     *
     * // Tested with MS SQLServer Express 2008
     * sql.execute """
     *     {@code create function FullName(@firstname VARCHAR(40)) returns VARCHAR(80)}
     *     begin
     *         declare {@code @ans} VARCHAR(80)
     *         {@code SET @ans = (SELECT firstname + ' ' + lastname FROM PERSON WHERE firstname = @firstname)}
     *         return {@code @ans}
     *     end
     * """
     *
     * // Tested with Oracle XE 10g
     * sql.execute """
     *     create function FullName(p_firstname VARCHAR) return VARCHAR is
     *     ans VARCHAR(80);
     *     begin
     *         SELECT CONCAT(CONCAT(firstname, ' '), lastname) INTO ans FROM PERSON WHERE firstname = p_firstname;
     *         return ans;
     *     end;
     * """
     * 
* and here is how you access the stored function for all databases: *
     * sql.call("{? = call FullName(?)}", [Sql.VARCHAR, 'Sam']) { name {@code ->}
     *     assert name == 'Sam Pullara'
     * }
     * 
*

* Resource handling is performed automatically where appropriate. * * @param sql the sql statement * @param params a list of parameters * @param closure called for each row with a GroovyResultSet * @throws SQLException if a database access error occurs */ public void call(String sql, List params, @ClosureParams(value=SimpleType.class, options="java.lang.Object[]") Closure closure) throws Exception { callWithRows(sql, params, NO_RESULT_SETS, closure); } /** * Performs a stored procedure call with the given parameters, * calling the closure once with all result objects. *

* See {@link #call(String, List, Closure)} for more details about * creating a Hemisphere(IN first, IN last, OUT dwells) stored procedure. * Once created, it can be called like this: *

     * def first = 'Scott'
     * def last = 'Davis'
     * sql.call "{call Hemisphere($first, $last, ${Sql.VARCHAR})}", { dwells {@code ->}
     *     println dwells
     * }
     * 
*

* As another example, see {@link #call(String, List, Closure)} for more details about * creating a FullName(IN first) stored function. * Once created, it can be called like this: *

     * def first = 'Sam'
     * sql.call("{$Sql.VARCHAR = call FullName($first)}") { name {@code ->}
     *     assert name == 'Sam Pullara'
     * }
     * 
*

* Resource handling is performed automatically where appropriate. * * @param gstring a GString containing the SQL query with embedded params * @param closure called for each row with a GroovyResultSet * @throws SQLException if a database access error occurs * @see #call(String, List, Closure) * @see #expand(Object) */ public void call(GString gstring, @ClosureParams(value=SimpleType.class, options="java.lang.Object[]") Closure closure) throws Exception { List params = getParameters(gstring); String sql = asSql(gstring, params); call(sql, params, closure); } /** * Performs a stored procedure call with the given parameters, * calling the closure once with all result objects, * and also returning the rows of the ResultSet. *

* Use this when calling a stored procedure that utilizes both * output parameters and returns a single ResultSet. *

* Once created, the stored procedure can be called like this: *

     * def first = 'Jeff'
     * def last = 'Sheets'
     * def rows = sql.callWithRows "{call Hemisphere2($first, $last, ${Sql.VARCHAR})}", { dwells {@code ->}
     *     println dwells
     * }
     * 
*

* Resource handling is performed automatically where appropriate. * * @param gstring a GString containing the SQL query with embedded params * @param closure called once with all out parameter results * @return a list of GroovyRowResult objects * @throws SQLException if a database access error occurs * @see #callWithRows(String, List, Closure) */ public List callWithRows(GString gstring, @ClosureParams(value=SimpleType.class, options="java.lang.Object[]") Closure closure) throws SQLException { List params = getParameters(gstring); String sql = asSql(gstring, params); return callWithRows(sql, params, closure); } /** * Performs a stored procedure call with the given parameters, * calling the closure once with all result objects, * and also returning the rows of the ResultSet. *

* Use this when calling a stored procedure that utilizes both * output parameters and returns a single ResultSet. *

* Once created, the stored procedure can be called like this: *

     * def rows = sql.callWithRows '{call Hemisphere2(?, ?, ?)}', ['Guillaume', 'Laforge', Sql.VARCHAR], { dwells {@code ->}
     *     println dwells
     * }
     * 
*

* Resource handling is performed automatically where appropriate. * * @param sql the sql statement * @param params a list of parameters * @param closure called once with all out parameter results * @return a list of GroovyRowResult objects * @throws SQLException if a database access error occurs * @see #callWithRows(GString, Closure) */ public List callWithRows(String sql, List params, @ClosureParams(value=SimpleType.class, options="java.lang.Object[]") Closure closure) throws SQLException { return callWithRows(sql, params, FIRST_RESULT_SET, closure).get(0); } /** * Performs a stored procedure call with the given parameters, * calling the closure once with all result objects, * and also returning a list of lists with the rows of the ResultSet(s). *

* Use this when calling a stored procedure that utilizes both * output parameters and returns multiple ResultSets. *

* Once created, the stored procedure can be called like this: *

     * def first = 'Jeff'
     * def last = 'Sheets'
     * def rowsList = sql.callWithAllRows "{call Hemisphere2($first, $last, ${Sql.VARCHAR})}", { dwells {@code ->}
     *     println dwells
     * }
     * 
*

* Resource handling is performed automatically where appropriate. * * @param gstring a GString containing the SQL query with embedded params * @param closure called once with all out parameter results * @return a list containing lists of GroovyRowResult objects * @throws SQLException if a database access error occurs * @see #callWithAllRows(String, List, Closure) */ public List> callWithAllRows(GString gstring, @ClosureParams(value=SimpleType.class, options="java.lang.Object[]") Closure closure) throws SQLException { List params = getParameters(gstring); String sql = asSql(gstring, params); return callWithAllRows(sql, params, closure); } /** * Performs a stored procedure call with the given parameters, * calling the closure once with all result objects, * and also returning a list of lists with the rows of the ResultSet(s). *

* Use this when calling a stored procedure that utilizes both * output parameters and returns multiple ResultSets. *

* Once created, the stored procedure can be called like this: *

     * def rowsList = sql.callWithAllRows '{call Hemisphere2(?, ?, ?)}', ['Guillaume', 'Laforge', Sql.VARCHAR], { dwells {@code ->}
     *     println dwells
     * }
     * 
*

* Resource handling is performed automatically where appropriate. * * @param sql the sql statement * @param params a list of parameters * @param closure called once with all out parameter results * @return a list containing lists of GroovyRowResult objects * @throws SQLException if a database access error occurs * @see #callWithRows(GString, Closure) */ public List> callWithAllRows(String sql, List params, @ClosureParams(value=SimpleType.class, options="java.lang.Object[]") Closure closure) throws SQLException { return callWithRows(sql, params, ALL_RESULT_SETS, closure); } /** * If this SQL object was created with a Connection then this method closes * the connection. If this SQL object was created from a DataSource then * this method only frees any cached objects (statements in particular). */ @Override public void close() { namedParamSqlCache.clear(); namedParamIndexPropCache.clear(); clearStatementCache(); if (useConnection != null) { try { useConnection.close(); } catch (SQLException e) { LOG.finest("Caught exception closing connection: " + e.getMessage()); } } } public DataSource getDataSource() { return dataSource; } /** * If this SQL object was created with a Connection then this method commits * the connection. If this SQL object was created from a DataSource then * this method does nothing. * * @throws SQLException if a database access error occurs */ public void commit() throws SQLException { if (useConnection == null) { LOG.info("Commit operation not supported when using datasets unless using withTransaction or cacheConnection - attempt to commit ignored"); return; } try { useConnection.commit(); } catch (SQLException e) { LOG.warning("Caught exception committing connection: " + e.getMessage()); throw e; } } /** * If this SQL object was created with a Connection then this method rolls back * the connection. If this SQL object was created from a DataSource then * this method does nothing. * * @throws SQLException if a database access error occurs */ public void rollback() throws SQLException { if (useConnection == null) { LOG.info("Rollback operation not supported when using datasets unless using withTransaction or cacheConnection - attempt to rollback ignored"); return; } try { useConnection.rollback(); } catch (SQLException e) { LOG.warning("Caught exception rolling back connection: " + e.getMessage()); throw e; } } /** * @return Returns the updateCount. */ public int getUpdateCount() { return updateCount; } /** * If this instance was created with a single Connection then the connection * is returned. Otherwise if this instance was created with a DataSource * then this method returns null * * @return the connection wired into this object, or null if this object * uses a DataSource */ public Connection getConnection() { return useConnection; } private void setConnection(Connection connection) { useConnection = connection; } /** * Allows a closure to be passed in to configure the JDBC statements before they are executed. * It can be used to do things like set the query size etc. When this method is invoked, the supplied * closure is saved. Statements subsequently created from other methods will then be * configured using this closure. The statement being configured is passed into the closure * as its single argument, e.g.: *
     * sql.withStatement{ stmt {@code ->} stmt.maxRows = 10 }
     * def firstTenRows = sql.rows("select * from table")
     * 
* * @param configureStatement the closure */ public void withStatement(@ClosureParams(value=SimpleType.class, options="java.sql.Statement") Closure configureStatement) { this.configureStatement = configureStatement; } /** * Enables statement caching.
* if cacheStatements is true, cache is created and all created prepared statements will be cached. * if cacheStatements is false, all cached statements will be properly closed. * * @param cacheStatements the new value */ public void setCacheStatements(boolean cacheStatements) { this.cacheStatements = cacheStatements; if (!cacheStatements) { clearStatementCache(); } } /** * @return boolean true if cache is enabled (default is false) */ public boolean isCacheStatements() { return cacheStatements; } /** * Caches the connection used while the closure is active. * If the closure takes a single argument, it will be called * with the connection, otherwise it will be called with no arguments. * * @param closure the given closure * @throws SQLException if a database error occurs */ public void cacheConnection(@ClosureParams(value=SimpleType.class, options="java.sql.Connection") Closure closure) throws SQLException { boolean savedCacheConnection = cacheConnection; cacheConnection = true; Connection connection = null; try { connection = createConnection(); callClosurePossiblyWithConnection(closure, connection); } finally { cacheConnection = false; closeResources(connection, null); cacheConnection = savedCacheConnection; if (dataSource != null && !cacheConnection) { useConnection = null; } } } /** * Performs the closure within a transaction using a cached connection. * If the closure takes a single argument, it will be called * with the connection, otherwise it will be called with no arguments. * * @param closure the given closure * @throws SQLException if a database error occurs */ public void withTransaction(@ClosureParams(value=SimpleType.class, options="java.sql.Connection") Closure closure) throws SQLException { boolean savedCacheConnection = cacheConnection; cacheConnection = true; Connection connection = null; boolean savedAutoCommit = true; try { connection = createConnection(); savedAutoCommit = connection.getAutoCommit(); connection.setAutoCommit(false); callClosurePossiblyWithConnection(closure, connection); connection.commit(); } catch (SQLException | Error | RuntimeException e) { handleError(connection, e); throw e; } catch (Exception e) { handleError(connection, e); throw new SQLException("Unexpected exception during transaction", e); } finally { if (connection != null) { try { connection.setAutoCommit(savedAutoCommit); } catch (SQLException e) { LOG.finest("Caught exception resetting auto commit: " + e.getMessage() + " - continuing"); } } cacheConnection = false; closeResources(connection, null); cacheConnection = savedCacheConnection; if (dataSource != null && !cacheConnection) { useConnection = null; } } } /** * Returns true if the current Sql object is currently executing a withBatch * method call. * * @return true if a withBatch call is currently being executed. */ public boolean isWithinBatch() { return withinBatch; } /** * Performs the closure (containing batch operations) within a batch. * Uses a batch size of zero, i.e. no automatic partitioning of batches. *

* This means that executeBatch() will be called automatically after the withBatch * closure has finished but may be called explicitly if desired as well for more fine-grained * partitioning of the batch. *

* The closure will be called with a single argument; the database * statement (actually a BatchingStatementWrapper helper object) * associated with this batch. *

* Use it like this: *

     * def updateCounts = sql.withBatch { stmt {@code ->}
     *     stmt.addBatch("insert into TABLENAME ...")
     *     stmt.addBatch("insert into TABLENAME ...")
     *     stmt.addBatch("insert into TABLENAME ...")
     *     ...
     * }
     * 
* For integrity and performance reasons, you may wish to consider executing your batch command(s) within a transaction: *
     * sql.withTransaction {
     *     def result1 = sql.withBatch { ... }
     *     ...
     * }
     * 
* * @param closure the closure containing batch and optionally other statements * @return an array of update counts containing one element for each * command in the batch. The elements of the array are ordered according * to the order in which commands were added to the batch. * @throws SQLException if a database access error occurs, * or this method is called on a closed Statement, or the * driver does not support batch statements. Throws {@link java.sql.BatchUpdateException} * (a subclass of SQLException) if one of the commands sent to the * database fails to execute properly or attempts to return a result set. * @see #withBatch(int, Closure) */ public int[] withBatch(@ClosureParams(value=SimpleType.class, options="groovy.sql.BatchingStatementWrapper") Closure closure) throws SQLException { return withBatch(0, closure); } /** * Performs the closure (containing batch operations) within a batch using a given batch size. *

* After every batchSize addBatch(sqlBatchOperation) * operations, automatically calls an executeBatch() operation to "chunk" up the database operations * into partitions. Though not normally needed, you can also explicitly call executeBatch() which * after executing the current batch, resets the batch count back to zero. *

* The closure will be called with a single argument; the database statement * (actually a BatchingStatementWrapper helper object) * associated with this batch. *

* Use it like this for batchSize of 20: *

     * def updateCounts = sql.withBatch(20) { stmt {@code ->}
     *     stmt.addBatch("insert into TABLENAME ...")
     *     stmt.addBatch("insert into TABLENAME ...")
     *     stmt.addBatch("insert into TABLENAME ...")
     *     ...
     * }
     * 
* For integrity and performance reasons, you may wish to consider executing your batch command(s) within a transaction: *
     * sql.withTransaction {
     *     def result1 = sql.withBatch { ... }
     *     ...
     * }
     * 
* * @param batchSize partition the batch into batchSize pieces, i.e. after batchSize * addBatch() invocations, call executeBatch() automatically; * 0 means manual calls to executeBatch are required * @param closure the closure containing batch and optionally other statements * @return an array of update counts containing one element for each * command in the batch. The elements of the array are ordered according * to the order in which commands were added to the batch. * @throws SQLException if a database access error occurs, * or this method is called on a closed Statement, or the * driver does not support batch statements. Throws {@link java.sql.BatchUpdateException} * (a subclass of SQLException) if one of the commands sent to the * database fails to execute properly or attempts to return a result set. * @see #withBatch(Closure) * @see BatchingStatementWrapper * @see Statement */ public int[] withBatch(int batchSize, @ClosureParams(value=SimpleType.class, options="groovy.sql.BatchingStatementWrapper") Closure closure) throws SQLException { Connection connection = createConnection(); BatchingStatementWrapper statement = null; boolean savedWithinBatch = withinBatch; try { withinBatch = true; statement = new BatchingStatementWrapper(createStatement(connection), batchSize, LOG); closure.call(statement); return statement.executeBatch(); } catch (SQLException e) { LOG.warning("Error during batch execution: " + e.getMessage()); throw e; } finally { closeResources(statement); closeResources(connection); withinBatch = savedWithinBatch; } } /** * Performs the closure (containing batch operations specific to an associated prepared statement) * within a batch. Uses a batch size of zero, i.e. no automatic partitioning of batches. *

* This means that executeBatch() will be called automatically after the withBatch * closure has finished but may be called explicitly if desired as well for more fine-grained * partitioning of the batch. *

* The closure will be called with a single argument; the prepared * statement (actually a BatchingPreparedStatementWrapper helper object) * associated with this batch. *

* An example: *

     * def updateCounts = sql.withBatch('insert into TABLENAME(a, b, c) values (?, ?, ?)') { ps {@code ->}
     *     ps.addBatch([10, 12, 5])
     *     ps.addBatch([7, 3, 98])
     *     ps.addBatch(22, 67, 11)
     *     def partialUpdateCounts = ps.executeBatch() // optional interim batching
     *     ps.addBatch(30, 40, 50)
     *     ...
     * }
     * 
* For integrity and performance reasons, you may wish to consider executing your batch command(s) within a transaction: *
     * sql.withTransaction {
     *     def result1 = sql.withBatch { ... }
     *     ...
     * }
     * 
* * @param sql batch update statement * @param closure the closure containing batch statements (to bind parameters) and optionally other statements * @return an array of update counts containing one element for each * binding in the batch. The elements of the array are ordered according * to the order in which commands were executed. * @throws SQLException if a database access error occurs, * or this method is called on a closed Statement, or the * driver does not support batch statements. Throws {@link java.sql.BatchUpdateException} * (a subclass of SQLException) if one of the commands sent to the * database fails to execute properly or attempts to return a result set. * @see #withBatch(int, String, Closure) * @see BatchingPreparedStatementWrapper * @see PreparedStatement */ public int[] withBatch(String sql, @ClosureParams(value=SimpleType.class, options="groovy.sql.BatchingPreparedStatementWrapper") Closure closure) throws SQLException { return withBatch(0, sql, closure); } /** * Performs the closure (containing batch operations specific to an associated prepared statement) * within a batch using a given batch size. *

* After every batchSize addBatch(params) * operations, automatically calls an executeBatch() operation to "chunk" up the database operations * into partitions. Though not normally needed, you can also explicitly call executeBatch() which * after executing the current batch, resets the batch count back to zero. *

* The closure will be called with a single argument; the prepared * statement (actually a BatchingPreparedStatementWrapper helper object) * associated with this batch. *

* Below is an example using a batchSize of 20: *

     * def updateCounts = sql.withBatch(20, 'insert into TABLENAME(a, b, c) values (?, ?, ?)') { ps {@code ->}
     *     ps.addBatch(10, 12, 5)      // varargs style
     *     ps.addBatch([7, 3, 98])     // list
     *     ps.addBatch([22, 67, 11])
     *     ...
     * }
     * 
* Named parameters (into maps or domain objects) are also supported: *
     * def updateCounts = sql.withBatch(20, 'insert into TABLENAME(a, b, c) values (:foo, :bar, :baz)') { ps {@code ->}
     *     ps.addBatch([foo:10, bar:12, baz:5])  // map
     *     ps.addBatch(foo:7, bar:3, baz:98)     // Groovy named args allow outer brackets to be dropped
     *     ...
     * }
     * 
* Named ordinal parameters (into maps or domain objects) are also supported: *
     * def updateCounts = sql.withBatch(20, 'insert into TABLENAME(a, b, c) values (?1.foo, ?2.bar, ?2.baz)') { ps {@code ->}
     *     ps.addBatch([[foo:22], [bar:67, baz:11]])  // list of maps or domain objects
     *     ps.addBatch([foo:10], [bar:12, baz:5])     // varargs allows outer brackets to be dropped
     *     ps.addBatch([foo:7], [bar:3, baz:98])
     *     ...
     * }
     * // swap to batch size of 5 and illustrate simple and domain object cases ...
     * class Person { String first, last }
     * def updateCounts2 = sql.withBatch(5, 'insert into PERSON(id, first, last) values (?1, ?2.first, ?2.last)') { ps {@code ->}
     *     ps.addBatch(1, new Person(first:'Peter', last:'Pan'))
     *     ps.addBatch(2, new Person(first:'Snow', last:'White'))
     *     ...
     * }
     * 
* For integrity and performance reasons, you may wish to consider executing your batch command(s) within a transaction: *
     * sql.withTransaction {
     *     def result1 = sql.withBatch { ... }
     *     ...
     * }
     * 
* * @param batchSize partition the batch into batchSize pieces, i.e. after batchSize * addBatch() invocations, call executeBatch() automatically; * 0 means manual calls to executeBatch are required if additional partitioning of the batch is required * @param sql batch update statement * @param closure the closure containing batch statements (to bind parameters) and optionally other statements * @return an array of update counts containing one element for each * binding in the batch. The elements of the array are ordered according * to the order in which commands were executed. * @throws SQLException if a database access error occurs, * or this method is called on a closed Statement, or the * driver does not support batch statements. Throws {@link java.sql.BatchUpdateException} * (a subclass of SQLException) if one of the commands sent to the * database fails to execute properly or attempts to return a result set. * @see BatchingPreparedStatementWrapper * @see PreparedStatement */ public int[] withBatch(int batchSize, String sql, @ClosureParams(value=SimpleType.class, options="groovy.sql.BatchingPreparedStatementWrapper") Closure closure) throws SQLException { Connection connection = createConnection(); List indexPropList = null; SqlWithParams preCheck = buildSqlWithIndexedProps(sql); boolean savedWithinBatch = withinBatch; BatchingPreparedStatementWrapper psWrapper = null; if (preCheck != null) { indexPropList = new ArrayList(); for (Object next : preCheck.getParams()) { indexPropList.add((Tuple) next); } sql = preCheck.getSql(); } try { withinBatch = true; PreparedStatement statement = (PreparedStatement) getAbstractStatement(new CreatePreparedStatementCommand(0), connection, sql); configure(statement); psWrapper = new BatchingPreparedStatementWrapper(statement, indexPropList, batchSize, LOG, this); closure.call(psWrapper); return psWrapper.executeBatch(); } catch (SQLException e) { LOG.warning("Error during batch execution of '" + sql + "' with message: " + e.getMessage()); throw e; } finally { closeResources(psWrapper); closeResources(connection); withinBatch = savedWithinBatch; } } /** * Caches every created preparedStatement in Closure closure * Every cached preparedStatement is closed after closure has been called. * If the closure takes a single argument, it will be called * with the connection, otherwise it will be called with no arguments. * * @param closure the given closure * @throws SQLException if a database error occurs * @see #setCacheStatements(boolean) */ public void cacheStatements(@ClosureParams(value=SimpleType.class, options="java.sql.Connection") Closure closure) throws SQLException { boolean savedCacheStatements = cacheStatements; cacheStatements = true; Connection connection = null; try { connection = createConnection(); callClosurePossiblyWithConnection(closure, connection); } finally { cacheStatements = false; closeResources(connection, null); cacheStatements = savedCacheStatements; } } // protected implementation methods - extension points for subclasses //------------------------------------------------------------------------- /** * Base internal method for call(), callWithRows(), and callWithAllRows() style of methods. *

* Performs a stored procedure call with the given parameters, * calling the closure once with all result objects, * and also returning the rows of the ResultSet(s) (if processResultSets is set to * Sql.FIRST_RESULT_SET, Sql.ALL_RESULT_SETS) *

* Main purpose of processResultSets param is to retain original call() method * performance when this is set to Sql.NO_RESULT_SETS *

* Resource handling is performed automatically where appropriate. * * @param sql the sql statement * @param params a list of parameters * @param processResultsSets the result sets to process, either Sql.NO_RESULT_SETS, Sql.FIRST_RESULT_SET, or Sql.ALL_RESULT_SETS * @param closure called once with all out parameter results * @return a list of GroovyRowResult objects * @throws SQLException if a database access error occurs * @see #callWithRows(String, List, Closure) */ protected List> callWithRows(String sql, List params, int processResultsSets, @ClosureParams(value=SimpleType.class, options="java.lang.Object[]") Closure closure) throws SQLException { Connection connection = createConnection(); CallableStatement statement = null; List resultSetResources = new ArrayList(); try { statement = getCallableStatement(connection, sql, params); boolean hasResultSet = statement.execute(); List results = new ArrayList(); int indx = 0; int inouts = 0; for (Object value : params) { if (value instanceof OutParameter) { if (value instanceof ResultSetOutParameter) { GroovyResultSet resultSet = CallResultSet.getImpl(statement, indx); resultSetResources.add(resultSet); results.add(resultSet); } else { Object o = statement.getObject(indx + 1); if (o instanceof ResultSet) { GroovyResultSet resultSet = new GroovyResultSetProxy((ResultSet) o).getImpl(); results.add(resultSet); resultSetResources.add(resultSet); } else { results.add(o); } } inouts++; } indx++; } closure.call(results.toArray(new Object[inouts])); List> resultSets = new ArrayList>(); if (processResultsSets == NO_RESULT_SETS) { resultSets.add(new ArrayList()); return resultSets; } //Check both hasResultSet and getMoreResults() because of differences in vendor behavior if (!hasResultSet) { hasResultSet = statement.getMoreResults(); } while (hasResultSet && (processResultsSets != NO_RESULT_SETS)) { resultSets.add(asList(sql, statement.getResultSet())); if (processResultsSets == FIRST_RESULT_SET) { break; } else { hasResultSet = statement.getMoreResults(); } } return resultSets; } catch (SQLException e) { LOG.warning("Failed to execute: " + sql + " because: " + e.getMessage()); throw e; } finally { for (GroovyResultSet rs : resultSetResources) { closeResources(null, null, rs); } closeResources(connection, statement); } } /** * Useful helper method which handles resource management when executing a * query which returns a result set. * Derived classes of Sql can override "createQueryCommand" and then call * this method to access the ResultSet returned from the provided query * or alternatively can use the higher-level method of Sql which return * result sets which are funnelled through this method, e.g. eachRow, query. * * @param sql query to execute * @return the resulting ResultSet * @throws SQLException if a database error occurs */ protected final ResultSet executeQuery(String sql) throws SQLException { AbstractQueryCommand command = createQueryCommand(sql); ResultSet rs = null; try { rs = command.execute(); } finally { command.closeResources(); } return rs; } /** * Useful helper method which handles resource management when executing a * prepared query which returns a result set. * Derived classes of Sql can override "createPreparedQueryCommand" and then * call this method to access the ResultSet returned from the provided query. * * @param sql query to execute * @param params parameters matching question mark placeholders in the query * @return the resulting ResultSet * @throws SQLException if a database error occurs */ protected final ResultSet executePreparedQuery(String sql, List params) throws SQLException { AbstractQueryCommand command = createPreparedQueryCommand(sql, params); ResultSet rs = null; try { rs = command.execute(); } finally { command.closeResources(); } return rs; } /** * Hook to allow derived classes to override list of result collection behavior. * The default behavior is to return a list of GroovyRowResult objects corresponding * to each row in the ResultSet. * * @param sql query to execute * @param rs the ResultSet to process * @return the resulting list of rows * @throws SQLException if a database error occurs */ protected List asList(String sql, ResultSet rs) throws SQLException { return asList(sql, rs, null); } /** * Hook to allow derived classes to override list of result collection behavior. * The default behavior is to return a list of GroovyRowResult objects corresponding * to each row in the ResultSet. * * @param sql query to execute * @param rs the ResultSet to process * @param metaClosure called for meta data (only once after sql execution) * @return the resulting list of rows * @throws SQLException if a database error occurs */ protected List asList(String sql, ResultSet rs, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure) throws SQLException { return asList(sql, rs, 0, 0, metaClosure); } protected List asList(String sql, ResultSet rs, int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="java.sql.ResultSetMetaData") Closure metaClosure) throws SQLException { List results = new ArrayList(); try { if (metaClosure != null) { metaClosure.call(rs.getMetaData()); } boolean cursorAtRow = moveCursor(rs, offset); if (!cursorAtRow) return null; int i = 0; while ((maxRows <= 0 || i++ < maxRows) && rs.next()) { results.add(toRowResult(rs)); } return (results); } catch (SQLException e) { LOG.warning("Failed to retrieve row from ResultSet for: " + sql + " because: " + e.getMessage()); throw e; } finally { rs.close(); } } /** * Hook to allow derived classes to override sql generation from GStrings. * * @param gstring a GString containing the SQL query with embedded params * @param values the values to embed * @return the SQL version of the given query using ? instead of any parameter * @see #expand(Object) */ protected String asSql(GString gstring, List values) { String[] strings = gstring.getStrings(); if (strings.length <= 0) { throw new IllegalArgumentException("No SQL specified in GString: " + gstring); } boolean nulls = false; StringBuilder buffer = new StringBuilder(); boolean warned = false; Iterator iter = values.iterator(); for (int i = 0; i < strings.length; i++) { String text = strings[i]; if (text != null) { buffer.append(text); } if (iter.hasNext()) { Object value = iter.next(); if (value != null) { if (value instanceof ExpandedVariable) { buffer.append(((ExpandedVariable) value).getObject()); iter.remove(); } else { boolean validBinding = true; if (i < strings.length - 1) { String nextText = strings[i + 1]; if ((text.endsWith("\"") || text.endsWith("'")) && (nextText.startsWith("'") || nextText.startsWith("\""))) { if (!warned) { LOG.warning("In Groovy SQL please do not use quotes around dynamic expressions " + "(which start with $) as this means we cannot use a JDBC PreparedStatement " + "and so is a security hole. Groovy has worked around your mistake but the security hole is still there. " + "The expression so far is: " + buffer.toString() + "?" + nextText); warned = true; } buffer.append(value); iter.remove(); validBinding = false; } } if (validBinding) { buffer.append("?"); } } } else { nulls = true; iter.remove(); buffer.append("?'\"?"); // will replace these with nullish values } } } String sql = buffer.toString(); if (nulls) { sql = nullify(sql); } return sql; } /** * Hook to allow derived classes to override null handling. * Default behavior is to replace ?'"? references with NULLish * * @param sql the SQL statement * @return the modified SQL String */ protected String nullify(String sql) { /* * Some drivers (Oracle classes12.zip) have difficulty resolving data * type if setObject(null). We will modify the query to pass 'null', 'is * null', and 'is not null' */ //could be more efficient by compiling expressions in advance. int firstWhere = findWhereKeyword(sql); if (firstWhere >= 0) { Pattern[] patterns = {Pattern.compile("(?is)^(.{" + firstWhere + "}.*?)!=\\s{0,1}(\\s*)\\?'\"\\?(.*)"), Pattern.compile("(?is)^(.{" + firstWhere + "}.*?)<>\\s{0,1}(\\s*)\\?'\"\\?(.*)"), Pattern.compile("(?is)^(.{" + firstWhere + "}.*?[^<>])=\\s{0,1}(\\s*)\\?'\"\\?(.*)"),}; String[] replacements = {"$1 is not $2null$3", "$1 is not $2null$3", "$1 is $2null$3",}; for (int i = 0; i < patterns.length; i++) { Matcher matcher = patterns[i].matcher(sql); while (matcher.matches()) { sql = matcher.replaceAll(replacements[i]); matcher = patterns[i].matcher(sql); } } } return sql.replaceAll("\\?'\"\\?", "null"); } /** * Hook to allow derived classes to override where clause sniffing. * Default behavior is to find the first 'where' keyword in the sql * doing simple avoidance of the word 'where' within quotes. * * @param sql the SQL statement * @return the index of the found keyword or -1 if not found */ protected int findWhereKeyword(String sql) { char[] chars = sql.toLowerCase().toCharArray(); char[] whereChars = "where".toCharArray(); int i = 0; boolean inString = false; //TODO: Cater for comments? int inWhere = 0; while (i < chars.length) { switch (chars[i]) { case '\'': inString = !inString; break; default: if (!inString && chars[i] == whereChars[inWhere]) { inWhere++; if (inWhere == whereChars.length) { return i; } } else { inWhere = 0; } } i++; } return -1; } /** * Hook to allow derived classes to override behavior associated with * extracting params from a GString. * * @param gstring a GString containing the SQL query with embedded params * @return extracts the parameters from the expression as a List * @see #expand(Object) */ protected List getParameters(GString gstring) { return new ArrayList(Arrays.asList(gstring.getValues())); } /** * Hook to allow derived classes to override behavior associated with * setting params for a prepared statement. Default behavior is to * append the parameters to the given statement using setObject. * * @param params the parameters to append * @param statement the statement * @throws SQLException if a database access error occurs */ protected void setParameters(List params, PreparedStatement statement) throws SQLException { int i = 1; ParameterMetaData metaData = getParameterMetaDataSafe(statement); if (metaData != null) { if (metaData.getParameterCount() == 0 && params.size() == 1 && params.get(0) instanceof Map) { Map paramsMap = (Map) params.get(0); if (paramsMap.isEmpty()) return; } // GROOVY-8174: we'd like stricter checking here but many drivers currently in use just aren't consistent enough, so we log if (metaData.getParameterCount() != params.size()) { LOG.warning("Found " + metaData.getParameterCount() + " parameter placeholders but supplied with " + params.size() + " parameters"); } } for (Object value : params) { setObject(statement, i++, value); } } private ParameterMetaData getParameterMetaDataSafe(PreparedStatement statement) throws SQLException { try { return statement.getParameterMetaData(); } catch(SQLException se) { LOG.fine("Unable to retrieve parameter metadata - reduced checking will occur: " + se.getMessage()); return null; } } /** * Strategy method allowing derived classes to handle types differently * such as for CLOBs etc. * * @param statement the statement of interest * @param i the index of the object of interest * @param value the new object value * @throws SQLException if a database access error occurs */ protected void setObject(PreparedStatement statement, int i, Object value) throws SQLException { if (value instanceof InParameter || value instanceof OutParameter) { if (value instanceof InParameter) { InParameter in = (InParameter) value; Object val = in.getValue(); if (null == val) { statement.setNull(i, in.getType()); } else { statement.setObject(i, val, in.getType()); } } if (value instanceof OutParameter) { try { OutParameter out = (OutParameter) value; ((CallableStatement) statement).registerOutParameter(i, out.getType()); } catch (ClassCastException e) { throw new SQLException("Cannot register out parameter."); } } } else { try { statement.setObject(i, value); } catch (SQLException e) { if (value == null) { SQLException se = new SQLException("Your JDBC driver may not support null arguments for setObject. Consider using Groovy's InParameter feature." + (e.getMessage() == null ? "" : " (CAUSE: " + e.getMessage() + ")")); se.setNextException(e); throw se; } else { throw e; } } } } /** * An extension point allowing derived classes to change the behavior of * connection creation. The default behavior is to either use the * supplied connection or obtain it from the supplied datasource. * * @return the connection associated with this Sql * @throws java.sql.SQLException if a SQL error occurs */ protected Connection createConnection() throws SQLException { if ((cacheStatements || cacheConnection) && useConnection != null) { return useConnection; } if (dataSource != null) { // Use a doPrivileged here as many different properties need to be // read, and the policy shouldn't have to list them all. Connection con; try { con = AccessController.doPrivileged(new PrivilegedExceptionAction() { public Connection run() throws SQLException { return dataSource.getConnection(); } }); } catch (PrivilegedActionException pae) { Exception e = pae.getException(); if (e instanceof SQLException) { throw (SQLException) e; } else { throw (RuntimeException) e; } } if (cacheStatements || cacheConnection) { useConnection = con; } return con; } return useConnection; } /** * An extension point allowing derived classes to change the behavior * of resource closing. * * @param connection the connection to close * @param statement the statement to close * @param results the results to close */ protected void closeResources(Connection connection, Statement statement, ResultSet results) { if (results != null) { try { results.close(); } catch (SQLException e) { LOG.finest("Caught exception closing resultSet: " + e.getMessage() + " - continuing"); } } closeResources(connection, statement); } /** * An extension point allowing the behavior of resource closing to be * overridden in derived classes. * * @param connection the connection to close * @param statement the statement to close */ protected void closeResources(Connection connection, Statement statement) { if (cacheStatements) return; if (statement != null) { try { statement.close(); } catch (SQLException e) { LOG.finest("Caught exception closing statement: " + e.getMessage() + " - continuing"); } } closeResources(connection); } private void closeResources(BatchingPreparedStatementWrapper statement) { if (cacheStatements) return; closeResources((BatchingStatementWrapper) statement); } private static void closeResources(BatchingStatementWrapper statement) { if (statement != null) { try { statement.close(); } catch (SQLException e) { LOG.finest("Caught exception closing statement: " + e.getMessage() + " - continuing"); } } } /** * An extension point allowing the behavior of resource closing to be * overridden in derived classes. * * @param connection the connection to close */ protected void closeResources(Connection connection) { if (cacheConnection) return; if (connection != null && dataSource != null) { try { connection.close(); } catch (SQLException e) { LOG.finest("Caught exception closing connection: " + e.getMessage() + " - continuing"); } } } /** * Provides a hook for derived classes to be able to configure JDBC statements. * Default behavior is to call a previously saved closure, if any, using the * statement as a parameter. * * @param statement the statement to configure */ protected void configure(Statement statement) { // for thread safety, grab local copy Closure configureStatement = this.configureStatement; if (configureStatement != null) { configureStatement.call(statement); } } // private implementation methods //------------------------------------------------------------------------- private static List> calculateKeys(ResultSet keys) throws SQLException { // Prepare a list to contain the auto-generated column // values, and then fetch them from the statement. List> autoKeys = new ArrayList>(); int count = keys.getMetaData().getColumnCount(); // Copy the column values into a list of a list. while (keys.next()) { List rowKeys = new ArrayList(count); for (int i = 1; i <= count; i++) { rowKeys.add(keys.getObject(i)); } autoKeys.add(rowKeys); } return autoKeys; } private Statement createStatement(Connection connection) throws SQLException { if (resultSetHoldability == -1) { return connection.createStatement(resultSetType, resultSetConcurrency); } return connection.createStatement(resultSetType, resultSetConcurrency, resultSetHoldability); } private static void handleError(Connection connection, Throwable t) throws SQLException { if (connection != null) { LOG.warning("Rolling back due to: " + t.getMessage()); connection.rollback(); } } private static void callClosurePossiblyWithConnection(Closure closure, Connection connection) { if (closure.getMaximumNumberOfParameters() == 1) { closure.call(connection); } else { closure.call(); } } private void clearStatementCache() { Statement statements[]; if (statementCache.isEmpty()) return; statements = new Statement[statementCache.size()]; statementCache.values().toArray(statements); statementCache.clear(); for (Statement s : statements) { try { s.close(); } catch (Exception e) { // It's normally safe to ignore exceptions during cleanup but here if there is // a closed statement in the cache, the cache is possibly corrupted, hence log // at slightly elevated level than similar cases. LOG.info("Failed to close statement. Already closed? Exception message: " + e.getMessage()); } } } private Statement getAbstractStatement(AbstractStatementCommand cmd, Connection connection, String sql) throws SQLException { Statement stmt; if (cacheStatements) { stmt = statementCache.get(sql); if (stmt == null) { stmt = cmd.execute(connection, sql); statementCache.put(sql, stmt); } } else { stmt = cmd.execute(connection, sql); } return stmt; } private Statement getStatement(Connection connection, String sql) throws SQLException { LOG.fine(sql); Statement stmt = getAbstractStatement(new CreateStatementCommand(), connection, sql); configure(stmt); return stmt; } private PreparedStatement getPreparedStatement(Connection connection, String sql, List params, int returnGeneratedKeys) throws SQLException { SqlWithParams updated = checkForNamedParams(sql, params); LOG.fine(updated.getSql() + " | " + updated.getParams()); PreparedStatement statement = (PreparedStatement) getAbstractStatement(new CreatePreparedStatementCommand(returnGeneratedKeys), connection, updated.getSql()); setParameters(updated.getParams(), statement); configure(statement); return statement; } private CallableStatement getCallableStatement(Connection connection, String sql, List params) throws SQLException { LOG.fine(sql + " | " + params); CallableStatement statement = (CallableStatement) getAbstractStatement(new CreateCallableStatementCommand(), connection, sql); setParameters(params, statement); configure(statement); return statement; } public SqlWithParams checkForNamedParams(String sql, List params) { SqlWithParams preCheck = buildSqlWithIndexedProps(sql); if (preCheck == null) { return new SqlWithParams(sql, params); } List indexPropList = new ArrayList(); for (Object next : preCheck.getParams()) { indexPropList.add((Tuple) next); } return new SqlWithParams(preCheck.getSql(), getUpdatedParams(params, indexPropList)); } /** * @deprecated Use {@link #buildSqlWithIndexedProps(String)} instead */ @Deprecated public SqlWithParams preCheckForNamedParams(String sql) { return buildSqlWithIndexedProps(sql); } /** * Hook to allow derived classes to override behavior associated with the * parsing and indexing of parameters from a given sql statement. * * @param sql the sql statement to process * @return a {@link SqlWithParams} instance containing the parsed sql * and parameters containing the indexed location and property * name of parameters or {@code null} if no parsing of * the sql was performed. */ protected SqlWithParams buildSqlWithIndexedProps(String sql) { // look for quick exit if (!enableNamedQueries || !ExtractIndexAndSql.hasNamedParameters(sql)) { return null; } String newSql; List propList; if (cacheNamedQueries && namedParamSqlCache.containsKey(sql)) { newSql = namedParamSqlCache.get(sql); propList = namedParamIndexPropCache.get(sql); } else { ExtractIndexAndSql extractIndexAndSql = ExtractIndexAndSql.from(sql); newSql = extractIndexAndSql.getNewSql(); propList = extractIndexAndSql.getIndexPropList(); namedParamSqlCache.put(sql, newSql); namedParamIndexPropCache.put(sql, propList); } if (sql.equals(newSql)) { return null; } List indexPropList = new ArrayList(propList); return new SqlWithParams(newSql, indexPropList); } public List getUpdatedParams(List params, List indexPropList) { List updatedParams = new ArrayList(); for (Tuple tuple : indexPropList) { int index = (Integer) tuple.get(0); String prop = (String) tuple.get(1); if (index < 0 || index >= params.size()) throw new IllegalArgumentException("Invalid index " + index + " should be in range 1.." + params.size()); try { updatedParams.add(prop.equals("") ? params.get(index) : InvokerHelper.getProperty(params.get(index), prop)); } catch(MissingPropertyException mpe) { throw new IllegalArgumentException("Property '" + prop + "' not found for parameter " + index); } } return updatedParams; } private PreparedStatement getPreparedStatement(Connection connection, String sql, List params) throws SQLException { return getPreparedStatement(connection, sql, params, 0); } /** * @return boolean true if caching is enabled (the default is true) */ public boolean isCacheNamedQueries() { return cacheNamedQueries; } /** * Enables named query caching.
* if cacheNamedQueries is true, cache is created and processed named queries will be cached. * if cacheNamedQueries is false, no caching will occur saving memory at the cost of additional processing time. * * @param cacheNamedQueries the new value */ public void setCacheNamedQueries(boolean cacheNamedQueries) { this.cacheNamedQueries = cacheNamedQueries; } /** * @return boolean true if named query processing is enabled (the default is true) */ public boolean isEnableNamedQueries() { return enableNamedQueries; } /** * Enables named query support: *
    *
  • if enableNamedQueries is true, queries with ':propname' and '?1.propname' style placeholders will be processed.
  • *
  • if enableNamedQueries is false, this feature will be turned off.
  • *
* * @param enableNamedQueries the new value */ public void setEnableNamedQueries(boolean enableNamedQueries) { this.enableNamedQueries = enableNamedQueries; } // command pattern implementation classes //------------------------------------------------------------------------- private abstract class AbstractStatementCommand { /** * Execute the command that's defined by the subclass following * the Command pattern. Specialized parameters are held in the command instances. * * @param conn all commands accept a connection * @param sql all commands accept an SQL statement * @return statement that can be cached, etc. * @throws SQLException if a database error occurs */ protected abstract Statement execute(Connection conn, String sql) throws SQLException; } private class CreatePreparedStatementCommand extends AbstractStatementCommand { private final int returnGeneratedKeys; private CreatePreparedStatementCommand(int returnGeneratedKeys) { this.returnGeneratedKeys = returnGeneratedKeys; } @Override protected PreparedStatement execute(Connection connection, String sql) throws SQLException { if (returnGeneratedKeys == USE_COLUMN_NAMES && keyColumnNames != null) { return connection.prepareStatement(sql, keyColumnNames.toArray(EMPTY_STRING_ARRAY)); } if (returnGeneratedKeys != 0) { return connection.prepareStatement(sql, returnGeneratedKeys); } if (appearsLikeStoredProc(sql)) { if (resultSetHoldability == -1) { return connection.prepareCall(sql, resultSetType, resultSetConcurrency); } return connection.prepareCall(sql, resultSetType, resultSetConcurrency, resultSetHoldability); } if (resultSetHoldability == -1) { return connection.prepareStatement(sql, resultSetType, resultSetConcurrency); } return connection.prepareStatement(sql, resultSetType, resultSetConcurrency, resultSetHoldability); } private boolean appearsLikeStoredProc(String sql) { return sql.matches("\\s*[{]?\\s*[?]?\\s*[=]?\\s*[cC][aA][lL][lL].*"); } } private class CreateCallableStatementCommand extends AbstractStatementCommand { @Override protected CallableStatement execute(Connection connection, String sql) throws SQLException { return connection.prepareCall(sql); } } private class CreateStatementCommand extends AbstractStatementCommand { @Override protected Statement execute(Connection conn, String sql) throws SQLException { return createStatement(conn); } } protected abstract class AbstractQueryCommand { protected final String sql; protected Statement statement; private Connection connection; private int maxRows = 0; protected AbstractQueryCommand(String sql) { // Don't create statement in subclass constructors to avoid throw in constructors this.sql = sql; } /** * Execute the command that's defined by the subclass following * the Command pattern. Specialized parameters are held in the command instances. * * @return ResultSet from executing a query * @throws SQLException if a database error occurs */ protected final ResultSet execute() throws SQLException { connection = createConnection(); setInternalConnection(connection); statement = null; try { // The variation in the pattern is isolated ResultSet result = runQuery(connection); assert (null != statement); return result; } catch (SQLException e) { LOG.warning("Failed to execute: " + sql + " because: " + e.getMessage()); closeResources(); connection = null; statement = null; throw e; } } /** * After performing the execute operation and making use of its return, it's necessary * to free the resources allocated for the statement. */ protected final void closeResources() { Sql.this.closeResources(connection, statement); } /** * After performing the execute operation and making use of its return, it's necessary * to free the resources allocated for the statement. * * @param rs allows the caller to conveniently close its resource as well */ protected final void closeResources(ResultSet rs) { Sql.this.closeResources(connection, statement, rs); } /** * Perform the query. Must set statement field so that the main ({@link #execute()}) method can clean up. * This is the method that encloses the variant part of the code. * * @param connection the connection to use * @return ResultSet from an executeQuery method. * @throws SQLException if a database error occurs */ protected abstract ResultSet runQuery(Connection connection) throws SQLException; /** * Set the maximum number of rows to return in the ResultSet * * @param maxRows the maximum number of rows */ protected void setMaxRows(int maxRows) { this.maxRows = maxRows; } /** * Get the maximum number of rows to return in the ResultSet * * @return the maximum number of rows */ protected int getMaxRows() { return maxRows; } } private final class PreparedQueryCommand extends AbstractQueryCommand { private List params; private PreparedQueryCommand(String sql, List queryParams) { super(sql); params = queryParams; } @Override protected ResultSet runQuery(Connection connection) throws SQLException { PreparedStatement s = getPreparedStatement(connection, sql, params); statement = s; if (getMaxRows() != 0) statement.setMaxRows(getMaxRows()); return s.executeQuery(); } } private final class QueryCommand extends AbstractQueryCommand { private QueryCommand(String sql) { super(sql); } @Override protected ResultSet runQuery(Connection connection) throws SQLException { statement = getStatement(connection, sql); if (getMaxRows() != 0) statement.setMaxRows(getMaxRows()); return statement.executeQuery(sql); } } /** * Factory for the QueryCommand command pattern object allows subclasses to * supply implementations of the command class. The factory will be used in a pattern * similar to: *
     * AbstractQueryCommand q = createQueryCommand("update TABLE set count = 0) where count is null");
     * try {
     *     ResultSet rs = q.execute();
     *     return asList(rs);
     * } finally {
     *     q.closeResources();
     * }
     * 
* * @param sql statement to be executed * @return a command - invoke its execute() and closeResource() methods */ protected AbstractQueryCommand createQueryCommand(String sql) { return new QueryCommand(sql); } /** * Factory for the PreparedQueryCommand command pattern object allows subclass to supply implementations * of the command class. * * @param sql statement to be executed, including optional parameter placeholders (?) * @param queryParams List of parameter values corresponding to parameter placeholders * @return a command - invoke its execute() and closeResource() methods * @see #createQueryCommand(String) */ protected AbstractQueryCommand createPreparedQueryCommand(String sql, List queryParams) { return new PreparedQueryCommand(sql, queryParams); } /** * Stub needed for testing. Called when a connection is opened by one of the command-pattern classes * so that a test case can monitor the state of the connection through its subclass. * * @param conn the connection that is about to be used by a command */ protected void setInternalConnection(Connection conn) { } }