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: 13342 $

Author:

Chris Stevenson, James Strachan, Paul King, Marc DeXeT

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)
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 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() If this SQL object was created with a Connection then this method commits the connection.
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)
boolean	isCacheStatements()
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.
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	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.
void	setCacheStatements(boolean cacheStatements) Enables statement caching.
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

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

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

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

setCacheStatements

public void setCacheStatements(boolean cacheStatements)

Enables statement caching.
if b is true, cache is created and all created prepared statements will be cached.
if b is false, all cached statements will be properly closed.

Parameters:

cacheStatements - the new value

isCacheStatements

public boolean isCacheStatements()

Returns:

boolean true if cache is enabled

cacheConnection

public void cacheConnection(Closure closure)
                     throws SQLException

Caches the connection used while the closure is active.

Parameters:

closure - the given closure

Throws:

SQLException - if a database error occurs

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.

Parameters:

closure - the given closure

Throws:

SQLException - if a database error occurs

See Also:

setCacheStatements(boolean)