Start line:  
End line:  

Snippet Preview

Snippet HTML Code

Stack Overflow Questions
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 java.sql.Types;
  import java.util.*;
  
  
  import groovy.lang.Tuple;
  
  import static org.codehaus.groovy.runtime.SqlGroovyMethods.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 ->
     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 ->
     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.

Author(s):
Chris Stevenson
James Strachan
Paul King
Marc DeXeT
John Bito
John Hurst
David Durham
Daniel Henrique Alves Lima
David Sutherland
 
 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<ObjectEMPTY_LIST = Collections.emptyList();
     private static final int USE_COLUMN_NAMES = -1;
 
     private DataSource dataSource;
 
     private Connection useConnection;
 
     private int resultSetType = .;
     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<StringStatementstatementCache = new HashMap<StringStatement>();
     private final Map<StringStringnamedParamSqlCache = new HashMap<StringString>();
     private final Map<StringList<Tuple>> namedParamIndexPropCache = new HashMap<StringList<Tuple>>();
     private List<StringkeyColumnNames;

    
Creates a new Sql instance given a JDBC connection URL.

Parameters:
url a database url of the form jdbc:subprotocol:subname
Returns:
a new Sql instance with a connection
Throws:
java.sql.SQLException if a database access error occurs
 
     public static Sql newInstance(String urlthrows 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.

Parameters:
url a database url of the form jdbc:subprotocol:subname
c the Closure to call
Throws:
java.sql.SQLException if a database access error occurs
See also:
newInstance(java.lang.String)
 
     public static void withInstance(String urlClosure cthrows SQLException {
         Sql sql = null;
         try {
             sql = newInstance(url);
             c.call(sql);
         } finally {
             if (sql != nullsql.close();
         }
     }

    
Creates a new Sql instance given a JDBC connection URL and some properties.

Parameters:
url a database url of the form jdbc:subprotocol:subname
properties a list of arbitrary string tag/value pairs as connection arguments; normally at least a "user" and "password" property should be included
Returns:
a new Sql instance with a connection
Throws:
java.sql.SQLException if a database access error occurs
 
     public static Sql newInstance(String urlProperties propertiesthrows SQLException {
         Connection connection = DriverManager.getConnection(urlproperties);
         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.

Parameters:
url a database url of the form jdbc:subprotocol:subname
properties a list of arbitrary string tag/value pairs as connection arguments; normally at least a "user" and "password" property should be included
c the Closure to call
Throws:
java.sql.SQLException if a database access error occurs
See also:
newInstance(java.lang.String,java.util.Properties)
 
     public static void withInstance(String urlProperties propertiesClosure cthrows SQLException {
         Sql sql = null;
         try {
             sql = newInstance(urlproperties);
             c.call(sql);
         } finally {
             if (sql != nullsql.close();
         }
     }

    
Creates a new Sql instance given a JDBC connection URL, some properties and a driver class name.

Parameters:
url a database url of the form jdbc:subprotocol:subname
properties a list of arbitrary string tag/value pairs as connection arguments; normally at least a "user" and "password" property should be included
driverClassName the fully qualified class name of the driver class
Returns:
a new Sql instance with a connection
Throws:
java.sql.SQLException if a database access error occurs
java.lang.ClassNotFoundException if the driver class cannot be found or loaded
 
     public static Sql newInstance(String urlProperties propertiesString driverClassName)
             throws SQLExceptionClassNotFoundException {
         loadDriver(driverClassName);
         return newInstance(urlproperties);
     }

    
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.

Parameters:
url a database url of the form jdbc:subprotocol:subname
properties a list of arbitrary string tag/value pairs as connection arguments; normally at least a "user" and "password" property should be included
driverClassName the fully qualified class name of the driver class
c the Closure to call
Throws:
java.sql.SQLException if a database access error occurs
java.lang.ClassNotFoundException if the driver class cannot be found or loaded
See also:
newInstance(java.lang.String,java.util.Properties,java.lang.String)
 
     public static void withInstance(String urlProperties propertiesString driverClassNameClosure c)
             throws SQLExceptionClassNotFoundException {
         Sql sql = null;
         try {
             sql = newInstance(urlpropertiesdriverClassName);
             c.call(sql);
         } finally {
             if (sql != nullsql.close();
         }
     }

    
Creates a new Sql instance given a JDBC connection URL, a username and a password.

Parameters:
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
Returns:
a new Sql instance with a connection
Throws:
java.sql.SQLException if a database access error occurs
 
     public static Sql newInstance(String urlString userString passwordthrows SQLException {
         Connection connection = DriverManager.getConnection(urluserpassword);
         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.

Parameters:
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
c the Closure to call
Throws:
java.sql.SQLException if a database access error occurs
See also:
newInstance(java.lang.String,java.lang.String,java.lang.String)
 
     public static void withInstance(String urlString userString passwordClosure cthrows SQLException {
         Sql sql = null;
         try {
             sql = newInstance(urluserpassword);
             c.call(sql);
         } finally {
             if (sql != nullsql.close();
         }
     }

    
Creates a new Sql instance given a JDBC connection URL, a username, a password and a driver class name.

Parameters:
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
driverClassName the fully qualified class name of the driver class
Returns:
a new Sql instance with a connection
Throws:
java.sql.SQLException if a database access error occurs
java.lang.ClassNotFoundException if the driver class cannot be found or loaded
 
     public static Sql newInstance(String urlString userString passwordString driverClassName)
             throws SQLExceptionClassNotFoundException {
         loadDriver(driverClassName);
         return newInstance(urluserpassword);
     }

    
Invokes a closure passing it a new Sql instance created from the given JDBC connection URL. The created connection will be closed if required.

Parameters:
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
driverClassName the fully qualified class name of the driver class
c the Closure to call
Throws:
java.sql.SQLException if a database access error occurs
java.lang.ClassNotFoundException if the driver class cannot be found or loaded
See also:
newInstance(java.lang.String,java.lang.String,java.lang.String,java.lang.String)
 
     public static void withInstance(String urlString userString passwordString driverClassNameClosure c)
             throws SQLExceptionClassNotFoundException {
         Sql sql = null;
         try {
             sql = newInstance(urluserpassworddriverClassName);
             c.call(sql);
         } finally {
             if (sql != nullsql.close();
         }
     }

    
Creates a new Sql instance given a JDBC connection URL and a driver class name.

Parameters:
url a database url of the form jdbc:subprotocol:subname
driverClassName the fully qualified class name of the driver class
Returns:
a new Sql instance with a connection
Throws:
java.sql.SQLException if a database access error occurs
java.lang.ClassNotFoundException if the driver class cannot be found or loaded
 
     public static Sql newInstance(String urlString driverClassNamethrows SQLExceptionClassNotFoundException {
         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.

Parameters:
url a database url of the form jdbc:subprotocol:subname
driverClassName the fully qualified class name of the driver class
c the Closure to call
Throws:
java.sql.SQLException if a database access error occurs
java.lang.ClassNotFoundException if the driver class cannot be found or loaded
See also:
newInstance(java.lang.String,java.lang.String)
 
     public static void withInstance(String urlString driverClassNameClosure c)
             throws SQLExceptionClassNotFoundException {
         Sql sql = null;
         try {
             sql = newInstance(urldriverClassName);
             c.call(sql);
         } finally {
             if (sql != nullsql.close();
         }
     }

    
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
 )
 

Parameters:
args a Map contain further arguments
Returns:
a new Sql instance with a connection
Throws:
java.sql.SQLException if a database access error occurs
java.lang.ClassNotFoundException if the driver class cannot be found or loaded
 
     public static Sql newInstance(Map<StringObjectargsthrows SQLExceptionClassNotFoundException {
         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<StringObjectsqlArgs = new HashMap<StringObject>(args);
 
         Object driverClassName = sqlArgs.remove("driverClassName");
         if (driverClassName == nulldriverClassName = sqlArgs.remove("driver");
         if (driverClassName != nullloadDriver(driverClassName.toString());
 
         Properties props = (PropertiessqlArgs.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;
         if (props != null) {
             ..println("url = " + url);
             ..println("props = " + props);
             connection = DriverManager.getConnection(url.toString(), new Properties(props));
         } else if (sqlArgs.containsKey("user")) {
             Object user = sqlArgs.remove("user");
             Object password = sqlArgs.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.classsqlArgs);
         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.

Parameters:
args a Map contain further arguments
c the Closure to call
Throws:
java.sql.SQLException if a database access error occurs
java.lang.ClassNotFoundException if the driver class cannot be found or loaded
See also:
newInstance(java.util.Map)
 
     public static void withInstance(Map<StringObjectargsClosure cthrows SQLExceptionClassNotFoundException {
         Sql sql = null;
         try {
             sql = newInstance(args);
             c.call(sql);
         } finally {
             if (sql != nullsql.close();
         }
     }

    
Gets the resultSetType for statements created using the connection.

Returns:
the current resultSetType value
Since:
1.5.2
 
     public int getResultSetType() {
         return ;
     }

    
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.

Parameters:
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;
     }

    
Gets the resultSetConcurrency for statements created using the connection.

Returns:
the current resultSetConcurrency value
Since:
1.5.2
 
     public int getResultSetConcurrency() {
         return ;
     }

    
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.

Parameters:
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;
     }

    
Gets the resultSetHoldability for statements created using the connection.

Returns:
the current resultSetHoldability value or -1 if not set
Since:
1.5.2
 
     public int getResultSetHoldability() {
         return ;
     }

    
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.

Parameters:
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;
     }

    
Attempts to load the JDBC driver on the thread, current or system class loaders

Parameters:
driverClassName the fully qualified class name of the driver class
Throws:
java.lang.ClassNotFoundException if the class cannot be found or loaded
 
     public static void loadDriver(String driverClassNamethrows 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 .; }};
     public static final OutParameter BIGINT        = new OutParameter(){ public int getType() { return .; }};
     public static final OutParameter BINARY        = new OutParameter(){ public int getType() { return .; }};
     public static final OutParameter BIT           = new OutParameter(){ public int getType() { return .; }};
     public static final OutParameter BLOB          = new OutParameter(){ public int getType() { return .; }};
     public static final OutParameter BOOLEAN       = new OutParameter(){ public int getType() { return .; }};
     public static final OutParameter CHAR          = new OutParameter(){ public int getType() { return .; }};
     public static final OutParameter CLOB          = new OutParameter(){ public int getType() { return .; }};
     public static final OutParameter DATALINK      = new OutParameter(){ public int getType() { return .; }};
     public static final OutParameter DATE          = new OutParameter(){ public int getType() { return .; }};
     public static final OutParameter DECIMAL       = new OutParameter(){ public int getType() { return .; }};
     public static final OutParameter DISTINCT      = new OutParameter(){ public int getType() { return .; }};
     public static final OutParameter DOUBLE        = new OutParameter(){ public int getType() { return .; }};
     public static final OutParameter FLOAT         = new OutParameter(){ public int getType() { return .; }};
     public static final OutParameter INTEGER       = new OutParameter(){ public int getType() { return .; }};
     public static final OutParameter JAVA_OBJECT   = new OutParameter(){ public int getType() { return .; }};
     public static final OutParameter LONGVARBINARY = new OutParameter(){ public int getType() { return .; }};
     public static final OutParameter LONGVARCHAR   = new OutParameter(){ public int getType() { return .; }};
     public static final OutParameter NULL          = new OutParameter(){ public int getType() { return .; }};
     public static final OutParameter NUMERIC       = new OutParameter(){ public int getType() { return .; }};
     public static final OutParameter OTHER         = new OutParameter(){ public int getType() { return .; }};
     public static final OutParameter REAL          = new OutParameter(){ public int getType() { return .; }};
     public static final OutParameter REF           = new OutParameter(){ public int getType() { return .; }};
     public static final OutParameter SMALLINT      = new OutParameter(){ public int getType() { return .; }};
     public static final OutParameter STRUCT        = new OutParameter(){ public int getType() { return .; }};
     public static final OutParameter TIME          = new OutParameter(){ public int getType() { return .; }};
     public static final OutParameter TIMESTAMP     = new OutParameter(){ public int getType() { return .; }};
     public static final OutParameter TINYINT       = new OutParameter(){ public int getType() { return .; }};
     public static final OutParameter VARBINARY     = new OutParameter(){ public int getType() { return .; }};
     public static final OutParameter VARCHAR       = new OutParameter(){ public int getType() { return .; }};
 
     public static InParameter ARRAY(Object value) { return in(.value); }
     public static InParameter BIGINT(Object value) { return in(.value); }
     public static InParameter BINARY(Object value) { return in(.value); }
     public static InParameter BIT(Object value) { return in(.value); }
     public static InParameter BLOB(Object value) { return in(.value); }
     public static InParameter BOOLEAN(Object value) { return in(.value); }
     public static InParameter CHAR(Object value) { return in(.value); }
     public static InParameter CLOB(Object value) { return in(.value); }
     public static InParameter DATALINK(Object value) { return in(.value); }
     public static InParameter DATE(Object value) { return in(.value); }
     public static InParameter DECIMAL(Object value) { return in(.value); }
     public static InParameter DISTINCT(Object value) { return in(.value); }
     public static InParameter DOUBLE(Object value) { return in(.value); }
     public static InParameter FLOAT(Object value) { return in(.value); }
     public static InParameter INTEGER(Object value) { return in(.value); }
     public static InParameter JAVA_OBJECT(Object value) { return in(.value); }
     public static InParameter LONGVARBINARY(Object value) { return in(.value); }
     public static InParameter LONGVARCHAR(Object value) { return in(.value); }
     public static InParameter NULL(Object value) { return in(.value); }
     public static InParameter NUMERIC(Object value) { return in(.value); }
     public static InParameter OTHER(Object value) { return in(.value); }
     public static InParameter REAL(Object value) { return in(.value); }
     public static InParameter REF(Object value) { return in(.value); }
     public static InParameter SMALLINT(Object value) { return in(.value); }
     public static InParameter STRUCT(Object value) { return in(.value); }
     public static InParameter TIME(Object value) { return in(.value); }
     public static InParameter TIMESTAMP(Object value) { return in(.value); }
     public static InParameter TINYINT(Object value) { return in(.value); }
     public static InParameter VARBINARY(Object value) { return in(.value); }
     public static InParameter VARCHAR(Object value) { return in(.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

Parameters:
type the JDBC data type
value the object value
Returns:
an InParameter
 
     public static InParameter in(final int typefinal Object value) {
         return new InParameter() {
             public int getType() {
                 return type;
             }
 
             public Object getValue() {
                 return value;
             }
         };
     }

    
Create a new OutParameter

Parameters:
type the JDBC data type.
Returns:
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.

Parameters:
in the InParameter of interest
Returns:
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

Parameters:
type the JDBC data type.
Returns:
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 ->
     while (rs.next()) println rs.getString('firstname')
 }
 // query will be 'select * from PERSON where firstname like ?'
 // params will be [fieldVal]
 

Parameters:
object the object of interest
Returns:
the expanded variable
See also:
expand(java.lang.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.

Parameters:
dataSource the DataSource to use
 
     public Sql(DataSource dataSource) {
         this. = 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 close() method which will close the connection but also free any caches resources.

Parameters:
connection the Connection to use
 
     public Sql(Connection connection) {
         if (connection == null) {
             throw new NullPointerException("Must specify a non-null Connection");
         }
         this. = connection;
     }
 
     public Sql(Sql parent) {
         this. = parent.dataSource;
         this. = parent.useConnection;
     }
 
     private Sql() {
         // supports Map style newInstance method
     }
 
     public DataSet dataSet(String table) {
         return new DataSet(thistable);
     }
 
     public DataSet dataSet(Class<?> type) {
         return new DataSet(thistype);
     }

    
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 ->
     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
 }
 

All resources including the ResultSet are closed automatically after the closure is called.

Parameters:
sql the sql statement
closure called for each row with a GroovyResultSet
Throws:
java.sql.SQLException if a database access error occurs
 
     public void query(String sqlClosure closurethrows SQLException {
         Connection connection = createConnection();
         Statement statement = getStatement(connectionsql);
         ResultSet results = null;
         try {
             results = statement.executeQuery(sql);
             closure.call(results);
         } catch (SQLException e) {
             .warning("Failed to execute: " + sql + " because: " + e.getMessage());
             throw e;
         } finally {
             closeResources(connectionstatementresults);
         }
     }

    
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 ->
     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.

Parameters:
sql the sql statement
params a list of parameters
closure called for each row with a GroovyResultSet
Throws:
java.sql.SQLException if a database access error occurs
 
     public void query(String sqlList<ObjectparamsClosure closurethrows SQLException {
         Connection connection = createConnection();
         PreparedStatement statement = null;
         ResultSet results = null;
         try {
             statement = getPreparedStatement(connectionsqlparams);
             results = statement.executeQuery();
             closure.call(results);
         } catch (SQLException e) {
             .warning("Failed to execute: " + sql + " because: " + e.getMessage());
             throw e;
        } finally {
            closeResources(connectionstatementresults);
        }
    }

    
A variant of query(java.lang.String,java.util.List,groovy.lang.Closure) useful when providing the named parameters as a map.

Parameters:
sql the sql statement
map a map containing the named parameters
closure called for each row with a GroovyResultSet
Throws:
java.sql.SQLException if a database access error occurs
Since:
1.8.7
    public void query(String sqlMap mapClosure closurethrows SQLException {
        query(sqlsingletonList(map), closure);
    }

    
A variant of query(java.lang.String,java.util.List,groovy.lang.Closure) useful when providing the named parameters as named arguments.

Parameters:
map a map containing the named parameters
sql the sql statement
closure called for each row with a GroovyResultSet
Throws:
java.sql.SQLException if a database access error occurs
Since:
1.8.7
    public void query(Map mapString sqlClosure closurethrows SQLException {
        query(sqlsingletonList(map), closure);
    }
    private ArrayList<ObjectsingletonList(Object item) {
        ArrayList<Objectparams = new ArrayList<Object>();
        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 < $location", { ResultSet rs ->
     while (rs.next()) println rs.getString('firstname')
 }
 

All resources including the ResultSet are closed automatically after the closure is called.

Parameters:
gstring a GString containing the SQL query with embedded params
closure called for each row with a GroovyResultSet
Throws:
java.sql.SQLException if a database access error occurs
See also:
expand(java.lang.Object)
    public void query(GString gstringClosure closurethrows SQLException {
        List<Objectparams = getParameters(gstring);
        String sql = asSql(gstringparams);
        query(sqlparamsclosure);
    }

    
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 ->
    println "$row.firstname ${row[2]}}"
 }

 sql.eachRow "call my_stored_proc_returning_resultset()", {
     println it.firstname
 }
 

Resource handling is performed automatically where appropriate.

Parameters:
sql the sql statement
closure called for each row with a GroovyResultSet
Throws:
java.sql.SQLException if a database access error occurs
    public void eachRow(String sqlClosure closurethrows SQLException {
        eachRow(sql, (Closurenullclosure);
    }

    
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.

Parameters:
sql the sql statement
offset the 1-based offset for the first row to be processed
maxRows the maximum number of rows to be processed
closure called for each row with a GroovyResultSet
Throws:
java.sql.SQLException if a database access error occurs
    public void eachRow(String sqlint offsetint maxRowsClosure closurethrows SQLException {
        eachRow(sql, (ClosurenulloffsetmaxRowsclosure);
    }

    
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 ->
     (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)
 

Resource handling is performed automatically where appropriate.

Parameters:
sql the sql statement
metaClosure called for meta data (only once after sql execution)
rowClosure called for each row with a GroovyResultSet
Throws:
java.sql.SQLException if a database access error occurs
    public void eachRow(String sqlClosure metaClosureClosure rowClosurethrows SQLException {
        eachRow(sqlmetaClosure, 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.

Parameters:
sql the sql statement
offset the 1-based offset for the first row to be processed
maxRows the maximum number of rows to be processed
metaClosure called for meta data (only once after sql execution)
rowClosure called for each row with a GroovyResultSet
Throws:
java.sql.SQLException if a database access error occurs
    public void eachRow(String sqlClosure metaClosureint offsetint maxRowsClosure rowClosurethrows SQLException {
        Connection connection = createConnection();
        Statement statement = getStatement(connectionsql);
        ResultSet results = null;
        try {
            results = statement.executeQuery(sql);
            if (metaClosure != nullmetaClosure.call(results.getMetaData());
            boolean cursorAtRow = moveCursor(resultsoffset);
            if (!cursorAtRowreturn;
            GroovyResultSet groovyRS = new GroovyResultSetProxy(results).getImpl();
            int i = 0;
            while ((maxRows <= 0 || i++ < maxRows) && groovyRS.next()) {
                rowClosure.call(groovyRS);
            }
        } catch (SQLException e) {
            .warning("Failed to execute: " + sql + " because: " + e.getMessage());
            throw e;
        } finally {
            closeResources(connectionstatementresults);
        }
    }
    private boolean moveCursor(ResultSet resultsint offsetthrows SQLException {
        boolean cursorAtRow = true;
        if (results.getType() == .) {
            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.

Parameters:
sql the sql statement
params a list of parameters
offset the 1-based offset for the first row to be processed
maxRows the maximum number of rows to be processed
metaClosure called for meta data (only once after sql execution)
rowClosure called for each row with a GroovyResultSet
Throws:
java.sql.SQLException if a database access error occurs
    public void eachRow(String sqlList<ObjectparamsClosure metaClosureint offsetint maxRowsClosure rowClosurethrows SQLException {
        Connection connection = createConnection();
        PreparedStatement statement = null;
        ResultSet results = null;
        try {
            statement = getPreparedStatement(connectionsqlparams);
            results = statement.executeQuery();
            if (metaClosure != nullmetaClosure.call(results.getMetaData());
            boolean cursorAtRow = moveCursor(resultsoffset);
            if (!cursorAtRowreturn;
            GroovyResultSet groovyRS = new GroovyResultSetProxy(results).getImpl();
            int i = 0;
            while ((maxRows <= 0 || i++ < maxRows) && groovyRS.next()) {
                rowClosure.call(groovyRS);
            }
        } catch (SQLException e) {
            .warning("Failed to execute: " + sql + " because: " + e.getMessage());
            throw e;
        } finally {
            closeResources(connectionstatementresults);
        }
    }

    
A variant of eachRow(java.lang.String,java.util.List,groovy.lang.Closure,int,int,groovy.lang.Closure) allowing the named parameters to be supplied in a map.

Parameters:
sql the sql statement
map a map containing the named parameters
offset the 1-based offset for the first row to be processed
maxRows the maximum number of rows to be processed
metaClosure called for meta data (only once after sql execution)
rowClosure called for each row with a GroovyResultSet
Throws:
java.sql.SQLException if a database access error occurs
Since:
1.8.7
    public void eachRow(String sqlMap mapClosure metaClosureint offsetint maxRowsClosure rowClosurethrows SQLException {
        eachRow(sqlsingletonList(map), metaClosureoffsetmaxRowsrowClosure);
    }

    
A variant of eachRow(java.lang.String,java.util.List,groovy.lang.Closure,int,int,groovy.lang.Closure) allowing the named parameters to be supplied as named arguments.

Parameters:
map a map containing the named parameters
sql the sql statement
offset the 1-based offset for the first row to be processed
maxRows the maximum number of rows to be processed
metaClosure called for meta data (only once after sql execution)
rowClosure called for each row with a GroovyResultSet
Throws:
java.sql.SQLException if a database access error occurs
Since:
1.8.7
    public void eachRow(Map mapString sqlClosure metaClosureint offsetint maxRowsClosure rowClosurethrows SQLException {
        eachRow(sqlsingletonList(map), metaClosureoffsetmaxRowsrowClosure);
    }

    
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 ->
     (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)
 

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

Resource handling is performed automatically where appropriate.

Parameters:
sql the sql statement
params a list of parameters
metaClosure called for meta data (only once after sql execution)
rowClosure called for each row with a GroovyResultSet
Throws:
java.sql.SQLException if a database access error occurs
    public void eachRow(String sqlList<ObjectparamsClosure metaClosureClosure rowClosurethrows SQLException {
        eachRow(sqlparamsmetaClosure, 0, 0, rowClosure);
    }

    
A variant of eachRow(java.lang.String,java.util.List,groovy.lang.Closure,groovy.lang.Closure) useful when providing the named parameters as a map.

Parameters:
sql the sql statement
params a map of named parameters
metaClosure called for meta data (only once after sql execution)
rowClosure called for each row with a GroovyResultSet
Throws:
java.sql.SQLException if a database access error occurs
Since:
1.8.7
    public void eachRow(String sqlMap paramsClosure metaClosureClosure rowClosurethrows SQLException {
        eachRow(sqlsingletonList(params), metaClosurerowClosure);
    }

    
A variant of eachRow(java.lang.String,java.util.List,groovy.lang.Closure,groovy.lang.Closure) useful when providing the named parameters as named arguments.

Parameters:
params a map of named parameters
sql the sql statement
metaClosure called for meta data (only once after sql execution)
rowClosure called for each row with a GroovyResultSet
Throws:
java.sql.SQLException if a database access error occurs
Since:
1.8.7
    public void eachRow(Map paramsString sqlClosure metaClosureClosure rowClosurethrows SQLException {
        eachRow(sqlsingletonList(params), metaClosurerowClosure);
    }

    
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 ->
     println "${row[1]} $row.lastname"
 }
 

Resource handling is performed automatically where appropriate.

Parameters:
sql the sql statement
params a list of parameters
closure called for each row with a GroovyResultSet
Throws:
java.sql.SQLException if a database access error occurs
    public void eachRow(String sqlList<ObjectparamsClosure closurethrows SQLException {
        eachRow(sqlparamsnullclosure);
    }

    
A variant of eachRow(java.lang.String,java.util.List,groovy.lang.Closure) useful when providing the named parameters as a map.

Parameters:
sql the sql statement
params a map of named parameters
closure called for each row with a GroovyResultSet
Throws:
java.sql.SQLException if a database access error occurs
Since:
1.8.7
    public void eachRow(String sqlMap paramsClosure closurethrows SQLException {
        eachRow(sqlsingletonList(params), closure);
    }

    
A variant of eachRow(java.lang.String,java.util.List,groovy.lang.Closure) useful when providing the named parameters as named arguments.

Parameters:
params a map of named parameters
sql the sql statement
closure called for each row with a GroovyResultSet
Throws:
java.sql.SQLException if a database access error occurs
Since:
1.8.7
    public void eachRow(Map paramsString sqlClosure closurethrows SQLException {
        eachRow(sqlsingletonList(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.

Parameters:
sql the sql statement
params a list of parameters
offset the 1-based offset for the first row to be processed
maxRows the maximum number of rows to be processed
closure called for each row with a GroovyResultSet
Throws:
java.sql.SQLException if a database access error occurs
    public void eachRow(String sqlList<Objectparamsint offsetint maxRowsClosure closurethrows SQLException {
        eachRow(sqlparamsnulloffsetmaxRowsclosure);
    }

    
A variant of eachRow(java.lang.String,java.util.List,int,int,groovy.lang.Closure) useful when providing the named parameters as a map.

Parameters:
sql the sql statement
params a map of named parameters
offset the 1-based offset for the first row to be processed
maxRows the maximum number of rows to be processed
closure called for each row with a GroovyResultSet
Throws:
java.sql.SQLException if a database access error occurs
Since:
1.8.7
    public void eachRow(String sqlMap paramsint offsetint maxRowsClosure closurethrows SQLException {
        eachRow(sqlsingletonList(params), offsetmaxRowsclosure);
    }

    
A variant of eachRow(java.lang.String,java.util.List,int,int,groovy.lang.Closure) useful when providing the named parameters as named arguments.

Parameters:
params a map of named parameters
sql the sql statement
offset the 1-based offset for the first row to be processed
maxRows the maximum number of rows to be processed
closure called for each row with a GroovyResultSet
Throws:
java.sql.SQLException if a database access error occurs
Since:
1.8.7
    public void eachRow(Map paramsString sqlint offsetint maxRowsClosure closurethrows SQLException {
        eachRow(sqlsingletonList(params), offsetmaxRowsclosure);
    }

    
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 ->
     (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)
 

Resource handling is performed automatically where appropriate.

Parameters:
gstring a GString containing the SQL query with embedded params
metaClosure called for meta data (only once after sql execution)
rowClosure called for each row with a GroovyResultSet
Throws:
java.sql.SQLException if a database access error occurs
See also:
expand(java.lang.Object)
    public void eachRow(GString gstringClosure metaClosureClosure rowClosurethrows SQLException {
        List<Objectparams = getParameters(gstring);
        String sql = asSql(gstringparams);
        eachRow(sqlparamsmetaClosurerowClosure);
    }

    
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.

Parameters:
gstring a GString containing the SQL query with embedded params
metaClosure called for meta data (only once after sql execution)
offset the 1-based offset for the first row to be processed
maxRows the maximum number of rows to be processed
rowClosure called for each row with a GroovyResultSet
Throws:
java.sql.SQLException if a database access error occurs
    public void eachRow(GString gstringClosure metaClosureint offsetint maxRowsClosure rowClosurethrows SQLException {
        List<Objectparams = getParameters(gstring);
        String sql = asSql(gstringparams);
        eachRow(sqlparamsmetaClosureoffsetmaxRowsrowClosure);
    }

    
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.

Parameters:
gstring a GString containing the SQL query with embedded params
offset the 1-based offset for the first row to be processed
maxRows the maximum number of rows to be processed
closure called for each row with a GroovyResultSet
Throws:
java.sql.SQLException if a database access error occurs
    public void eachRow(GString gstringint offsetint maxRowsClosure closurethrows SQLException {
        List<Objectparams = getParameters(gstring);
        String sql = asSql(gstringparams);
        eachRow(sqlparamsoffsetmaxRowsclosure);
    }

    
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 < $location") { row ->
     println row.firstname
 }
 

Resource handling is performed automatically where appropriate.

Parameters:
gstring a GString containing the SQL query with embedded params
closure called for each row with a GroovyResultSet
Throws:
java.sql.SQLException if a database access error occurs
See also:
expand(java.lang.Object)
    public void eachRow(GString gstringClosure closurethrows SQLException {
        eachRow(gstringnullclosure);
    }

    
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.

Parameters:
sql the SQL statement
Returns:
a list of GroovyRowResult objects
Throws:
java.sql.SQLException if a database access error occurs
    public List<GroovyRowResultrows(String sqlthrows 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.

Parameters:
sql the SQL statement
offset the 1-based offset for the first row to be processed
maxRows the maximum number of rows to be processed
Returns:
a list of GroovyRowResult objects
Throws:
java.sql.SQLException if a database access error occurs
    public List<GroovyRowResultrows(String sqlint offsetint maxRowsthrows SQLException {
        return rows(sqloffsetmaxRowsnull);
    }


    
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 -> 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.

Parameters:
sql the SQL statement
metaClosure called with meta data of the ResultSet
Returns:
a list of GroovyRowResult objects
Throws:
java.sql.SQLException if a database access error occurs
    public List<GroovyRowResultrows(String sqlClosure metaClosurethrows 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.

Parameters:
sql the SQL statement
offset the 1-based offset for the first row to be processed
maxRows the maximum number of rows to be processed
metaClosure called for meta data (only once after sql execution)
Returns:
a list of GroovyRowResult objects
Throws:
java.sql.SQLException if a database access error occurs
    public List<GroovyRowResultrows(String sqlint offsetint maxRowsClosure metaClosurethrows 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<GroovyRowResultresult = asList(sqlrsoffsetmaxRowsmetaClosure);
            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.

Parameters:
sql the SQL statement
params a list of parameters
Returns:
a list of GroovyRowResult objects
Throws:
java.sql.SQLException if a database access error occurs
    public List<GroovyRowResultrows(String sqlList<Objectparamsthrows SQLException {
        return rows(sqlparamsnull);
    }

    
A variant of rows(java.lang.String,java.util.List) useful when providing the named parameters as named arguments.

Parameters:
params a map containing the named parameters
sql the SQL statement
Returns:
a list of GroovyRowResult objects
Throws:
java.sql.SQLException if a database access error occurs
Since:
1.8.7
    public List<GroovyRowResultrows(Map paramsString sqlthrows SQLException {
        return rows(sqlsingletonList(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.

Parameters:
sql the SQL statement
params a list of parameters
offset the 1-based offset for the first row to be processed
maxRows the maximum number of rows to be processed
Returns:
a list of GroovyRowResult objects
Throws:
java.sql.SQLException if a database access error occurs
    public List<GroovyRowResultrows(String sqlList<Objectparamsint offsetint maxRowsthrows SQLException {
        return rows(sqlparamsoffsetmaxRowsnull);
    }

    
A variant of rows(java.lang.String,java.util.List,int,int) useful when providing the named parameters as a map.

Parameters:
sql the SQL statement
params a map of named parameters
offset the 1-based offset for the first row to be processed
maxRows the maximum number of rows to be processed
Returns:
a list of GroovyRowResult objects
Throws:
java.sql.SQLException if a database access error occurs
Since:
1.8.7
    public List<GroovyRowResultrows(String sqlMap paramsint offsetint maxRowsthrows SQLException {
        return rows(sqlsingletonList(params), offsetmaxRows);
    }

    
A variant of rows(java.lang.String,java.util.List,int,int) useful when providing the named parameters as named arguments.

Parameters:
params a map of named parameters
sql the SQL statement
offset the 1-based offset for the first row to be processed
maxRows the maximum number of rows to be processed
Returns:
a list of GroovyRowResult objects
Throws:
java.sql.SQLException if a database access error occurs
Since:
1.8.7
    public List<GroovyRowResultrows(Map paramsString sqlint offsetint maxRowsthrows SQLException {
        return rows(sqlsingletonList(params), offsetmaxRows);
    }

    
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 rows(java.lang.String,java.util.List).

Parameters:
sql the SQL statement
params an array of parameters
Returns:
a list of GroovyRowResult objects
Throws:
java.sql.SQLException if a database access error occurs
    public List<GroovyRowResultrows(String sqlObject[] params)
            throws SQLException {
        return rows(sqlparams, 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 rows(java.lang.String,java.util.List,int,int).

Parameters:
sql the SQL statement
params an array of parameters
offset the 1-based offset for the first row to be processed
maxRows the maximum number of rows to be processed
Returns:
a list of GroovyRowResult objects
Throws:
java.sql.SQLException if a database access error occurs
    public List<GroovyRowResultrows(String sqlObject[] paramsint offsetint maxRowsthrows SQLException {
        return rows(sql, Arrays.asList(params), offsetmaxRowsnull);
    }

    
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 -> 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 -> 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.

Parameters:
sql the SQL statement
params a list of parameters
metaClosure called for meta data (only once after sql execution)
Returns:
a list of GroovyRowResult objects
Throws:
java.sql.SQLException if a database access error occurs
    public List<GroovyRowResultrows(String sqlList<ObjectparamsClosure metaClosure)
            throws SQLException {
        return rows(sqlparams, 0, 0, metaClosure);
    }

    
A variant of rows(java.lang.String,java.util.List,groovy.lang.Closure) useful when providing the named parameters as a map.

Parameters:
sql the SQL statement
params a map of named parameters
metaClosure called for meta data (only once after sql execution)
Returns:
a list of GroovyRowResult objects
Throws:
java.sql.SQLException if a database access error occurs
Since:
1.8.7
    public List<GroovyRowResultrows(String sqlMap paramsClosure metaClosurethrows SQLException {
        return rows(sqlsingletonList(params), metaClosure);
    }

    
A variant of rows(java.lang.String,java.util.List,groovy.lang.Closure) useful when providing the named parameters as named arguments.

Parameters:
params a map of named parameters
sql the SQL statement
metaClosure called for meta data (only once after sql execution)
Returns:
a list of GroovyRowResult objects
Throws:
java.sql.SQLException if a database access error occurs
Since:
1.8.7
    public List<GroovyRowResultrows(Map paramsString sqlClosure metaClosurethrows SQLException {
        return rows(sqlsingletonList(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.

Parameters:
sql the SQL statement
params a list of parameters
offset the 1-based offset for the first row to be processed
maxRows the maximum number of rows to be processed
metaClosure called for meta data (only once after sql execution)
Returns:
a list of GroovyRowResult objects
Throws:
java.sql.SQLException if a database access error occurs
    public List<GroovyRowResultrows(String sqlList<Objectparamsint offsetint maxRowsClosure metaClosure)
            throws SQLException {
        AbstractQueryCommand command = createPreparedQueryCommand(sqlparams);
        // 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(sqlcommand.execute(), offsetmaxRowsmetaClosure);
        } finally {
            command.closeResources();
        }
    }

    
A variant of rows(java.lang.String,java.util.List,int,int,groovy.lang.Closure) useful when providing the named parameters as a map.

Parameters:
sql the SQL statement
params a map of named parameters
offset the 1-based offset for the first row to be processed
maxRows the maximum number of rows to be processed
metaClosure called for meta data (only once after sql execution)
Returns:
a list of GroovyRowResult objects
Throws:
java.sql.SQLException if a database access error occurs
Since:
1.8.7
    public List<GroovyRowResultrows(String sqlMap paramsint offsetint maxRowsClosure metaClosurethrows SQLException {
        return rows(sqlsingletonList(params), offsetmaxRowsmetaClosure);
    }

    
A variant of rows(java.lang.String,java.util.List,int,int,groovy.lang.Closure) useful when providing the named parameters as named arguments.

Parameters:
params a map of named parameters
sql the SQL statement
offset the 1-based offset for the first row to be processed
maxRows the maximum number of rows to be processed
metaClosure called for meta data (only once after sql execution)
Returns:
a list of GroovyRowResult objects
Throws:
java.sql.SQLException if a database access error occurs
Since:
1.8.7
    public List<GroovyRowResultrows(Map paramsString sqlint offsetint maxRowsClosure metaClosurethrows SQLException {
        return rows(sqlsingletonList(params), offsetmaxRowsmetaClosure);
    }

    
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.

Parameters:
sql the SQL statement
offset the 1-based offset for the first row to be processed
maxRows the maximum number of rows to be processed
Returns:
a list of GroovyRowResult objects
Throws:
java.sql.SQLException if a database access error occurs
    public List<GroovyRowResultrows(GString sqlint offsetint maxRowsthrows SQLException {
        return rows(sqloffsetmaxRowsnull);
    }

    
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 < $location")
 println "Found ${ans.size()} rows"
 

Resource handling is performed automatically where appropriate.

Parameters:
gstring a GString containing the SQL query with embedded params
Returns:
a list of GroovyRowResult objects
Throws:
java.sql.SQLException if a database access error occurs
See also:
expand(java.lang.Object)
    public List<GroovyRowResultrows(GString gstringthrows SQLException {
        return rows(gstringnull);
    }

    
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 -> println "Found $meta.columnCount columns" }
 def ans = sql.rows("select * from PERSON where location_id < $location", printNumCols)
 println "Found ${ans.size()} rows"
 

Resource handling is performed automatically where appropriate.

Parameters:
gstring a GString containing the SQL query with embedded params
metaClosure called with meta data of the ResultSet
Returns:
a list of GroovyRowResult objects
Throws:
java.sql.SQLException if a database access error occurs
See also:
expand(java.lang.Object)
    public List<GroovyRowResultrows(GString gstringClosure metaClosure)
            throws SQLException {
        List<Objectparams = getParameters(gstring);
        String sql = asSql(gstringparams);
        return rows(sqlparamsmetaClosure);
    }

    
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.

Parameters:
gstring the SQL statement
offset the 1-based offset for the first row to be processed
maxRows the maximum number of rows to be processed
metaClosure called for meta data (only once after sql execution)
Returns:
a list of GroovyRowResult objects
Throws:
java.sql.SQLException if a database access error occurs
    public List<GroovyRowResultrows(GString gstringint offsetint maxRowsClosure metaClosurethrows SQLException {
        List<Objectparams = getParameters(gstring);
        String sql = asSql(gstringparams);
        return rows(sqlparamsoffsetmaxRowsmetaClosure);
    }

    
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.

Parameters:
sql the SQL statement
Returns:
a GroovyRowResult object or null if no row is found
Throws:
java.sql.SQLException if a database access error occurs
    public GroovyRowResult firstRow(String sqlthrows SQLException {
        List<GroovyRowResultrows = 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 < $location")
 println ans.firstname
 

Resource handling is performed automatically where appropriate.

Parameters:
gstring a GString containing the SQL query with embedded params
Returns:
a GroovyRowResult object or null if no row is found
Throws:
java.sql.SQLException if a database access error occurs
See also:
expand(java.lang.Object)
    public GroovyRowResult firstRow(GString gstringthrows SQLException {
        List<Objectparams = getParameters(gstring);
        String sql = asSql(gstringparams);
        return firstRow(sqlparams);
    }

    
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.

Parameters:
sql the SQL statement
params a list of parameters
Returns:
a GroovyRowResult object or null if no row is found
Throws:
java.sql.SQLException if a database access error occurs
    public GroovyRowResult firstRow(String sqlList<Objectparamsthrows SQLException {
        List<GroovyRowResultrows = null;
        try {
            rows = rows(sqlparams, 1, 1, null);
        }
        //should be SQLFeatureNotSupportedException instead once we move to Java 1.6
        catch (SQLException featureNotSupportedException) {
            rows = rows(sqlparams);
        }
        if (rows.isEmpty()) return null;
        return rows.get(0);
    }

    
A variant of firstRow(java.lang.String,java.util.List) useful when providing the named parameters as named arguments.

Parameters:
params a map containing the named parameters
sql the SQL statement
Returns:
a GroovyRowResult object or null if no row is found
Throws:
java.sql.SQLException if a database access error occurs
Since:
1.8.7
    public GroovyRowResult firstRow(Map paramsString sqlthrows SQLException {
        return firstRow(sqlsingletonList(params));
    }

    
Performs the given SQL query and return the first row of the result set.

An Object array variant of firstRow(java.lang.String,java.util.List).

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

Parameters:
sql the SQL statement
params an array of parameters
Returns:
a GroovyRowResult object or null if no row is found
Throws:
java.sql.SQLException if a database access error occurs
    public GroovyRowResult firstRow(String sqlObject[] paramsthrows 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.

Parameters:
sql the SQL to execute
Returns:
true if the first result is a ResultSet object; false if it is an update count or there are no results
Throws:
java.sql.SQLException if a database access error occurs
    public boolean execute(String sqlthrows SQLException {
        Connection connection = createConnection();
        Statement statement = null;
        try {
            statement = getStatement(connectionsql);
            boolean isResultSet = statement.execute(sql);
            this. = statement.getUpdateCount();
            return isResultSet;
        } catch (SQLException e) {
            .warning("Failed to execute: " + sql + " because: " + e.getMessage());
            throw e;
        } finally {
            closeResources(connectionstatement);
        }
    }

    
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 ->
   if (first) {
     first = false
     assert !isResultSet && result == 0
   } else {
     assert isResultSet && result == [[ID:1, FIRSTNAME:'James', LASTNAME:'Strachan'], [ID:4, FIRSTNAME:'Jean', LASTNAME:'Gabin']]
   }
 }
 

Resource handling is performed automatically where appropriate.

Parameters:
sql the SQL to execute
processResults a Closure which will be passed two parameters: either true plus a list of GroovyRowResult values derived from statement.getResultSet() or false plus the update count from statement.getUpdateCount(). The closure will be called for each result produced from executing the SQL.
Throws:
java.sql.SQLException if a database access error occurs
Since:
2.3.2
    public void