This example Groovy source code file (Sql.java) is included in the DevDaily.com
"Java Source Code
Warehouse" project. The intent of this project is to help you "Learn Java by Example" TM.
/*
* Copyright 2003-2011 the original author or authors.
*
* Licensed 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 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.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.*;
import java.util.logging.Logger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import javax.sql.DataSource;
import groovy.lang.Tuple;
import org.codehaus.groovy.runtime.InvokerHelper;
import org.codehaus.groovy.runtime.SqlGroovyMethods;
/**
* 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.
*
* <h4>Typical usage
*
* First you need to set up your sql instance. There are several constructors
* and a few <code>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):
* <pre>
* def db = [url:'jdbc:hsqldb:mem:testDB', user:'sa', password:'', driver:'org.hsqldb.jdbcDriver']
* def sql = Sql.newInstance(db.url, db.user, db.password, db.driver)
* </pre>
* or if you have an existing connection (perhaps from a connection pool) or a
* datasource use one of the constructors:
* <pre>
* def sql = new Sql(datasource)
* </pre>
* Now you can invoke sql, e.g. to create a table:
* <pre>
* sql.execute '''
* create table PROJECT (
* id integer not null,
* name varchar(50),
* url varchar(100),
* )
* '''
* </pre>
* Or insert a row using JDBC PreparedStatement inspired syntax:
* <pre>
* def params = [10, 'Groovy', 'http://groovy.codehaus.org']
* sql.execute 'insert into PROJECT (id, name, url) values (?, ?, ?)', params
* </pre>
* Or insert a row using GString syntax:
* <pre>
* 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)"
* </pre>
* Or a row update:
* <pre>
* def newUrl = 'http://grails.org'
* def project = 'Grails'
* sql.executeUpdate "update PROJECT set url=$newUrl where name=$project"
* </pre>
* Now try a query using <code>eachRow:
* <pre>
* println 'Some GR8 projects:'
* sql.eachRow('select * from PROJECT') { row ->
* println "${row.name.padRight(10)} ($row.url)"
* }
* </pre>
* Which will produce something like this:
* <pre>
* Some GR8 projects:
* Groovy (http://groovy.codehaus.org)
* Grails (http://grails.org)
* Griffon (http://griffon.codehaus.org)
* Gradle (http://gradle.org)
* </pre>
* Now try a query using <code>rows:
* <pre>
* def rows = sql.rows("select * from PROJECT where name like 'Gra%'")
* assert rows.size() == 2
* println rows.join('\n')
* </pre>
* with output like this:
* <pre>
* [ID:20, NAME:Grails, URL:http://grails.org]
* [ID:40, NAME:Gradle, URL:http://gradle.org]
* </pre>
* Also, <code>eachRow and rows support paging. Here's an example:
* <pre>
* sql.eachRow('select * from PROJECT', 2, 2) { row ->
* println "${row.name.padRight(10)} ($row.url)"
* }
* </pre>
* Which will start at the second row and return a maximum of 2 rows. Here's an example result:
* <pre>
* Grails (http://grails.org)
* Griffon (http://griffon.codehaus.org)
* </pre>
*
* Finally, we should clean up:
* <pre>
* sql.close()
* </pre>
* If we are using a DataSource and we haven't enabled statement caching, then
* strictly speaking the final <code>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.
* <p/>
*
* <h4>Named and named ordinal parameters
*
* Several of the methods in this class which have a String-based sql query and
* params in a List<Object> or Object[] 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.
* <p/>
* Named parameter queries use placeholder values in the query String. Two forms are supported
* ':propname1' and '?.propname2'. For these variations, a single <em>model object is
* supplied in the parameter list. 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:
* <pre>
* println sql.rows('select * from PROJECT where name=:foo', [foo:'Gradle'])
* println sql.rows('select * from PROJECT where name=:foo and id=?.bar', [foo:'Gradle', bar:40])
* class MyDomainClass { def baz = 'Griffon' }
* println sql.rows('select * from PROJECT where name=?.baz', new MyDomainClass())
* </pre>
* 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 is an example:
* <pre>
* println sql.rows("select * from PROJECT where name=?1.baz and id=?2.num", new MyDomainClass(), [num:30])
* </pre>
*
* <h4>More details
*
* See the method and constructor JavaDoc for more details.
* <p/>
* 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.
*
* @author Chris Stevenson
* @author <a href="mailto:james@coredevelopers.net">James Strachan
* @author Paul King
* @author Marc DeXeT
* @author John Bito
* @author John Hurst
* @author David Durham
* @author Daniel Henrique Alves Lima
*/
public class Sql {
/**
* Hook to allow derived classes to access the log
*/
protected static final Logger LOG = Logger.getLogger(Sql.class.getName());
private static final List<Object> EMPTY_LIST = Collections.emptyList();
private static final Pattern NAMED_QUERY_PATTERN = Pattern.compile("(?::|\\?(\\d?)\\.?)(\\w*)");
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<String, Statement> statementCache = new HashMap();
private final Map<String, String> namedParamSqlCache = new HashMap();
private final Map<String, List namedParamIndexPropCache = new HashMap>();
/**
* Creates a new Sql instance given a JDBC connection URL.
*
* @param url a database url of the form
* <code> 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);
}
/**
* Creates a new Sql instance given a JDBC connection URL
* and some properties.
*
* @param url a database url of the form
* <code> 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);
}
/**
* 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
* <code>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 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);
}
/**
* Creates a new Sql instance given a JDBC connection URL,
* a username and a password.
*
* @param url a database url of the form
* <code>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);
}
/**
* 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
* <code>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 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);
}
/**
* Creates a new Sql instance given a JDBC connection URL
* and a driver class name.
*
* @param url a database url of the form
* <code>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 class cannot be found or loaded
*/
public static Sql newInstance(String url, String driverClassName) throws SQLException, ClassNotFoundException {
loadDriver(driverClassName);
return newInstance(url);
}
/**
* Creates a new Sql instance given parameters in a Map.
* Recognized keys for the Map include:
* <pre>
* driverClassName the fully qualified class name of the driver class
* driver a synonym for driverClassName
* url a database url of the form: jdbc:<em>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
* <em>other any of the public setter methods of this class may be used with property notation
* e.g. <em>cacheStatements: true, resultSetConcurrency: ResultSet.CONCUR_READ_ONLY
* </pre>
* Of these, '<code>url' is required. Others may be needed depending on your database.
* If '<code>properties' is supplied, neither 'user' nor 'password' should be supplied.
* If one of '<code>user' or 'password' is supplied, both should be supplied.
*<p/>
* Example usage:
* <pre>
* import groovy.sql.Sql
* import static java.sql.ResultSet.*
*
* def sql = Sql.newInstance(
* url:'jdbc:hsqldb:mem:testDB',
* user:'sa',
* password:'',
* driver:'org.hsqldb.jdbcDriver',
* cacheStatements: true,
* resultSetConcurrency: CONCUR_READ_ONLY
* )
* </pre>
*
* @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 class cannot be found or loaded
*/
public static Sql newInstance(Map<String, Object> args) throws SQLException, ClassNotFoundException {
if (args.containsKey("driverClassName") && args.containsKey("driver"))
throw new IllegalArgumentException("Only one of 'driverClassName' and 'driver' should be provided");
Object driverClassName = args.remove("driverClassName");
if (driverClassName == null) driverClassName = args.remove("driver");
if (driverClassName != null) loadDriver(driverClassName.toString());
Object url = args.remove("url");
if (url == null) throw new IllegalArgumentException("Argument 'url' is required");
Properties props = (Properties) args.remove("properties");
if (props != null && args.containsKey("user"))
throw new IllegalArgumentException("Only one of 'properties' and 'user' should be supplied");
if (props != null && args.containsKey("password"))
throw new IllegalArgumentException("Only one of 'properties' and 'password' should be supplied");
if (args.containsKey("user") ^ args.containsKey("password"))
throw new IllegalArgumentException("Found one but not both of 'user' and 'password'");
Connection connection;
if (props != null) connection = DriverManager.getConnection(url.toString(), props);
else if (args.containsKey("user")) {
Object user = args.remove("user");
Object password = args.remove("password");
connection = DriverManager.getConnection(url.toString(),
(user == null ? null : user.toString()),
(password == null ? null : password.toString()));
} else connection = DriverManager.getConnection(url.toString());
Sql result = (Sql) InvokerHelper.invokeConstructorOf(Sql.class, args);
result.setConnection(connection);
return result;
}
/**
* 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 <code>ResultSet
* constants:
* <code>ResultSet.TYPE_FORWARD_ONLY,
* <code>ResultSet.TYPE_SCROLL_INSENSITIVE, or
* <code>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 <code>ResultSet
* constants:
* <code>ResultSet.CONCUR_READ_ONLY or
* <code>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 <code>ResultSet
* constants:
* <code>ResultSet.HOLD_CURSORS_OVER_COMMIT or
* <code>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); }
/**
* 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.
* <p/>
* Example usage:
* <pre>
* def fieldName = 'firstname'
* def fieldOp = Sql.expand('like')
* def fieldVal = '%a%'
* sql.query "select * from PERSON where ${Sql.expand(fieldName)} $fieldOp ${fieldVal}", { ResultSet rs ->
* while (rs.next()) println rs.getString('firstname')
* }
* // query will be 'select * from PERSON where firstname like ?'
* // params will be [fieldVal]
* </pre>
*
* @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 caches 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
* <code>ResultSet object. The given closure is called
* with the <code>ResultSet as its argument.
* <p/>
* Example usages:
* <pre>
* sql.query("select * from PERSON where firstname like 'S%'") { ResultSet rs ->
* while (rs.next()) println rs.getString('firstname') + ' ' + rs.getString(3)
* }
*
* sql.query("call get_people_places()") { ResultSet rs ->
* while (rs.next()) println rs.toRowResult().firstname
* }
* </pre>
*
* 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 GroovyResultSet
* @throws SQLException if a database access error occurs
*/
public void query(String sql, Closure closure) throws SQLException {
Connection connection = createConnection();
Statement statement = getStatement(connection, sql);
ResultSet results = null;
try {
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
* <code>ResultSet object. The given closure is called
* with the <code>ResultSet as its argument.
* The query may contain placeholder question marks which match the given list of parameters.
* <p/>
* Example usage:
* <pre>
* sql.query('select * from PERSON where lastname like ?', ['%a%']) { ResultSet rs ->
* while (rs.next()) println rs.getString('lastname')
* }
* </pre>
*
* This method supports named and named ordinal parameters.
* See the class Javadoc for more details.
* <p/>
* 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 GroovyResultSet
* @throws SQLException if a database access error occurs
*/
public void query(String sql, List<Object> params, 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);
}
}
/**
* Performs the given SQL query, which should return a single
* <code>ResultSet object. The given closure is called
* with the <code>ResultSet as its argument.
* The query may contain GString expressions.
* <p/>
* Example usage:
* <pre>
* def location = 25
* sql.query "select * from PERSON where location_id < $location", { ResultSet rs ->
* while (rs.next()) println rs.getString('firstname')
* }
* </pre>
*
* 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 GroovyResultSet
* @throws SQLException if a database access error occurs
* @see #expand(Object)
*/
public void query(GString gstring, Closure closure) throws SQLException {
List<Object> 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 <code>GroovyResultSet which is a ResultSet
* that supports accessing the fields using property style notation and ordinal index values.
* <p/>
* Example usages:
* <pre>
* sql.eachRow("select * from PERSON where firstname like 'S%'") { row ->
* println "$row.firstname ${row[2]}}"
* }
*
* sql.eachRow "call my_stored_proc_returning_resultset()", {
* println it.firstname
* }
* </pre>
*
* 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, Closure closure) throws SQLException {
eachRow(sql, (Closure) null, closure);
}
/**
* Performs the given SQL query calling the given <code>closure with each row of the result set starting at
* the provided <code>offset, and including up to maxRows number of rows.
* The row will be a <code>GroovyResultSet which is a ResultSet
* that supports accessing the fields using property style notation and ordinal index values.
* <p/>
* Note that the underlying implementation is based on either invoking <code>ResultSet.absolute(),
* or if the ResultSet type is <code>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.
*
* <p/>
* Note that different database and JDBC driver implementations may work differently with respect to this method.
* Specifically, one should expect that <code>ResultSet.TYPE_FORWARD_ONLY may be less efficient than a
* "scrollable" type.
*
* <p/>
* 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, Closure closure) throws SQLException {
eachRow(sql, (Closure) null, offset, maxRows, closure);
}
/**
* Performs the given SQL query calling the given <code>rowClosure with each row of the
* result set.
* The row will be a <code>GroovyResultSet which is a ResultSet
* that supports accessing the fields using property style notation and ordinal index values.
* In addition, the <code>metaClosure will be called once passing in the
* <code>ResultSetMetaData as argument.
* <p/>
* Example usage:
* <pre>
* def printColNames = { meta ->
* (1..meta.columnCount).each {
* print meta.getColumnLabel(it).padRight(20)
* }
* println()
* }
* def printRow = { row ->
* row.toRowResult().values().each{ print it.toString().padRight(20) }
* println()
* }
* sql.eachRow("select * from PERSON", printColNames, printRow)
* </pre>
*
* 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, Closure metaClosure, Closure rowClosure) throws SQLException {
eachRow(sql, metaClosure, 0, 0, rowClosure);
}
/**
* Performs the given SQL query calling the given <code>rowClosure with each row of the result set starting at
* the provided <code>offset, and including up to maxRows number of rows.
* The row will be a <code>GroovyResultSet which is a ResultSet
* that supports accessing the fields using property style notation and ordinal index values.
* <p/>
* In addition, the <code>metaClosure will be called once passing in the
* <code>ResultSetMetaData as argument.
* <p/>
* Note that the underlying implementation is based on either invoking <code>ResultSet.absolute(),
* or if the ResultSet type is <code>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.
*
* <p/>
* Note that different database and JDBC driver implementations may work differently with respect to this method.
* Specifically, one should expect that <code>ResultSet.TYPE_FORWARD_ONLY may be less efficient than a
* "scrollable" type.
*
* <p/>
* 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, Closure metaClosure, int offset, int maxRows, Closure rowClosure) throws SQLException {
Connection connection = createConnection();
Statement statement = getStatement(connection, sql);
ResultSet results = null;
try {
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 (groovyRS.next() && (maxRows <= 0 || i++ < maxRows)) {
rowClosure.call(groovyRS);
}
} catch (SQLException e) {
LOG.warning("Failed to execute: " + sql + " because: " + e.getMessage());
throw e;
} finally {
closeResources(connection, statement, results);
}
}
private 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 <code>rowClosure with each row of the result set starting at
* the provided <code>offset, and including up to maxRows number of rows.
* The row will be a <code>GroovyResultSet which is a ResultSet
* that supports accessing the fields using property style notation and ordinal index values.
* <p/>
* In addition, the <code>metaClosure will be called once passing in the
* <code>ResultSetMetaData as argument.
* The query may contain placeholder question marks which match the given list of parameters.
* <p/>
* Note that the underlying implementation is based on either invoking <code>ResultSet.absolute(),
* or if the ResultSet type is <code>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.
* <p/>
* Note that different database and JDBC driver implementations may work differently with respect to this method.
* Specifically, one should expect that <code>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<Object> params, Closure metaClosure, int offset, int maxRows, 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 (groovyRS.next() && (maxRows <= 0 || i++ < maxRows)) {
rowClosure.call(groovyRS);
}
}
catch (SQLException e) {
LOG.warning("Failed to execute: " + sql + " because: " + e.getMessage());
throw e;
}
finally {
closeResources(connection, statement, results);
}
}
/**
* Performs the given SQL query calling the given Closure with each row of the result set.
* The row will be a <code>GroovyResultSet which is a ResultSet
* that supports accessing the fields using property style notation and ordinal index values.
* In addition, the <code>metaClosure will be called once passing in the
* <code>ResultSetMetaData as argument.
* The query may contain placeholder question marks which match the given list of parameters.
* <p/>
* Example usage:
* <pre>
* def printColNames = { meta ->
* (1..meta.columnCount).each {
* print meta.getColumnLabel(it).padRight(20)
* }
* println()
* }
* def printRow = { row ->
* row.toRowResult().values().each{ print it.toString().padRight(20) }
* println()
* }
* sql.eachRow("select * from PERSON where lastname like ?", ['%a%'], printColNames, printRow)
* </pre>
*
* This method supports named and named ordinal parameters.
* See the class Javadoc for more details.
* <p/>
* 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<Object> params, Closure metaClosure, Closure rowClosure) throws SQLException {
eachRow(sql, params, metaClosure, 0, 0, rowClosure);
}
/**
* Performs the given SQL query calling the given Closure with each row of the result set.
* The row will be a <code>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.
* <p/>
* Example usage:
* <pre>
* sql.eachRow("select * from PERSON where lastname like ?", ['%a%']) { row ->
* println "${row[1]} $row.lastname"
* }
* </pre>
*
* 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<Object> params, Closure closure) throws SQLException {
eachRow(sql, params, null, closure);
}
/**
* Performs the given SQL query calling the given <code>closure with each row of the result set starting at
* the provided <code>offset, and including up to maxRows number of rows.
* The row will be a <code>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.
* <p/>
* Note that the underlying implementation is based on either invoking <code>ResultSet.absolute(),
* or if the ResultSet type is <code>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.
* <p/>
* Note that different database and JDBC driver implementations may work differently with respect to this method.
* Specifically, one should expect that <code>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<Object> params, int offset, int maxRows, Closure closure) throws SQLException {
eachRow(sql, params, null, offset, maxRows, closure);
}
/**
* Performs the given SQL query calling the given Closure with each row of the result set.
* The row will be a <code>GroovyResultSet which is a ResultSet
* that supports accessing the fields using property style notation and ordinal index values.
* <p/>
* In addition, the <code>metaClosure will be called once passing in the
* <code>ResultSetMetaData as argument.
* The query may contain GString expressions.
* <p/>
* Example usage:
* <pre>
* def location = 25
* def printColNames = { meta ->
* (1..meta.columnCount).each {
* print meta.getColumnLabel(it).padRight(20)
* }
* println()
* }
* def printRow = { row ->
* row.toRowResult().values().each{ print it.toString().padRight(20) }
* println()
* }
* sql.eachRow("select * from PERSON where location_id < $location", printColNames, printRow)
* </pre>
*
* 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, Closure metaClosure, Closure rowClosure) throws SQLException {
List<Object> params = getParameters(gstring);
String sql = asSql(gstring, params);
eachRow(sql, params, metaClosure, rowClosure);
}
/**
* Performs the given SQL query calling the given <code>closure with each row of the result set starting at
* the provided <code>offset, and including up to maxRows number of rows.
* The row will be a <code>GroovyResultSet which is a ResultSet
* that supports accessing the fields using property style notation and ordinal index values.
* In addition, the <code>metaClosure will be called once passing in the
* <code>ResultSetMetaData as argument.
* The query may contain GString expressions.
* <p/>
* Note that the underlying implementation is based on either invoking <code>ResultSet.absolute(),
* or if the ResultSet type is <code>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.
* <p/>
* Note that different database and JDBC driver implementations may work differently with respect to this method.
* Specifically, one should expect that <code>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, Closure metaClosure, int offset, int maxRows, Closure rowClosure) throws SQLException {
List<Object> params = getParameters(gstring);
String sql = asSql(gstring, params);
eachRow(sql, params, metaClosure, offset, maxRows, rowClosure);
}
/**
* Performs the given SQL query calling the given <code>closure with each row of the result set starting at
* the provided <code>offset, and including up to maxRows number of rows.
* The row will be a <code>GroovyResultSet which is a ResultSet
* that supports accessing the fields using property style notation and ordinal index values.
* The query may contain GString expressions.
* <p/>
* Note that the underlying implementation is based on either invoking <code>ResultSet.absolute(),
* or if the ResultSet type is <code>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.
* <p/>
* Note that different database and JDBC driver implementations may work differently with respect to this method.
* Specifically, one should expect that <code>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, Closure closure) throws SQLException {
List<Object> 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 <code>GroovyResultSet which is a ResultSet
* that supports accessing the fields using property style notation and ordinal index values.
* The query may contain GString expressions.
* <p/>
* Example usage:
* <pre>
* def location = 25
* sql.eachRow("select * from PERSON where location_id < $location") { row ->
* println row.firstname
* }
* </pre>
*
* 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, Closure closure) throws SQLException {
eachRow(gstring, null, closure);
}
/**
* Performs the given SQL query and return the rows of the result set.
* <p/>
* Example usage:
* <pre>
* def ans = sql.rows("select * from PERSON where firstname like 'S%'")
* println "Found ${ans.size()} rows"
* </pre>
*
* 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<GroovyRowResult> 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.
* <p/>
* Note that the underlying implementation is based on either invoking <code>ResultSet.absolute(),
* or if the ResultSet type is <code>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.
* <p/>
* Note that different database and JDBC driver implementations may work differently with respect to this method.
* Specifically, one should expect that <code>ResultSet.TYPE_FORWARD_ONLY may be less efficient than a
* "scrollable" type.
* <p/>
* 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<GroovyRowResult> 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 <code>metaClosure will be called once passing in the
* <code>ResultSetMetaData as argument.
* <p/>
* Example usage:
* <pre>
* def printNumCols = { meta -> println "Found $meta.columnCount columns" }
* def ans = sql.rows("select * from PERSON", printNumCols)
* println "Found ${ans.size()} rows"
* </pre>
*
* 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<GroovyRowResult> rows(String sql, 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 <code>metaClosure will be called once passing in the
* <code>ResultSetMetaData as argument.
* <p/>
* Note that the underlying implementation is based on either invoking <code>ResultSet.absolute(),
* or if the ResultSet type is <code>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.
* <p/>
* Note that different database and JDBC driver implementations may work differently with respect to this method.
* Specifically, one should expect that <code>ResultSet.TYPE_FORWARD_ONLY may be less efficient than a
* "scrollable" type.
* <p/>
* 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<GroovyRowResult> rows(String sql, int offset, int maxRows, Closure metaClosure) throws SQLException {
AbstractQueryCommand command = createQueryCommand(sql);
ResultSet rs = null;
try {
rs = command.execute();
List<GroovyRowResult> 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.
* <p/>
* Example usage:
* <pre>
* def ans = sql.rows("select * from PERSON where lastname like ?", ['%a%'])
* println "Found ${ans.size()} rows"
* </pre>
* <p/>
* 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<GroovyRowResult> rows(String sql, List
