Overview | Package | Class | Deprecated | Index | Help
| Groovy 1.7.0
| FRAMES NO FRAMES
SUMMARY: NESTED | FIELD | CONSTR | METHOD | DETAIL: FIELD | CONSTR | METHOD

## groovy.sql
Class Sql

    java.lang.Object
      groovy.sql.Sql

    class Sql
    extends Object

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.

The class provides numerous extension points for overriding the facade behavior associated with the various aspects of managing the interaction with the underlying database.

author:
  Chris Stevenson

author:
  James Strachan

author:
  Paul King

author:
  Marc DeXeT

author:
  John Bito

author:
  John Hurst

version:
  $Revision: 17997 $

Nested Class Summary
`class` | `Sql.AbstractQueryCommand`
`class` | `Sql.AbstractStatementCommand`
`class` | `Sql.CreatePreparedStatementCommand`
`class` | `Sql.CreateStatementCommand`
`class` | `Sql.PreparedQueryCommand`
`class` | `Sql.QueryCommand`

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`
`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`
`protected static Logger` | `log` Hook to allow derived classes to access the log

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

Method Summary
`static InParameter` | `ARRAY(Object value)`
`static InParameter` | `BIGINT(Object value)`
`static InParameter` | `BINARY(Object value)`
`static InParameter` | `BIT(Object value)`
`static InParameter` | `BLOB(Object value)`
`static InParameter` | `BOOLEAN(Object value)`
`static InParameter` | `CHAR(Object value)`
`static InParameter` | `CLOB(Object value)`
`static InParameter` | `DATALINK(Object value)`
`static InParameter` | `DATE(Object value)`
`static InParameter` | `DECIMAL(Object value)`
`static InParameter` | `DISTINCT(Object value)`
`static InParameter` | `DOUBLE(Object value)`
`static InParameter` | `FLOAT(Object value)`
`static InParameter` | `INTEGER(Object value)`
`static InParameter` | `JAVA_OBJECT(Object value)`
`static InParameter` | `LONGVARBINARY(Object value)`
`static InParameter` | `LONGVARCHAR(Object value)`
`static InParameter` | `NULL(Object value)`
`static InParameter` | `NUMERIC(Object value)`
`static InParameter` | `OTHER(Object value)`
`static InParameter` | `REAL(Object value)`
`static InParameter` | `REF(Object value)`
`static InParameter` | `SMALLINT(Object value)`
`static InParameter` | `STRUCT(Object value)`
`static InParameter` | `TIME(Object value)`
`static InParameter` | `TIMESTAMP(Object value)`
`static InParameter` | `TINYINT(Object value)`
`static InParameter` | `VARBINARY(Object value)`
`static InParameter` | `VARCHAR(Object value)`
`protected List` | `asList(String sql, ResultSet rs)` Hook to allow derived classes to override list of result collection behavior.
`protected List` | `asList(String sql, ResultSet rs, Closure metaClosure)` Hook to allow derived classes to override list of result collection behavior.
`protected String` | `asSql(GString gstring, List values)` Hook to allow derived classes to override sql generation from GStrings.
`void` | `cacheConnection(Closure closure)` Caches the connection used while the closure is active.
`void` | `cacheStatements(Closure closure)` Caches every created preparedStatement in Closure closure Every cached preparedStatement is closed after closure has been called.
`int` | `call(String sql)` Performs a stored procedure call.
`int` | `call(GString gstring)` Performs a stored procedure call with the given embedded parameters.
`int` | `call(String sql, List params)` Performs a stored procedure call with the given parameters.
`void` | `call(String sql, List params, Closure closure)` Performs a stored procedure call with the given parameters.
`void` | `call(GString gstring, Closure closure)` Performs a stored procedure call with the given parameters, calling the closure once with all result objects.
`void` | `close()` If this SQL object was created with a Connection then this method closes the connection.
`protected void` | `closeResources(Connection connection, Statement statement, ResultSet results)` An extension point allowing derived classes to change the behavior of resource closing.
`protected void` | `closeResources(Connection connection, Statement statement)` An extension point allowing the behavior of resource closing to be overridden in derived classes.
`void` | `commit()` If this SQL object was created with a Connection then this method commits the connection.
`protected void` | `configure(Statement statement)` Provides a hook for derived classes to be able to configure JDBC statements.
`protected Connection` | `createConnection()` An extension point allowing derived classes to change the behavior of connection creation.
`protected AbstractQueryCommand` | `createPreparedQueryCommand(String sql, List queryParams)` Factory for the PreparedQueryCommand command pattern object allows subclass to supply implementations of the command class.
`protected AbstractQueryCommand` | `createQueryCommand(String sql)` Factory for the QueryCommand command pattern object allows subclasses to supply implementations of the command class.
`DataSet` | `dataSet(String table)`
`DataSet` | `dataSet(Class type)`
`void` | `eachRow(String sql, Closure closure)` Performs the given SQL query calling the given Closure with each row of the result set.
`void` | `eachRow(String sql, Closure metaClosure, Closure rowClosure)` Performs the given SQL query calling the given `rowClosure` with each row of the result set.
`void` | `eachRow(String sql, List params, Closure metaClosure, Closure closure)` Performs the given SQL query calling the given Closure with each row of the result set.
`void` | `eachRow(String sql, List params, Closure closure)` Performs the given SQL query calling the given Closure with each row of the result set.
`void` | `eachRow(GString gstring, Closure metaClosure, Closure closure)` Performs the given SQL query calling the given Closure with each row of the result set.
`void` | `eachRow(GString gstring, Closure closure)` Performs the given SQL query calling the given Closure with each row of the result set.
`boolean` | `execute(String sql)` Executes the given piece of SQL.
`boolean` | `execute(String sql, List params)` Executes the given piece of SQL with parameters.
`boolean` | `execute(GString gstring)` Executes the given SQL with embedded expressions inside.
`List` | `executeInsert(String sql)` Executes the given SQL statement (typically an INSERT statement).
`List` | `executeInsert(String sql, List params)` Executes the given SQL statement (typically an INSERT statement).
`List` | `executeInsert(GString gstring)` Executes the given SQL statement (typically an INSERT statement).
`protected ResultSet` | `executePreparedQuery(String sql, List params)` Hook to allow derived classes to access ResultSet returned from query.
`protected ResultSet` | `executeQuery(String sql)` Hook to allow derived classes to access ResultSet returned from query.
`int` | `executeUpdate(String sql)` Executes the given SQL update.
`int` | `executeUpdate(String sql, List params)` Executes the given SQL update with parameters.
`int` | `executeUpdate(GString gstring)` Executes the given SQL update with embedded expressions inside.
`static ExpandedVariable` | `expand(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(String sql)` Hook to allow derived classes to override where clause sniffing.
`Object` | `firstRow(String sql)` Performs the given SQL query and return the first row of the result set.
`Object` | `firstRow(GString gstring)` Performs the given SQL query and return the first row of the result set.
`Object` | `firstRow(String sql, List params)` Performs the given SQL query and return the first row of the result set.
`Connection` | `getConnection()` If this instance was created with a single Connection then the connection is returned.
`DataSource` | `getDataSource()`
`protected 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()`
`static InParameter` | `in(int type, Object value)` Create a new InParameter
`static InOutParameter` | `inout(InParameter in)` Create an inout parameter using this in parameter.
`boolean` | `isCacheStatements()`
`static void` | `loadDriver(String driverClassName)` Attempts to load the JDBC driver on the thread, current or system class loaders
`static Sql` | `newInstance(String url)` Creates a new Sql instance given a JDBC connection URL.
`static Sql` | `newInstance(String url, Properties properties)` Creates a new Sql instance given a JDBC connection URL and some properties.
`static Sql` | `newInstance(String url, Properties properties, String driverClassName)` Creates a new Sql instance given a JDBC connection URL, some properties and a driver class name.
`static Sql` | `newInstance(String url, String user, String password)` Creates a new Sql instance given a JDBC connection URL, a username and a password.
`static Sql` | `newInstance(String url, String user, String password, String driverClassName)` Creates a new Sql instance given a JDBC connection URL, a username, a password and a driver class name.
`static Sql` | `newInstance(String url, String driverClassName)` Creates a new Sql instance given a JDBC connection URL and a driver class name.
`protected String` | `nullify(String sql)` Hook to allow derived classes to override null handling.
`static OutParameter` | `out(int type)` Create a new OutParameter
`void` | `query(String sql, Closure closure)` Performs the given SQL query, which should return a single `ResultSet` object.
`void` | `query(String sql, List params, Closure closure)` Performs the given SQL query, which should return a single `ResultSet` object.
`void` | `query(GString gstring, Closure closure)` Performs the given SQL query, which should return a single `ResultSet` object.
`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.
`List` | `rows(String sql)` Performs the given SQL query and return the rows of the result set.
`List` | `rows(String sql, Closure metaClosure)` Performs the given SQL query and return the rows of the result set.
`List` | `rows(String sql, List params)` Performs the given SQL query and return the rows of the result set.
`List` | `rows(String sql, List params, Closure metaClosure)` Performs the given SQL query and return the rows of the result set.
`List` | `rows(GString gstring)` Performs the given SQL query and return the rows of the result set.
`List` | `rows(GString gstring, Closure metaClosure)` Performs the given SQL query and return the rows of the result set.
`void` | `setCacheStatements(boolean cacheStatements)` Enables statement caching.
`protected void` | `setInternalConnection(Connection conn)` Stub needed for testing.
`protected void` | `setObject(PreparedStatement statement, int i, Object value)` Strategy method allowing derived classes to handle types differently such as for CLOBs etc.
`protected void` | `setParameters(List params, PreparedStatement statement)` Hook to allow derived classes to override behavior associated with setting params for a prepared 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)` Performs the closure within a batch using a cached connection.
`void` | `withStatement(Closure configureStatement)` Allows a closure to be passed in to configure the JDBC statements before they are executed.
`void` | `withTransaction(Closure closure)` Performs the closure within a transaction using a cached connection.

Methods inherited from class Object
wait, wait, wait, hashCode, getClass, equals, toString, notify, notifyAll

Field Detail

### ARRAY

    static final OutParameter ARRAY

### BIGINT

    static final OutParameter BIGINT

### BINARY

    static final OutParameter BINARY

### BIT

    static final OutParameter BIT

### BLOB

    static final OutParameter BLOB

### BOOLEAN

    static final OutParameter BOOLEAN

### CHAR

    static final OutParameter CHAR

### CLOB

    static final OutParameter CLOB

### DATALINK

    static final OutParameter DATALINK

### DATE

    static final OutParameter DATE

### DECIMAL

    static final OutParameter DECIMAL

### DISTINCT

    static final OutParameter DISTINCT

### DOUBLE

    static final OutParameter DOUBLE

### FLOAT

    static final OutParameter FLOAT

### INTEGER

    static final OutParameter INTEGER

### JAVA_OBJECT

    static final OutParameter JAVA_OBJECT

### LONGVARBINARY

    static final OutParameter LONGVARBINARY

### LONGVARCHAR

    static final OutParameter LONGVARCHAR

### NULL

    static final OutParameter NULL

### NUMERIC

    static final OutParameter NUMERIC

### OTHER

    static final OutParameter OTHER

### REAL

    static final OutParameter REAL

### REF

    static final OutParameter REF

### SMALLINT

    static final OutParameter SMALLINT

### STRUCT

    static final OutParameter STRUCT

### TIME

    static final OutParameter TIME

### TIMESTAMP

    static final OutParameter TIMESTAMP

### TINYINT

    static final OutParameter TINYINT

### VARBINARY

    static final OutParameter VARBINARY

### VARCHAR

    static final OutParameter VARCHAR

### log

    protected static Logger log

  Hook to allow derived classes to access the log

Constructor Detail

### Sql

    public 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.
  param:
    dataSource the DataSource to use

### Sql

    public Sql(Connection connection)

  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. You can do this on the connection object directly or by calling the Connection#close() method.
  param:
    connection the Connection to use

### Sql

    public Sql(Sql parent)

Method Detail

### ARRAY

    public static InParameter ARRAY(Object value)

### BIGINT

    public static InParameter BIGINT(Object value)

### BINARY

    public static InParameter BINARY(Object value)

### BIT

    public static InParameter BIT(Object value)

### BLOB

    public static InParameter BLOB(Object value)

### BOOLEAN

    public static InParameter BOOLEAN(Object value)

### CHAR

    public static InParameter CHAR(Object value)

### CLOB

    public static InParameter CLOB(Object value)

### DATALINK

    public static InParameter DATALINK(Object value)

### DATE

    public static InParameter DATE(Object value)

### DECIMAL

    public static InParameter DECIMAL(Object value)

### DISTINCT

    public static InParameter DISTINCT(Object value)

### DOUBLE

    public static InParameter DOUBLE(Object value)

### FLOAT

    public static InParameter FLOAT(Object value)

### INTEGER

    public static InParameter INTEGER(Object value)

### JAVA_OBJECT

    public static InParameter JAVA_OBJECT(Object value)

### LONGVARBINARY

    public static InParameter LONGVARBINARY(Object value)

### LONGVARCHAR

    public static InParameter LONGVARCHAR(Object value)

### NULL

    public static InParameter NULL(Object value)

### NUMERIC

    public static InParameter NUMERIC(Object value)

### OTHER

    public static InParameter OTHER(Object value)

### REAL

    public static InParameter REAL(Object value)

### REF

    public static InParameter REF(Object value)

### SMALLINT

    public static InParameter SMALLINT(Object value)

### STRUCT

    public static InParameter STRUCT(Object value)

### TIME

    public static InParameter TIME(Object value)

### TIMESTAMP

    public static InParameter TIMESTAMP(Object value)

### TINYINT

    public static InParameter TINYINT(Object value)

### VARBINARY

    public static InParameter VARBINARY(Object value)

### VARCHAR

    public static InParameter VARCHAR(Object value)

### asList

    protected List asList(String 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.
  param:
    sql query to execute
  param:
    rs the ResultSet to process
  return:
    the resulting list of rows
  throws:
    SQLException if a database error occurs

### asList

    protected List asList(String sql, ResultSet rs, Closure metaClosure)

  Hook to allow derived classes to override list of result collection behavior. The default behavior is to return a list of GroovyRowResult objects corresponding to each row in the ResultSet.
  param:
    sql query to execute
  param:
    rs the ResultSet to process
  param:
    metaClosure called for meta data (only once after sql execution)
  return:
    the resulting list of rows
  throws:
    SQLException if a database error occurs

### asSql

    protected String asSql(GString gstring, List values)

  Hook to allow derived classes to override sql generation from GStrings.
  param:
    gstring a GString containing the SQL query with embedded params
  param:
    values the values to embed
  return:
    the SQL version of the given query using ? instead of any parameter
  see:
    #expand(Object)

### cacheConnection

    public void cacheConnection(Closure closure)

  Caches the connection used while the closure is active. If the closure takes a single argument, it will be called with the connection, otherwise it will be called with no arguments.
  param:
    closure the given closure
  throws:
    SQLException if a database error occurs

### 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.
  param:
    closure the given closure
  throws:
    SQLException if a database error occurs
  see:
    #setCacheStatements(boolean)

### call

    public int call(String sql)

  Performs a stored procedure call.

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

       sql.execute """
           CREATE PROCEDURE HouseSwap(_first1 VARCHAR(50), _first2 VARCHAR(50))
           BEGIN
               DECLARE _loc1 INT;
               DECLARE _loc2 INT;
               SELECT location_id into _loc1 FROM PERSON where firstname = _first1;
               SELECT location_id into _loc2 FROM PERSON where firstname = _first2;
               UPDATE PERSON
               set location_id = case firstname
                   when _first1 then _loc2
                   when _first2 then _loc1
               end
               where (firstname = _first1 OR firstname = _first2);
           END
       """

  then you can invoke the procedure as follows:

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

  param:
    sql the SQL statement
  return:
    the number of rows updated or 0 for SQL statements that return nothing
  throws:
    SQLException if a database access error occurs

### call

    public int call(GString gstring)

  Performs a stored procedure call with the given embedded parameters.

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

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

  param:
    gstring a GString containing the SQL query with embedded params
  return:
    the number of rows updated or 0 for SQL statements that return nothing
  throws:
    SQLException if a database access error occurs
  see:
    #expand(Object)
  see:
    #call(String)

### call

    public int call(String sql, List params)

  Performs a stored procedure call with the given parameters.

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

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

  param:
    sql the SQL statement
  param:
    params a list of parameters
  return:
    the number of rows updated or 0 for SQL statements that return nothing
  throws:
    SQLException if a database access error occurs
  see:
    #call(String)

### call

    public void call(String sql, List params, Closure closure)

  Performs a stored procedure call with the given parameters. The closure is called once with all the out parameters.

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

       // Tested with MySql 5.0.75
       sql.execute """
           CREATE PROCEDURE Hemisphere(
               IN p_firstname VARCHAR(50),
               IN p_lastname VARCHAR(50),
               OUT ans VARCHAR(50))
           BEGIN
           DECLARE loc INT;
           SELECT location_id into loc FROM PERSON where firstname = p_firstname and lastname = p_lastname;
           CASE loc
               WHEN 40 THEN
                   SET ans = 'Southern Hemisphere';
               ELSE
                   SET ans = 'Northern Hemisphere';
           END CASE;
           END;
       """

  we can now call the stored procedure as follows:

       sql.call '{call Hemisphere(?, ?, ?)}', ['Guillaume', 'Laforge', Sql.VARCHAR], { dwells ->
           println dwells
       }

  which will output '`Northern Hemisphere`'.

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

       // Tested with MySql 5.0.75
       sql.execute """
           create function FullName(p_firstname VARCHAR(40)) returns VARCHAR(80)
           begin
               declare ans VARCHAR(80);
               SELECT CONCAT(firstname, ' ', lastname) INTO ans FROM PERSON WHERE firstname = p_firstname;
               return ans;
           end
       """

       // Tested with MS SQLServer Express 2008
       sql.execute """
           create function FullName(@firstname VARCHAR(40)) returns VARCHAR(80)
           begin
               declare @ans VARCHAR(80)
               SET @ans = (SELECT firstname + ' ' + lastname FROM PERSON WHERE firstname = @firstname)
               return @ans
           end
       """

       // Tested with Oracle XE 10g
       sql.execute """
           create function FullName(p_firstname VARCHAR) return VARCHAR is
           ans VARCHAR(80);
           begin
               SELECT CONCAT(CONCAT(firstname, ' '), lastname) INTO ans FROM PERSON WHERE firstname = p_firstname;
               return ans;
           end;
       """

  and here is how you access the stored function for all databases:

       sql.call("{? = call FullName(?)}", [Sql.VARCHAR, 'Sam']) { name ->
           assert name == 'Sam Pullara'
       }

  param:
    sql the sql statement
  param:
    params a list of parameters
  param:
    closure called for each row with a GroovyResultSet
  throws:
    SQLException if a database access error occurs

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

  param:
    gstring a GString containing the SQL query with embedded params
  param:
    closure called for each row with a GroovyResultSet
  throws:
    SQLException if a database access error occurs
  see:
    #call(String, List, Closure)
  see:
    #expand(Object)

### close

    public void close()

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

### closeResources

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

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

### closeResources

    protected void closeResources(Connection connection, Statement statement)

  An extension point allowing the behavior of resource closing to be overridden in derived classes.
  param:
    connection the connection to close
  param:
    statement the statement to close

### commit

    public void commit()

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

### configure

    protected void configure(Statement statement)

  Provides a hook for derived classes to be able to configure JDBC statements. Default behavior is to call a previously saved closure, if any, using the statement as a parameter.
  param:
    statement the statement to configure

### createConnection

    protected 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.
  return:
    the connection associated with this Sql
  throws:
    java.sql.SQLException if a SQL error occurs

### createPreparedQueryCommand

    protected AbstractQueryCommand createPreparedQueryCommand(String sql, List queryParams)

  Factory for the PreparedQueryCommand command pattern object allows subclass to supply implementations of the command class.
  see:
    #createQueryCommand(String)
  param:
    sql statement to be executed, including optional parameter placeholders (?)
  param:
    queryParams List of parameter values corresponding to parameter placeholders
  return:
    a command - invoke its execute() and closeResource() methods

### createQueryCommand

    protected AbstractQueryCommand createQueryCommand(String sql)

  Factory for the QueryCommand command pattern object allows subclasses to supply implementations of the command class. The factory will be used in a pattern similar to

       AbstractQueryCommand q = createQueryCommand("update TABLE set count = 0) where count is null");
       try {
       	   ResultSet rs = q.execute();
           return asList(rs);
       } finally {
           q.closeResources();
       }

  param:
    sql statement to be executed
  return:
    a command - invoke its execute() and closeResource() methods

### dataSet

    public DataSet dataSet(String table)

### dataSet

    public DataSet dataSet(Class type)

### eachRow

    public void eachRow(String sql, Closure closure)

  Performs the given SQL query calling the given Closure with each row of the result set. The row will be a `GroovyRowResult` which is a Map that also supports accessing the fields using 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
       }

  param:
    sql the sql statement
  param:
    closure called for each row with a GroovyResultSet
  throws:
    SQLException if a database access error occurs

### eachRow

    public void eachRow(String sql, Closure metaClosure, Closure rowClosure)

  Performs the given SQL query calling the given `rowClosure` with each row of the result set. The row will be a `GroovyRowResult` which is a Map that also supports accessing the fields using 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)

  param:
    sql the sql statement
  param:
    metaClosure called for meta data (only once after sql execution)
  param:
    rowClosure called for each row with a GroovyResultSet
  throws:
    SQLException if a database access error occurs

### eachRow

    public void eachRow(String sql, List params, Closure metaClosure, Closure closure)

  Performs the given SQL query calling the given Closure with each row of the result set. The row will be a `GroovyRowResult` which is a Map that also supports accessing the fields using 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)

  param:
    sql the sql statement
  param:
    params a list of parameters
  param:
    metaClosure called for meta data (only once after sql execution)
  param:
    closure called for each row with a GroovyResultSet
  throws:
    SQLException if a database access error occurs

### eachRow

    public void eachRow(String sql, List params, Closure closure)

  Performs the given SQL query calling the given Closure with each row of the result set. The row will be a `GroovyRowResult` which is a Map that also supports accessing the fields using 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"
       }

  param:
    sql the sql statement
  param:
    params a list of parameters
  param:
    closure called for each row with a GroovyResultSet
  throws:
    SQLException if a database access error occurs

### eachRow

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

  Performs the given SQL query calling the given Closure with each row of the result set. The row will be a `GroovyRowResult` which is a Map that also supports accessing the fields using 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)

  param:
    gstring a GString containing the SQL query with embedded params
  param:
    metaClosure called for meta data (only once after sql execution)
  param:
    closure called for each row with a GroovyResultSet
  throws:
    SQLException if a database access error occurs
  see:
    #expand(Object)

### eachRow

    public void eachRow(GString gstring, Closure closure)

  Performs the given SQL query calling the given Closure with each row of the result set. The row will be a `GroovyRowResult` which is a Map that also supports accessing the fields using 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
       }

  param:
    gstring a GString containing the SQL query with embedded params
  param:
    closure called for each row with a GroovyResultSet
  throws:
    SQLException if a database access error occurs
  see:
    #expand(Object)

### execute

    public boolean execute(String sql)

  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

  param:
    sql the SQL to execute
  return:
    `true` if the first result is a `ResultSet` object; `false` if it is an update count or there are no results
  throws:
    SQLException if a database access error occurs

### execute

    public boolean execute(String sql, 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

  param:
    sql the SQL statement
  param:
    params a list of parameters
  return:
    `true` if the first result is a `ResultSet` object; `false` if it is an update count or there are no results
  throws:
    SQLException if a database access error occurs

### execute

    public boolean execute(GString gstring)

  Executes the given SQL with embedded expressions inside. Also saves the updateCount, if any, for subsequent examination.

  Example usage:

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

  param:
    gstring a GString containing the SQL query with embedded params
  return:
    `true` if the first result is a `ResultSet` object; `false` if it is an update count or there are no results
  throws:
    SQLException if a database access error occurs
  see:
    #expand(Object)

### executeInsert

    public List executeInsert(String sql)

  Executes the given SQL statement (typically an INSERT statement). Use this variant when you want to receive the values of any auto-generated columns, such as an autoincrement ID field. See #executeInsert(GString) for more details.
  param:
    sql The SQL statement to execute
  return:
    A list of the auto-generated column values for each inserted row (typically auto-generated keys)
  throws:
    SQLException if a database access error occurs

### executeInsert

    public List executeInsert(String sql, 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.
  param:
    sql The SQL statement to execute
  param:
    params The parameter values that will be substituted into the SQL statement's parameter slots
  return:
    A list of the auto-generated column values for each inserted row (typically auto-generated keys)
  throws:
    SQLException if a database access error occurs

### executeInsert

    public List executeInsert(GString gstring)

  Executes the given SQL statement (typically an INSERT statement). Use this variant when you want to receive the values of any auto-generated columns, such as an autoincrement ID field. The query may contain GString expressions.

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

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

           def sql = Sql.newInstance("jdbc:mysql://localhost:3306/groovy",
                                     "user",
                                     "password",
                                     "com.mysql.jdbc.Driver")

           def keys = sql.executeInsert("insert into test_table (INT_DATA, STRING_DATA) "
                                 + "VALUES (1, 'Key Largo')")

           def id = keys[0][0]

           // 'id' now contains the value of the new row's ID column.
           // It can be used to update an object representation's
           // id attribute for example.
           ...

  param:
    gstring a GString containing the SQL query with embedded params
  return:
    A list of the auto-generated column values for each inserted row (typically auto-generated keys)
  throws:
    SQLException if a database access error occurs
  see:
    #expand(Object)

### executePreparedQuery

    protected final ResultSet executePreparedQuery(String sql, List params)

  Hook to allow derived classes to access ResultSet returned from query.
  param:
    sql query to execute
  param:
    params parameters matching question mark placeholders in the query
  return:
    the resulting ResultSet
  throws:
    SQLException if a database error occurs

### executeQuery

    protected final ResultSet executeQuery(String sql)

  Hook to allow derived classes to access ResultSet returned from query.
  param:
    sql query to execute
  return:
    the resulting ResultSet
  throws:
    SQLException if a database error occurs

### executeUpdate

    public int executeUpdate(String sql)

  Executes the given SQL update.
  param:
    sql the SQL to execute
  return:
    the number of rows updated or 0 for SQL statements that return nothing
  throws:
    SQLException if a database access error occurs

### executeUpdate

    public int executeUpdate(String sql, List params)

  Executes the given SQL update with parameters.
  param:
    sql the SQL statement
  param:
    params a list of parameters
  return:
    the number of rows updated or 0 for SQL statements that return nothing
  throws:
    SQLException if a database access error occurs

### executeUpdate

    public int executeUpdate(GString gstring)

  Executes the given SQL update with embedded expressions inside.
  param:
    gstring a GString containing the SQL query with embedded params
  return:
    the number of rows updated or 0 for SQL statements that return nothing
  throws:
    SQLException if a database access error occurs
  see:
    #expand(Object)

### expand

    public static ExpandedVariable expand(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]

  param:
    object the object of interest
  return:
    the expanded variable
  see:
    #expand(Object)

### findWhereKeyword

    protected int findWhereKeyword(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.
  param:
    sql the SQL statement
  return:
    the index of the found keyword or -1 if not found

### firstRow

    public Object firstRow(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

  param:
    sql the SQL statement
  return:
    a GroovyRowResult object or `null` if no row is found
  throws:
    SQLException if a database access error occurs

### firstRow

    public Object firstRow(GString gstring)

  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

  param:
    gstring a GString containing the SQL query with embedded params
  return:
    a GroovyRowResult object or `null` if no row is found
  throws:
    SQLException if a database access error occurs
  see:
    #expand(Object)

### firstRow

    public Object firstRow(String sql, List params)

  Performs the given SQL query and return the first row of the result set. The query may contain placeholder question marks which match the given list of parameters.

  Example usages:

       def ans = sql.firstRow("select * from PERSON where lastname like ?", ['%a%'])
       println ans.firstname

  If your database returns scalar functions as ResultSets, you can also use firstRow to gain access to stored procedure results, e.g. using hsqldb 1.9 RC4:

       sql.execute """
           create function FullName(p_firstname VARCHAR(40)) returns VARCHAR(80)
           BEGIN atomic
           DECLARE ans VARCHAR(80);
           SET ans = (SELECT firstname || ' ' || lastname FROM PERSON WHERE firstname = p_firstname);
           RETURN ans;
           END
       """

       assert sql.firstRow("{call FullName(?)}", ['Sam'])[0] == 'Sam Pullara'

  param:
    sql the SQL statement
  param:
    params a list of parameters
  return:
    a GroovyRowResult object or `null` if no row is found
  throws:
    SQLException if a database access error occurs

### getConnection

    public Connection getConnection()

  If this instance was created with a single Connection then the connection is returned. Otherwise if this instance was created with a DataSource then this method returns null
  return:
    the connection wired into this object, or null if this object uses a DataSource

### getDataSource

    public DataSource getDataSource()

### getParameters

    protected List getParameters(GString gstring)

  Hook to allow derived classes to override behavior associated with extracting params from a GString.
  param:
    gstring a GString containing the SQL query with embedded params
  return:
    extracts the parameters from the expression as a List
  see:
    #expand(Object)

### getResultSetConcurrency

    public int getResultSetConcurrency()

  Gets the resultSetConcurrency for statements created using the connection.
  return:
    the current resultSetConcurrency value
  since:
    1.5.2

### getResultSetHoldability

    public int getResultSetHoldability()

  Gets the resultSetHoldability for statements created using the connection.
  return:
    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.
  return:
    the current resultSetType value
  since:
    1.5.2

### getUpdateCount

    public int getUpdateCount()

  return:
    Returns the updateCount.

### in

    public static InParameter in(int type, Object value)

  Create a new InParameter
  param:
    type the JDBC data type
  param:
    value the object value
  return:
    an InParameter

### inout

    public static InOutParameter inout(InParameter in)

  Create an inout parameter using this in parameter.
  param:
    in the InParameter of interest
  return:
    the resulting InOutParameter

### isCacheStatements

    public boolean isCacheStatements()

  return:
    boolean true if cache is enabled

### loadDriver

    public static void loadDriver(String driverClassName)

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

### newInstance

    public static Sql newInstance(String url)

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

### newInstance

    public static Sql newInstance(String url, Properties properties)

  Creates a new Sql instance given a JDBC connection URL and some properties.
  param:
    url a database url of the form `jdbc:subprotocol:subname`
  param:
    properties a list of arbitrary string tag/value pairs as connection arguments; normally at least a "user" and "password" property should be included
  return:
    a new Sql instance with a connection
  throws:
    SQLException if a database access error occurs

### newInstance

    public static Sql newInstance(String url, Properties properties, String driverClassName)

  Creates a new Sql instance given a JDBC connection URL, some properties and a driver class name.
  param:
    url a database url of the form `jdbc:subprotocol:subname`
  param:
    properties a list of arbitrary string tag/value pairs as connection arguments; normally at least a "user" and "password" property should be included
  param:
    driverClassName the fully qualified class name of the driver class
  return:
    a new Sql instance with a connection
  throws:
    SQLException if a database access error occurs
  throws:
    ClassNotFoundException if the class cannot be found or loaded

### newInstance

    public static Sql newInstance(String url, String user, String password)

  Creates a new Sql instance given a JDBC connection URL, a username and a password.
  param:
    url a database url of the form `jdbc:subprotocol:subname`
  param:
    user the database user on whose behalf the connection is being made
  param:
    password the user's password
  return:
    a new Sql instance with a connection
  throws:
    SQLException if a database access error occurs

### newInstance

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

  Creates a new Sql instance given a JDBC connection URL, a username, a password and a driver class name.
  param:
    url a database url of the form `jdbc:subprotocol:subname`
  param:
    user the database user on whose behalf the connection is being made
  param:
    password the user's password
  param:
    driverClassName the fully qualified class name of the driver class
  return:
    a new Sql instance with a connection
  throws:
    SQLException if a database access error occurs
  throws:
    ClassNotFoundException if the class cannot be found or loaded

### newInstance

    public static Sql newInstance(String url, String driverClassName)

  Creates a new Sql instance given a JDBC connection URL and a driver class name.
  param:
    url a database url of the form `jdbc:subprotocol:subname`
  param:
    driverClassName the fully qualified class name of the driver class
  return:
    a new Sql instance with a connection
  throws:
    SQLException if a database access error occurs
  throws:
    ClassNotFoundException if the class cannot be found or loaded

### nullify

    protected String nullify(String sql)

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

### out

    public static OutParameter out(int type)

  Create a new OutParameter
  param:
    type the JDBC data type.
  return:
    an OutParameter

### query

    public void query(String sql, 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.

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

### query

    public void query(String sql, 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')
       }

  All resources including the ResultSet are closed automatically after the closure is called.
  param:
    sql the sql statement
  param:
    params a list of parameters
  param:
    closure called for each row with a GroovyResultSet
  throws:
    SQLException if a database access error occurs

### query

    public void query(GString gstring, 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 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.
  param:
    gstring a GString containing the SQL query with embedded params
  param:
    closure called for each row with a GroovyResultSet
  throws:
    SQLException if a database access error occurs
  see:
    #expand(Object)

### resultSet

    public static ResultSetOutParameter resultSet(int type)

  Create a new ResultSetOutParameter
  param:
    type the JDBC data type.
  return:
    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 List rows(String sql)

  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"

  param:
    sql the SQL statement
  return:
    a list of GroovyRowResult objects
  throws:
    SQLException if a database access error occurs

### rows

    public List rows(String sql, Closure metaClosure)

  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"

  param:
    sql the SQL statement
  param:
    metaClosure called with meta data of the ResultSet
  return:
    a list of GroovyRowResult objects
  throws:
    SQLException if a database access error occurs

### rows

    public List rows(String sql, 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"

  param:
    sql the SQL statement
  param:
    params a list of parameters
  return:
    a list of GroovyRowResult objects
  throws:
    SQLException if a database access error occurs

### rows

    public List rows(String sql, List params, Closure metaClosure)

  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"

  param:
    sql the SQL statement
  param:
    params a list of parameters
  param:
    metaClosure called for meta data (only once after sql execution)
  return:
    a list of GroovyRowResult objects
  throws:
    SQLException if a database access error occurs

### rows

    public List rows(GString gstring)

  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"

  param:
    gstring a GString containing the SQL query with embedded params
  return:
    a list of GroovyRowResult objects
  throws:
    SQLException if a database access error occurs
  see:
    #expand(Object)

### rows

    public List rows(GString gstring, Closure metaClosure)

  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"

  param:
    gstring a GString containing the SQL query with embedded params
  param:
    metaClosure called with meta data of the ResultSet
  return:
    a list of GroovyRowResult objects
  throws:
    SQLException if a database access error occurs
  see:
    #expand(Object)

### setCacheStatements

    public void setCacheStatements(boolean cacheStatements)

  Enables statement caching. if b is true, cache is created and all created prepared statements will be cached. if b is false, all cached statements will be properly closed.
  param:
    cacheStatements the new value

### setInternalConnection

    protected void setInternalConnection(Connection conn)

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

### setObject

    protected void setObject(PreparedStatement statement, int i, Object value)

  Strategy method allowing derived classes to handle types differently such as for CLOBs etc.
  param:
    statement the statement of interest
  param:
    i the index of the object of interest
  param:
    value the new object value
  throws:
    SQLException if a database access error occurs

### setParameters

    protected void setParameters(List params, PreparedStatement statement)

  Hook to allow derived classes to override behavior associated with setting params for a prepared statement. Default behavior is to append the parameters to the given statement using `setObject`.
  param:
    params the parameters to append
  param:
    statement the statement
  throws:
    SQLException if a database access error occurs

### 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.
  param:
    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.
  param:
    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.
  param:
    resultSetType one of the following `ResultSet` constants: `ResultSet.TYPE_FORWARD_ONLY`, `ResultSet.TYPE_SCROLL_INSENSITIVE`, or `ResultSet.TYPE_SCROLL_SENSITIVE`
  since:
    1.5.2

### withBatch

    public int[] withBatch(Closure closure)

  Performs the closure within a batch using a cached connection. The closure will be called with a single argument; the statement associated with this batch. Use it like this:

       def updateCounts = sql.withBatch { stmt ->
           stmt.addBatch("insert into TABLENAME ...")
           stmt.addBatch("insert into TABLENAME ...")
           stmt.addBatch("insert into TABLENAME ...")
       }

  param:
    closure the closure containing batch and optionally other statements
  return:
    an array of update counts containing one element for each command in the batch. The elements of the array are ordered according to the order in which commands were added to the batch.
  throws:
    SQLException if a database access error occurs, or this method is called on a closed `Statement`, or the driver does not support batch statements. Throws 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.

### withStatement

    public void withStatement(Closure configureStatement)

  Allows a closure to be passed in to configure the JDBC statements before they are executed. It can be used to do things like set the query size etc. When this method is invoked, the supplied closure is saved. Statements subsequently created from other methods will then be configured using this closure. The statement being configured is passed into the closure as its single argument, e.g.:

       sql.withStatement{ stmt -> stmt.maxRows == 10 }
       def firstTenRows = sql.rows("select * from table")

  param:
    configureStatement the closure

### 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.
  param:
    closure the given closure
  throws:
    SQLException if a database error occurs

Copyright © 2003-2009 The Codehaus. All rights reserved.