Overview | Package | Class | Tree | Deprecated | Index | Help
PREV CLASS NEXT CLASS | FRAMES NO FRAMES
SUMMARY: NESTED | FIELD | CONSTR | METHOD | DETAIL: FIELD | CONSTR | METHOD

## groovy.sql
Class Sql

    java.lang.Object
      groovy.sql.Sql

Direct Known Subclasses:
  DataSet

    public class Sql

extends Object

Represents an extent of objects

Version:
  $Revision: 10922 $
Author:
  Chris Stevenson, James Strachan

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

Constructor Summary
`Sql(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
`static InParameter` | `ARRAY(Object value)`
`protected String` | `asSql(GString gstring, List values)`
`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)`
`int` | `call(GString gstring)` 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.
`int` | `call(String sql)` Performs a stored procedure call.
`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.
`static InParameter` | `CHAR(Object value)`
`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, Statement statement)`
`protected void` | `closeResources(Connection connection, Statement statement, ResultSet results)`
`void` | `commit()`
`protected void` | `configure(Statement statement)` Provides a hook to be able to configure JDBC statements, such as to configure
`protected Connection` | `createConnection()`
`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 closure with the result set.
`void` | `eachRow(String sql, Closure closure)` Performs the given SQL query calling the closure with each row of the result set.
`void` | `eachRow(String sql, Closure metaClosure, Closure rowClosure)` Performs the given SQL query calling closures for metadata and each row
`void` | `eachRow(String sql, List params, Closure closure)` Performs the given SQL query calling the closure with 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 params)` Executes the given piece of SQL with parameters.
`List` | `executeInsert(GString gstring)` Executes the given SQL with embedded expressions inside, and returns the values of any auto-generated colums, such as an autoincrement ID field.
`List` | `executeInsert(String sql)` Executes the given SQL statement.
`List` | `executeInsert(String sql, List params)` Executes the given SQL statement with a particular list of parameter values.
`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 params)` Executes the given SQL update with parameters.
`static ExpandedVariable` | `expand(Object object)` Creates a variable to be expanded in the Sql string rather than representing an sql parameter.
`protected int` | `findWhereKeyword(String sql)` Find the first 'where' keyword in the sql.
`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 params)` Performs the given SQL query with the list of params 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` | `getParameters(GString 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)`
`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(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)` replace ?'"?
`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 calling the closure with the result set.
`void` | `query(String sql, Closure closure)` Performs the given SQL query calling the closure with the result set.
`void` | `query(String sql, List params, Closure closure)` Performs the given SQL query with parameters calling the closure with the result set.
`void` | `queryEach(GString gstring, Closure closure)` Deprecated. please use eachRow instead
`void` | `queryEach(String sql, Closure closure)` Deprecated. please use eachRow instead
`void` | `queryEach(String sql, List params, Closure closure)` Deprecated. please use eachRow instead
`static InParameter` | `REAL(Object value)`
`static InParameter` | `REF(Object value)`
`static ResultSetOutParameter` | `resultSet(int type)` Create a new ResultSetOutParameter
`void` | `rollback()`
`List` | `rows(GString gstring)` Performs the given SQL query and return the rows of the result set.
`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 with the list of params and return the rows of the result set.
`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)` Appends the parameters to the given 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)`
`void` | `withStatement(Closure configureStatement)` Allows a closure to be passed in to configure the JDBC statements before they are executed to do things like set the query size etc.

Methods inherited from class java.lang.Object
`clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait`

Field Detail

### log

    protected Logger 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. You can do this on the connection object directly or by calling the `Connection.close()` method.

  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

### getResultSetType

    public int getResultSetType()

  Gets the resultSetType for statements created using the connection.

  Returns:
    the current resultSetType value

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

### getResultSetConcurrency

    public int getResultSetConcurrency()

  Gets the resultSetConcurrency for statements created using the connection.

  Returns:
    the current resultSetConcurrency value

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

### getResultSetHoldability

    public int getResultSetHoldability()

  Gets the resultSetHoldability for statements created using the connection.

  Returns:
    the current resultSetHoldability value or -1 if not set

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

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

  Creates a variable to be expanded in the Sql string rather than representing an sql parameter.

  Parameters:
    `object` - the object of interest
  Returns:
    the expanded variable

### 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 calling the closure with the result set.

  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 params,
                      Closure closure)
               throws SQLException

  Performs the given SQL query with parameters calling the closure with the result set.

  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 calling the closure with the result set.

  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

### eachRow

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

  Performs the given SQL query calling the closure with each row of the result set.

  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 closures for metadata and each row

  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 params,
                        Closure closure)
                 throws SQLException

  Performs the given SQL query calling the closure with the result set.

  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 closure)
                 throws SQLException

  Performs the given SQL query calling the closure with the result set.

  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

### queryEach

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

  Deprecated. please use eachRow instead

  Throws:
    `SQLException`

### queryEach

    public void queryEach(String sql,
                          List params,
                          Closure closure)
                   throws SQLException

  Deprecated. please use eachRow instead

  Throws:
    `SQLException`

### queryEach

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

  Deprecated. please use eachRow instead

  Throws:
    `SQLException`

### rows

    public List rows(String sql)
              throws SQLException

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

  Parameters:
    `sql` - the SQL statement
  Returns:
    a list of GroovyRowResult objects
  Throws:
    `SQLException` - if a database access error occurs

### rows

    public List rows(GString gstring)
              throws SQLException

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

  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

### rows

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

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

  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 rows(String sql,
                     List params)
              throws SQLException

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

  Parameters:
    `sql` - the SQL statement
    `params` - a list of parameters
  Returns:
    a list of GroovyRowResult objects
  Throws:
    `SQLException` - if a database access error occurs

### firstRow

    public Object firstRow(String sql)
                    throws SQLException

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

  Parameters:
    `sql` - the SQL statement
  Returns:
    a GroovyRowResult object
  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.

  Parameters:
    `gstring` - a GString containing the SQL query with embedded params
  Returns:
    a GroovyRowResult object
  Throws:
    `SQLException` - if a database access error occurs

### firstRow

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

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

  Parameters:
    `sql` - the SQL statement
    `params` - a list of parameters
  Returns:
    a GroovyRowResult object
  Throws:
    `SQLException` - if a database access error occurs

### execute

    public boolean execute(String sql)
                    throws SQLException

  Executes the given piece of SQL.

  Parameters:
    `sql` - the SQL statement
  Returns:
    `true` if the first result is a `ResultSet` object; `false` if it is an update count or there are no results
  Throws:
    `SQLException` - if a database access error occurs

### execute

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

  Executes the given piece of SQL with parameters.

  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(GString gstring)
                    throws SQLException

  Executes the given SQL with embedded expressions inside.

  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

### executeInsert

    public List executeInsert(String sql)
                       throws SQLException

  Executes the given SQL statement. See `executeInsert(GString)` for more details.

  Parameters:
    `sql` - The SQL statement to execute
  Returns:
    A list of the auto-generated column values for each inserted row
  Throws:
    `SQLException` - if a database access error occurs

### executeInsert

    public List executeInsert(String sql,
                              List params)
                       throws SQLException

  Executes the given SQL statement with a particular list of parameter values. See `executeInsert(GString)` for more details.

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

### executeInsert

    public List executeInsert(GString gstring)
                       throws SQLException

  Executes the given SQL with embedded expressions inside, and returns the values of any auto-generated colums, such as an autoincrement ID field. These 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.insert("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.
           ...

  Parameters:
    `gstring` - a GString containing the SQL query with embedded params
  Returns:
    A list of column values representing each row's auto-generated keys
  Throws:
    `SQLException` - if a database access error occurs

### executeUpdate

    public int executeUpdate(String sql)
                      throws SQLException

  Executes the given SQL update.

  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

### executeUpdate

    public int executeUpdate(String sql,
                             List params)
                      throws SQLException

  Executes the given SQL update with parameters.

  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(GString gstring)
                      throws SQLException

  Executes the given SQL update with embedded expressions inside.

  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

### call

    public int call(String sql)
             throws Exception

  Performs a stored procedure call.

  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(String sql,
                    List params)
             throws Exception

  Performs a stored procedure call with the given parameters.

  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`

### call

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

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

  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 int call(GString gstring)
             throws Exception

  Performs a stored procedure call with the given parameters.

  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`

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

  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`

### close

    public void close()
               throws SQLException

  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

### getDataSource

    public DataSource getDataSource()

### commit

    public void commit()

### rollback

    public void rollback()

### 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 to do things like set the query size etc.

  Parameters:
    `configureStatement` - the closure

### asSql

    protected String asSql(GString gstring,
                           List values)

  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

### nullify

    protected String nullify(String sql)

  replace ?'"? references with NULLish

  Parameters:
    `sql` - the SQL statement
  Returns:
    the modified SQL String

### findWhereKeyword

    protected int findWhereKeyword(String sql)

  Find the first 'where' keyword in the sql.

  Parameters:
    `sql` - the SQL statement
  Returns:
    the index of the found keyword or -1 if not found

### getParameters

    protected List getParameters(GString gstring)

  Parameters:
    `gstring` - a GString containing the SQL query with embedded params
  Returns:
    extracts the parameters from the expression as a List

### setParameters

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

  Appends the parameters to the given 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.

  Throws:
    `SQLException` - if a database access error occurs

### createConnection

    protected Connection createConnection()
                                   throws SQLException

  Throws:
    `SQLException`

### closeResources

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

### closeResources

    protected void closeResources(Connection connection,
                                  Statement statement)

### configure

    protected void configure(Statement statement)

  Provides a hook to be able to configure JDBC statements, such as to configure

  Parameters:
    `statement` - the statement to configure

Overview | Package | Class | Tree | Deprecated | Index | Help
| Copyright © 2003-2008 The Codehaus. All rights reserved.
PREV CLASS NEXT CLASS | FRAMES NO FRAMES
SUMMARY: NESTED | FIELD | CONSTR | METHOD | DETAIL: FIELD | CONSTR | METHOD