Groovy 1.6.4

groovy.sql
Class Sql

java.lang.Object
  groovy.sql.Sql

class Sql
extends Object

A facade over Java's normal JDBC apis providing greatly simplified resource management and result set handling. Under the covers the facade hides away details associated with getting connections, constructing and configuring statements, interacting with the connection, closing resources and logging errors. Special features of the facade include using closures to iterate through result sets, a special GString syntax for representing prepared statements and treating result sets like collections of maps with the normal Groovy collection methods available. The class provides numerous extension points for overriding the facade behavior associated with the various aspects of managing the connection with the underlying database.

author:
Chris Stevenson
author:
James Strachan
author:
Paul King
author:
Marc DeXeT
version:
$Revision: 17066 $


Field Summary
static OutParameter ARRAY

static OutParameter BIGINT

static OutParameter BINARY

static OutParameter BIT

static OutParameter BLOB

static OutParameter BOOLEAN

static OutParameter CHAR

static OutParameter CLOB

static OutParameter DATALINK

static OutParameter DATE

static OutParameter DECIMAL

static OutParameter DISTINCT

static OutParameter DOUBLE

static OutParameter FLOAT

static OutParameter INTEGER

static OutParameter JAVA_OBJECT

static OutParameter LONGVARBINARY

static OutParameter LONGVARCHAR

static OutParameter NULL

static OutParameter NUMERIC

static OutParameter OTHER

static OutParameter REAL

static OutParameter REF

static OutParameter SMALLINT

static OutParameter STRUCT

static OutParameter TIME

static OutParameter TIMESTAMP

static OutParameter TINYINT

static OutParameter VARBINARY

static OutParameter VARCHAR

protected Logger log

Hook to allow derived classes to access the log

 
Constructor Summary
Sql(DataSource dataSource)

Constructs an SQL instance using the given DataSource.

Sql(Connection connection)

Constructs an SQL instance using the given Connection.

Sql(Sql parent)

 
Method Summary
static InParameter ARRAY(Object value)

static InParameter BIGINT(Object value)

static InParameter BINARY(Object value)

static InParameter BIT(Object value)

static InParameter BLOB(Object value)

static InParameter BOOLEAN(Object value)

static InParameter CHAR(Object value)

static InParameter CLOB(Object value)

static InParameter DATALINK(Object value)

static InParameter DATE(Object value)

static InParameter DECIMAL(Object value)

static InParameter DISTINCT(Object value)

static InParameter DOUBLE(Object value)

static InParameter FLOAT(Object value)

static InParameter INTEGER(Object value)

static InParameter JAVA_OBJECT(Object value)

static InParameter LONGVARBINARY(Object value)

static InParameter LONGVARCHAR(Object value)

static InParameter NULL(Object value)

static InParameter NUMERIC(Object value)

static InParameter OTHER(Object value)

static InParameter REAL(Object value)

static InParameter REF(Object value)

static InParameter SMALLINT(Object value)

static InParameter STRUCT(Object value)

static InParameter TIME(Object value)

static InParameter TIMESTAMP(Object value)

static InParameter TINYINT(Object value)

static InParameter VARBINARY(Object value)

static InParameter VARCHAR(Object value)

protected String asSql(GString gstring, List values)

Hook to allow derived classes to override sql generation from Gstrings.

void cacheConnection(Closure closure)

Caches the connection used while the closure is active.

void cacheStatements(Closure closure)

Caches every created preparedStatement in closure closure
Every cached preparedStatement is closed after closure has been called.

int call(String sql)

Performs a stored procedure call.

int call(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.

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.

void close()

If this SQL object was created with a Connection then this method closes the connection.

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

An extension point allowing derived classes to change the behavior of resource closing.

protected void closeResources(Connection connection, Statement statement)

An extension point allowing the behavior of resource closing to be overriden in derived classes.

void commit()

If this SQL object was created with a Connection then this method commits the connection.

protected void configure(Statement statement)

Provides a hook for derived classes to be able to configure JDBC statements.

protected Connection createConnection()

An extension point allowing derived classes to change the behavior of connection creation.

DataSet dataSet(String table)

DataSet dataSet(Class type)

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.

void eachRow(GString gstring, Closure closure)

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

boolean execute(String sql)

Executes the given piece of SQL.

boolean execute(String sql, List params)

Executes the given piece of SQL with parameters.

boolean execute(GString gstring)

Executes the given SQL with embedded expressions inside.

List executeInsert(String sql)

Executes the given SQL statement.

List executeInsert(String sql, List params)

Executes the given SQL statement with a particular list of parameter values.

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.

int executeUpdate(String sql)

Executes the given SQL update.

int executeUpdate(String sql, List params)

Executes the given SQL update with parameters.

int executeUpdate(GString gstring)

Executes the given SQL update with embedded expressions inside.

static ExpandedVariable expand(Object object)

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

protected int findWhereKeyword(String sql)

Hook to allow derived classes to override where clause sniffing.

Object firstRow(String sql)

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

Object firstRow(GString gstring)

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

Object firstRow(String sql, List params)

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

Connection getConnection()

If this instance was created with a single Connection then the connection is returned.

DataSource getDataSource()

protected List getParameters(GString gstring)

Hook to allow derived classes to override behavior associated with extracting params from a GString.

int getResultSetConcurrency()

Gets the resultSetConcurrency for statements created using the connection.

int getResultSetHoldability()

Gets the resultSetHoldability for statements created using the connection.

int getResultSetType()

Gets the resultSetType for statements created using the connection.

int getUpdateCount()

static InParameter in(int type, Object value)

Create a new InParameter

static InOutParameter inout(InParameter in)

Create an inout parameter using this in parameter.

boolean isCacheStatements()

static void loadDriver(String driverClassName)

Attempts to load the JDBC driver on the thread, current or system class loaders

static Sql newInstance(String url)

Creates a new Sql instance given a JDBC connection URL.

static Sql newInstance(String url, Properties properties)

Creates a new Sql instance given a JDBC connection URL and some properties.

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

Creates a new Sql instance given a JDBC connection URL, some properties and a driver class name.

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

Creates a new Sql instance given a JDBC connection URL, a username and a password.

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

Creates a new Sql instance given a JDBC connection URL, a username, a password and a driver class name.

static Sql newInstance(String url, String driverClassName)

Creates a new Sql instance given a JDBC connection URL and a driver class name.

protected String nullify(String sql)

Hook to allow derived classes to override null handling.

static OutParameter out(int type)

Create a new OutParameter

void query(String sql, Closure closure)

Performs the given SQL query 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 query(GString gstring, Closure closure)

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

static ResultSetOutParameter resultSet(int type)

Create a new ResultSetOutParameter

void rollback()

If this SQL object was created with a Connection then this method rolls back the connection.

List rows(String sql)

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

List rows(GString gstring)

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)

Hook to allow derived classes to override behavior associated with setting params for a prepared statement.

void setResultSetConcurrency(int resultSetConcurrency)

Sets the resultSetConcurrency for statements created using the connection.

void setResultSetHoldability(int resultSetHoldability)

Sets the resultSetHoldability for statements created using the connection.

void setResultSetType(int resultSetType)

Sets the resultSetType for statements created using the connection.

int[] withBatch(Closure closure)

Performs the closure within a batch using a cached connection.

void withStatement(Closure configureStatement)

Allows a closure to be passed in to configure the JDBC statements before they are executed.

void withTransaction(Closure closure)

Performs the closure within a transaction using a cached connection.

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

Field Detail

ARRAY

static final OutParameter ARRAY


BIGINT

static final OutParameter BIGINT


BINARY

static final OutParameter BINARY


BIT

static final OutParameter BIT


BLOB

static final OutParameter BLOB


BOOLEAN

static final OutParameter BOOLEAN


CHAR

static final OutParameter CHAR


CLOB

static final OutParameter CLOB


DATALINK

static final OutParameter DATALINK


DATE

static final OutParameter DATE


DECIMAL

static final OutParameter DECIMAL


DISTINCT

static final OutParameter DISTINCT


DOUBLE

static final OutParameter DOUBLE


FLOAT

static final OutParameter FLOAT


INTEGER

static final OutParameter INTEGER


JAVA_OBJECT

static final OutParameter JAVA_OBJECT


LONGVARBINARY

static final OutParameter LONGVARBINARY


LONGVARCHAR

static final OutParameter LONGVARCHAR


NULL

static final OutParameter NULL


NUMERIC

static final OutParameter NUMERIC


OTHER

static final OutParameter OTHER


REAL

static final OutParameter REAL


REF

static final OutParameter REF


SMALLINT

static final OutParameter SMALLINT


STRUCT

static final OutParameter STRUCT


TIME

static final OutParameter TIME


TIMESTAMP

static final OutParameter TIMESTAMP


TINYINT

static final OutParameter TINYINT


VARBINARY

static final OutParameter VARBINARY


VARCHAR

static final OutParameter VARCHAR


log

protected Logger log
Hook to allow derived classes to access the log


 
Constructor Detail

Sql

public Sql(DataSource dataSource)
Constructs an SQL instance using the given DataSource. Each operation will use a Connection from the DataSource pool and close it when the operation is completed putting it back into the pool.
param:
dataSource the DataSource to use


Sql

public Sql(Connection connection)
Constructs an SQL instance using the given Connection. It is the caller's responsibility to close the Connection after the Sql instance has been used. You can do this on the connection object directly or by calling the Connection#close() method.
param:
connection the Connection to use


Sql

public Sql(Sql parent)


 
Method Detail

ARRAY

public static InParameter ARRAY(Object value)


BIGINT

public static InParameter BIGINT(Object value)


BINARY

public static InParameter BINARY(Object value)


BIT

public static InParameter BIT(Object value)


BLOB

public static InParameter BLOB(Object value)


BOOLEAN

public static InParameter BOOLEAN(Object value)


CHAR

public static InParameter CHAR(Object value)


CLOB

public static InParameter CLOB(Object value)


DATALINK

public static InParameter DATALINK(Object value)


DATE

public static InParameter DATE(Object value)


DECIMAL

public static InParameter DECIMAL(Object value)


DISTINCT

public static InParameter DISTINCT(Object value)


DOUBLE

public static InParameter DOUBLE(Object value)


FLOAT

public static InParameter FLOAT(Object value)


INTEGER

public static InParameter INTEGER(Object value)


JAVA_OBJECT

public static InParameter JAVA_OBJECT(Object value)


LONGVARBINARY

public static InParameter LONGVARBINARY(Object value)


LONGVARCHAR

public static InParameter LONGVARCHAR(Object value)


NULL

public static InParameter NULL(Object value)


NUMERIC

public static InParameter NUMERIC(Object value)


OTHER

public static InParameter OTHER(Object value)


REAL

public static InParameter REAL(Object value)


REF

public static InParameter REF(Object value)


SMALLINT

public static InParameter SMALLINT(Object value)


STRUCT

public static InParameter STRUCT(Object value)


TIME

public static InParameter TIME(Object value)


TIMESTAMP

public static InParameter TIMESTAMP(Object value)


TINYINT

public static InParameter TINYINT(Object value)


VARBINARY

public static InParameter VARBINARY(Object value)


VARCHAR

public static InParameter VARCHAR(Object value)


asSql

protected String asSql(GString gstring, List values)
Hook to allow derived classes to override sql generation from Gstrings.
param:
gstring a GString containing the SQL query with embedded params
param:
values the values to embed
return:
the SQL version of the given query using ? instead of any parameter


cacheConnection

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


cacheStatements

public void cacheStatements(Closure closure)
Caches every created preparedStatement in closure closure
Every cached preparedStatement is closed after closure has been called. If the closure takes a single argument, it will be called with the connection, otherwise it will be called with no arguments.
param:
closure the given closure
throws:
SQLException if a database error occurs
see:
#setCacheStatements(boolean)


call

public int call(String sql)
Performs a stored procedure call.
param:
sql the SQL statement
return:
the number of rows updated or 0 for SQL statements that return nothing
throws:
SQLException if a database access error occurs


call

public int call(String sql, List params)
Performs a stored procedure call with the given parameters.
param:
sql the SQL statement
param:
params a list of parameters
return:
the number of rows updated or 0 for SQL statements that return nothing
throws:
SQLException if a database access error occurs


call

public void call(String sql, List params, Closure closure)
Performs a stored procedure call with the given parameters. The closure is called once with all the out parameters.
param:
sql the sql statement
param:
params a list of parameters
param:
closure called for each row with a GroovyResultSet
throws:
SQLException if a database access error occurs


call

public int call(GString gstring)
Performs a stored procedure call with the given parameters.
param:
gstring a GString containing the SQL query with embedded params
return:
the number of rows updated or 0 for SQL statements that return nothing
throws:
SQLException if a database access error occurs


call

public void call(GString gstring, Closure closure)
Performs a stored procedure call with the given parameters, calling the closure once with all result objects.
param:
gstring a GString containing the SQL query with embedded params
param:
closure called for each row with a GroovyResultSet
throws:
SQLException if a database access error occurs


close

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


closeResources

protected void closeResources(Connection connection, Statement statement, ResultSet results)
An extension point allowing derived classes to change the behavior of resource closing.
param:
connection the connection to close
param:
statement the statement to close
param:
results the results to close


closeResources

protected void closeResources(Connection connection, Statement statement)
An extension point allowing the behavior of resource closing to be overriden in derived classes.
param:
connection the connection to close
param:
statement the statement to close


commit

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


configure

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


createConnection

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


dataSet

public DataSet dataSet(String table)


dataSet

public DataSet dataSet(Class type)


eachRow

public void eachRow(String sql, Closure closure)
Performs the given SQL query calling the closure with each row of the result set.
param:
sql the sql statement
param:
closure called for each row with a GroovyResultSet
throws:
SQLException if a database access error occurs


eachRow

public void eachRow(String sql, Closure metaClosure, Closure rowClosure)
Performs the given SQL query calling closures for metadata and each row
param:
sql the sql statement
param:
metaClosure called for meta data (only once after sql execution)
param:
rowClosure called for each row with a GroovyResultSet
throws:
SQLException if a database access error occurs


eachRow

public void eachRow(String sql, List params, Closure closure)
Performs the given SQL query calling the closure with the result set.
param:
sql the sql statement
param:
params a list of parameters
param:
closure called for each row with a GroovyResultSet
throws:
SQLException if a database access error occurs


eachRow

public void eachRow(GString gstring, Closure closure)
Performs the given SQL query calling the closure with the result set.
param:
gstring a GString containing the SQL query with embedded params
param:
closure called for each row with a GroovyResultSet
throws:
SQLException if a database access error occurs


execute

public boolean execute(String sql)
Executes the given piece of SQL.
param:
sql the SQL to execute
return:
true if the first result is a ResultSet object; false if it is an update count or there are no results
throws:
SQLException if a database access error occurs


execute

public boolean execute(String sql, List params)
Executes the given piece of SQL with parameters.
param:
sql the SQL statement
param:
params a list of parameters
return:
true if the first result is a ResultSet object; false if it is an update count or there are no results
throws:
SQLException if a database access error occurs


execute

public boolean execute(GString gstring)
Executes the given SQL with embedded expressions inside.
param:
gstring a GString containing the SQL query with embedded params
return:
true if the first result is a ResultSet object; false if it is an update count or there are no results
throws:
SQLException if a database access error occurs


executeInsert

public List executeInsert(String sql)
Executes the given SQL statement. See #executeInsert(GString) for more details.
param:
sql The SQL statement to execute
return:
A list of the auto-generated column values for each inserted row
throws:
SQLException if a database access error occurs


executeInsert

public List executeInsert(String sql, List params)
Executes the given SQL statement with a particular list of parameter values. See #executeInsert(GString) for more details.
param:
sql The SQL statement to execute
param:
params The parameter values that will be substituted into the SQL statement's parameter slots
return:
A list of the auto-generated column values for each inserted row
throws:
SQLException if a database access error occurs


executeInsert

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

param:
gstring a GString containing the SQL query with embedded params
return:
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)
Executes the given SQL update.
param:
sql the SQL to execute
return:
the number of rows updated or 0 for SQL statements that return nothing
throws:
SQLException if a database access error occurs


executeUpdate

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


executeUpdate

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


expand

public static ExpandedVariable expand(Object object)
Creates a variable to be expanded in the Sql string rather than representing an sql parameter.
param:
object the object of interest
return:
the expanded variable


findWhereKeyword

protected int findWhereKeyword(String sql)
Hook to allow derived classes to override where clause sniffing. Default behavior is to find the first 'where' keyword in the sql doing simple avoidance of the word 'where' within quotes.
param:
sql the SQL statement
return:
the index of the found keyword or -1 if not found


firstRow

public Object firstRow(String sql)
Performs the given SQL query and return the first row of the result set.
param:
sql the SQL statement
return:
a GroovyRowResult object or null if no row is found
throws:
SQLException if a database access error occurs


firstRow

public Object firstRow(GString gstring)
Performs the given SQL query and return the first row of the result set.
param:
gstring a GString containing the SQL query with embedded params
return:
a GroovyRowResult object or null if no row is found
throws:
SQLException if a database access error occurs


firstRow

public 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.
param:
sql the SQL statement
param:
params a list of parameters
return:
a GroovyRowResult object or null if no row is found
throws:
SQLException if a database access error occurs


getConnection

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


getDataSource

public DataSource getDataSource()


getParameters

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


getResultSetConcurrency

public int getResultSetConcurrency()
Gets the resultSetConcurrency for statements created using the connection.
return:
the current resultSetConcurrency value
since:
1.5.2


getResultSetHoldability

public int getResultSetHoldability()
Gets the resultSetHoldability for statements created using the connection.
return:
the current resultSetHoldability value or -1 if not set
since:
1.5.2


getResultSetType

public int getResultSetType()
Gets the resultSetType for statements created using the connection.
return:
the current resultSetType value
since:
1.5.2


getUpdateCount

public int getUpdateCount()
return:
Returns the updateCount.


in

public static InParameter in(int type, Object value)
Create a new InParameter
param:
type the JDBC data type
param:
value the object value
return:
an InParameter


inout

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


isCacheStatements

public boolean isCacheStatements()
return:
boolean true if cache is enabled


loadDriver

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


newInstance

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


newInstance

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


newInstance

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


newInstance

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


newInstance

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


newInstance

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


nullify

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


out

public static OutParameter out(int type)
Create a new OutParameter
param:
type the JDBC data type.
return:
an OutParameter


query

public void query(String sql, Closure closure)
Performs the given SQL query calling the closure with the result set.
param:
sql the sql statement
param:
closure called for each row with a GroovyResultSet
throws:
SQLException if a database access error occurs


query

public void query(String sql, List params, Closure closure)
Performs the given SQL query with parameters calling the closure with the result set.
param:
sql the sql statement
param:
params a list of parameters
param:
closure called for each row with a GroovyResultSet
throws:
SQLException if a database access error occurs


query

public void query(GString gstring, Closure closure)
Performs the given SQL query calling the closure with the result set.
param:
gstring a GString containing the SQL query with embedded params
param:
closure called for each row with a GroovyResultSet
throws:
SQLException if a database access error occurs


resultSet

public static ResultSetOutParameter resultSet(int type)
Create a new ResultSetOutParameter
param:
type the JDBC data type.
return:
a ResultSetOutParameter


rollback

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


rows

public List rows(String sql)
Performs the given SQL query and return the rows of the result set.
param:
sql the SQL statement
return:
a list of GroovyRowResult objects
throws:
SQLException if a database access error occurs


rows

public List rows(GString gstring)
Performs the given SQL query and return the rows of the result set.
param:
gstring a GString containing the SQL query with embedded params
return:
a list of GroovyRowResult objects
throws:
SQLException if a database access error occurs


rows

public List rows(String sql, Closure metaClosure)
Performs the given SQL query and return the rows of the result set.
param:
sql the SQL statement
param:
metaClosure called with meta data of the ResultSet
return:
a list of GroovyRowResult objects
throws:
SQLException if a database access error occurs


rows

public List rows(String sql, List params)
Performs the given SQL query with the list of params and return the rows of the result set.
param:
sql the SQL statement
param:
params a list of parameters
return:
a list of GroovyRowResult objects
throws:
SQLException if a database access error occurs


setCacheStatements

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


setObject

protected void setObject(PreparedStatement statement, int i, Object value)
Strategy method allowing derived classes to handle types differently such as for CLOBs etc.
param:
statement the statement of interest
param:
i the index of the object of interest
param:
value the new object value
throws:
SQLException if a database access error occurs


setParameters

protected void setParameters(List params, PreparedStatement statement)
Hook to allow derived classes to override behavior associated with setting params for a prepared statement. Default behavior is to append the parameters to the given statement using setObject.
param:
params the parameters to append
param:
statement the statement
throws:
SQLException if a database access error occurs


setResultSetConcurrency

public void setResultSetConcurrency(int resultSetConcurrency)
Sets the resultSetConcurrency for statements created using the connection. May cause SQLFeatureNotSupportedException exceptions to occur if the underlying database doesn't support the requested concurrency value.
param:
resultSetConcurrency one of the following ResultSet constants: ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE
since:
1.5.2


setResultSetHoldability

public void setResultSetHoldability(int resultSetHoldability)
Sets the resultSetHoldability for statements created using the connection. May cause SQLFeatureNotSupportedException exceptions to occur if the underlying database doesn't support the requested holdability value.
param:
resultSetHoldability one of the following ResultSet constants: ResultSet.HOLD_CURSORS_OVER_COMMIT or ResultSet.CLOSE_CURSORS_AT_COMMIT
since:
1.5.2


setResultSetType

public void setResultSetType(int resultSetType)
Sets the resultSetType for statements created using the connection. May cause SQLFeatureNotSupportedException exceptions to occur if the underlying database doesn't support the requested type value.
param:
resultSetType one of the following ResultSet constants: ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, or ResultSet.TYPE_SCROLL_SENSITIVE
since:
1.5.2


withBatch

public int[] withBatch(Closure closure)
Performs the closure within a batch using a cached connection. The closure will be called with a single argument; the statement associated with this batch. Use it like this:
 def updateCounts = sql.withBatch { stmt ->
     stmt.addBatch("insert into TABLENAME ...")
     stmt.addBatch("insert into TABLENAME ...")
     stmt.addBatch("insert into TABLENAME ...")
 }
 
param:
closure the closure containing batch and optionally other statements
return:
an array of update counts containing one element for each command in the batch. The elements of the array are ordered according to the order in which commands were added to the batch.
exception:
SQLException if a database access error occurs, or this method is called on a closed Statement, or the driver does not support batch statements. Throws BatchUpdateException (a subclass of SQLException) if one of the commands sent to the database fails to execute properly or attempts to return a result set.


withStatement

public void withStatement(Closure configureStatement)
Allows a closure to be passed in to configure the JDBC statements before they are executed. It can be used to do things like set the query size etc. When this method is invoked, the supplied closure is saved. Statements subsequent created from other methods will then be configured using this closure. The statement being configured is passed into the closure as its single argument, e.g.:
 sql.withStatement{ stmt -> stmt.maxRows == 10 }
 def firstTenRows = sql.rows("select * from table")
 
param:
configureStatement the closure


withTransaction

public void withTransaction(Closure closure)
Performs the closure within a transaction using a cached connection. If the closure takes a single argument, it will be called with the connection, otherwise it will be called with no arguments.
param:
closure the given closure
throws:
SQLException if a database error occurs


 

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