public class Sql extends Object
newInstance
factory methods available to do this.
In simple cases, you can just provide
the necessary details to set up a connection (e.g. for hsqldb):
def db = [url:'jdbc:hsqldb:mem:testDB', user:'sa', password:'', driver:'org.hsqldb.jdbc.JDBCDriver'] def sql = Sql.newInstance(db.url, db.user, db.password, db.driver)or if you have an existing connection (perhaps from a connection pool) or a datasource use one of the constructors:
def sql = new Sql(datasource)Now you can invoke sql, e.g. to create a table:
sql.execute ''' create table PROJECT ( id integer not null, name varchar(50), url varchar(100), ) '''Or insert a row using JDBC PreparedStatement inspired syntax:
def params = [10, 'Groovy', 'http://groovy.codehaus.org'] sql.execute 'insert into PROJECT (id, name, url) values (?, ?, ?)', paramsOr insert a row using GString syntax:
def map = [id:20, name:'Grails', url:'http://grails.codehaus.org'] sql.execute "insert into PROJECT (id, name, url) values ($map.id, $map.name, $map.url)"Or a row update:
def newUrl = 'http://grails.org' def project = 'Grails' sql.executeUpdate "update PROJECT set url=$newUrl where name=$project"Now try a query using
eachRow
:
println 'Some GR8 projects:' sql.eachRow('select * from PROJECT') { row -> println "${row.name.padRight(10)} ($row.url)" }Which will produce something like this:
Some GR8 projects: Groovy (http://groovy.codehaus.org) Grails (http://grails.org) Griffon (http://griffon.codehaus.org) Gradle (http://gradle.org)Now try a query using
rows
:
def rows = sql.rows("select * from PROJECT where name like 'Gra%'") assert rows.size() == 2 println rows.join('\n')with output like this:
[ID:20, NAME:Grails, URL:http://grails.org] [ID:40, NAME:Gradle, URL:http://gradle.org]Also,
eachRow
and rows
support paging. Here's an example:
sql.eachRow('select * from PROJECT', 2, 2) { row -> println "${row.name.padRight(10)} ($row.url)" }Which will start at the second row and return a maximum of 2 rows. Here's an example result:
Grails (http://grails.org) Griffon (http://griffon.codehaus.org)Finally, we should clean up:
sql.close()If we are using a DataSource and we haven't enabled statement caching, then strictly speaking the final
close()
method isn't required - as all connection
handling is performed transparently on our behalf; however, it doesn't hurt to
have it there as it will return silently in that case.
If instead of newInstance
you use withInstance
, then
close()
will be called automatically for you.
sql.firstRow('select * from PersonTable')
This example is potentially at risk of SQL injection:
sql.firstRow('select * from PersonTable where SurnameColumn = ' + userInput)
This in turn will be fine if 'userInput
' is something like 'Smith' but maybe
not so fine if 'userInput
' is something like 'Smith; DROP table PersonTable'.
Instead, use one of the variants with parameters and placeholders:
sql.firstRow("select * from PersonTable where SurnameColumn = ?", [userInput])
or the GString variants which will be converted to the placeholder variants under the covers:
sql.firstRow("select * from PersonTable where SurnameColumn = $userInput")
or the named parameter variants discussed next.
Modifier and Type | Class and Description |
---|---|
protected class |
Sql.AbstractQueryCommand |
Constructor and Description |
---|
Sql(Connection connection)
Constructs an SQL instance using the given Connection.
|
Sql(DataSource dataSource)
Constructs an SQL instance using the given DataSource.
|
Sql(Sql parent) |
Modifier and Type | Method and Description |
---|---|
static InParameter |
ARRAY(Object value) |
protected List<GroovyRowResult> |
asList(String sql,
ResultSet rs)
Hook to allow derived classes to override list of result collection behavior.
|
protected List<GroovyRowResult> |
asList(String sql,
ResultSet rs,
Closure metaClosure)
Hook to allow derived classes to override list of result collection behavior.
|
protected List<GroovyRowResult> |
asList(String sql,
ResultSet rs,
int offset,
int maxRows,
Closure metaClosure) |
protected String |
asSql(GString gstring,
List<Object> values)
Hook to allow derived classes to override sql generation from GStrings.
|
static InParameter |
BIGINT(Object value) |
static InParameter |
BINARY(Object value) |
static InParameter |
BIT(Object value) |
static InParameter |
BLOB(Object value) |
static InParameter |
BOOLEAN(Object value) |
protected SqlWithParams |
buildSqlWithIndexedProps(String sql)
Hook to allow derived classes to override behavior associated with the
parsing and indexing of parameters from a given sql statement.
|
void |
cacheConnection(Closure closure)
Caches the connection used while the closure is active.
|
void |
cacheStatements(Closure closure)
Caches every created preparedStatement in Closure closure
Every cached preparedStatement is closed after closure has been called.
|
int |
call(GString gstring)
Performs a stored procedure call with the given embedded parameters.
|
void |
call(GString gstring,
Closure closure)
Performs a stored procedure call with the given parameters,
calling the closure once with all result objects.
|
int |
call(String sql)
Performs a stored procedure call.
|
int |
call(String sql,
List<Object> params)
Performs a stored procedure call with the given parameters.
|
void |
call(String sql,
List<Object> params,
Closure closure)
Performs a stored procedure call with the given parameters.
|
int |
call(String sql,
Object[] params)
Performs a stored procedure call with the given parameters.
|
List<List<GroovyRowResult>> |
callWithAllRows(GString gstring,
Closure closure)
Performs a stored procedure call with the given parameters,
calling the closure once with all result objects,
and also returning a list of lists with the rows of the ResultSet(s).
|
List<List<GroovyRowResult>> |
callWithAllRows(String sql,
List<Object> params,
Closure closure)
Performs a stored procedure call with the given parameters,
calling the closure once with all result objects,
and also returning a list of lists with the rows of the ResultSet(s).
|
List<GroovyRowResult> |
callWithRows(GString gstring,
Closure closure)
Performs a stored procedure call with the given parameters,
calling the closure once with all result objects,
and also returning the rows of the ResultSet.
|
List<GroovyRowResult> |
callWithRows(String sql,
List<Object> params,
Closure closure)
Performs a stored procedure call with the given parameters,
calling the closure once with all result objects,
and also returning the rows of the ResultSet.
|
protected List<List<GroovyRowResult>> |
callWithRows(String sql,
List<Object> params,
int processResultsSets,
Closure closure)
Base internal method for call(), callWithRows(), and callWithAllRows() style of methods.
|
static InParameter |
CHAR(Object value) |
SqlWithParams |
checkForNamedParams(String sql,
List<Object> params) |
static InParameter |
CLOB(Object value) |
void |
close()
If this SQL object was created with a Connection then this method closes
the connection.
|
protected void |
closeResources(Connection connection)
An extension point allowing the behavior of resource closing to be
overridden in derived classes.
|
protected void |
closeResources(Connection connection,
Statement statement)
An extension point allowing the behavior of resource closing to be
overridden in derived classes.
|
protected void |
closeResources(Connection connection,
Statement statement,
ResultSet results)
An extension point allowing derived classes to change the behavior
of resource closing.
|
void |
commit()
If this SQL object was created with a Connection then this method commits
the connection.
|
protected void |
configure(Statement statement)
Provides a hook for derived classes to be able to configure JDBC statements.
|
protected Connection |
createConnection()
An extension point allowing derived classes to change the behavior of
connection creation.
|
protected Sql.AbstractQueryCommand |
createPreparedQueryCommand(String sql,
List<Object> queryParams)
Factory for the PreparedQueryCommand command pattern object allows subclass to supply implementations
of the command class.
|
protected Sql.AbstractQueryCommand |
createQueryCommand(String sql)
Factory for the QueryCommand command pattern object allows subclasses to
supply implementations of the command class.
|
static InParameter |
DATALINK(Object value) |
DataSet |
dataSet(Class<?> type) |
DataSet |
dataSet(String table) |
static InParameter |
DATE(Object value) |
static InParameter |
DECIMAL(Object value) |
static InParameter |
DISTINCT(Object value) |
static InParameter |
DOUBLE(Object value) |
void |
eachRow(GString gstring,
Closure closure)
Performs the given SQL query calling the given Closure with each row of the result set.
|
void |
eachRow(GString gstring,
Closure metaClosure,
Closure rowClosure)
Performs the given SQL query calling the given Closure with each row of the result set.
|
void |
eachRow(GString gstring,
Closure metaClosure,
int offset,
int maxRows,
Closure rowClosure)
Performs the given SQL query calling the given
closure with each row of the result set starting at
the provided offset , and including up to maxRows number of rows. |
void |
eachRow(GString gstring,
int offset,
int maxRows,
Closure closure)
Performs the given SQL query calling the given
closure with each row of the result set starting at
the provided offset , and including up to maxRows number of rows. |
void |
eachRow(Map params,
String sql,
Closure closure)
A variant of
eachRow(String, java.util.List, groovy.lang.Closure)
useful when providing the named parameters as named arguments. |
void |
eachRow(Map params,
String sql,
Closure metaClosure,
Closure rowClosure)
A variant of
eachRow(String, java.util.List, groovy.lang.Closure, groovy.lang.Closure)
useful when providing the named parameters as named arguments. |
void |
eachRow(Map map,
String sql,
Closure metaClosure,
int offset,
int maxRows,
Closure rowClosure)
A variant of
eachRow(String, java.util.List, groovy.lang.Closure, int, int, groovy.lang.Closure)
allowing the named parameters to be supplied as named arguments. |
void |
eachRow(Map params,
String sql,
int offset,
int maxRows,
Closure closure)
A variant of
eachRow(String, java.util.List, int, int, groovy.lang.Closure)
useful when providing the named parameters as named arguments. |
void |
eachRow(String sql,
Closure closure)
Performs the given SQL query calling the given Closure with each row of the result set.
|
void |
eachRow(String sql,
Closure metaClosure,
Closure rowClosure)
Performs the given SQL query calling the given
rowClosure with each row of the
result set. |
void |
eachRow(String sql,
Closure metaClosure,
int offset,
int maxRows,
Closure rowClosure)
Performs the given SQL query calling the given
rowClosure with each row of the result set starting at
the provided offset , and including up to maxRows number of rows. |
void |
eachRow(String sql,
int offset,
int maxRows,
Closure closure)
Performs the given SQL query calling the given
closure with each row of the result set starting at
the provided offset , and including up to maxRows number of rows. |
void |
eachRow(String sql,
List<Object> params,
Closure closure)
Performs the given SQL query calling the given Closure with each row of the result set.
|
void |
eachRow(String sql,
List<Object> params,
Closure metaClosure,
Closure rowClosure)
Performs the given SQL query calling the given Closure with each row of the result set.
|
void |
eachRow(String sql,
List<Object> params,
Closure metaClosure,
int offset,
int maxRows,
Closure rowClosure)
Performs the given SQL query calling the given
rowClosure with each row of the result set starting at
the provided offset , and including up to maxRows number of rows. |
void |
eachRow(String sql,
List<Object> params,
int offset,
int maxRows,
Closure closure)
Performs the given SQL query calling the given
closure with each row of the result set starting at
the provided offset , and including up to maxRows number of rows. |
void |
eachRow(String sql,
Map params,
Closure closure)
A variant of
eachRow(String, java.util.List, groovy.lang.Closure)
useful when providing the named parameters as a map. |
void |
eachRow(String sql,
Map params,
Closure metaClosure,
Closure rowClosure)
A variant of
eachRow(String, java.util.List, groovy.lang.Closure, groovy.lang.Closure)
useful when providing the named parameters as a map. |
void |
eachRow(String sql,
Map map,
Closure metaClosure,
int offset,
int maxRows,
Closure rowClosure)
A variant of
eachRow(String, java.util.List, groovy.lang.Closure, int, int, groovy.lang.Closure)
allowing the named parameters to be supplied in a map. |
void |
eachRow(String sql,
Map params,
int offset,
int maxRows,
Closure closure)
A variant of
eachRow(String, java.util.List, int, int, groovy.lang.Closure)
useful when providing the named parameters as a map. |
boolean |
execute(GString gstring)
Executes the given SQL with embedded expressions inside.
|
void |
execute(GString gstring,
Closure processResults)
Executes the given SQL with embedded expressions inside.
|
boolean |
execute(Map params,
String sql)
A variant of
execute(String, java.util.List)
useful when providing the named parameters as named arguments. |
void |
execute(Map params,
String sql,
Closure processResults)
A variant of
execute(String, java.util.List, Closure)
useful when providing the named parameters as named arguments. |
boolean |
execute(String sql)
Executes the given piece of SQL.
|
void |
execute(String sql,
Closure processResults)
Executes the given piece of SQL.
|
boolean |
execute(String sql,
List<Object> params)
Executes the given piece of SQL with parameters.
|
void |
execute(String sql,
List<Object> params,
Closure processResults)
Executes the given piece of SQL with parameters.
|
boolean |
execute(String sql,
Object[] params)
Executes the given piece of SQL with parameters.
|
void |
execute(String sql,
Object[] params,
Closure processResults)
Executes the given piece of SQL with parameters.
|
List<List<Object>> |
executeInsert(GString gstring)
Executes the given SQL statement (typically an INSERT statement).
|
List<GroovyRowResult> |
executeInsert(GString gstring,
List<String> keyColumnNames)
Executes the given SQL statement (typically an INSERT statement).
|
List<List<Object>> |
executeInsert(Map params,
String sql)
A variant of
executeInsert(String, java.util.List)
useful when providing the named parameters as named arguments. |
List<GroovyRowResult> |
executeInsert(Map params,
String sql,
List<String> keyColumnNames)
A variant of
executeInsert(String, List, List)
useful when providing the named parameters as named arguments. |
List<List<Object>> |
executeInsert(String sql)
Executes the given SQL statement (typically an INSERT statement).
|
List<List<Object>> |
executeInsert(String sql,
List<Object> params)
Executes the given SQL statement (typically an INSERT statement).
|
List<GroovyRowResult> |
executeInsert(String sql,
List<Object> params,
List<String> keyColumnNames)
Executes the given SQL statement (typically an INSERT statement).
|
List<List<Object>> |
executeInsert(String sql,
Object[] params)
Executes the given SQL statement (typically an INSERT statement).
|
List<GroovyRowResult> |
executeInsert(String sql,
String[] keyColumnNames)
Executes the given SQL statement (typically an INSERT statement).
|
List<GroovyRowResult> |
executeInsert(String sql,
String[] keyColumnNames,
Object[] params)
Executes the given SQL statement (typically an INSERT statement).
|
protected ResultSet |
executePreparedQuery(String sql,
List<Object> params)
Useful helper method which handles resource management when executing a
prepared query which returns a result set.
|
protected ResultSet |
executeQuery(String sql)
Useful helper method which handles resource management when executing a
query which returns a result set.
|
int |
executeUpdate(GString gstring)
Executes the given SQL update with embedded expressions inside.
|
int |
executeUpdate(Map params,
String sql)
A variant of
executeUpdate(String, java.util.List)
useful when providing the named parameters as named arguments. |
int |
executeUpdate(String sql)
Executes the given SQL update.
|
int |
executeUpdate(String sql,
List<Object> params)
Executes the given SQL update with parameters.
|
int |
executeUpdate(String sql,
Object[] params)
Executes the given SQL update with parameters.
|
static ExpandedVariable |
expand(Object object)
When using GString SQL queries, allows a variable to be expanded
in the Sql string rather than representing an sql parameter.
|
protected int |
findWhereKeyword(String sql)
Hook to allow derived classes to override where clause sniffing.
|
GroovyRowResult |
firstRow(GString gstring)
Performs the given SQL query and return
the first row of the result set.
|
GroovyRowResult |
firstRow(Map params,
String sql)
A variant of
firstRow(String, java.util.List)
useful when providing the named parameters as named arguments. |
GroovyRowResult |
firstRow(String sql)
Performs the given SQL query and return the first row of the result set.
|
GroovyRowResult |
firstRow(String sql,
List<Object> params)
Performs the given SQL query and return the first row of the result set.
|
GroovyRowResult |
firstRow(String sql,
Object[] params)
Performs the given SQL query and return the first row of the result set.
|
static InParameter |
FLOAT(Object value) |
Connection |
getConnection()
If this instance was created with a single Connection then the connection
is returned.
|
DataSource |
getDataSource() |
protected List<Object> |
getParameters(GString gstring)
Hook to allow derived classes to override behavior associated with
extracting params from a GString.
|
int |
getResultSetConcurrency()
Gets the resultSetConcurrency for statements created using the connection.
|
int |
getResultSetHoldability()
Gets the resultSetHoldability for statements created using the connection.
|
int |
getResultSetType()
Gets the resultSetType for statements created using the connection.
|
int |
getUpdateCount() |
List<Object> |
getUpdatedParams(List<Object> params,
List<Tuple> indexPropList) |
static InParameter |
in(int type,
Object value)
Create a new InParameter
|
static InOutParameter |
inout(InParameter in)
Create an inout parameter using this in parameter.
|
static InParameter |
INTEGER(Object value) |
boolean |
isCacheNamedQueries() |
boolean |
isCacheStatements() |
boolean |
isEnableNamedQueries() |
boolean |
isWithinBatch()
Returns true if the current Sql object is currently executing a withBatch
method call.
|
static InParameter |
JAVA_OBJECT(Object value) |
static void |
loadDriver(String driverClassName)
Attempts to load the JDBC driver on the thread, current or system class
loaders
|
static InParameter |
LONGVARBINARY(Object value) |
static InParameter |
LONGVARCHAR(Object value) |
static Sql |
newInstance(Map<String,Object> args)
Creates a new Sql instance given parameters in a Map.
|
static Sql |
newInstance(String url)
Creates a new Sql instance given a JDBC connection URL.
|
static Sql |
newInstance(String url,
Properties properties)
Creates a new Sql instance given a JDBC connection URL
and some properties.
|
static Sql |
newInstance(String url,
Properties properties,
String driverClassName)
Creates a new Sql instance given a JDBC connection URL,
some properties and a driver class name.
|
static Sql |
newInstance(String url,
String driverClassName)
Creates a new Sql instance given a JDBC connection URL
and a driver class name.
|
static Sql |
newInstance(String url,
String user,
String password)
Creates a new Sql instance given a JDBC connection URL,
a username and a password.
|
static Sql |
newInstance(String url,
String user,
String password,
String driverClassName)
Creates a new Sql instance given a JDBC connection URL,
a username, a password and a driver class name.
|
static InParameter |
NULL(Object value) |
protected String |
nullify(String sql)
Hook to allow derived classes to override null handling.
|
static InParameter |
NUMERIC(Object value) |
static InParameter |
OTHER(Object value) |
static OutParameter |
out(int type)
Create a new OutParameter
|
SqlWithParams |
preCheckForNamedParams(String sql)
Deprecated.
Use
buildSqlWithIndexedProps(String) instead |
void |
query(GString gstring,
Closure closure)
Performs the given SQL query, which should return a single
ResultSet object. |
void |
query(Map map,
String sql,
Closure closure)
A variant of
query(String, java.util.List, groovy.lang.Closure)
useful when providing the named parameters as named arguments. |
void |
query(String sql,
Closure closure)
Performs the given SQL query, which should return a single
ResultSet object. |
void |
query(String sql,
List<Object> params,
Closure closure)
Performs the given SQL query, which should return a single
ResultSet object. |
void |
query(String sql,
Map map,
Closure closure)
A variant of
query(String, java.util.List, groovy.lang.Closure)
useful when providing the named parameters as a map. |
static InParameter |
REAL(Object value) |
static InParameter |
REF(Object value) |
static ResultSetOutParameter |
resultSet(int type)
Create a new ResultSetOutParameter
|
void |
rollback()
If this SQL object was created with a Connection then this method rolls back
the connection.
|
List<GroovyRowResult> |
rows(GString gstring)
Performs the given SQL query and return the rows of the result set.
|
List<GroovyRowResult> |
rows(GString gstring,
Closure metaClosure)
Performs the given SQL query and return the rows of the result set.
|
List<GroovyRowResult> |
rows(GString sql,
int offset,
int maxRows)
Performs the given SQL query and return a "page" of rows from the result set.
|
List<GroovyRowResult> |
rows(GString gstring,
int offset,
int maxRows,
Closure metaClosure)
Performs the given SQL query and return a "page" of rows from the result set.
|
List<GroovyRowResult> |
rows(Map params,
String sql)
A variant of
rows(String, java.util.List)
useful when providing the named parameters as named arguments. |
List<GroovyRowResult> |
rows(Map params,
String sql,
Closure metaClosure)
A variant of
rows(String, java.util.List, groovy.lang.Closure)
useful when providing the named parameters as named arguments. |
List<GroovyRowResult> |
rows(Map params,
String sql,
int offset,
int maxRows)
A variant of
rows(String, java.util.List, int, int)
useful when providing the named parameters as named arguments. |
List<GroovyRowResult> |
rows(Map params,
String sql,
int offset,
int maxRows,
Closure metaClosure)
A variant of
rows(String, java.util.List, int, int, groovy.lang.Closure)
useful when providing the named parameters as named arguments. |
List<GroovyRowResult> |
rows(String sql)
Performs the given SQL query and return the rows of the result set.
|
List<GroovyRowResult> |
rows(String sql,
Closure metaClosure)
Performs the given SQL query and return the rows of the result set.
|
List<GroovyRowResult> |
rows(String sql,
int offset,
int maxRows)
Performs the given SQL query and return a "page" of rows from the result set.
|
List<GroovyRowResult> |
rows(String sql,
int offset,
int maxRows,
Closure metaClosure)
Performs the given SQL query and return a "page" of rows from the result set.
|
List<GroovyRowResult> |
rows(String sql,
List<Object> params)
Performs the given SQL query and return the rows of the result set.
|
List<GroovyRowResult> |
rows(String sql,
List<Object> params,
Closure metaClosure)
Performs the given SQL query and return the rows of the result set.
|
List<GroovyRowResult> |
rows(String sql,
List<Object> params,
int offset,
int maxRows)
Performs the given SQL query and return a "page" of rows from the result set.
|
List<GroovyRowResult> |
rows(String sql,
List<Object> params,
int offset,
int maxRows,
Closure metaClosure)
Performs the given SQL query and return a "page" of rows from the result set.
|
List<GroovyRowResult> |
rows(String sql,
Map params,
Closure metaClosure)
A variant of
rows(String, java.util.List, groovy.lang.Closure)
useful when providing the named parameters as a map. |
List<GroovyRowResult> |
rows(String sql,
Map params,
int offset,
int maxRows)
A variant of
rows(String, java.util.List, int, int)
useful when providing the named parameters as a map. |
List<GroovyRowResult> |
rows(String sql,
Map params,
int offset,
int maxRows,
Closure metaClosure)
A variant of
rows(String, java.util.List, int, int, groovy.lang.Closure)
useful when providing the named parameters as a map. |
List<GroovyRowResult> |
rows(String sql,
Object[] params)
Performs the given SQL query and return the rows of the result set.
|
List<GroovyRowResult> |
rows(String sql,
Object[] params,
int offset,
int maxRows)
Performs the given SQL query and return the rows of the result set.
|
void |
setCacheNamedQueries(boolean cacheNamedQueries)
Enables named query caching.
if cacheNamedQueries is true, cache is created and processed named queries will be cached. |
void |
setCacheStatements(boolean cacheStatements)
Enables statement caching.
if cacheStatements is true, cache is created and all created prepared statements will be cached. |
void |
setEnableNamedQueries(boolean enableNamedQueries)
Enables named query support:
if enableNamedQueries is true, queries with ':propname' and '?1.propname' style placeholders will be processed.
if enableNamedQueries is false, this feature will be turned off.
|
protected void |
setInternalConnection(Connection conn)
Stub needed for testing.
|
protected void |
setObject(PreparedStatement statement,
int i,
Object value)
Strategy method allowing derived classes to handle types differently
such as for CLOBs etc.
|
protected void |
setParameters(List<Object> params,
PreparedStatement statement)
Hook to allow derived classes to override behavior associated with
setting params for a prepared statement.
|
void |
setResultSetConcurrency(int resultSetConcurrency)
Sets the resultSetConcurrency for statements created using the connection.
|
void |
setResultSetHoldability(int resultSetHoldability)
Sets the resultSetHoldability for statements created using the connection.
|
void |
setResultSetType(int resultSetType)
Sets the resultSetType for statements created using the connection.
|
static InParameter |
SMALLINT(Object value) |
static InParameter |
STRUCT(Object value) |
static InParameter |
TIME(Object value) |
static InParameter |
TIMESTAMP(Object value) |
static InParameter |
TINYINT(Object value) |
static InParameter |
VARBINARY(Object value) |
static InParameter |
VARCHAR(Object value) |
int[] |
withBatch(Closure closure)
Performs the closure (containing batch operations) within a batch.
|
int[] |
withBatch(int batchSize,
Closure closure)
Performs the closure (containing batch operations) within a batch using a given batch size.
|
int[] |
withBatch(int batchSize,
String sql,
Closure closure)
Performs the closure (containing batch operations specific to an associated prepared statement)
within a batch using a given batch size.
|
int[] |
withBatch(String sql,
Closure closure)
Performs the closure (containing batch operations specific to an associated prepared statement)
within a batch.
|
static void |
withInstance(Map<String,Object> args,
Closure c)
Invokes a closure passing it a new Sql instance created from the given map of arguments.
|
static void |
withInstance(String url,
Closure c)
Invokes a closure passing it a new Sql instance created from the given JDBC connection URL.
|
static void |
withInstance(String url,
Properties properties,
Closure c)
Invokes a closure passing it a new Sql instance created from the given JDBC connection URL and properties.
|
static void |
withInstance(String url,
Properties properties,
String driverClassName,
Closure c)
Invokes a closure passing it a new Sql instance created from the given JDBC connection URL,
properties and driver classname.
|
static void |
withInstance(String url,
String driverClassName,
Closure c)
Invokes a closure passing it a new Sql instance created from the given JDBC connection URL.
|
static void |
withInstance(String url,
String user,
String password,
Closure c)
Invokes a closure passing it a new Sql instance created from the given JDBC connection URL, user and password.
|
static void |
withInstance(String url,
String user,
String password,
String driverClassName,
Closure c)
Invokes a closure passing it a new Sql instance created from the given JDBC connection URL.
|
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.
|
protected static final Logger LOG
public static final OutParameter ARRAY
public static final OutParameter BIGINT
public static final OutParameter BINARY
public static final OutParameter BIT
public static final OutParameter BLOB
public static final OutParameter BOOLEAN
public static final OutParameter CHAR
public static final OutParameter CLOB
public static final OutParameter DATALINK
public static final OutParameter DATE
public static final OutParameter DECIMAL
public static final OutParameter DISTINCT
public static final OutParameter DOUBLE
public static final OutParameter FLOAT
public static final OutParameter INTEGER
public static final OutParameter JAVA_OBJECT
public static final OutParameter LONGVARBINARY
public static final OutParameter LONGVARCHAR
public static final OutParameter NULL
public static final OutParameter NUMERIC
public static final OutParameter OTHER
public static final OutParameter REAL
public static final OutParameter REF
public static final OutParameter SMALLINT
public static final OutParameter STRUCT
public static final OutParameter TIME
public static final OutParameter TIMESTAMP
public static final OutParameter TINYINT
public static final OutParameter VARBINARY
public static final OutParameter VARCHAR
public static final int NO_RESULT_SETS
public static final int FIRST_RESULT_SET
public static final int ALL_RESULT_SETS
public Sql(DataSource dataSource)
dataSource
- the DataSource to usepublic Sql(Connection connection)
close()
method which will close the connection but also
free any caches resources.connection
- the Connection to usepublic Sql(Sql parent)
public static Sql newInstance(String url) throws SQLException
url
- a database url of the form
jdbc:subprotocol:subname
SQLException
- if a database access error occurspublic static void withInstance(String url, Closure c) throws SQLException
url
- a database url of the form
jdbc:subprotocol:subname
c
- the Closure to callSQLException
- if a database access error occursnewInstance(String)
public static Sql newInstance(String url, Properties properties) throws SQLException
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 includedSQLException
- if a database access error occurspublic static void withInstance(String url, Properties properties, Closure c) throws SQLException
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 includedc
- the Closure to callSQLException
- if a database access error occursnewInstance(String, java.util.Properties)
public static Sql newInstance(String url, Properties properties, String driverClassName) throws SQLException, ClassNotFoundException
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 includeddriverClassName
- the fully qualified class name of the driver classSQLException
- if a database access error occursClassNotFoundException
- if the driver class cannot be found or loadedpublic static void withInstance(String url, Properties properties, String driverClassName, Closure c) throws SQLException, ClassNotFoundException
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 includeddriverClassName
- the fully qualified class name of the driver classc
- the Closure to callSQLException
- if a database access error occursClassNotFoundException
- if the driver class cannot be found or loadednewInstance(String, java.util.Properties, String)
public static Sql newInstance(String url, String user, String password) throws SQLException
url
- a database url of the form
jdbc:subprotocol:subname
user
- the database user on whose behalf the connection
is being madepassword
- the user's passwordSQLException
- if a database access error occurspublic static void withInstance(String url, String user, String password, Closure c) throws SQLException
url
- a database url of the form
jdbc:subprotocol:subname
user
- the database user on whose behalf the connection
is being madepassword
- the user's passwordc
- the Closure to callSQLException
- if a database access error occursnewInstance(String, String, String)
public static Sql newInstance(String url, String user, String password, String driverClassName) throws SQLException, ClassNotFoundException
url
- a database url of the form
jdbc:subprotocol:subname
user
- the database user on whose behalf the connection
is being madepassword
- the user's passworddriverClassName
- the fully qualified class name of the driver classSQLException
- if a database access error occursClassNotFoundException
- if the driver class cannot be found or loadedpublic static void withInstance(String url, String user, String password, String driverClassName, Closure c) throws SQLException, ClassNotFoundException
url
- a database url of the form
jdbc:subprotocol:subname
user
- the database user on whose behalf the connection
is being madepassword
- the user's passworddriverClassName
- the fully qualified class name of the driver classc
- the Closure to callSQLException
- if a database access error occursClassNotFoundException
- if the driver class cannot be found or loadednewInstance(String, String, String, String)
public static Sql newInstance(String url, String driverClassName) throws SQLException, ClassNotFoundException
url
- a database url of the form
jdbc:subprotocol:subname
driverClassName
- the fully qualified class name of the driver classSQLException
- if a database access error occursClassNotFoundException
- if the driver class cannot be found or loadedpublic static void withInstance(String url, String driverClassName, Closure c) throws SQLException, ClassNotFoundException
url
- a database url of the form
jdbc:subprotocol:subname
driverClassName
- the fully qualified class name of the driver classc
- the Closure to callSQLException
- if a database access error occursClassNotFoundException
- if the driver class cannot be found or loadednewInstance(String, String)
public static Sql newInstance(Map<String,Object> args) throws SQLException, ClassNotFoundException
driverClassName the fully qualified class name of the driver class
driver a synonym for driverClassName
url a database url of the form: jdbc:subprotocol:subname
user the database user on whose behalf the connection is being made
password the user's password
properties a list of arbitrary string tag/value pairs as connection arguments;
normally at least a "user" and "password" property should be included
other any of the public setter methods of this class may be used with property notation
e.g. cacheStatements: true, resultSetConcurrency: ResultSet.CONCUR_READ_ONLY
Of these, 'url
' is required. Others may be needed depending on your database.properties
' is supplied, neither 'user
' nor 'password
' should be supplied.user
' or 'password
' is supplied, both should be supplied.
Example usage:
import groovy.sql.Sql import static java.sql.ResultSet.* def sql = Sql.newInstance( url:'jdbc:hsqldb:mem:testDB', user:'sa', password:'', driver:'org.hsqldb.jdbc.JDBCDriver', cacheStatements: true, resultSetConcurrency: CONCUR_READ_ONLY )
args
- a Map contain further argumentsSQLException
- if a database access error occursClassNotFoundException
- if the driver class cannot be found or loadedpublic static void withInstance(Map<String,Object> args, Closure c) throws SQLException, ClassNotFoundException
args
- a Map contain further argumentsc
- the Closure to callSQLException
- if a database access error occursClassNotFoundException
- if the driver class cannot be found or loadednewInstance(java.util.Map)
public int getResultSetType()
public void setResultSetType(int resultSetType)
resultSetType
- one of the following ResultSet
constants:
ResultSet.TYPE_FORWARD_ONLY
,
ResultSet.TYPE_SCROLL_INSENSITIVE
, or
ResultSet.TYPE_SCROLL_SENSITIVE
public int getResultSetConcurrency()
public void setResultSetConcurrency(int resultSetConcurrency)
resultSetConcurrency
- one of the following ResultSet
constants:
ResultSet.CONCUR_READ_ONLY
or
ResultSet.CONCUR_UPDATABLE
public int getResultSetHoldability()
public void setResultSetHoldability(int resultSetHoldability)
resultSetHoldability
- one of the following ResultSet
constants:
ResultSet.HOLD_CURSORS_OVER_COMMIT
or
ResultSet.CLOSE_CURSORS_AT_COMMIT
public static void loadDriver(String driverClassName) throws ClassNotFoundException
driverClassName
- the fully qualified class name of the driver classClassNotFoundException
- if the class cannot be found or loadedpublic static InParameter ARRAY(Object value)
public static InParameter BIGINT(Object value)
public static InParameter BINARY(Object value)
public static InParameter BIT(Object value)
public static InParameter BLOB(Object value)
public static InParameter BOOLEAN(Object value)
public static InParameter CHAR(Object value)
public static InParameter CLOB(Object value)
public static InParameter DATALINK(Object value)
public static InParameter DATE(Object value)
public static InParameter DECIMAL(Object value)
public static InParameter DISTINCT(Object value)
public static InParameter DOUBLE(Object value)
public static InParameter FLOAT(Object value)
public static InParameter INTEGER(Object value)
public static InParameter JAVA_OBJECT(Object value)
public static InParameter LONGVARBINARY(Object value)
public static InParameter LONGVARCHAR(Object value)
public static InParameter NULL(Object value)
public static InParameter NUMERIC(Object value)
public static InParameter OTHER(Object value)
public static InParameter REAL(Object value)
public static InParameter REF(Object value)
public static InParameter SMALLINT(Object value)
public static InParameter STRUCT(Object value)
public static InParameter TIME(Object value)
public static InParameter TIMESTAMP(Object value)
public static InParameter TINYINT(Object value)
public static InParameter VARBINARY(Object value)
public static InParameter VARCHAR(Object value)
public static InParameter in(int type, Object value)
type
- the JDBC data typevalue
- the object valuepublic static OutParameter out(int type)
type
- the JDBC data type.public static InOutParameter inout(InParameter in)
in
- the InParameter of interestpublic static ResultSetOutParameter resultSet(int type)
type
- the JDBC data type.public static ExpandedVariable expand(Object object)
Example usage:
def fieldName = 'firstname' def fieldOp = Sql.expand('like') def fieldVal = '%a%' sql.query "select * from PERSON where ${Sql.expand(fieldName)} $fieldOp ${fieldVal}", { ResultSet rs -> while (rs.next()) println rs.getString('firstname') } // query will be 'select * from PERSON where firstname like ?' // params will be [fieldVal]
object
- the object of interestexpand(Object)
public void query(String sql, Closure closure) throws SQLException
ResultSet
object. The given closure is called
with the ResultSet
as its argument.
Example usages:
sql.query("select * from PERSON where firstname like 'S%'") { ResultSet rs -> while (rs.next()) println rs.getString('firstname') + ' ' + rs.getString(3) } sql.query("call get_people_places()") { ResultSet rs -> while (rs.next()) println rs.toRowResult().firstname }
All resources including the ResultSet are closed automatically after the closure is called.
sql
- the sql statementclosure
- called for each row with a GroovyResultSetSQLException
- if a database access error occurspublic void query(String sql, List<Object> params, Closure closure) throws SQLException
ResultSet
object. The given closure is called
with the ResultSet
as its argument.
The query may contain placeholder question marks which match the given list of parameters.
Example usage:
sql.query('select * from PERSON where lastname like ?', ['%a%']) { ResultSet rs -> while (rs.next()) println rs.getString('lastname') }
This method supports named and named ordinal parameters. See the class Javadoc for more details.
All resources including the ResultSet are closed automatically after the closure is called.
sql
- the sql statementparams
- a list of parametersclosure
- called for each row with a GroovyResultSetSQLException
- if a database access error occurspublic void query(String sql, Map map, Closure closure) throws SQLException
query(String, java.util.List, groovy.lang.Closure)
useful when providing the named parameters as a map.sql
- the sql statementmap
- a map containing the named parametersclosure
- called for each row with a GroovyResultSetSQLException
- if a database access error occurspublic void query(Map map, String sql, Closure closure) throws SQLException
query(String, java.util.List, groovy.lang.Closure)
useful when providing the named parameters as named arguments.map
- a map containing the named parameterssql
- the sql statementclosure
- called for each row with a GroovyResultSetSQLException
- if a database access error occurspublic void query(GString gstring, Closure closure) throws SQLException
ResultSet
object. The given closure is called
with the ResultSet
as its argument.
The query may contain GString expressions.
Example usage:
def location = 25 sql.query "select * from PERSON where location_id < $location", { ResultSet rs -> while (rs.next()) println rs.getString('firstname') }
All resources including the ResultSet are closed automatically after the closure is called.
gstring
- a GString containing the SQL query with embedded paramsclosure
- called for each row with a GroovyResultSetSQLException
- if a database access error occursexpand(Object)
public void eachRow(String sql, Closure closure) throws SQLException
GroovyResultSet
which is a ResultSet
that supports accessing the fields using property style notation and ordinal index values.
Example usages:
sql.eachRow("select * from PERSON where firstname like 'S%'") { row -> println "$row.firstname ${row[2]}}" } sql.eachRow "call my_stored_proc_returning_resultset()", { println it.firstname }
Resource handling is performed automatically where appropriate.
sql
- the sql statementclosure
- called for each row with a GroovyResultSetSQLException
- if a database access error occurspublic void eachRow(String sql, int offset, int maxRows, Closure closure) throws SQLException
closure
with each row of the result set starting at
the provided offset
, and including up to maxRows
number of rows.
The row will be a GroovyResultSet
which is a ResultSet
that supports accessing the fields using property style notation and ordinal index values.
Note that the underlying implementation is based on either invoking ResultSet.absolute()
,
or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY
, the ResultSet.next()
method
is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect
on the initial positioning within the result set.
Note that different database and JDBC driver implementations may work differently with respect to this method.
Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY
may be less efficient than a
"scrollable" type.
Resource handling is performed automatically where appropriate.
sql
- the sql statementoffset
- the 1-based offset for the first row to be processedmaxRows
- the maximum number of rows to be processedclosure
- called for each row with a GroovyResultSetSQLException
- if a database access error occurspublic void eachRow(String sql, Closure metaClosure, Closure rowClosure) throws SQLException
rowClosure
with each row of the
result set.
The row will be a GroovyResultSet
which is a ResultSet
that supports accessing the fields using property style notation and ordinal index values.
In addition, the metaClosure
will be called once passing in the
ResultSetMetaData
as argument.
Example usage:
def printColNames = { meta -> (1..meta.columnCount).each { print meta.getColumnLabel(it).padRight(20) } println() } def printRow = { row -> row.toRowResult().values().each{ print it.toString().padRight(20) } println() } sql.eachRow("select * from PERSON", printColNames, printRow)
Resource handling is performed automatically where appropriate.
sql
- the sql statementmetaClosure
- called for meta data (only once after sql execution)rowClosure
- called for each row with a GroovyResultSetSQLException
- if a database access error occurspublic void eachRow(String sql, Closure metaClosure, int offset, int maxRows, Closure rowClosure) throws SQLException
rowClosure
with each row of the result set starting at
the provided offset
, and including up to maxRows
number of rows.
The row will be a GroovyResultSet
which is a ResultSet
that supports accessing the fields using property style notation and ordinal index values.
In addition, the metaClosure
will be called once passing in the
ResultSetMetaData
as argument.
Note that the underlying implementation is based on either invoking ResultSet.absolute()
,
or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY
, the ResultSet.next()
method
is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect
on the initial positioning within the result set.
Note that different database and JDBC driver implementations may work differently with respect to this method.
Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY
may be less efficient than a
"scrollable" type.
Resource handling is performed automatically where appropriate.
sql
- the sql statementoffset
- the 1-based offset for the first row to be processedmaxRows
- the maximum number of rows to be processedmetaClosure
- called for meta data (only once after sql execution)rowClosure
- called for each row with a GroovyResultSetSQLException
- if a database access error occurspublic void eachRow(String sql, List<Object> params, Closure metaClosure, int offset, int maxRows, Closure rowClosure) throws SQLException
rowClosure
with each row of the result set starting at
the provided offset
, and including up to maxRows
number of rows.
The row will be a GroovyResultSet
which is a ResultSet
that supports accessing the fields using property style notation and ordinal index values.
In addition, the metaClosure
will be called once passing in the
ResultSetMetaData
as argument.
The query may contain placeholder question marks which match the given list of parameters.
Note that the underlying implementation is based on either invoking ResultSet.absolute()
,
or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY
, the ResultSet.next()
method
is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect
on the initial positioning within the result set.
Note that different database and JDBC driver implementations may work differently with respect to this method.
Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY
may be less efficient than a
"scrollable" type.
sql
- the sql statementparams
- a list of parametersoffset
- the 1-based offset for the first row to be processedmaxRows
- the maximum number of rows to be processedmetaClosure
- called for meta data (only once after sql execution)rowClosure
- called for each row with a GroovyResultSetSQLException
- if a database access error occurspublic void eachRow(String sql, Map map, Closure metaClosure, int offset, int maxRows, Closure rowClosure) throws SQLException
eachRow(String, java.util.List, groovy.lang.Closure, int, int, groovy.lang.Closure)
allowing the named parameters to be supplied in a map.sql
- the sql statementmap
- a map containing the named parametersoffset
- the 1-based offset for the first row to be processedmaxRows
- the maximum number of rows to be processedmetaClosure
- called for meta data (only once after sql execution)rowClosure
- called for each row with a GroovyResultSetSQLException
- if a database access error occurspublic void eachRow(Map map, String sql, Closure metaClosure, int offset, int maxRows, Closure rowClosure) throws SQLException
eachRow(String, java.util.List, groovy.lang.Closure, int, int, groovy.lang.Closure)
allowing the named parameters to be supplied as named arguments.map
- a map containing the named parameterssql
- the sql statementoffset
- the 1-based offset for the first row to be processedmaxRows
- the maximum number of rows to be processedmetaClosure
- called for meta data (only once after sql execution)rowClosure
- called for each row with a GroovyResultSetSQLException
- if a database access error occurspublic void eachRow(String sql, List<Object> params, Closure metaClosure, Closure rowClosure) throws SQLException
GroovyResultSet
which is a ResultSet
that supports accessing the fields using property style notation and ordinal index values.
In addition, the metaClosure
will be called once passing in the
ResultSetMetaData
as argument.
The query may contain placeholder question marks which match the given list of parameters.
Example usage:
def printColNames = { meta -> (1..meta.columnCount).each { print meta.getColumnLabel(it).padRight(20) } println() } def printRow = { row -> row.toRowResult().values().each{ print it.toString().padRight(20) } println() } sql.eachRow("select * from PERSON where lastname like ?", ['%a%'], printColNames, printRow)
This method supports named and named ordinal parameters. See the class Javadoc for more details.
Resource handling is performed automatically where appropriate.
sql
- the sql statementparams
- a list of parametersmetaClosure
- called for meta data (only once after sql execution)rowClosure
- called for each row with a GroovyResultSetSQLException
- if a database access error occurspublic void eachRow(String sql, Map params, Closure metaClosure, Closure rowClosure) throws SQLException
eachRow(String, java.util.List, groovy.lang.Closure, groovy.lang.Closure)
useful when providing the named parameters as a map.sql
- the sql statementparams
- a map of named parametersmetaClosure
- called for meta data (only once after sql execution)rowClosure
- called for each row with a GroovyResultSetSQLException
- if a database access error occurspublic void eachRow(Map params, String sql, Closure metaClosure, Closure rowClosure) throws SQLException
eachRow(String, java.util.List, groovy.lang.Closure, groovy.lang.Closure)
useful when providing the named parameters as named arguments.params
- a map of named parameterssql
- the sql statementmetaClosure
- called for meta data (only once after sql execution)rowClosure
- called for each row with a GroovyResultSetSQLException
- if a database access error occurspublic void eachRow(String sql, List<Object> params, Closure closure) throws SQLException
GroovyResultSet
which is a ResultSet
that supports accessing the fields using property style notation and ordinal index values.
The query may contain placeholder question marks which match the given list of parameters.
Example usage:
sql.eachRow("select * from PERSON where lastname like ?", ['%a%']) { row -> println "${row[1]} $row.lastname" }
Resource handling is performed automatically where appropriate.
sql
- the sql statementparams
- a list of parametersclosure
- called for each row with a GroovyResultSetSQLException
- if a database access error occurspublic void eachRow(String sql, Map params, Closure closure) throws SQLException
eachRow(String, java.util.List, groovy.lang.Closure)
useful when providing the named parameters as a map.sql
- the sql statementparams
- a map of named parametersclosure
- called for each row with a GroovyResultSetSQLException
- if a database access error occurspublic void eachRow(Map params, String sql, Closure closure) throws SQLException
eachRow(String, java.util.List, groovy.lang.Closure)
useful when providing the named parameters as named arguments.params
- a map of named parameterssql
- the sql statementclosure
- called for each row with a GroovyResultSetSQLException
- if a database access error occurspublic void eachRow(String sql, List<Object> params, int offset, int maxRows, Closure closure) throws SQLException
closure
with each row of the result set starting at
the provided offset
, and including up to maxRows
number of rows.
The row will be a GroovyResultSet
which is a ResultSet
that supports accessing the fields using property style notation and ordinal index values.
The query may contain placeholder question marks which match the given list of parameters.
Note that the underlying implementation is based on either invoking ResultSet.absolute()
,
or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY
, the ResultSet.next()
method
is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect
on the initial positioning within the result set.
Note that different database and JDBC driver implementations may work differently with respect to this method.
Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY
may be less efficient than a
"scrollable" type.
sql
- the sql statementparams
- a list of parametersoffset
- the 1-based offset for the first row to be processedmaxRows
- the maximum number of rows to be processedclosure
- called for each row with a GroovyResultSetSQLException
- if a database access error occurspublic void eachRow(String sql, Map params, int offset, int maxRows, Closure closure) throws SQLException
eachRow(String, java.util.List, int, int, groovy.lang.Closure)
useful when providing the named parameters as a map.sql
- the sql statementparams
- a map of named parametersoffset
- the 1-based offset for the first row to be processedmaxRows
- the maximum number of rows to be processedclosure
- called for each row with a GroovyResultSetSQLException
- if a database access error occurspublic void eachRow(Map params, String sql, int offset, int maxRows, Closure closure) throws SQLException
eachRow(String, java.util.List, int, int, groovy.lang.Closure)
useful when providing the named parameters as named arguments.params
- a map of named parameterssql
- the sql statementoffset
- the 1-based offset for the first row to be processedmaxRows
- the maximum number of rows to be processedclosure
- called for each row with a GroovyResultSetSQLException
- if a database access error occurspublic void eachRow(GString gstring, Closure metaClosure, Closure rowClosure) throws SQLException
GroovyResultSet
which is a ResultSet
that supports accessing the fields using property style notation and ordinal index values.
In addition, the metaClosure
will be called once passing in the
ResultSetMetaData
as argument.
The query may contain GString expressions.
Example usage:
def location = 25 def printColNames = { meta -> (1..meta.columnCount).each { print meta.getColumnLabel(it).padRight(20) } println() } def printRow = { row -> row.toRowResult().values().each{ print it.toString().padRight(20) } println() } sql.eachRow("select * from PERSON where location_id < $location", printColNames, printRow)
Resource handling is performed automatically where appropriate.
gstring
- a GString containing the SQL query with embedded paramsmetaClosure
- called for meta data (only once after sql execution)rowClosure
- called for each row with a GroovyResultSetSQLException
- if a database access error occursexpand(Object)
public void eachRow(GString gstring, Closure metaClosure, int offset, int maxRows, Closure rowClosure) throws SQLException
closure
with each row of the result set starting at
the provided offset
, and including up to maxRows
number of rows.
The row will be a GroovyResultSet
which is a ResultSet
that supports accessing the fields using property style notation and ordinal index values.
In addition, the metaClosure
will be called once passing in the
ResultSetMetaData
as argument.
The query may contain GString expressions.
Note that the underlying implementation is based on either invoking ResultSet.absolute()
,
or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY
, the ResultSet.next()
method
is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect
on the initial positioning within the result set.
Note that different database and JDBC driver implementations may work differently with respect to this method.
Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY
may be less efficient than a
"scrollable" type.
gstring
- a GString containing the SQL query with embedded paramsmetaClosure
- called for meta data (only once after sql execution)offset
- the 1-based offset for the first row to be processedmaxRows
- the maximum number of rows to be processedrowClosure
- called for each row with a GroovyResultSetSQLException
- if a database access error occurspublic void eachRow(GString gstring, int offset, int maxRows, Closure closure) throws SQLException
closure
with each row of the result set starting at
the provided offset
, and including up to maxRows
number of rows.
The row will be a GroovyResultSet
which is a ResultSet
that supports accessing the fields using property style notation and ordinal index values.
The query may contain GString expressions.
Note that the underlying implementation is based on either invoking ResultSet.absolute()
,
or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY
, the ResultSet.next()
method
is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect
on the initial positioning within the result set.
Note that different database and JDBC driver implementations may work differently with respect to this method.
Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY
may be less efficient than a
"scrollable" type.
gstring
- a GString containing the SQL query with embedded paramsoffset
- the 1-based offset for the first row to be processedmaxRows
- the maximum number of rows to be processedclosure
- called for each row with a GroovyResultSetSQLException
- if a database access error occurspublic void eachRow(GString gstring, Closure closure) throws SQLException
GroovyResultSet
which is a ResultSet
that supports accessing the fields using property style notation and ordinal index values.
The query may contain GString expressions.
Example usage:
def location = 25 sql.eachRow("select * from PERSON where location_id < $location") { row -> println row.firstname }
Resource handling is performed automatically where appropriate.
gstring
- a GString containing the SQL query with embedded paramsclosure
- called for each row with a GroovyResultSetSQLException
- if a database access error occursexpand(Object)
public List<GroovyRowResult> rows(String sql) throws SQLException
Example usage:
def ans = sql.rows("select * from PERSON where firstname like 'S%'") println "Found ${ans.size()} rows"
Resource handling is performed automatically where appropriate.
sql
- the SQL statementSQLException
- if a database access error occurspublic List<GroovyRowResult> rows(String sql, int offset, int maxRows) throws SQLException
Note that the underlying implementation is based on either invoking ResultSet.absolute()
,
or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY
, the ResultSet.next()
method
is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect
on the initial positioning within the result set.
Note that different database and JDBC driver implementations may work differently with respect to this method.
Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY
may be less efficient than a
"scrollable" type.
Resource handling is performed automatically where appropriate.
sql
- the SQL statementoffset
- the 1-based offset for the first row to be processedmaxRows
- the maximum number of rows to be processedSQLException
- if a database access error occurspublic List<GroovyRowResult> rows(String sql, Closure metaClosure) throws SQLException
metaClosure
will be called once passing in the
ResultSetMetaData
as argument.
Example usage:
def printNumCols = { meta -> println "Found $meta.columnCount columns" } def ans = sql.rows("select * from PERSON", printNumCols) println "Found ${ans.size()} rows"
Resource handling is performed automatically where appropriate.
sql
- the SQL statementmetaClosure
- called with meta data of the ResultSetSQLException
- if a database access error occurspublic List<GroovyRowResult> rows(String sql, int offset, int maxRows, Closure metaClosure) throws SQLException
metaClosure
will be called once passing in the
ResultSetMetaData
as argument.
Note that the underlying implementation is based on either invoking ResultSet.absolute()
,
or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY
, the ResultSet.next()
method
is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect
on the initial positioning within the result set.
Note that different database and JDBC driver implementations may work differently with respect to this method.
Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY
may be less efficient than a
"scrollable" type.
Resource handling is performed automatically where appropriate.
sql
- the SQL statementoffset
- the 1-based offset for the first row to be processedmaxRows
- the maximum number of rows to be processedmetaClosure
- called for meta data (only once after sql execution)SQLException
- if a database access error occurspublic List<GroovyRowResult> rows(String sql, List<Object> params) throws SQLException
Example usage:
def ans = sql.rows("select * from PERSON where lastname like ?", ['%a%']) println "Found ${ans.size()} rows"
This method supports named and named ordinal parameters by supplying such
parameters in the params
list. See the class Javadoc for more details.
Resource handling is performed automatically where appropriate.
sql
- the SQL statementparams
- a list of parametersSQLException
- if a database access error occurspublic List<GroovyRowResult> rows(Map params, String sql) throws SQLException
rows(String, java.util.List)
useful when providing the named parameters as named arguments.params
- a map containing the named parameterssql
- the SQL statementSQLException
- if a database access error occurspublic List<GroovyRowResult> rows(String sql, List<Object> params, int offset, int maxRows) throws SQLException
Note that the underlying implementation is based on either invoking ResultSet.absolute()
,
or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY
, the ResultSet.next()
method
is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect
on the initial positioning within the result set.
Note that different database and JDBC driver implementations may work differently with respect to this method.
Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY
may be less efficient than a
"scrollable" type.
This method supports named and named ordinal parameters by supplying such
parameters in the params
list. See the class Javadoc for more details.
Resource handling is performed automatically where appropriate.
sql
- the SQL statementparams
- a list of parametersoffset
- the 1-based offset for the first row to be processedmaxRows
- the maximum number of rows to be processedSQLException
- if a database access error occurspublic List<GroovyRowResult> rows(String sql, Map params, int offset, int maxRows) throws SQLException
rows(String, java.util.List, int, int)
useful when providing the named parameters as a map.sql
- the SQL statementparams
- a map of named parametersoffset
- the 1-based offset for the first row to be processedmaxRows
- the maximum number of rows to be processedSQLException
- if a database access error occurspublic List<GroovyRowResult> rows(Map params, String sql, int offset, int maxRows) throws SQLException
rows(String, java.util.List, int, int)
useful when providing the named parameters as named arguments.params
- a map of named parameterssql
- the SQL statementoffset
- the 1-based offset for the first row to be processedmaxRows
- the maximum number of rows to be processedSQLException
- if a database access error occurspublic List<GroovyRowResult> rows(String sql, Object[] params) throws SQLException
This method supports named and named ordinal parameters by supplying such
parameters in the params
array. See the class Javadoc for more details.
An Object array variant of rows(String, List)
.
sql
- the SQL statementparams
- an array of parametersSQLException
- if a database access error occurspublic List<GroovyRowResult> rows(String sql, Object[] params, int offset, int maxRows) throws SQLException
This method supports named and named ordinal parameters by supplying such
parameters in the params
array. See the class Javadoc for more details.
An Object array variant of rows(String, List, int, int)
.
sql
- the SQL statementparams
- an array of parametersoffset
- the 1-based offset for the first row to be processedmaxRows
- the maximum number of rows to be processedSQLException
- if a database access error occurspublic List<GroovyRowResult> rows(String sql, List<Object> params, Closure metaClosure) throws SQLException
metaClosure
will be called once passing in the
ResultSetMetaData
as argument.
The query may contain placeholder question marks which match the given list of parameters.
Example usage:
def printNumCols = { meta -> println "Found $meta.columnCount columns" } def ans = sql.rows("select * from PERSON where lastname like ?", ['%a%'], printNumCols) println "Found ${ans.size()} rows"
This method supports named and named ordinal parameters by supplying such
parameters in the params
list. Here is an example:
def printNumCols = { meta -> println "Found $meta.columnCount columns" } def mapParam = [foo: 'Smith'] def domainParam = new MyDomainClass(bar: 'John') def qry = 'select * from PERSON where lastname=?1.foo and firstname=?2.bar' def ans = sql.rows(qry, [mapParam, domainParam], printNumCols) println "Found ${ans.size()} rows" def qry2 = 'select * from PERSON where firstname=:first and lastname=:last' def ans2 = sql.rows(qry2, [[last:'Smith', first:'John']], printNumCols) println "Found ${ans2.size()} rows"See the class Javadoc for more details.
Resource handling is performed automatically where appropriate.
sql
- the SQL statementparams
- a list of parametersmetaClosure
- called for meta data (only once after sql execution)SQLException
- if a database access error occurspublic List<GroovyRowResult> rows(String sql, Map params, Closure metaClosure) throws SQLException
rows(String, java.util.List, groovy.lang.Closure)
useful when providing the named parameters as a map.sql
- the SQL statementparams
- a map of named parametersmetaClosure
- called for meta data (only once after sql execution)SQLException
- if a database access error occurspublic List<GroovyRowResult> rows(Map params, String sql, Closure metaClosure) throws SQLException
rows(String, java.util.List, groovy.lang.Closure)
useful when providing the named parameters as named arguments.params
- a map of named parameterssql
- the SQL statementmetaClosure
- called for meta data (only once after sql execution)SQLException
- if a database access error occurspublic List<GroovyRowResult> rows(String sql, List<Object> params, int offset, int maxRows, Closure metaClosure) throws SQLException
metaClosure
will be called once passing in the
ResultSetMetaData
as argument.
The query may contain placeholder question marks which match the given list of parameters.
Note that the underlying implementation is based on either invoking ResultSet.absolute()
,
or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY
, the ResultSet.next()
method
is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect
on the initial positioning within the result set.
Note that different database and JDBC driver implementations may work differently with respect to this method.
Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY
may be less efficient than a
"scrollable" type.
This method supports named and named ordinal parameters by supplying such
parameters in the params
list. See the class Javadoc for more details.
Resource handling is performed automatically where appropriate.
sql
- the SQL statementparams
- a list of parametersoffset
- the 1-based offset for the first row to be processedmaxRows
- the maximum number of rows to be processedmetaClosure
- called for meta data (only once after sql execution)SQLException
- if a database access error occurspublic List<GroovyRowResult> rows(String sql, Map params, int offset, int maxRows, Closure metaClosure) throws SQLException
rows(String, java.util.List, int, int, groovy.lang.Closure)
useful when providing the named parameters as a map.sql
- the SQL statementparams
- a map of named parametersoffset
- the 1-based offset for the first row to be processedmaxRows
- the maximum number of rows to be processedmetaClosure
- called for meta data (only once after sql execution)SQLException
- if a database access error occurspublic List<GroovyRowResult> rows(Map params, String sql, int offset, int maxRows, Closure metaClosure) throws SQLException
rows(String, java.util.List, int, int, groovy.lang.Closure)
useful when providing the named parameters as named arguments.params
- a map of named parameterssql
- the SQL statementoffset
- the 1-based offset for the first row to be processedmaxRows
- the maximum number of rows to be processedmetaClosure
- called for meta data (only once after sql execution)SQLException
- if a database access error occurspublic List<GroovyRowResult> rows(GString sql, int offset, int maxRows) throws SQLException
Note that the underlying implementation is based on either invoking ResultSet.absolute()
,
or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY
, the ResultSet.next()
method
is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect
on the initial positioning within the result set.
Note that different database and JDBC driver implementations may work differently with respect to this method.
Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY
may be less efficient than a
"scrollable" type.
Resource handling is performed automatically where appropriate.
sql
- the SQL statementoffset
- the 1-based offset for the first row to be processedmaxRows
- the maximum number of rows to be processedSQLException
- if a database access error occurspublic List<GroovyRowResult> rows(GString gstring) throws SQLException
Example usage:
def location = 25 def ans = sql.rows("select * from PERSON where location_id < $location") println "Found ${ans.size()} rows"
Resource handling is performed automatically where appropriate.
gstring
- a GString containing the SQL query with embedded paramsSQLException
- if a database access error occursexpand(Object)
public List<GroovyRowResult> rows(GString gstring, Closure metaClosure) throws SQLException
metaClosure
will be called once passing in the
ResultSetMetaData
as argument.
The query may contain GString expressions.
Example usage:
def location = 25 def printNumCols = { meta -> println "Found $meta.columnCount columns" } def ans = sql.rows("select * from PERSON where location_id < $location", printNumCols) println "Found ${ans.size()} rows"
Resource handling is performed automatically where appropriate.
gstring
- a GString containing the SQL query with embedded paramsmetaClosure
- called with meta data of the ResultSetSQLException
- if a database access error occursexpand(Object)
public List<GroovyRowResult> rows(GString gstring, int offset, int maxRows, Closure metaClosure) throws SQLException
metaClosure
will be called once passing in the
ResultSetMetaData
as argument.
The query may contain GString expressions.
Note that the underlying implementation is based on either invoking ResultSet.absolute()
,
or if the ResultSet type is ResultSet.TYPE_FORWARD_ONLY
, the ResultSet.next()
method
is invoked equivalently. The first row of a ResultSet is 1, so passing in an offset of 1 or less has no effect
on the initial positioning within the result set.
Note that different database and JDBC driver implementations may work differently with respect to this method.
Specifically, one should expect that ResultSet.TYPE_FORWARD_ONLY
may be less efficient than a
"scrollable" type.
Resource handling is performed automatically where appropriate.
gstring
- the SQL statementoffset
- the 1-based offset for the first row to be processedmaxRows
- the maximum number of rows to be processedmetaClosure
- called for meta data (only once after sql execution)SQLException
- if a database access error occurspublic GroovyRowResult firstRow(String sql) throws SQLException
Example usage:
def ans = sql.firstRow("select * from PERSON where firstname like 'S%'") println ans.firstname
Resource handling is performed automatically where appropriate.
sql
- the SQL statementnull
if no row is foundSQLException
- if a database access error occurspublic GroovyRowResult firstRow(GString gstring) throws SQLException
Example usage:
def location = 25 def ans = sql.firstRow("select * from PERSON where location_id < $location") println ans.firstname
Resource handling is performed automatically where appropriate.
gstring
- a GString containing the SQL query with embedded paramsnull
if no row is foundSQLException
- if a database access error occursexpand(Object)
public GroovyRowResult firstRow(String sql, List<Object> params) throws SQLException
Example usages:
def ans = sql.firstRow("select * from PERSON where lastname like ?", ['%a%']) println ans.firstnameIf your database returns scalar functions as ResultSets, you can also use firstRow to gain access to stored procedure results, e.g. using hsqldb 1.9 RC4:
sql.execute """ create function FullName(p_firstname VARCHAR(40)) returns VARCHAR(80) BEGIN atomic DECLARE ans VARCHAR(80); SET ans = (SELECT firstname || ' ' || lastname FROM PERSON WHERE firstname = p_firstname); RETURN ans; END """ assert sql.firstRow("{call FullName(?)}", ['Sam'])[0] == 'Sam Pullara'
This method supports named and named ordinal parameters by supplying such
parameters in the params
list. See the class Javadoc for more details.
Resource handling is performed automatically where appropriate.
sql
- the SQL statementparams
- a list of parametersnull
if no row is foundSQLException
- if a database access error occurspublic GroovyRowResult firstRow(Map params, String sql) throws SQLException
firstRow(String, java.util.List)
useful when providing the named parameters as named arguments.params
- a map containing the named parameterssql
- the SQL statementnull
if no row is foundSQLException
- if a database access error occurspublic GroovyRowResult firstRow(String sql, Object[] params) throws SQLException
An Object array variant of firstRow(String, List)
.
This method supports named and named ordinal parameters by supplying such
parameters in the params
array. See the class Javadoc for more details.
sql
- the SQL statementparams
- an array of parametersnull
if no row is foundSQLException
- if a database access error occurspublic boolean execute(String sql) throws SQLException
Example usages:
sql.execute "DROP TABLE IF EXISTS person" sql.execute """ CREATE TABLE person ( id INTEGER NOT NULL, firstname VARCHAR(100), lastname VARCHAR(100), location_id INTEGER ) """ sql.execute """ INSERT INTO person (id, firstname, lastname, location_id) VALUES (4, 'Paul', 'King', 40) """ assert sql.updateCount == 1
Resource handling is performed automatically where appropriate.
sql
- the SQL to executetrue
if the first result is a ResultSet
object; false
if it is an update count or there are
no resultsSQLException
- if a database access error occurspublic void execute(String sql, Closure processResults) throws SQLException
Example usages:
boolean first = true sql.execute "{call FindAllByFirst('J')}", { isResultSet, result -> if (first) { first = false assert !isResultSet && result == 0 } else { assert isResultSet && result == [[ID:1, FIRSTNAME:'James', LASTNAME:'Strachan'], [ID:4, FIRSTNAME:'Jean', LASTNAME:'Gabin']] } }
Resource handling is performed automatically where appropriate.
sql
- the SQL to executeprocessResults
- a Closure which will be passed two parameters: either true
plus a list of GroovyRowResult values
derived from statement.getResultSet()
or false
plus the update count from statement.getUpdateCount()
.
The closure will be called for each result produced from executing the SQL.SQLException
- if a database access error occurspublic boolean execute(String sql, List<Object> params) throws SQLException
Example usage:
sql.execute """ insert into PERSON (id, firstname, lastname, location_id) values (?, ?, ?, ?) """, [1, "Guillaume", "Laforge", 10] assert sql.updateCount == 1
This method supports named and named ordinal parameters. See the class Javadoc for more details.
Resource handling is performed automatically where appropriate.
sql
- the SQL statementparams
- a list of parameterstrue
if the first result is a ResultSet
object; false
if it is an update count or there are
no resultsSQLException
- if a database access error occurspublic void execute(String sql, List<Object> params, Closure processResults) throws SQLException
This method supports named and named ordinal parameters. See the class Javadoc for more details.
Resource handling is performed automatically where appropriate.
sql
- the SQL statementparams
- a list of parametersprocessResults
- a Closure which will be passed two parameters: either true
plus a list of GroovyRowResult values
derived from statement.getResultSet()
or false
plus the update count from statement.getUpdateCount()
.
The closure will be called for each result produced from executing the SQL.SQLException
- if a database access error occursexecute(String, Closure)
public boolean execute(Map params, String sql) throws SQLException
execute(String, java.util.List)
useful when providing the named parameters as named arguments.params
- a map containing the named parameterssql
- the SQL statementtrue
if the first result is a ResultSet
object; false
if it is an update count or there are
no resultsSQLException
- if a database access error occurspublic void execute(Map params, String sql, Closure processResults) throws SQLException
execute(String, java.util.List, Closure)
useful when providing the named parameters as named arguments.params
- a map containing the named parameterssql
- the SQL statementprocessResults
- a Closure which will be passed two parameters: either true
plus a list of GroovyRowResult values
derived from statement.getResultSet()
or false
plus the update count from statement.getUpdateCount()
.
The closure will be called for each result produced from executing the SQL.SQLException
- if a database access error occurspublic boolean execute(String sql, Object[] params) throws SQLException
An Object array variant of execute(String, List)
.
This method supports named and named ordinal parameters by supplying such
parameters in the params
array. See the class Javadoc for more details.
sql
- the SQL statementparams
- an array of parameterstrue
if the first result is a ResultSet
object; false
if it is an update count or there are
no resultsSQLException
- if a database access error occurspublic void execute(String sql, Object[] params, Closure processResults) throws SQLException
An Object array variant of execute(String, List, Closure)
.
This method supports named and named ordinal parameters by supplying such
parameters in the params
array. See the class Javadoc for more details.
sql
- the SQL statementparams
- an array of parametersprocessResults
- a Closure which will be passed two parameters: either true
plus a list of GroovyRowResult values
derived from statement.getResultSet()
or false
plus the update count from statement.getUpdateCount()
.
The closure will be called for each result produced from executing the SQL.SQLException
- if a database access error occursexecute(String, List, Closure)
public boolean execute(GString gstring) throws SQLException
Example usage:
def scott = [firstname: "Scott", lastname: "Davis", id: 5, location_id: 50] sql.execute """ insert into PERSON (id, firstname, lastname, location_id) values ($scott.id, $scott.firstname, $scott.lastname, $scott.location_id) """ assert sql.updateCount == 1
Resource handling is performed automatically where appropriate.
gstring
- a GString containing the SQL query with embedded paramstrue
if the first result is a ResultSet
object; false
if it is an update count or there are
no resultsSQLException
- if a database access error occursexpand(Object)
public void execute(GString gstring, Closure processResults) throws SQLException
gstring
- a GString containing the SQL query with embedded paramsprocessResults
- a Closure which will be passed two parameters: either true
plus a list of GroovyRowResult values
derived from statement.getResultSet()
or false
plus the update count from statement.getUpdateCount()
.
The closure will be called for each result produced from executing the SQL.SQLException
- if a database access error occursexpand(Object)
,
execute(String, List, Closure)
public List<List<Object>> executeInsert(String sql) throws SQLException
executeInsert(GString)
for more details.
Resource handling is performed automatically where appropriate.
sql
- The SQL statement to executeSQLException
- if a database access error occurspublic List<List<Object>> executeInsert(String sql, List<Object> params) throws SQLException
executeInsert(GString)
for more details.
This method supports named and named ordinal parameters. See the class Javadoc for more details.
Resource handling is performed automatically where appropriate.
sql
- The SQL statement to executeparams
- The parameter values that will be substituted
into the SQL statement's parameter slotsSQLException
- if a database access error occurspublic List<GroovyRowResult> executeInsert(String sql, List<Object> params, List<String> keyColumnNames) throws SQLException
executeInsert(GString)
for more details.
This method supports named and named ordinal parameters. See the class Javadoc for more details.
Resource handling is performed automatically where appropriate.
sql
- The SQL statement to executeparams
- The parameter values that will be substituted
into the SQL statement's parameter slotskeyColumnNames
- a list of column names indicating the columns that should be returned from the
inserted row or rows (some drivers may be case sensitive, e.g. may require uppercase names)SQLException
- if a database access error occursConnection.prepareStatement(String, String[])
public List<List<Object>> executeInsert(Map params, String sql) throws SQLException
executeInsert(String, java.util.List)
useful when providing the named parameters as named arguments.params
- a map containing the named parameterssql
- The SQL statement to executeSQLException
- if a database access error occurspublic List<GroovyRowResult> executeInsert(Map params, String sql, List<String> keyColumnNames) throws SQLException
executeInsert(String, List, List)
useful when providing the named parameters as named arguments.
This variant allows you to receive the values of any auto-generated columns,
such as an autoincrement ID field (or fields) when you know the column name(s) of the ID field(s).params
- a map containing the named parameterssql
- The SQL statement to executekeyColumnNames
- a list of column names indicating the columns that should be returned from the
inserted row or rows (some drivers may be case sensitive, e.g. may require uppercase names)SQLException
- if a database access error occursConnection.prepareStatement(String, String[])
public List<List<Object>> executeInsert(String sql, Object[] params) throws SQLException
An Object array variant of executeInsert(String, List)
.
This method supports named and named ordinal parameters by supplying such
parameters in the params
array. See the class Javadoc for more details.
sql
- The SQL statement to executeparams
- The parameter values that will be substituted
into the SQL statement's parameter slotsSQLException
- if a database access error occurspublic List<GroovyRowResult> executeInsert(String sql, String[] keyColumnNames) throws SQLException
This method supports named and named ordinal parameters by supplying such
parameters in the params
array. See the class Javadoc for more details.
sql
- The SQL statement to executekeyColumnNames
- an array of column names indicating the columns that should be returned from the
inserted row or rows (some drivers may be case sensitive, e.g. may require uppercase names)SQLException
- if a database access error occurspublic List<GroovyRowResult> executeInsert(String sql, String[] keyColumnNames, Object[] params) throws SQLException
An array variant of executeInsert(String, List, List)
.
This method supports named and named ordinal parameters by supplying such
parameters in the params
array. See the class Javadoc for more details.
sql
- The SQL statement to executekeyColumnNames
- an array of column names indicating the columns that should be returned from the
inserted row or rows (some drivers may be case sensitive, e.g. may require uppercase names)params
- The parameter values that will be substituted
into the SQL statement's parameter slotsSQLException
- if a database access error occurspublic List<List<Object>> executeInsert(GString gstring) throws SQLException
Generated key values can be accessed using
array notation. For example, to return the second auto-generated
column value of the third row, use keys[3][1]
. The
method is designed to be used with SQL INSERT statements, but is
not limited to them.
The standard use for this method is when a table has an autoincrement ID column and you want to know what the ID is for a newly inserted row. In this example, we insert a single row into a table in which the first column contains the autoincrement ID:
def sql = Sql.newInstance("jdbc:mysql://localhost:3306/groovy", "user", "password", "com.mysql.jdbc.Driver") def keys = sql.executeInsert("insert into test_table (INT_DATA, STRING_DATA) " + "VALUES (1, 'Key Largo')") def id = keys[0][0] // 'id' now contains the value of the new row's ID column. // It can be used to update an object representation's // id attribute for example. ...
Resource handling is performed automatically where appropriate.
gstring
- a GString containing the SQL query with embedded paramsSQLException
- if a database access error occursexpand(Object)
public List<GroovyRowResult> executeInsert(GString gstring, List<String> keyColumnNames) throws SQLException
Resource handling is performed automatically where appropriate.
gstring
- a GString containing the SQL query with embedded paramskeyColumnNames
- a list of column names indicating the columns that should be returned from the
inserted row or rows (some drivers may be case sensitive, e.g. may require uppercase names)SQLException
- if a database access error occursConnection.prepareStatement(String, String[])
,
expand(Object)
public int executeUpdate(String sql) throws SQLException
Resource handling is performed automatically where appropriate.
sql
- the SQL to executeSQLException
- if a database access error occurspublic int executeUpdate(String sql, List<Object> params) throws SQLException
This method supports named and named ordinal parameters. See the class Javadoc for more details.
Resource handling is performed automatically where appropriate.
sql
- the SQL statementparams
- a list of parametersSQLException
- if a database access error occurspublic int executeUpdate(Map params, String sql) throws SQLException
executeUpdate(String, java.util.List)
useful when providing the named parameters as named arguments.params
- a map containing the named parameterssql
- the SQL statementSQLException
- if a database access error occurspublic int executeUpdate(String sql, Object[] params) throws SQLException
An Object array variant of executeUpdate(String, List)
.
sql
- the SQL statementparams
- an array of parametersSQLException
- if a database access error occurspublic int executeUpdate(GString gstring) throws SQLException
Resource handling is performed automatically where appropriate.
gstring
- a GString containing the SQL query with embedded paramsSQLException
- if a database access error occursexpand(Object)
public int call(String sql) throws Exception
Example usage (tested with MySQL) - suppose we have the following stored procedure:
sql.execute """ CREATE PROCEDURE HouseSwap(_first1 VARCHAR(50), _first2 VARCHAR(50)) BEGIN DECLARE _loc1 INT; DECLARE _loc2 INT; SELECT location_id into _loc1 FROM PERSON where firstname = _first1; SELECT location_id into _loc2 FROM PERSON where firstname = _first2; UPDATE PERSON set location_id = case firstname when _first1 then _loc2 when _first2 then _loc1 end where (firstname = _first1 OR firstname = _first2); END """then you can invoke the procedure as follows:
def rowsChanged = sql.call("{call HouseSwap('Guillaume', 'Paul')}") assert rowsChanged == 2
sql
- the SQL statementSQLException
- if a database access error occursException
public int call(GString gstring) throws Exception
Example usage - see call(String)
for more details about
creating a HouseSwap(IN name1, IN name2)
stored procedure.
Once created, it can be called like this:
def p1 = 'Paul' def p2 = 'Guillaume' def rowsChanged = sql.call("{call HouseSwap($p1, $p2)}") assert rowsChanged == 2
Resource handling is performed automatically where appropriate.
gstring
- a GString containing the SQL query with embedded paramsSQLException
- if a database access error occursException
expand(Object)
,
call(String)
public int call(String sql, List<Object> params) throws Exception
Example usage - see call(String)
for more details about
creating a HouseSwap(IN name1, IN name2)
stored procedure.
Once created, it can be called like this:
def rowsChanged = sql.call("{call HouseSwap(?, ?)}", ['Guillaume', 'Paul']) assert rowsChanged == 2
Resource handling is performed automatically where appropriate.
sql
- the SQL statementparams
- a list of parametersSQLException
- if a database access error occursException
call(String)
public int call(String sql, Object[] params) throws Exception
An Object array variant of call(String, List)
.
sql
- the SQL statementparams
- an array of parametersSQLException
- if a database access error occursException
call(String)
public void call(String sql, List<Object> params, Closure closure) throws Exception
Example usage - suppose we create a stored procedure (ignore its simplistic implementation):
// Tested with MySql 5.0.75 sql.execute """ CREATE PROCEDURE Hemisphere( IN p_firstname VARCHAR(50), IN p_lastname VARCHAR(50), OUT ans VARCHAR(50)) BEGIN DECLARE loc INT; SELECT location_id into loc FROM PERSON where firstname = p_firstname and lastname = p_lastname; CASE loc WHEN 40 THEN SET ans = 'Southern Hemisphere'; ELSE SET ans = 'Northern Hemisphere'; END CASE; END; """we can now call the stored procedure as follows:
sql.call '{call Hemisphere(?, ?, ?)}', ['Guillaume', 'Laforge', Sql.VARCHAR], { dwells -> println dwells }which will output '
Northern Hemisphere
'.
We can also access stored functions with scalar return values where the return value will be treated as an OUT parameter. Here are examples for various databases for creating such a procedure:
// Tested with MySql 5.0.75 sql.execute """ create function FullName(p_firstname VARCHAR(40)) returns VARCHAR(80) begin declare ans VARCHAR(80); SELECT CONCAT(firstname, ' ', lastname) INTO ans FROM PERSON WHERE firstname = p_firstname; return ans; end """ // Tested with MS SQLServer Express 2008 sql.execute """and here is how you access the stored function for all databases:create function FullName(@firstname VARCHAR(40)) returns VARCHAR(80)
begin declare@ans
VARCHAR(80)SET @ans = (SELECT firstname + ' ' + lastname FROM PERSON WHERE firstname = @firstname)
return@ans
end """ // Tested with Oracle XE 10g sql.execute """ create function FullName(p_firstname VARCHAR) return VARCHAR is ans VARCHAR(80); begin SELECT CONCAT(CONCAT(firstname, ' '), lastname) INTO ans FROM PERSON WHERE firstname = p_firstname; return ans; end; """
sql.call("{? = call FullName(?)}", [Sql.VARCHAR, 'Sam']) { name -> assert name == 'Sam Pullara' }
Resource handling is performed automatically where appropriate.
sql
- the sql statementparams
- a list of parametersclosure
- called for each row with a GroovyResultSetSQLException
- if a database access error occursException
public void call(GString gstring, Closure closure) throws Exception
See call(String, List, Closure)
for more details about
creating a Hemisphere(IN first, IN last, OUT dwells)
stored procedure.
Once created, it can be called like this:
def first = 'Scott' def last = 'Davis' sql.call "{call Hemisphere($first, $last, ${Sql.VARCHAR})}", { dwells -> println dwells }
As another example, see call(String, List, Closure)
for more details about
creating a FullName(IN first)
stored function.
Once created, it can be called like this:
def first = 'Sam' sql.call("{$Sql.VARCHAR = call FullName($first)}") { name -> assert name == 'Sam Pullara' }
Resource handling is performed automatically where appropriate.
gstring
- a GString containing the SQL query with embedded paramsclosure
- called for each row with a GroovyResultSetSQLException
- if a database access error occursException
call(String, List, Closure)
,
expand(Object)
public List<GroovyRowResult> callWithRows(GString gstring, Closure closure) throws SQLException
Use this when calling a stored procedure that utilizes both output parameters and returns a single ResultSet.
Once created, the stored procedure can be called like this:
def first = 'Jeff' def last = 'Sheets' def rows = sql.callWithRows "{call Hemisphere2($first, $last, ${Sql.VARCHAR})}", { dwells -> println dwells }
Resource handling is performed automatically where appropriate.
gstring
- a GString containing the SQL query with embedded paramsclosure
- called once with all out parameter resultsSQLException
- if a database access error occurscallWithRows(String, List, Closure)
public List<GroovyRowResult> callWithRows(String sql, List<Object> params, Closure closure) throws SQLException
Use this when calling a stored procedure that utilizes both output parameters and returns a single ResultSet.
Once created, the stored procedure can be called like this:
def rows = sql.callWithRows '{call Hemisphere2(?, ?, ?)}', ['Guillaume', 'Laforge', Sql.VARCHAR], { dwells -> println dwells }
Resource handling is performed automatically where appropriate.
sql
- the sql statementparams
- a list of parametersclosure
- called once with all out parameter resultsSQLException
- if a database access error occurscallWithRows(GString, Closure)
public List<List<GroovyRowResult>> callWithAllRows(GString gstring, Closure closure) throws SQLException
Use this when calling a stored procedure that utilizes both output parameters and returns multiple ResultSets.
Once created, the stored procedure can be called like this:
def first = 'Jeff' def last = 'Sheets' def rowsList = sql.callWithAllRows "{call Hemisphere2($first, $last, ${Sql.VARCHAR})}", { dwells -> println dwells }
Resource handling is performed automatically where appropriate.
gstring
- a GString containing the SQL query with embedded paramsclosure
- called once with all out parameter resultsSQLException
- if a database access error occurscallWithAllRows(String, List, Closure)
public List<List<GroovyRowResult>> callWithAllRows(String sql, List<Object> params, Closure closure) throws SQLException
Use this when calling a stored procedure that utilizes both output parameters and returns multiple ResultSets.
Once created, the stored procedure can be called like this:
def rowsList = sql.callWithAllRows '{call Hemisphere2(?, ?, ?)}', ['Guillaume', 'Laforge', Sql.VARCHAR], { dwells -> println dwells }
Resource handling is performed automatically where appropriate.
sql
- the sql statementparams
- a list of parametersclosure
- called once with all out parameter resultsSQLException
- if a database access error occurscallWithRows(GString, Closure)
protected List<List<GroovyRowResult>> callWithRows(String sql, List<Object> params, int processResultsSets, Closure closure) throws SQLException
Performs a stored procedure call with the given parameters, calling the closure once with all result objects, and also returning the rows of the ResultSet(s) (if processResultSets is set to Sql.FIRST_RESULT_SET, Sql.ALL_RESULT_SETS)
Main purpose of processResultSets param is to retain original call() method performance when this is set to Sql.NO_RESULT_SETS
Resource handling is performed automatically where appropriate.
sql
- the sql statementparams
- a list of parametersprocessResultsSets
- the result sets to process, either Sql.NO_RESULT_SETS, Sql.FIRST_RESULT_SET, or Sql.ALL_RESULT_SETSclosure
- called once with all out parameter resultsSQLException
- if a database access error occurscallWithRows(String, List, Closure)
public void close()
public DataSource getDataSource()
public void commit() throws SQLException
SQLException
- if a database access error occurspublic void rollback() throws SQLException
SQLException
- if a database access error occurspublic int getUpdateCount()
public Connection getConnection()
public void withStatement(Closure configureStatement)
sql.withStatement{ stmt -> stmt.maxRows = 10 } def firstTenRows = sql.rows("select * from table")
configureStatement
- the closurepublic void setCacheStatements(boolean cacheStatements)
cacheStatements
- the new valuepublic boolean isCacheStatements()
public void cacheConnection(Closure closure) throws SQLException
closure
- the given closureSQLException
- if a database error occurspublic void withTransaction(Closure closure) throws SQLException
closure
- the given closureSQLException
- if a database error occurspublic boolean isWithinBatch()
public int[] withBatch(Closure closure) throws SQLException
This means that executeBatch()
will be called automatically after the withBatch
closure has finished but may be called explicitly if desired as well for more fine-grained
partitioning of the batch.
The closure will be called with a single argument; the database
statement (actually a BatchingStatementWrapper
helper object)
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 ...") ... }For integrity and performance reasons, you may wish to consider executing your batch command(s) within a transaction:
sql.withTransaction { def result1 = sql.withBatch { ... } ... }
closure
- the closure containing batch and optionally other statementsSQLException
- 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.withBatch(int, Closure)
public int[] withBatch(int batchSize, Closure closure) throws SQLException
After every batchSize
addBatch(sqlBatchOperation)
operations, automatically calls an executeBatch()
operation to "chunk" up the database operations
into partitions. Though not normally needed, you can also explicitly call executeBatch()
which
after executing the current batch, resets the batch count back to zero.
The closure will be called with a single argument; the database statement
(actually a BatchingStatementWrapper
helper object)
associated with this batch.
Use it like this for batchSize of 20:
def updateCounts = sql.withBatch(20) { stmt -> stmt.addBatch("insert into TABLENAME ...") stmt.addBatch("insert into TABLENAME ...") stmt.addBatch("insert into TABLENAME ...") ... }For integrity and performance reasons, you may wish to consider executing your batch command(s) within a transaction:
sql.withTransaction { def result1 = sql.withBatch { ... } ... }
batchSize
- partition the batch into batchSize pieces, i.e. after batchSize
addBatch()
invocations, call executeBatch()
automatically;
0 means manual calls to executeBatch are requiredclosure
- the closure containing batch and optionally other statementsSQLException
- 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.withBatch(Closure)
,
BatchingStatementWrapper
,
Statement
public int[] withBatch(String sql, Closure closure) throws SQLException
This means that executeBatch()
will be called automatically after the withBatch
closure has finished but may be called explicitly if desired as well for more fine-grained
partitioning of the batch.
The closure will be called with a single argument; the prepared
statement (actually a BatchingPreparedStatementWrapper
helper object)
associated with this batch.
An example:
def updateCounts = sql.withBatch('insert into TABLENAME(a, b, c) values (?, ?, ?)') { ps -> ps.addBatch([10, 12, 5]) ps.addBatch([7, 3, 98]) ps.addBatch(22, 67, 11) def partialUpdateCounts = ps.executeBatch() // optional interim batching ps.addBatch(30, 40, 50) ... }For integrity and performance reasons, you may wish to consider executing your batch command(s) within a transaction:
sql.withTransaction { def result1 = sql.withBatch { ... } ... }
sql
- batch update statementclosure
- the closure containing batch statements (to bind parameters) and optionally other statementsSQLException
- 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.withBatch(int, String, Closure)
,
BatchingPreparedStatementWrapper
,
PreparedStatement
public int[] withBatch(int batchSize, String sql, Closure closure) throws SQLException
After every batchSize
addBatch(params)
operations, automatically calls an executeBatch()
operation to "chunk" up the database operations
into partitions. Though not normally needed, you can also explicitly call executeBatch()
which
after executing the current batch, resets the batch count back to zero.
The closure will be called with a single argument; the prepared
statement (actually a BatchingPreparedStatementWrapper
helper object)
associated with this batch.
Below is an example using a batchSize of 20:
def updateCounts = sql.withBatch(20, 'insert into TABLENAME(a, b, c) values (?, ?, ?)') { ps -> ps.addBatch(10, 12, 5) // varargs style ps.addBatch([7, 3, 98]) // list ps.addBatch([22, 67, 11]) ... }Named parameters (into maps or domain objects) are also supported:
def updateCounts = sql.withBatch(20, 'insert into TABLENAME(a, b, c) values (:foo, :bar, :baz)') { ps -> ps.addBatch([foo:10, bar:12, baz:5]) // map ps.addBatch(foo:7, bar:3, baz:98) // Groovy named args allow outer brackets to be dropped ... }Named ordinal parameters (into maps or domain objects) are also supported:
def updateCounts = sql.withBatch(20, 'insert into TABLENAME(a, b, c) values (?1.foo, ?2.bar, ?2.baz)') { ps -> ps.addBatch([[foo:22], [bar:67, baz:11]]) // list of maps or domain objects ps.addBatch([foo:10], [bar:12, baz:5]) // varargs allows outer brackets to be dropped ps.addBatch([foo:7], [bar:3, baz:98]) ... } // swap to batch size of 5 and illustrate simple and domain object cases ... class Person { String first, last } def updateCounts2 = sql.withBatch(5, 'insert into PERSON(id, first, last) values (?1, ?2.first, ?2.last)') { ps -> ps.addBatch(1, new Person(first:'Peter', last:'Pan')) ps.addBatch(2, new Person(first:'Snow', last:'White')) ... }For integrity and performance reasons, you may wish to consider executing your batch command(s) within a transaction:
sql.withTransaction { def result1 = sql.withBatch { ... } ... }
batchSize
- partition the batch into batchSize pieces, i.e. after batchSize
addBatch()
invocations, call executeBatch()
automatically;
0 means manual calls to executeBatch are required if additional partitioning of the batch is requiredsql
- batch update statementclosure
- the closure containing batch statements (to bind parameters) and optionally other statementsSQLException
- 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.BatchingPreparedStatementWrapper
,
PreparedStatement
public void cacheStatements(Closure closure) throws SQLException
closure
- the given closureSQLException
- if a database error occurssetCacheStatements(boolean)
protected final ResultSet executeQuery(String sql) throws SQLException
sql
- query to executeSQLException
- if a database error occursprotected final ResultSet executePreparedQuery(String sql, List<Object> params) throws SQLException
sql
- query to executeparams
- parameters matching question mark placeholders in the querySQLException
- if a database error occursprotected List<GroovyRowResult> asList(String sql, ResultSet rs) throws SQLException
sql
- query to executers
- the ResultSet to processSQLException
- if a database error occursprotected List<GroovyRowResult> asList(String sql, ResultSet rs, Closure metaClosure) throws SQLException
sql
- query to executers
- the ResultSet to processmetaClosure
- called for meta data (only once after sql execution)SQLException
- if a database error occursprotected List<GroovyRowResult> asList(String sql, ResultSet rs, int offset, int maxRows, Closure metaClosure) throws SQLException
SQLException
protected String asSql(GString gstring, List<Object> values)
gstring
- a GString containing the SQL query with embedded paramsvalues
- the values to embedexpand(Object)
protected String nullify(String sql)
sql
- the SQL statementprotected int findWhereKeyword(String sql)
sql
- the SQL statementprotected List<Object> getParameters(GString gstring)
gstring
- a GString containing the SQL query with embedded paramsexpand(Object)
protected void setParameters(List<Object> params, PreparedStatement statement) throws SQLException
setObject
.params
- the parameters to appendstatement
- the statementSQLException
- if a database access error occursprotected void setObject(PreparedStatement statement, int i, Object value) throws SQLException
statement
- the statement of interesti
- the index of the object of interestvalue
- the new object valueSQLException
- if a database access error occursprotected Connection createConnection() throws SQLException
SQLException
- if a SQL error occursprotected void closeResources(Connection connection, Statement statement, ResultSet results)
connection
- the connection to closestatement
- the statement to closeresults
- the results to closeprotected void closeResources(Connection connection, Statement statement)
connection
- the connection to closestatement
- the statement to closeprotected void closeResources(Connection connection)
connection
- the connection to closeprotected void configure(Statement statement)
statement
- the statement to configurepublic SqlWithParams checkForNamedParams(String sql, List<Object> params)
@Deprecated public SqlWithParams preCheckForNamedParams(String sql)
buildSqlWithIndexedProps(String)
insteadprotected SqlWithParams buildSqlWithIndexedProps(String sql)
sql
- the sql statement to processSqlWithParams
instance containing the parsed sql
and parameters containing the indexed location and property
name of parameters or null
if no parsing of
the sql was performed.public List<Object> getUpdatedParams(List<Object> params, List<Tuple> indexPropList)
public boolean isCacheNamedQueries()
public void setCacheNamedQueries(boolean cacheNamedQueries)
cacheNamedQueries
- the new valuepublic boolean isEnableNamedQueries()
public void setEnableNamedQueries(boolean enableNamedQueries)
enableNamedQueries
- the new valueprotected Sql.AbstractQueryCommand createQueryCommand(String sql)
AbstractQueryCommand q = createQueryCommand("update TABLE set count = 0) where count is null"); try { ResultSet rs = q.execute(); return asList(rs); } finally { q.closeResources(); }
sql
- statement to be executedprotected Sql.AbstractQueryCommand createPreparedQueryCommand(String sql, List<Object> queryParams)
sql
- statement to be executed, including optional parameter placeholders (?)queryParams
- List of parameter values corresponding to parameter placeholderscreateQueryCommand(String)
protected void setInternalConnection(Connection conn)
conn
- the connection that is about to be used by a command