Overview | Package | Class | Deprecated | Index | Help

Groovy Documentation

| FRAMES NO FRAMES
SUMMARY: NESTED | FIELD | CONSTR | METHOD | DETAIL: FIELD | CONSTR | METHOD

## groovy.sql
[Java] Class Sql

    java.lang.Object
      groovy.sql.Sql

    public class Sql

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

Authors:
  Chris Stevenson
  James Strachan
  Paul King
  Marc DeXeT
  John Bito
  John Hurst
  David Durham
  Daniel Henrique Alves Lima
  David Sutherland

Nested Class Summary
`protected class` | `Sql.AbstractQueryCommand`

Field Summary
`static OutParameter` | `ARRAY`
`static OutParameter` | `BIGINT`
`static OutParameter` | `BINARY`
`static OutParameter` | `BIT`
`static OutParameter` | `BLOB`
`static OutParameter` | `BOOLEAN`
`static OutParameter` | `CHAR`
`static OutParameter` | `CLOB`
`static OutParameter` | `DATALINK`
`static OutParameter` | `DATE`
`static OutParameter` | `DECIMAL`
`static OutParameter` | `DISTINCT`
`static OutParameter` | `DOUBLE`
`static OutParameter` | `FLOAT`
`static OutParameter` | `INTEGER`
`static OutParameter` | `JAVA_OBJECT`
`protected static Logger` | `LOG` Hook to allow derived classes to access the log
`static OutParameter` | `LONGVARBINARY`
`static OutParameter` | `LONGVARCHAR`
`static OutParameter` | `NULL`
`static OutParameter` | `NUMERIC`
`static OutParameter` | `OTHER`
`static OutParameter` | `REAL`
`static OutParameter` | `REF`
`static OutParameter` | `SMALLINT`
`static OutParameter` | `STRUCT`
`static OutParameter` | `TIME`
`static OutParameter` | `TIMESTAMP`
`static OutParameter` | `TINYINT`
`static OutParameter` | `VARBINARY`
`static OutParameter` | `VARCHAR`

Constructor Summary
`Sql(javax.sql.DataSource dataSource)` Constructs an SQL instance using the given DataSource.
`Sql(java.sql.Connection connection)`
`Sql(Sql parent)`

Method Summary
`static InParameter` | `ARRAY(java.lang.Object value)`
`static InParameter` | `BIGINT(java.lang.Object value)`
`static InParameter` | `BINARY(java.lang.Object value)`
`static InParameter` | `BIT(java.lang.Object value)`
`static InParameter` | `BLOB(java.lang.Object value)`
`static InParameter` | `BOOLEAN(java.lang.Object value)`
`static InParameter` | `CHAR(java.lang.Object value)`
`static InParameter` | `CLOB(java.lang.Object value)`
`static InParameter` | `DATALINK(java.lang.Object value)`
`static InParameter` | `DATE(java.lang.Object value)`
`static InParameter` | `DECIMAL(java.lang.Object value)`
`static InParameter` | `DISTINCT(java.lang.Object value)`
`static InParameter` | `DOUBLE(java.lang.Object value)`
`static InParameter` | `FLOAT(java.lang.Object value)`
`static InParameter` | `INTEGER(java.lang.Object value)`
`static InParameter` | `JAVA_OBJECT(java.lang.Object value)`
`static InParameter` | `LONGVARBINARY(java.lang.Object value)`
`static InParameter` | `LONGVARCHAR(java.lang.Object value)`
`static InParameter` | `NULL(java.lang.Object value)`
`static InParameter` | `NUMERIC(java.lang.Object value)`
`static InParameter` | `OTHER(java.lang.Object value)`
`static InParameter` | `REAL(java.lang.Object value)`
`static InParameter` | `REF(java.lang.Object value)`
`static InParameter` | `SMALLINT(java.lang.Object value)`
`static InParameter` | `STRUCT(java.lang.Object value)`
`static InParameter` | `TIME(java.lang.Object value)`
`static InParameter` | `TIMESTAMP(java.lang.Object value)`
`static InParameter` | `TINYINT(java.lang.Object value)`
`static InParameter` | `VARBINARY(java.lang.Object value)`
`static InParameter` | `VARCHAR(java.lang.Object value)`
`protected java.util.List` | `asList(java.lang.String sql, java.sql.ResultSet rs)` Hook to allow derived classes to override list of result collection behavior.
`protected java.util.List` | `asList(java.lang.String sql, java.sql.ResultSet rs, Closure metaClosure)`
`protected java.util.List` | `asList(java.lang.String sql, java.sql.ResultSet rs, int offset, int maxRows, Closure metaClosure)`
`protected java.lang.String` | `asSql(GString gstring, java.util.List values)` Hook to allow derived classes to override sql generation from GStrings.
`void` | `cacheConnection(Closure closure)`
`void` | `cacheStatements(Closure closure)` Caches every created preparedStatement in Closure closure Every cached preparedStatement is closed after closure has been called.
`int` | `call(java.lang.String sql)`
`int` | `call(GString gstring)`
`int` | `call(java.lang.String sql, java.util.List params)`
`int` | `call(java.lang.String sql, java.lang.Object[] params)` Performs a stored procedure call with the given parameters.
`void` | `call(java.lang.String sql, java.util.List params, Closure closure)`
`void` | `call(GString gstring, Closure closure)` Performs a stored procedure call with the given parameters, calling the closure once with all result objects.
`SqlWithParams` | `checkForNamedParams(java.lang.String sql, java.util.List params)`
`void` | `close()`
`protected void` | `closeResources(java.sql.Connection connection, Statement statement, java.sql.ResultSet results)` An extension point allowing derived classes to change the behavior of resource closing.
`protected void` | `closeResources(java.sql.Connection connection, Statement statement)`
`protected void` | `closeResources(java.sql.Connection connection)`
`void` | `commit()`
`protected void` | `configure(Statement statement)`
`protected java.sql.Connection` | `createConnection()` An extension point allowing derived classes to change the behavior of connection creation.
`protected Sql.AbstractQueryCommand` | `createPreparedQueryCommand(java.lang.String sql, java.util.List queryParams)`
`protected Sql.AbstractQueryCommand` | `createQueryCommand(java.lang.String sql)` Stub needed for testing.
`DataSet` | `dataSet(java.lang.String table)`
`DataSet` | `dataSet(java.lang.Class type)`
`void` | `eachRow(java.lang.String sql, Closure closure)`
`void` | `eachRow(java.lang.String sql, int offset, int maxRows, Closure closure)`
`void` | `eachRow(java.lang.String sql, Closure metaClosure, Closure rowClosure)`
`void` | `eachRow(java.lang.String sql, Closure metaClosure, int offset, int maxRows, Closure rowClosure)`
`void` | `eachRow(java.lang.String sql, java.util.List params, Closure metaClosure, int offset, int maxRows, Closure 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.
`void` | `eachRow(java.lang.String sql, java.util.Map map, Closure metaClosure, int offset, int maxRows, Closure rowClosure)` A variant of eachRow(String, java.util.List, groovy.lang.Closure, int, int, groovy.lang.Closure) allowing the named parameters to be supplied in a map.
`void` | `eachRow(java.util.Map map, java.lang.String sql, Closure metaClosure, int offset, int maxRows, Closure rowClosure)`
`void` | `eachRow(java.lang.String sql, java.util.List params, Closure metaClosure, Closure rowClosure)`
`void` | `eachRow(java.lang.String sql, java.util.Map params, Closure metaClosure, Closure rowClosure)`
`void` | `eachRow(java.util.Map params, java.lang.String sql, Closure metaClosure, Closure rowClosure)`
`void` | `eachRow(java.lang.String sql, java.util.List params, Closure closure)`
`void` | `eachRow(java.lang.String sql, java.util.Map params, Closure closure)`
`void` | `eachRow(java.util.Map params, java.lang.String sql, Closure closure)`
`void` | `eachRow(java.lang.String sql, java.util.List params, int offset, int maxRows, Closure closure)`
`void` | `eachRow(java.lang.String sql, java.util.Map params, int offset, int maxRows, Closure closure)`
`void` | `eachRow(java.util.Map params, java.lang.String sql, int offset, int maxRows, Closure closure)`
`void` | `eachRow(GString gstring, Closure metaClosure, Closure rowClosure)`
`void` | `eachRow(GString gstring, Closure metaClosure, int offset, int maxRows, Closure rowClosure)`
`void` | `eachRow(GString gstring, int offset, int maxRows, Closure closure)`
`void` | `eachRow(GString gstring, Closure closure)`
`boolean` | `execute(java.lang.String sql)`
`boolean` | `execute(java.lang.String sql, java.util.List params)` Executes the given piece of SQL with parameters.
`boolean` | `execute(java.util.Map params, java.lang.String sql)` A variant of execute(String, java.util.List) useful when providing the named parameters as named arguments.
`boolean` | `execute(java.lang.String sql, java.lang.Object[] params)`
`boolean` | `execute(GString gstring)`
`java.util.List` | `executeInsert(java.lang.String sql)`
`java.util.List` | `executeInsert(java.lang.String sql, java.util.List params)` Executes the given SQL statement (typically an INSERT statement).
`java.util.List` | `executeInsert(java.util.Map params, java.lang.String sql)` A variant of firstRow(String, java.util.List) useful when providing the named parameters as named arguments.
`java.util.List` | `executeInsert(java.lang.String sql, java.lang.Object[] params)`
`java.util.List` | `executeInsert(GString gstring)`
`protected java.sql.ResultSet` | `executePreparedQuery(java.lang.String sql, java.util.List params)`
`protected java.sql.ResultSet` | `executeQuery(java.lang.String sql)` Useful helper method which handles resource management when executing a query which returns a result set.
`int` | `executeUpdate(java.lang.String sql)`
`int` | `executeUpdate(java.lang.String sql, java.util.List params)` Executes the given SQL update with parameters.
`int` | `executeUpdate(java.util.Map params, java.lang.String sql)` A variant of executeUpdate(String, java.util.List) useful when providing the named parameters as named arguments.
`int` | `executeUpdate(java.lang.String sql, java.lang.Object[] params)`
`int` | `executeUpdate(GString gstring)`
`static ExpandedVariable` | `expand(java.lang.Object object)` When using GString SQL queries, allows a variable to be expanded in the Sql string rather than representing an sql parameter.
`protected int` | `findWhereKeyword(java.lang.String sql)` Hook to allow derived classes to override where clause sniffing.
`GroovyRowResult` | `firstRow(java.lang.String sql)` Performs the given SQL query and return the first row of the result set.
`GroovyRowResult` | `firstRow(GString gstring)`
`GroovyRowResult` | `firstRow(java.lang.String sql, java.util.List params)`
`GroovyRowResult` | `firstRow(java.util.Map params, java.lang.String sql)`
`GroovyRowResult` | `firstRow(java.lang.String sql, java.lang.Object[] params)`
`java.sql.Connection` | `getConnection()`
`javax.sql.DataSource` | `getDataSource()`
`protected java.util.List` | `getParameters(GString gstring)` Hook to allow derived classes to override behavior associated with extracting params from a GString.
`int` | `getResultSetConcurrency()` Gets the resultSetConcurrency for statements created using the connection.
`int` | `getResultSetHoldability()` Gets the resultSetHoldability for statements created using the connection.
`int` | `getResultSetType()` Gets the resultSetType for statements created using the connection.
`int` | `getUpdateCount()` @return Returns the updateCount.
`java.util.List` | `getUpdatedParams(java.util.List params, java.util.List indexPropList)` @return boolean true if caching is enabled (the default is true)
`static InParameter` | `in(int type, java.lang.Object value)`
`static InOutParameter` | `inout(InParameter in)` Create an inout parameter using this in parameter.
`boolean` | `isCacheNamedQueries()`
`boolean` | `isCacheStatements()`
`boolean` | `isEnableNamedQueries()`
`boolean` | `isWithinBatch()` Returns true if the current Sql object is currently executing a withBatch method call.
`static void` | `loadDriver(java.lang.String driverClassName)` Attempts to load the JDBC driver on the thread, current or system class loaders
`static Sql` | `newInstance(java.lang.String url)` Creates a new Sql instance given a JDBC connection URL.
`static Sql` | `newInstance(java.lang.String url, java.util.Properties properties)` Creates a new Sql instance given a JDBC connection URL and some properties.
`static Sql` | `newInstance(java.lang.String url, java.util.Properties properties, java.lang.String driverClassName)` Creates a new Sql instance given a JDBC connection URL, some properties and a driver class name.
`static Sql` | `newInstance(java.lang.String url, java.lang.String user, java.lang.String password)` Creates a new Sql instance given a JDBC connection URL, a username and a password.
`static Sql` | `newInstance(java.lang.String url, java.lang.String user, java.lang.String password, java.lang.String driverClassName)` Creates a new Sql instance given a JDBC connection URL, a username, a password and a driver class name.
`static Sql` | `newInstance(java.lang.String url, java.lang.String driverClassName)` Creates a new Sql instance given a JDBC connection URL and a driver class name.
`static Sql` | `newInstance(java.util.Map args)` Creates a new Sql instance given parameters in a Map.
`protected java.lang.String` | `nullify(java.lang.String sql)` Hook to allow derived classes to override null handling.
`static OutParameter` | `out(int type)` Create a new OutParameter
`SqlWithParams` | `preCheckForNamedParams(java.lang.String sql)`
`void` | `query(java.lang.String sql, Closure closure)`
`void` | `query(java.lang.String sql, java.util.List params, Closure closure)` Performs the given SQL query, which should return a single `ResultSet` object.
`void` | `query(java.lang.String sql, java.util.Map map, Closure closure)` A variant of query(String, java.util.List, groovy.lang.Closure) useful when providing the named parameters as a map.
`void` | `query(java.util.Map map, java.lang.String sql, Closure closure)`
`void` | `query(GString gstring, Closure closure)`
`static ResultSetOutParameter` | `resultSet(int type)` Create a new ResultSetOutParameter
`void` | `rollback()` If this SQL object was created with a Connection then this method rolls back the connection.
`java.util.List` | `rows(java.lang.String sql)`
`java.util.List` | `rows(java.lang.String sql, int offset, int maxRows)`
`java.util.List` | `rows(java.lang.String sql, Closure metaClosure)`
`java.util.List` | `rows(java.lang.String sql, int offset, int maxRows, Closure metaClosure)`
`java.util.List` | `rows(java.lang.String sql, java.util.List params)` Performs the given SQL query and return the rows of the result set.
`java.util.List` | `rows(java.util.Map params, java.lang.String sql)`
`java.util.List` | `rows(java.lang.String sql, java.util.List params, int offset, int maxRows)`
`java.util.List` | `rows(java.lang.String sql, java.util.Map params, int offset, int maxRows)`
`java.util.List` | `rows(java.util.Map params, java.lang.String sql, int offset, int maxRows)`
`java.util.List` | `rows(java.lang.String sql, java.lang.Object[] params)`
`java.util.List` | `rows(java.lang.String sql, java.lang.Object[] params, int offset, int maxRows)`
`java.util.List` | `rows(java.lang.String sql, java.util.List params, Closure metaClosure)`
`java.util.List` | `rows(java.lang.String sql, java.util.Map params, Closure metaClosure)`
`java.util.List` | `rows(java.util.Map params, java.lang.String sql, Closure metaClosure)`
`java.util.List` | `rows(java.lang.String sql, java.util.List params, int offset, int maxRows, Closure metaClosure)`
`java.util.List` | `rows(java.lang.String sql, java.util.Map params, int offset, int maxRows, Closure metaClosure)` A variant of rows(String, java.util.List, int, int, groovy.lang.Closure) useful when providing the named parameters as a map.
`java.util.List` | `rows(java.util.Map params, java.lang.String sql, int offset, int maxRows, Closure metaClosure)`
`java.util.List` | `rows(GString sql, int offset, int maxRows)`
`java.util.List` | `rows(GString gstring)`
`java.util.List` | `rows(GString gstring, Closure metaClosure)`
`java.util.List` | `rows(GString gstring, int offset, int maxRows, Closure metaClosure)` Performs the given SQL query and return a "page" of rows from the result set.
`void` | `setCacheNamedQueries(boolean cacheNamedQueries)`
`void` | `setCacheStatements(boolean cacheStatements)` @return boolean true if cache is enabled (default is false)
`void` | `setEnableNamedQueries(boolean enableNamedQueries)`
`protected void` | `setInternalConnection(java.sql.Connection conn)`
`protected void` | `setObject(java.sql.PreparedStatement statement, int i, java.lang.Object value)`
`protected void` | `setParameters(java.util.List params, java.sql.PreparedStatement statement)`
`void` | `setResultSetConcurrency(int resultSetConcurrency)` Sets the resultSetConcurrency for statements created using the connection.
`void` | `setResultSetHoldability(int resultSetHoldability)` Sets the resultSetHoldability for statements created using the connection.
`void` | `setResultSetType(int resultSetType)` Sets the resultSetType for statements created using the connection.
`int[]` | `withBatch(Closure closure)`
`int[]` | `withBatch(int batchSize, Closure closure)`
`int[]` | `withBatch(java.lang.String sql, Closure closure)` Performs the closure (containing batch operations specific to an associated prepared statement) within a batch.
`int[]` | `withBatch(int batchSize, java.lang.String sql, Closure closure)`
`static void` | `withInstance(java.lang.String url, Closure c)` Invokes a closure passing it a new Sql instance created from the given JDBC connection URL.
`static void` | `withInstance(java.lang.String url, java.util.Properties properties, Closure c)` Invokes a closure passing it a new Sql instance created from the given JDBC connection URL and properties.
`static void` | `withInstance(java.lang.String url, java.util.Properties properties, java.lang.String driverClassName, Closure c)` Invokes a closure passing it a new Sql instance created from the given JDBC connection URL, properties and driver classname.
`static void` | `withInstance(java.lang.String url, java.lang.String user, java.lang.String password, Closure c)` Invokes a closure passing it a new Sql instance created from the given JDBC connection URL, user and password.
`static void` | `withInstance(java.lang.String url, java.lang.String user, java.lang.String password, java.lang.String driverClassName, Closure c)` Invokes a closure passing it a new Sql instance created from the given JDBC connection URL.
`static void` | `withInstance(java.lang.String url, java.lang.String driverClassName, Closure c)` Invokes a closure passing it a new Sql instance created from the given JDBC connection URL.
`static void` | `withInstance(java.util.Map args, Closure c)` Invokes a closure passing it a new Sql instance created from the given map of arguments.
`void` | `withStatement(Closure configureStatement)`
`void` | `withTransaction(Closure closure)` Performs the closure within a transaction using a cached connection.

Methods inherited from class java.lang.Object
java.lang.Object#wait(long), java.lang.Object#wait(long, int), java.lang.Object#wait(), java.lang.Object#equals(java.lang.Object), java.lang.Object#toString(), java.lang.Object#hashCode(), java.lang.Object#getClass(), java.lang.Object#notify(), java.lang.Object#notifyAll()

Field Detail

### ARRAY

    public static final OutParameter ARRAY

### BIGINT

    public static final OutParameter BIGINT

### BINARY

    public static final OutParameter BINARY

### BIT

    public static final OutParameter BIT

### BLOB

    public static final OutParameter BLOB

### BOOLEAN

    public static final OutParameter BOOLEAN

### CHAR

    public static final OutParameter CHAR

### CLOB

    public static final OutParameter CLOB

### DATALINK

    public static final OutParameter DATALINK

### DATE

    public static final OutParameter DATE

### DECIMAL

    public static final OutParameter DECIMAL

### DISTINCT

    public static final OutParameter DISTINCT

### DOUBLE

    public static final OutParameter DOUBLE

### FLOAT

    public static final OutParameter FLOAT

### INTEGER

    public static final OutParameter INTEGER

### JAVA_OBJECT

    public static final OutParameter JAVA_OBJECT

### LOG

    protected static final Logger LOG

  Hook to allow derived classes to access the log

### LONGVARBINARY

    public static final OutParameter LONGVARBINARY

### LONGVARCHAR

    public static final OutParameter LONGVARCHAR

### NULL

    public static final OutParameter NULL

### NUMERIC

    public static final OutParameter NUMERIC

### OTHER

    public static final OutParameter OTHER

### REAL

    public static final OutParameter REAL

### REF

    public static final OutParameter REF

### SMALLINT

    public static final OutParameter SMALLINT

### STRUCT

    public static final OutParameter STRUCT

### TIME

    public static final OutParameter TIME

### TIMESTAMP

    public static final OutParameter TIMESTAMP

### TINYINT

    public static final OutParameter TINYINT

### VARBINARY

    public static final OutParameter VARBINARY

### VARCHAR

    public static final OutParameter VARCHAR

Constructor Detail

### Sql

    public Sql(javax.sql.DataSource dataSource)

  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

### Sql

    public Sql(java.sql.Connection connection)

### Sql

    public Sql(Sql parent)

Method Detail

### ARRAY

    public static InParameter ARRAY(java.lang.Object value)

### BIGINT

    public static InParameter BIGINT(java.lang.Object value)

### BINARY

    public static InParameter BINARY(java.lang.Object value)

### BIT

    public static InParameter BIT(java.lang.Object value)

### BLOB

    public static InParameter BLOB(java.lang.Object value)

### BOOLEAN

    public static InParameter BOOLEAN(java.lang.Object value)

### CHAR

    public static InParameter CHAR(java.lang.Object value)

### CLOB

    public static InParameter CLOB(java.lang.Object value)

### DATALINK

    public static InParameter DATALINK(java.lang.Object value)

### DATE

    public static InParameter DATE(java.lang.Object value)

### DECIMAL

    public static InParameter DECIMAL(java.lang.Object value)

### DISTINCT

    public static InParameter DISTINCT(java.lang.Object value)

### DOUBLE

    public static InParameter DOUBLE(java.lang.Object value)

### FLOAT

    public static InParameter FLOAT(java.lang.Object value)

### INTEGER

    public static InParameter INTEGER(java.lang.Object value)

### JAVA_OBJECT

    public static InParameter JAVA_OBJECT(java.lang.Object value)

### LONGVARBINARY

    public static InParameter LONGVARBINARY(java.lang.Object value)

### LONGVARCHAR

    public static InParameter LONGVARCHAR(java.lang.Object value)

### NULL

    public static InParameter NULL(java.lang.Object value)

### NUMERIC

    public static InParameter NUMERIC(java.lang.Object value)

### OTHER

    public static InParameter OTHER(java.lang.Object value)

### REAL

    public static InParameter REAL(java.lang.Object value)

### REF

    public static InParameter REF(java.lang.Object value)

### SMALLINT

    public static InParameter SMALLINT(java.lang.Object value)

### STRUCT

    public static InParameter STRUCT(java.lang.Object value)

### TIME

    public static InParameter TIME(java.lang.Object value)

### TIMESTAMP

    public static InParameter TIMESTAMP(java.lang.Object value)

### TINYINT

    public static InParameter TINYINT(java.lang.Object value)

### VARBINARY

    public static InParameter VARBINARY(java.lang.Object value)

### VARCHAR

    public static InParameter VARCHAR(java.lang.Object value)

### asList

    protected java.util.List asList(java.lang.String sql, java.sql.ResultSet rs)

  Hook to allow derived classes to override list of result collection behavior. The default behavior is to return a list of GroovyRowResult objects corresponding to each row in the ResultSet.
  throws:
    SQLException if a database error occurs
  Parameters:
    `sql` - query to execute
    `rs` - the ResultSet to process
  Returns:
    the resulting list of rows

### asList

    protected java.util.List asList(java.lang.String sql, java.sql.ResultSet rs, Closure metaClosure)

### asList

    protected java.util.List asList(java.lang.String sql, java.sql.ResultSet rs, int offset, int maxRows, Closure metaClosure)

### asSql

    protected java.lang.String asSql(GString gstring, java.util.List values)

  Hook to allow derived classes to override sql generation from GStrings.
  Parameters:
    `gstring` - a GString containing the SQL query with embedded params
    `values` - the values to embed
  Returns:
    the SQL version of the given query using ? instead of any parameter
  See Also:
    expand(Object)

### cacheConnection

    public void cacheConnection(Closure closure)

### cacheStatements

    public void cacheStatements(Closure closure)

  Caches every created preparedStatement in Closure closure Every cached preparedStatement is closed after closure has been called. If the closure takes a single argument, it will be called with the connection, otherwise it will be called with no arguments.
  throws:
    SQLException if a database error occurs
  Parameters:
    `closure` - the given closure
  See Also:
    setCacheStatements(boolean)

### call

    public int call(java.lang.String sql)

### call

    public int call(GString gstring)

### call

    public int call(java.lang.String sql, java.util.List params)

### call

    public int call(java.lang.String sql, java.lang.Object[] params)

  Performs a stored procedure call with the given parameters.

  An Object array variant of call(String, List).
  throws:
    SQLException if a database access error occurs
  Parameters:
    `sql` - the SQL statement
    `params` - an array of parameters
  Returns:
    the number of rows updated or 0 for SQL statements that return nothing
  See Also:
    call(String)

### call

    public void call(java.lang.String sql, java.util.List params, Closure closure)

### call

    public void call(GString gstring, Closure closure)

  Performs a stored procedure call with the given parameters, calling the closure once with all result objects.

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

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

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

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

  Resource handling is performed automatically where appropriate.
  throws:
    SQLException if a database access error occurs
  Parameters:
    `gstring` - a GString containing the SQL query with embedded params
    `closure` - called for each row with a GroovyResultSet
  See Also:
    call(String, List, Closure)
    expand(Object)

### checkForNamedParams

    public SqlWithParams checkForNamedParams(java.lang.String sql, java.util.List params)

### close

    public void close()

### closeResources

    protected void closeResources(java.sql.Connection connection, Statement statement, java.sql.ResultSet results)

  An extension point allowing derived classes to change the behavior of resource closing.
  Parameters:
    `connection` - the connection to close
    `statement` - the statement to close
    `results` - the results to close

### closeResources

    protected void closeResources(java.sql.Connection connection, Statement statement)

### closeResources

    protected void closeResources(java.sql.Connection connection)

### commit

    public void commit()

### configure

    protected void configure(Statement statement)

### createConnection

    protected java.sql.Connection createConnection()

  An extension point allowing derived classes to change the behavior of connection creation. The default behavior is to either use the supplied connection or obtain it from the supplied datasource.
  throws:
    java.sql.SQLException if a SQL error occurs
  Returns:
    the connection associated with this Sql

### createPreparedQueryCommand

    protected Sql.AbstractQueryCommand createPreparedQueryCommand(java.lang.String sql, java.util.List queryParams)

### createQueryCommand

    protected Sql.AbstractQueryCommand createQueryCommand(java.lang.String sql)

  Stub needed for testing. Called when a connection is opened by one of the command-pattern classes so that a test case can monitor the state of the connection through its subclass.
  Parameters:
    `conn` - the connection that is about to be used by a command

### dataSet

    public DataSet dataSet(java.lang.String table)

### dataSet

    public DataSet dataSet(java.lang.Class type)

### eachRow

    public void eachRow(java.lang.String sql, Closure closure)

### eachRow

    public void eachRow(java.lang.String sql, int offset, int maxRows, Closure closure)

### eachRow

    public void eachRow(java.lang.String sql, Closure metaClosure, Closure rowClosure)

### eachRow

    public void eachRow(java.lang.String sql, Closure metaClosure, int offset, int maxRows, Closure rowClosure)

### eachRow

    public void eachRow(java.lang.String sql, java.util.List params, Closure metaClosure, int offset, int maxRows, Closure 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. 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.
  throws:
    SQLException if a database access error occurs
  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

### eachRow

    public void eachRow(java.lang.String sql, java.util.Map map, Closure metaClosure, int offset, int maxRows, Closure rowClosure)

  A variant of eachRow(String, java.util.List, groovy.lang.Closure, int, int, groovy.lang.Closure) allowing the named parameters to be supplied in a map.
  throws:
    SQLException if a database access error occurs
  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
  Since:
    1.8.7

### eachRow

    public void eachRow(java.util.Map map, java.lang.String sql, Closure metaClosure, int offset, int maxRows, Closure rowClosure)

### eachRow

    public void eachRow(java.lang.String sql, java.util.List params, Closure metaClosure, Closure rowClosure)

### eachRow

    public void eachRow(java.lang.String sql, java.util.Map params, Closure metaClosure, Closure rowClosure)

### eachRow

    public void eachRow(java.util.Map params, java.lang.String sql, Closure metaClosure, Closure rowClosure)

### eachRow

    public void eachRow(java.lang.String sql, java.util.List params, Closure closure)

### eachRow

    public void eachRow(java.lang.String sql, java.util.Map params, Closure closure)

### eachRow

    public void eachRow(java.util.Map params, java.lang.String sql, Closure closure)

### eachRow

    public void eachRow(java.lang.String sql, java.util.List params, int offset, int maxRows, Closure closure)

### eachRow

    public void eachRow(java.lang.String sql, java.util.Map params, int offset, int maxRows, Closure closure)

### eachRow

    public void eachRow(java.util.Map params, java.lang.String sql, int offset, int maxRows, Closure closure)

### eachRow

    public void eachRow(GString gstring, Closure metaClosure, Closure rowClosure)

### eachRow

    public void eachRow(GString gstring, Closure metaClosure, int offset, int maxRows, Closure rowClosure)

### eachRow

    public void eachRow(GString gstring, int offset, int maxRows, Closure closure)

### eachRow

    public void eachRow(GString gstring, Closure closure)

### execute

    public boolean execute(java.lang.String sql)

### execute

    public boolean execute(java.lang.String sql, java.util.List params)

  Executes the given piece of SQL with parameters. Also saves the updateCount, if any, for subsequent examination.

  Example usage:

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

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

  Resource handling is performed automatically where appropriate.
  throws:
    SQLException if a database access error occurs
  Parameters:
    `sql` - the SQL statement
    `params` - a list of parameters
  Returns:
    `true` if the first result is a `ResultSet` object; `false` if it is an update count or there are no results

### execute

    public boolean execute(java.util.Map params, java.lang.String sql)

  A variant of execute(String, java.util.List) useful when providing the named parameters as named arguments.
  throws:
    SQLException if a database access error occurs
  Parameters:
    `params` - a map containing the named parameters
    `sql` - the SQL statement
  Returns:
    `true` if the first result is a `ResultSet` object; `false` if it is an update count or there are no results
  Since:
    1.8.7

### execute

    public boolean execute(java.lang.String sql, java.lang.Object[] params)

### execute

    public boolean execute(GString gstring)

### executeInsert

    public java.util.List executeInsert(java.lang.String sql)

### executeInsert

    public java.util.List executeInsert(java.lang.String sql, java.util.List params)

  Executes the given SQL statement (typically an INSERT statement). Use this variant when you want to receive the values of any auto-generated columns, such as an autoincrement ID field. The query may contain placeholder question marks which match the given list of parameters. See executeInsert(GString) for more details.

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

  Resource handling is performed automatically where appropriate.
  throws:
    SQLException if a database access error occurs
  Parameters:
    `sql` - The SQL statement to execute
    `params` - The parameter values that will be substituted into the SQL statement's parameter slots
  Returns:
    A list of the auto-generated column values for each inserted row (typically auto-generated keys)

### executeInsert

    public java.util.List executeInsert(java.util.Map params, java.lang.String sql)

  A variant of firstRow(String, java.util.List) useful when providing the named parameters as named arguments.
  throws:
    SQLException if a database access error occurs
  Parameters:
    `params` - a map containing the named parameters
    `sql` - The SQL statement to execute
  Returns:
    A list of the auto-generated column values for each inserted row (typically auto-generated keys)
  Since:
    1.8.7

### executeInsert

    public java.util.List executeInsert(java.lang.String sql, java.lang.Object[] params)

### executeInsert

    public java.util.List executeInsert(GString gstring)

### executePreparedQuery

    protected final java.sql.ResultSet executePreparedQuery(java.lang.String sql, java.util.List params)

### executeQuery

    protected final java.sql.ResultSet executeQuery(java.lang.String sql)

  Useful helper method which handles resource management when executing a query which returns a result set. Derived classes of Sql can override "createQueryCommand" and then call this method to access the ResultSet returned from the provided query or alternatively can use the higher-level method of Sql which return result sets which are funnelled through this method, e.g. eachRow, query.
  throws:
    SQLException if a database error occurs
  Parameters:
    `sql` - query to execute
  Returns:
    the resulting ResultSet

### executeUpdate

    public int executeUpdate(java.lang.String sql)

### executeUpdate

    public int executeUpdate(java.lang.String sql, java.util.List params)

  Executes the given SQL update with parameters.

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

  Resource handling is performed automatically where appropriate.
  throws:
    SQLException if a database access error occurs
  Parameters:
    `sql` - the SQL statement
    `params` - a list of parameters
  Returns:
    the number of rows updated or 0 for SQL statements that return nothing

### executeUpdate

    public int executeUpdate(java.util.Map params, java.lang.String sql)

  A variant of executeUpdate(String, java.util.List) useful when providing the named parameters as named arguments.
  throws:
    SQLException if a database access error occurs
  Parameters:
    `params` - a map containing the named parameters
    `sql` - the SQL statement
  Returns:
    the number of rows updated or 0 for SQL statements that return nothing
  Since:
    1.8.7

### executeUpdate

    public int executeUpdate(java.lang.String sql, java.lang.Object[] params)

### executeUpdate

    public int executeUpdate(GString gstring)

### expand

    public static ExpandedVariable expand(java.lang.Object object)

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

### findWhereKeyword

    protected int findWhereKeyword(java.lang.String sql)

  Hook to allow derived classes to override where clause sniffing. Default behavior is to find the first 'where' keyword in the sql doing simple avoidance of the word 'where' within quotes.
  Parameters:
    `sql` - the SQL statement
  Returns:
    the index of the found keyword or -1 if not found

### firstRow

    public GroovyRowResult firstRow(java.lang.String sql)

  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.
  throws:
    SQLException if a database access error occurs
  Parameters:
    `sql` - the SQL statement
  Returns:
    a GroovyRowResult object or `null` if no row is found

### firstRow

    public GroovyRowResult firstRow(GString gstring)

### firstRow

    public GroovyRowResult firstRow(java.lang.String sql, java.util.List params)

### firstRow

    public GroovyRowResult firstRow(java.util.Map params, java.lang.String sql)

### firstRow

    public GroovyRowResult firstRow(java.lang.String sql, java.lang.Object[] params)

### getConnection

    public java.sql.Connection getConnection()

### getDataSource

    public javax.sql.DataSource getDataSource()

### getParameters

    protected java.util.List getParameters(GString gstring)

  Hook to allow derived classes to override behavior associated with extracting params from a GString.
  Parameters:
    `gstring` - a GString containing the SQL query with embedded params
  Returns:
    extracts the parameters from the expression as a List
  See Also:
    expand(Object)

### getResultSetConcurrency

    public int getResultSetConcurrency()

  Gets the resultSetConcurrency for statements created using the connection.
  Returns:
    the current resultSetConcurrency value
  Since:
    1.5.2

### getResultSetHoldability

    public int getResultSetHoldability()

  Gets the resultSetHoldability for statements created using the connection.
  Returns:
    the current resultSetHoldability value or -1 if not set
  Since:
    1.5.2

### getResultSetType

    public int getResultSetType()

  Gets the resultSetType for statements created using the connection.
  Returns:
    the current resultSetType value
  Since:
    1.5.2

### getUpdateCount

    public int getUpdateCount()

  Returns:
    Returns the updateCount.

### getUpdatedParams

    public java.util.List getUpdatedParams(java.util.List params, java.util.List indexPropList)

  Returns:
    boolean true if caching is enabled (the default is true)

### in

    public static InParameter in(int type, java.lang.Object value)

### inout

    public static InOutParameter inout(InParameter in)

  Create an inout parameter using this in parameter.
  Parameters:
    `in` - the InParameter of interest
  Returns:
    the resulting InOutParameter

### isCacheNamedQueries

    public boolean isCacheNamedQueries()

### isCacheStatements

    public boolean isCacheStatements()

### isEnableNamedQueries

    public boolean isEnableNamedQueries()

### isWithinBatch

    public boolean isWithinBatch()

  Returns true if the current Sql object is currently executing a withBatch method call.
  Returns:
    true if a withBatch call is currently being executed.

### loadDriver

    public static void loadDriver(java.lang.String driverClassName)

  Attempts to load the JDBC driver on the thread, current or system class loaders
  throws:
    ClassNotFoundException if the class cannot be found or loaded
  Parameters:
    `driverClassName` - the fully qualified class name of the driver class

### newInstance

    public static Sql newInstance(java.lang.String url)

  Creates a new Sql instance given a JDBC connection URL.
  throws:
    SQLException if a database access error occurs
  Parameters:
    `url` - a database url of the form `jdbc:subprotocol:subname`
  Returns:
    a new Sql instance with a connection

### newInstance

    public static Sql newInstance(java.lang.String url, java.util.Properties properties)

  Creates a new Sql instance given a JDBC connection URL and some properties.
  throws:
    SQLException if a database access error occurs
  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

### newInstance

    public static Sql newInstance(java.lang.String url, java.util.Properties properties, java.lang.String driverClassName)

  Creates a new Sql instance given a JDBC connection URL, some properties and a driver class name.
  throws:
    SQLException if a database access error occurs
  throws:
    ClassNotFoundException if the driver class cannot be found or loaded
  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

### newInstance

    public static Sql newInstance(java.lang.String url, java.lang.String user, java.lang.String password)

  Creates a new Sql instance given a JDBC connection URL, a username and a password.
  throws:
    SQLException if a database access error occurs
  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

### newInstance

    public static Sql newInstance(java.lang.String url, java.lang.String user, java.lang.String password, java.lang.String driverClassName)

  Creates a new Sql instance given a JDBC connection URL, a username, a password and a driver class name.
  throws:
    SQLException if a database access error occurs
  throws:
    ClassNotFoundException if the driver class cannot be found or loaded
  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

### newInstance

    public static Sql newInstance(java.lang.String url, java.lang.String driverClassName)

  Creates a new Sql instance given a JDBC connection URL and a driver class name.
  throws:
    SQLException if a database access error occurs
  throws:
    ClassNotFoundException if the driver class cannot be found or loaded
  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

### newInstance

    public static Sql newInstance(java.util.Map args)

  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
       )

  throws:
    SQLException if a database access error occurs
  throws:
    ClassNotFoundException if the driver class cannot be found or loaded
  Parameters:
    `args` - a Map contain further arguments
  Returns:
    a new Sql instance with a connection

### nullify

    protected java.lang.String nullify(java.lang.String sql)

  Hook to allow derived classes to override null handling. Default behavior is to replace ?'"? references with NULLish
  Parameters:
    `sql` - the SQL statement
  Returns:
    the modified SQL String

### out

    public static OutParameter out(int type)

  Create a new OutParameter
  Parameters:
    `type` - the JDBC data type.
  Returns:
    an OutParameter

### preCheckForNamedParams

    public SqlWithParams preCheckForNamedParams(java.lang.String sql)

### query

    public void query(java.lang.String sql, Closure closure)

### query

    public void query(java.lang.String sql, java.util.List params, Closure closure)

  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.
  throws:
    SQLException if a database access error occurs
  Parameters:
    `sql` - the sql statement
    `params` - a list of parameters
    `closure` - called for each row with a GroovyResultSet

### query

    public void query(java.lang.String sql, java.util.Map map, Closure closure)

  A variant of query(String, java.util.List, groovy.lang.Closure) useful when providing the named parameters as a map.
  throws:
    SQLException if a database access error occurs
  Parameters:
    `sql` - the sql statement
    `map` - a map containing the named parameters
    `closure` - called for each row with a GroovyResultSet
  Since:
    1.8.7

### query

    public void query(java.util.Map map, java.lang.String sql, Closure closure)

### query

    public void query(GString gstring, Closure closure)

### resultSet

    public static ResultSetOutParameter resultSet(int type)

  Create a new ResultSetOutParameter
  Parameters:
    `type` - the JDBC data type.
  Returns:
    a ResultSetOutParameter

### rollback

    public void rollback()

  If this SQL object was created with a Connection then this method rolls back the connection. If this SQL object was created from a DataSource then this method does nothing.
  throws:
    SQLException if a database access error occurs

### rows

    public java.util.List rows(java.lang.String sql)

### rows

    public java.util.List rows(java.lang.String sql, int offset, int maxRows)

### rows

    public java.util.List rows(java.lang.String sql, Closure metaClosure)

### rows

    public java.util.List rows(java.lang.String sql, int offset, int maxRows, Closure metaClosure)

### rows

    public java.util.List rows(java.lang.String sql, java.util.List params)

  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.
  throws:
    SQLException if a database access error occurs
  Parameters:
    `sql` - the SQL statement
    `params` - a list of parameters
  Returns:
    a list of GroovyRowResult objects

### rows

    public java.util.List rows(java.util.Map params, java.lang.String sql)

### rows

    public java.util.List rows(java.lang.String sql, java.util.List params, int offset, int maxRows)

### rows

    public java.util.List rows(java.lang.String sql, java.util.Map params, int offset, int maxRows)

### rows

    public java.util.List rows(java.util.Map params, java.lang.String sql, int offset, int maxRows)

### rows

    public java.util.List rows(java.lang.String sql, java.lang.Object[] params)

### rows

    public java.util.List rows(java.lang.String sql, java.lang.Object[] params, int offset, int maxRows)

### rows

    public java.util.List rows(java.lang.String sql, java.util.List params, Closure metaClosure)

### rows

    public java.util.List rows(java.lang.String sql, java.util.Map params, Closure metaClosure)

### rows

    public java.util.List rows(java.util.Map params, java.lang.String sql, Closure metaClosure)

### rows

    public java.util.List rows(java.lang.String sql, java.util.List params, int offset, int maxRows, Closure metaClosure)

### rows

    public java.util.List rows(java.lang.String sql, java.util.Map params, int offset, int maxRows, Closure metaClosure)

  A variant of rows(String, java.util.List, int, int, groovy.lang.Closure) useful when providing the named parameters as a map.
  throws:
    SQLException if a database access error occurs
  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
  Since:
    1.8.7

### rows

    public java.util.List rows(java.util.Map params, java.lang.String sql, int offset, int maxRows, Closure metaClosure)

### rows

    public java.util.List rows(GString sql, int offset, int maxRows)

### rows

    public java.util.List rows(GString gstring)

### rows

    public java.util.List rows(GString gstring, Closure metaClosure)

### rows

    public java.util.List rows(GString gstring, int offset, int maxRows, Closure metaClosure)

  Performs the given SQL query and return a "page" of rows from the result set. A page is defined as starting at a 1-based offset, and containing a maximum number of rows. In addition, the `metaClosure` will be called once passing in the `ResultSetMetaData` as argument. The query may contain GString expressions.

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

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

  Resource handling is performed automatically where appropriate.
  throws:
    SQLException if a database access error occurs
  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

### setCacheNamedQueries

    public void setCacheNamedQueries(boolean cacheNamedQueries)

### setCacheStatements

    public void setCacheStatements(boolean cacheStatements)

  Returns:
    boolean true if cache is enabled (default is false)

### setEnableNamedQueries

    public void setEnableNamedQueries(boolean enableNamedQueries)

### setInternalConnection

    protected void setInternalConnection(java.sql.Connection conn)

### setObject

    protected void setObject(java.sql.PreparedStatement statement, int i, java.lang.Object value)

### setParameters

    protected void setParameters(java.util.List params, java.sql.PreparedStatement statement)

### setResultSetConcurrency

    public void setResultSetConcurrency(int 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.
  Parameters:
    `resultSetConcurrency` - one of the following `ResultSet` constants: `ResultSet.CONCUR_READ_ONLY` or `ResultSet.CONCUR_UPDATABLE`
  Since:
    1.5.2

### setResultSetHoldability

    public void setResultSetHoldability(int 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.
  Parameters:
    `resultSetHoldability` - one of the following `ResultSet` constants: `ResultSet.HOLD_CURSORS_OVER_COMMIT` or `ResultSet.CLOSE_CURSORS_AT_COMMIT`
  Since:
    1.5.2

### setResultSetType

    public void setResultSetType(int 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.
  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

### withBatch

    public int[] withBatch(Closure closure)

### withBatch

    public int[] withBatch(int batchSize, Closure closure)

### withBatch

    public int[] withBatch(java.lang.String sql, Closure closure)

  Performs the closure (containing batch operations specific to an associated prepared statement) within a batch. Uses a batch size of zero, i.e. no automatic partitioning of batches.

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

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

  An example:

       def updateCounts = sql.withBatch('insert into TABLENAME(a, b, c) values (?, ?, ?)') { ps ->
           ps.addBatch([10, 12, 5])
           ps.addBatch([7, 3, 98])
           ps.addBatch(22, 67, 11)
           def partialUpdateCounts = ps.executeBatch() // optional interim batching
           ps.addBatch(30, 40, 50)
           ...
       }

  For integrity and performance reasons, you may wish to consider executing your batch command(s) within a transaction:

       sql.withTransaction {
           def result1 = sql.withBatch { ... }
           ...
       }

  throws:
    SQLException if a database access error occurs, or this method is called on a closed `Statement`, or the driver does not support batch statements. Throws java.sql.BatchUpdateException (a subclass of `SQLException`) if one of the commands sent to the database fails to execute properly or attempts to return a result set.
  Parameters:
    `sql` - batch update statement
    `closure` - the closure containing batch statements (to bind parameters) and optionally other statements
  Returns:
    an array of update counts containing one element for each binding in the batch. The elements of the array are ordered according to the order in which commands were executed.
  See Also:
    withBatch(int, String, Closure)
    BatchingPreparedStatementWrapper
    java.sql.PreparedStatement

### withBatch

    public int[] withBatch(int batchSize, java.lang.String sql, Closure closure)

### withInstance

    public static void withInstance(java.lang.String url, Closure c)

  Invokes a closure passing it a new Sql instance created from the given JDBC connection URL. The created connection will be closed if required.
  throws:
    SQLException if a database access error occurs
  Parameters:
    `url` - a database url of the form `jdbc:subprotocol:subname`
    `c` - the Closure to call
  See Also:
    newInstance(String)

### withInstance

    public static void withInstance(java.lang.String url, java.util.Properties properties, Closure c)

  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.
  throws:
    SQLException if a database access error occurs
  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
  See Also:
    newInstance(String, java.util.Properties)

### withInstance

    public static void withInstance(java.lang.String url, java.util.Properties properties, java.lang.String driverClassName, Closure c)

  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.
  throws:
    SQLException if a database access error occurs
  throws:
    ClassNotFoundException if the driver class cannot be found or loaded
  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
  See Also:
    newInstance(String, java.util.Properties, String)

### withInstance

    public static void withInstance(java.lang.String url, java.lang.String user, java.lang.String password, Closure c)

  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.
  throws:
    SQLException if a database access error occurs
  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
  See Also:
    newInstance(String, String, String)

### withInstance

    public static void withInstance(java.lang.String url, java.lang.String user, java.lang.String password, java.lang.String driverClassName, Closure c)

  Invokes a closure passing it a new Sql instance created from the given JDBC connection URL. The created connection will be closed if required.
  throws:
    SQLException if a database access error occurs
  throws:
    ClassNotFoundException if the driver class cannot be found or loaded
  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
  See Also:
    newInstance(String, String, String, String)

### withInstance

    public static void withInstance(java.lang.String url, java.lang.String driverClassName, Closure c)

  Invokes a closure passing it a new Sql instance created from the given JDBC connection URL. The created connection will be closed if required.
  throws:
    SQLException if a database access error occurs
  throws:
    ClassNotFoundException if the driver class cannot be found or loaded
  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
  See Also:
    newInstance(String, String)

### withInstance

    public static void withInstance(java.util.Map args, Closure c)

  Invokes a closure passing it a new Sql instance created from the given map of arguments. The created connection will be closed if required.
  throws:
    SQLException if a database access error occurs
  throws:
    ClassNotFoundException if the driver class cannot be found or loaded
  Parameters:
    `args` - a Map contain further arguments
    `c` - the Closure to call
  See Also:
    newInstance(java.util.Map)

### withStatement

    public void withStatement(Closure configureStatement)

### withTransaction

    public void withTransaction(Closure closure)

  Performs the closure within a transaction using a cached connection. If the closure takes a single argument, it will be called with the connection, otherwise it will be called with no arguments.
  throws:
    SQLException if a database error occurs
  Parameters:
    `closure` - the given closure

Groovy Documentation