groovy.sql

## Class Sql

- java.lang.Object
- - groovy.sql.Sql

- Direct Known Subclasses:
    DataSet

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

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

  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.

  #### Named and named ordinal parameters

  Several of the methods in this class which have a String-based sql query and params in a List
  Version:
    $Revision: 21733 $
  Author:
    Chris Stevenson, James Strachan, Paul King, Marc DeXeT, John Bito, John Hurst

- - ### Nested Class Summary

    Nested Classes
    Modifier and Type | Class and Description
    --- | ---
    `protected class` | `Sql.AbstractQueryCommand`
    `protected class` | `Sql.PreparedQueryCommand`
    `protected class` | `Sql.QueryCommand`

  - ### Field Summary

    Fields
    Modifier and Type | Field and Description
    --- | ---
    `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

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

  - ### Method Summary

    Methods
    Modifier and Type | Method and Description
    --- | ---
    `static InParameter` | `ARRAY(Object value)`
    `protected List<GroovyRowResult>` | `asList(String sql, ResultSet rs)` Hook to allow derived classes to override list of result collection behavior.
    `protected List<GroovyRowResult>` | `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<Object> values)` Hook to allow derived classes to override sql generation from GStrings.
    `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)`
    `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(GString gstring)` Performs a stored procedure call with the given embedded parameters.
    `void` | `call(GString gstring, Closure closure)` Performs a stored procedure call with the given parameters, calling the closure once with all result objects.
    `int` | `call(String sql)` Performs a stored procedure call.
    `int` | `call(String sql, List<Object> params)` Performs a stored procedure call with the given parameters.
    `void` | `call(String sql, List<Object> params, Closure closure)` Performs a stored procedure call with the given parameters.
    `int` | `call(String sql, Object[] params)` Performs a stored procedure call with the given parameters.
    `static InParameter` | `CHAR(Object value)`
    `SqlWithParams` | `checkForNamedParams(String sql, List<Object> params)`
    `static InParameter` | `CLOB(Object value)`
    `void` | `close()` If this SQL object was created with a Connection then this method closes the connection.
    `protected void` | `closeResources(Connection connection)` An extension point allowing the behavior of resource closing to be overridden in derived classes.
    `protected void` | `closeResources(Connection connection, Statement statement)` An extension point allowing the behavior of resource closing to be overridden in derived classes.
    `protected void` | `closeResources(Connection connection, Statement statement, ResultSet results)` An extension point allowing derived classes to change the behavior of resource closing.
    `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 Sql.AbstractQueryCommand` | `createPreparedQueryCommand(String sql, List<Object> queryParams)` Factory for the PreparedQueryCommand command pattern object allows subclass to supply implementations of the command class.
    `protected Sql.AbstractQueryCommand` | `createQueryCommand(String sql)` Factory for the QueryCommand command pattern object allows subclasses to supply implementations of the command class.
    `static InParameter` | `DATALINK(Object value)`
    `DataSet` | `dataSet(Class<?> type)`
    `DataSet` | `dataSet(String table)`
    `static InParameter` | `DATE(Object value)`
    `static InParameter` | `DECIMAL(Object value)`
    `static InParameter` | `DISTINCT(Object value)`
    `static InParameter` | `DOUBLE(Object value)`
    `void` | `eachRow(GString gstring, 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(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<Object> params, Closure closure)` Performs the given SQL query calling the given Closure with each row of the result set.
    `void` | `eachRow(String sql, List<Object> params, Closure metaClosure, Closure closure)` Performs the given SQL query calling the given Closure with each row of the result set.
    `boolean` | `execute(GString gstring)` Executes the given SQL with embedded expressions inside.
    `boolean` | `execute(String sql)` Executes the given piece of SQL.
    `boolean` | `execute(String sql, List<Object> params)` Executes the given piece of SQL with parameters.
    `boolean` | `execute(String sql, Object[] params)` Executes the given piece of SQL with parameters.
    `List<List<Object>>` | `executeInsert(GString gstring)` Executes the given SQL statement (typically an INSERT statement).
    `List<List<Object>>` | `executeInsert(String sql)` Executes the given SQL statement (typically an INSERT statement).
    `List<List<Object>>` | `executeInsert(String sql, List<Object> params)` Executes the given SQL statement (typically an INSERT statement).
    `List<List<Object>>` | `executeInsert(String sql, Object[] params)` Executes the given SQL statement (typically an INSERT statement).
    `protected ResultSet` | `executePreparedQuery(String sql, List<Object> 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(GString gstring)` Executes the given SQL update with embedded expressions inside.
    `int` | `executeUpdate(String sql)` Executes the given SQL update.
    `int` | `executeUpdate(String sql, List<Object> params)` Executes the given SQL update with parameters.
    `int` | `executeUpdate(String sql, Object[] params)` Executes the given SQL update with parameters.
    `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(GString gstring)` Performs the given SQL query and return the first row of the result set.
    `Object` | `firstRow(String sql)` Performs the given SQL query and return the first row of the result set.
    `Object` | `firstRow(String sql, List<Object> params)` Performs the given SQL query and return the first row of the result set.
    `Object` | `firstRow(String sql, Object[] params)` Performs the given SQL query and return the first row of the result set.
    `static InParameter` | `FLOAT(Object value)`
    `Connection` | `getConnection()` If this instance was created with a single Connection then the connection is returned.
    `DataSource` | `getDataSource()`
    `protected List<Object>` | `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.
    `static InParameter` | `INTEGER(Object value)`
    `boolean` | `isCacheNamedQueries()`
    `boolean` | `isCacheStatements()`
    `boolean` | `isEnableNamedQueries()`
    `boolean` | `isWithinBatch()` Returns true if the current Sql object is currently executing a withBatch method call.
    `static InParameter` | `JAVA_OBJECT(Object value)`
    `static void` | `loadDriver(String driverClassName)` Attempts to load the JDBC driver on the thread, current or system class loaders
    `static InParameter` | `LONGVARBINARY(Object value)`
    `static InParameter` | `LONGVARCHAR(Object value)`
    `static Sql` | `newInstance(Map<String,Object> args)` Creates a new Sql instance given parameters in a Map.
    `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 driverClassName)` Creates a new Sql instance given a JDBC connection URL 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 InParameter` | `NULL(Object value)`
    `protected String` | `nullify(String sql)` Hook to allow derived classes to override null handling.
    `static InParameter` | `NUMERIC(Object value)`
    `static InParameter` | `OTHER(Object value)`
    `static OutParameter` | `out(int type)` Create a new OutParameter
    `void` | `query(GString gstring, Closure closure)` Performs the given SQL query, which should return a single `ResultSet` object.
    `void` | `query(String sql, Closure closure)` Performs the given SQL query, which should return a single `ResultSet` object.
    `void` | `query(String sql, List<Object> params, Closure closure)` Performs the given SQL query, which should return a single `ResultSet` object.
    `static InParameter` | `REAL(Object value)`
    `static InParameter` | `REF(Object value)`
    `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<GroovyRowResult>` | `rows(GString gstring)` Performs the given SQL query and return the rows of the result set.
    `List<GroovyRowResult>` | `rows(GString gstring, Closure metaClosure)` Performs the given SQL query and return the rows of the result set.
    `List<GroovyRowResult>` | `rows(String sql)` Performs the given SQL query and return the rows of the result set.
    `List<GroovyRowResult>` | `rows(String sql, Closure metaClosure)` Performs the given SQL query and return the rows of the result set.
    `List<GroovyRowResult>` | `rows(String sql, List<Object> params)` Performs the given SQL query and return the rows of the result set.
    `List<GroovyRowResult>` | `rows(String sql, List<Object> params, Closure metaClosure)` Performs the given SQL query and return the rows of the result set.
    `List<GroovyRowResult>` | `rows(String sql, Object[] params)` Performs the given SQL query and return the rows of the result set.
    `void` | `setCacheNamedQueries(boolean cacheNamedQueries)` Enables named query caching.
    `void` | `setCacheStatements(boolean cacheStatements)` Enables statement caching.
    `void` | `setEnableNamedQueries(boolean enableNamedQueries)` Enables named query support.
    `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<Object> 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.
    `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)`
    `int[]` | `withBatch(Closure closure)` Performs the closure within a batch using a cached connection.
    `int[]` | `withBatch(int batchSize, 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 java.lang.Object

      `clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait`

- - ### Field Detail

    - #### LOG

          protected static final Logger LOG

      Hook to allow derived classes to access the log

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

    - #### 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(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(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. Depending on which features you are using, you may be able to do this on the connection object directly but the preferred approach is to call the `close()` method which will close the connection but also free any caches resources.
      Parameters:
        `connection` - the Connection to use

    - #### Sql

          public Sql(Sql parent)

  - ### Method Detail

    - #### newInstance

          public static Sql newInstance(String url)
                                 throws SQLException

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

    - #### newInstance

          public static Sql newInstance(String url,
                        Properties properties)
                                 throws SQLException

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

    - #### newInstance

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

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

    - #### newInstance

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

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

    - #### newInstance

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

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

    - #### newInstance

          public static Sql newInstance(String url,
                        String driverClassName)
                                 throws SQLException,
                                        ClassNotFoundException

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

    - #### newInstance

          public static Sql newInstance(Map<String,Object> args)
                                 throws SQLException,
                                        ClassNotFoundException

      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.jdbcDriver',
               cacheStatements: true,
               resultSetConcurrency: CONCUR_READ_ONLY
           )

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

    - #### getResultSetType

          public int getResultSetType()

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

    - #### getResultSetConcurrency

          public int getResultSetConcurrency()

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

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

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

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

    - #### loadDriver

          public static void loadDriver(String driverClassName)
                                 throws ClassNotFoundException

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

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

    - #### in

          public static InParameter in(int type,
                       Object value)

      Create a new InParameter
      Parameters:
        `type` - the JDBC data type
        `value` - the object value
      Returns:
        an InParameter

    - #### out

          public static OutParameter out(int type)

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

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

    - #### resultSet

          public static ResultSetOutParameter resultSet(int type)

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

    - #### 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]

      Parameters:
        `object` - the object of interest
      Returns:
        the expanded variable
      See Also:
        `expand(Object)`

    - #### dataSet

          public DataSet dataSet(String table)

    - #### dataSet

          public DataSet dataSet(Class<?> type)

    - #### query

          public void query(String sql,
                   Closure closure)
                     throws SQLException

      Performs the given SQL query, which should return a single `ResultSet` object. The given closure is called with the `ResultSet` as its argument.

      Example usages:

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

           sql.query("call get_people_places()") { ResultSet rs ->
               while (rs.next()) println rs.toRowResult().firstname
           }

      All resources including the ResultSet are closed automatically after the closure is called.
      Parameters:
        `sql` - the sql statement
        `closure` - called for each row with a GroovyResultSet
      Throws:
        `SQLException` - if a database access error occurs

    - #### query

          public void query(String sql,
                   List<Object> params,
                   Closure closure)
                     throws SQLException

      Performs the given SQL query, which should return a single `ResultSet` object. The given closure is called with the `ResultSet` as its argument. The query may contain placeholder question marks which match the given list of parameters.

      Example usage:

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

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

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

    - #### query

          public void query(GString gstring,
                   Closure closure)
                     throws SQLException

      Performs the given SQL query, which should return a single `ResultSet` object. The given closure is called with the `ResultSet` as its argument. The query may contain GString expressions.

      Example usage:

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

      All resources including the ResultSet are closed automatically after the closure is called.
      Parameters:
        `gstring` - a GString containing the SQL query with embedded params
        `closure` - called for each row with a GroovyResultSet
      Throws:
        `SQLException` - if a database access error occurs
      See Also:
        `expand(Object)`

    - #### eachRow

          public void eachRow(String sql,
                     Closure closure)
                       throws SQLException

      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
           }

      Resource handling is performed automatically where appropriate.
      Parameters:
        `sql` - the sql statement
        `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)
                       throws SQLException

      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)

      Resource handling is performed automatically where appropriate.
      Parameters:
        `sql` - the sql statement
        `metaClosure` - called for meta data (only once after sql execution)
        `rowClosure` - called for each row with a GroovyResultSet
      Throws:
        `SQLException` - if a database access error occurs

    - #### eachRow

          public void eachRow(String sql,
                     List<Object> params,
                     Closure metaClosure,
                     Closure closure)
                       throws SQLException

      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)

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

      Resource handling is performed automatically where appropriate.
      Parameters:
        `sql` - the sql statement
        `params` - a list of parameters
        `metaClosure` - called for meta data (only once after sql execution)
        `closure` - called for each row with a GroovyResultSet
      Throws:
        `SQLException` - if a database access error occurs

    - #### eachRow

          public void eachRow(String sql,
                     List<Object> params,
                     Closure closure)
                       throws SQLException

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

      Resource handling is performed automatically where appropriate.
      Parameters:
        `sql` - the sql statement
        `params` - a list of parameters
        `closure` - called for each row with a GroovyResultSet
      Throws:
        `SQLException` - if a database access error occurs

    - #### eachRow

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

      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)

      Resource handling is performed automatically where appropriate.
      Parameters:
        `gstring` - a GString containing the SQL query with embedded params
        `metaClosure` - called for meta data (only once after sql execution)
        `closure` - called for each row with a GroovyResultSet
      Throws:
        `SQLException` - if a database access error occurs
      See Also:
        `expand(Object)`

    - #### eachRow

          public void eachRow(GString gstring,
                     Closure closure)
                       throws SQLException

      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
           }

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

    - #### rows

          public List<GroovyRowResult> rows(String sql)
                                     throws SQLException

      Performs the given SQL query and return the rows of the result set.

      Example usage:

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

      Resource handling is performed automatically where appropriate.
      Parameters:
        `sql` - the SQL statement
      Returns:
        a list of GroovyRowResult objects
      Throws:
        `SQLException` - if a database access error occurs

    - #### rows

          public List<GroovyRowResult> rows(String sql,
                                   Closure metaClosure)
                                     throws SQLException

      Performs the given SQL query and return the rows of the result set. In addition, the `metaClosure` will be called once passing in the `ResultSetMetaData` as argument.

      Example usage:

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

      Resource handling is performed automatically where appropriate.
      Parameters:
        `sql` - the SQL statement
        `metaClosure` - called with meta data of the ResultSet
      Returns:
        a list of GroovyRowResult objects
      Throws:
        `SQLException` - if a database access error occurs

    - #### rows

          public List<GroovyRowResult> rows(String sql,
                                   List<Object> params)
                                     throws SQLException

      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"

      Resource handling is performed automatically where appropriate.
      Parameters:
        `sql` - the SQL statement
        `params` - a list of parameters
      Returns:
        a list of GroovyRowResult objects
      Throws:
        `SQLException` - if a database access error occurs

    - #### rows

          public List<GroovyRowResult> rows(String sql,
                                   Object[] params)
                                     throws SQLException

      Performs the given SQL query and return the rows of the result set. An Object array variant of `rows(String, List)`.
      Parameters:
        `sql` - the SQL statement
        `params` - an array of parameters
      Returns:
        a list of GroovyRowResult objects
      Throws:
        `SQLException` - if a database access error occurs

    - #### rows

          public List<GroovyRowResult> rows(String sql,
                                   List<Object> params,
                                   Closure metaClosure)
                                     throws SQLException

      Performs the given SQL query and return the rows of the result set. In addition, the `metaClosure` will be called once passing in the `ResultSetMetaData` as argument. The query may contain placeholder question marks which match the given list of parameters.

      Example usage:

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

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

           def printNumCols = { meta -> println "Found $meta.columnCount columns" }

           def mapParam = [foo: 'Smith']
           def domainParam = new MyDomainClass(bar: 'John')
           def qry = 'select * from PERSON where lastname=?1.foo and firstname=?2.bar'
           def ans = sql.rows(qry, [mapParam, domainParam], printNumCols)
           println "Found ${ans.size()} rows"

           def qry2 = 'select * from PERSON where firstname=:first and lastname=:last'
           def ans2 = sql.rows(qry2, [[last:'Smith', first:'John']], printNumCols)
           println "Found ${ans2.size()} rows"

      See the class Javadoc for more details.

      Resource handling is performed automatically where appropriate.
      Parameters:
        `sql` - the SQL statement
        `params` - a list of parameters
        `metaClosure` - called for meta data (only once after sql execution)
      Returns:
        a list of GroovyRowResult objects
      Throws:
        `SQLException` - if a database access error occurs

    - #### rows

          public List<GroovyRowResult> rows(GString gstring)
                                     throws SQLException

      Performs the given SQL query and return the rows of the result set. The query may contain GString expressions.

      Example usage:

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

      Resource handling is performed automatically where appropriate.
      Parameters:
        `gstring` - a GString containing the SQL query with embedded params
      Returns:
        a list of GroovyRowResult objects
      Throws:
        `SQLException` - if a database access error occurs
      See Also:
        `expand(Object)`

    - #### rows

          public List<GroovyRowResult> rows(GString gstring,
                                   Closure metaClosure)
                                     throws SQLException

      Performs the given SQL query and return the rows of the result set. In addition, the `metaClosure` will be called once passing in the `ResultSetMetaData` as argument. The query may contain GString expressions.

      Example usage:

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

      Resource handling is performed automatically where appropriate.
      Parameters:
        `gstring` - a GString containing the SQL query with embedded params
        `metaClosure` - called with meta data of the ResultSet
      Returns:
        a list of GroovyRowResult objects
      Throws:
        `SQLException` - if a database access error occurs
      See Also:
        `expand(Object)`

    - #### firstRow

          public Object firstRow(String sql)
                          throws SQLException

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

      Example usage:

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

      Resource handling is performed automatically where appropriate.
      Parameters:
        `sql` - the SQL statement
      Returns:
        a GroovyRowResult object or `null` if no row is found
      Throws:
        `SQLException` - if a database access error occurs

    - #### firstRow

          public Object firstRow(GString gstring)
                          throws SQLException

      Performs the given SQL query and return the first row of the result set. The query may contain GString expressions.

      Example usage:

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

      Resource handling is performed automatically where appropriate.
      Parameters:
        `gstring` - a GString containing the SQL query with embedded params
      Returns:
        a GroovyRowResult object or `null` if no row is found
      Throws:
        `SQLException` - if a database access error occurs
      See Also:
        `expand(Object)`

    - #### firstRow

          public Object firstRow(String sql,
                        List<Object> params)
                          throws SQLException

      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'

      Resource handling is performed automatically where appropriate.
      Parameters:
        `sql` - the SQL statement
        `params` - a list of parameters
      Returns:
        a GroovyRowResult object or `null` if no row is found
      Throws:
        `SQLException` - if a database access error occurs

    - #### firstRow

          public Object firstRow(String sql,
                        Object[] params)
                          throws SQLException

      Performs the given SQL query and return the first row of the result set. An Object array variant of `firstRow(String, List)`.
      Parameters:
        `sql` - the SQL statement
        `params` - an array of parameters
      Returns:
        a GroovyRowResult object or `null` if no row is found
      Throws:
        `SQLException` - if a database access error occurs

    - #### execute

          public boolean execute(String sql)
                          throws SQLException

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

      Example usages:

           sql.execute "drop table if exists PERSON"

           sql.execute """
               create table PERSON (
                   id integer not null,
                   firstname varchar(100),
                   lastname varchar(100),
                   location_id integer
               )
           """

           sql.execute """
               insert into PERSON (id, firstname, lastname, location_id) values (4, 'Paul', 'King', 40)
           """
           assert sql.updateCount == 1

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

    - #### execute

          public boolean execute(String sql,
                        List<Object> params)
                          throws SQLException

      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.
      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
      Throws:
        `SQLException` - if a database access error occurs

    - #### execute

          public boolean execute(String sql,
                        Object[] params)
                          throws SQLException

      Executes the given piece of SQL with parameters. An Object array variant of `execute(String, List)`.
      Parameters:
        `sql` - the SQL statement
        `params` - an array of parameters
      Returns:
        `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)
                          throws SQLException

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

      Example usage:

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

      Resource handling is performed automatically where appropriate.
      Parameters:
        `gstring` - a GString containing the SQL query with embedded params
      Returns:
        `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 Also:
        `expand(Object)`

    - #### executeInsert

          public List<List<Object>> executeInsert(String sql)
                                           throws SQLException

      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. Resource handling is performed automatically where appropriate.
      Parameters:
        `sql` - The SQL statement to execute
      Returns:
        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<List<Object>> executeInsert(String sql,
                                         List<Object> params)
                                           throws SQLException

      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.
      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)
      Throws:
        `SQLException` - if a database access error occurs

    - #### executeInsert

          public List<List<Object>> executeInsert(String sql,
                                         Object[] params)
                                           throws SQLException

      Executes the given SQL statement (typically an INSERT statement). An Object array variant of `executeInsert(String, List)`.
      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)
      Throws:
        `SQLException` - if a database access error occurs

    - #### executeInsert

          public List<List<Object>> executeInsert(GString gstring)
                                           throws SQLException

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

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

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

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

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

               def id = keys[0][0]

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

      Resource handling is performed automatically where appropriate.
      Parameters:
        `gstring` - a GString containing the SQL query with embedded params
      Returns:
        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 Also:
        `expand(Object)`

    - #### executeUpdate

          public int executeUpdate(String sql)
                            throws SQLException

      Executes the given SQL update. Resource handling is performed automatically where appropriate.
      Parameters:
        `sql` - the SQL to execute
      Returns:
        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<Object> params)
                            throws SQLException

      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.
      Parameters:
        `sql` - the SQL statement
        `params` - a list of parameters
      Returns:
        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,
                          Object[] params)
                            throws SQLException

      Executes the given SQL update with parameters. An Object array variant of `executeUpdate(String, List)`.
      Parameters:
        `sql` - the SQL statement
        `params` - an array of parameters
      Returns:
        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)
                            throws SQLException

      Executes the given SQL update with embedded expressions inside. Resource handling is performed automatically where appropriate.
      Parameters:
        `gstring` - a GString containing the SQL query with embedded params
      Returns:
        the number of rows updated or 0 for SQL statements that return nothing
      Throws:
        `SQLException` - if a database access error occurs
      See Also:
        `expand(Object)`

    - #### call

          public int call(String sql)
                   throws Exception

      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

      Parameters:
        `sql` - the SQL statement
      Returns:
        the number of rows updated or 0 for SQL statements that return nothing
      Throws:
        `SQLException` - if a database access error occurs
        `Exception`

    - #### call

          public int call(GString gstring)
                   throws Exception

      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

      Resource handling is performed automatically where appropriate.
      Parameters:
        `gstring` - a GString containing the SQL query with embedded params
      Returns:
        the number of rows updated or 0 for SQL statements that return nothing
      Throws:
        `SQLException` - if a database access error occurs
        `Exception`
      See Also:
        `expand(Object)`, `call(String)`

    - #### call

          public int call(String sql,
                 List<Object> params)
                   throws Exception

      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

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

    - #### call

          public int call(String sql,
                 Object[] params)
                   throws Exception

      Performs a stored procedure call with the given parameters.

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

    - #### call

          public void call(String sql,
                  List<Object> params,
                  Closure closure)
                    throws Exception

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

      Resource handling is performed automatically where appropriate.
      Parameters:
        `sql` - the sql statement
        `params` - a list of parameters
        `closure` - called for each row with a GroovyResultSet
      Throws:
        `SQLException` - if a database access error occurs
        `Exception`

    - #### call

          public void call(GString gstring,
                  Closure closure)
                    throws Exception

      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.
      Parameters:
        `gstring` - a GString containing the SQL query with embedded params
        `closure` - called for each row with a GroovyResultSet
      Throws:
        `SQLException` - if a database access error occurs
        `Exception`
      See Also:
        `call(String, List, Closure)`, `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 only frees any cached objects (statements in particular).

    - #### getDataSource

          public DataSource getDataSource()

    - #### commit

          public void commit()
                      throws SQLException

      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

    - #### rollback

          public void rollback()
                        throws SQLException

      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

    - #### getUpdateCount

          public int getUpdateCount()

      Returns:
        Returns the updateCount.

    - #### 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
      Returns:
        the connection wired into this object, or null if this object uses a DataSource

    - #### 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")

      Parameters:
        `configureStatement` - the closure

    - #### setCacheStatements

          public void setCacheStatements(boolean cacheStatements)

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

    - #### isCacheStatements

          public boolean isCacheStatements()

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

    - #### cacheConnection

          public void cacheConnection(Closure closure)
                               throws SQLException

      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.
      Parameters:
        `closure` - the given closure
      Throws:
        `SQLException` - if a database error occurs

    - #### withTransaction

          public void withTransaction(Closure closure)
                               throws SQLException

      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.
      Parameters:
        `closure` - the given closure
      Throws:
        `SQLException` - if a database error occurs

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

    - #### withBatch

          public int[] withBatch(Closure closure)
                          throws SQLException

      Performs the closure within a batch using a cached connection. Uses a batch size of zero, i.e. no automatic partitioning of batches. Use it like this:

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

      Parameters:
        `closure` - the closure containing batch and optionally other statements
      Returns:
        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.
      See Also:
        `withBatch(int, Closure)`

    - #### withBatch

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

      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 for batchSize of 20:

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

      Parameters:
        `batchSize` - partition the batch into batchSize pieces, i.e. after batchSize `addBatch()` invocations, call `executeBatch()` automatically; 0 means manual calls to executeBatch are required
        `closure` - the closure containing batch and optionally other statements
      Returns:
        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.
      See Also:
        `withBatch(Closure)`

    - #### cacheStatements

          public void cacheStatements(Closure closure)
                               throws SQLException

      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.
      Parameters:
        `closure` - the given closure
      Throws:
        `SQLException` - if a database error occurs
      See Also:
        `setCacheStatements(boolean)`

    - #### executeQuery

          protected final ResultSet executeQuery(String sql)
                                          throws SQLException

      Hook to allow derived classes to access ResultSet returned from query.
      Parameters:
        `sql` - query to execute
      Returns:
        the resulting ResultSet
      Throws:
        `SQLException` - if a database error occurs

    - #### executePreparedQuery

          protected final ResultSet executePreparedQuery(String sql,
                                       List<Object> params)
                                                  throws SQLException

      Hook to allow derived classes to access ResultSet returned from query.
      Parameters:
        `sql` - query to execute
        `params` - parameters matching question mark placeholders in the query
      Returns:
        the resulting ResultSet
      Throws:
        `SQLException` - if a database error occurs

    - #### asList

          protected List<GroovyRowResult> asList(String sql,
                                     ResultSet rs)
                                          throws SQLException

      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.
      Parameters:
        `sql` - query to execute
        `rs` - the ResultSet to process
      Returns:
        the resulting list of rows
      Throws:
        `SQLException` - if a database error occurs

    - #### asList

          protected List<GroovyRowResult> asList(String sql,
                                     ResultSet rs,
                                     Closure metaClosure)
                                          throws SQLException

      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.
      Parameters:
        `sql` - query to execute
        `rs` - the ResultSet to process
        `metaClosure` - called for meta data (only once after sql execution)
      Returns:
        the resulting list of rows
      Throws:
        `SQLException` - if a database error occurs

    - #### asSql

          protected String asSql(GString gstring,
                     List<Object> 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)`

    - #### nullify

          protected String nullify(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

    - #### 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.
      Parameters:
        `sql` - the SQL statement
      Returns:
        the index of the found keyword or -1 if not found

    - #### getParameters

          protected List<Object> 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)`

    - #### setParameters

          protected void setParameters(List<Object> params,
                           PreparedStatement statement)
                                throws SQLException

      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`.
      Parameters:
        `params` - the parameters to append
        `statement` - the statement
      Throws:
        `SQLException` - if a database access error occurs

    - #### setObject

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

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

    - #### createConnection

          protected Connection createConnection()
                                         throws SQLException

      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.
      Returns:
        the connection associated with this Sql
      Throws:
        `SQLException` - if a SQL 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.
      Parameters:
        `connection` - the connection to close
        `statement` - the statement to close
        `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.
      Parameters:
        `connection` - the connection to close
        `statement` - the statement to close

    - #### closeResources

          protected void closeResources(Connection connection)

      An extension point allowing the behavior of resource closing to be overridden in derived classes.
      Parameters:
        `connection` - the connection to close

    - #### 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.
      Parameters:
        `statement` - the statement to configure

    - #### checkForNamedParams

          public SqlWithParams checkForNamedParams(String sql,
                                          List<Object> params)

    - #### isCacheNamedQueries

          public boolean isCacheNamedQueries()

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

    - #### setCacheNamedQueries

          public void setCacheNamedQueries(boolean cacheNamedQueries)

      Enables named query caching. if cacheNamedQueries is true, cache is created and processed named queries will be cached. if cacheNamedQueries is false, no caching will occur saving memory at the cost of additional processing time.
      Parameters:
        `cacheNamedQueries` - the new value

    - #### isEnableNamedQueries

          public boolean isEnableNamedQueries()

      Returns:
        boolean true if named query processing is enabled (the default is true)

    - #### setEnableNamedQueries

          public void setEnableNamedQueries(boolean enableNamedQueries)

      Enables named query support. if enableNamedQueries is true, queries with ':propname' and '?1.propname' style placeholders will be processed. if enableNamedQueries is false, this feature will be turned off.
      Parameters:
        `enableNamedQueries` - the new value

    - #### createQueryCommand

          protected Sql.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();
           }

      Parameters:
        `sql` - statement to be executed
      Returns:
        a command - invoke its execute() and closeResource() methods

    - #### createPreparedQueryCommand

          protected Sql.AbstractQueryCommand createPreparedQueryCommand(String sql,
                                                            List<Object> queryParams)

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

    - #### 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.
      Parameters:
        `conn` - the connection that is about to be used by a command