Class Sql
- All Implemented Interfaces:
AutoCloseable
- Direct Known Subclasses:
DataSet
Typical usage
First you need to set up your sql instance. There are several constructors and a fewnewInstance
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.
Avoiding SQL injection
If you find yourself creating queries based on any kind of input from the user or a 3rd party application you might wish to avoid the pure string method variants in this class. While this is safe: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.
Named and named ordinal parameters
Several of the methods in this class (ones which have a String-based sql query and params in a List<Object> or Object[] or Map) support named or named ordinal parameters. These methods are useful for queries with large numbers of parameters - though the GString variations are often preferred in such cases too. Reminder: when you see a variant with Object[] as the type of the last parameter, Groovy allows vararg style parameters so you don't explicitly need to create an Object[] and if the first parameter is of type Map, Groovy supports named arguments - examples of both are contained in the examples below.Named parameter queries use placeholder values in the query String. Two forms are supported ':propname1' and '?.propname2'. For these variations, a single model object is supplied in the parameter list/array/map. The propname refers to a property of that model object. The model object could be a map, Expando or domain class instance. Here are some examples:
// using rows() with a named parameter with the parameter supplied in a map println sql.rows('select * from PROJECT where name=:foo', [foo:'Gradle']) // as above for eachRow() sql.eachRow('select * from PROJECT where name=:foo', [foo:'Gradle']) { // process row } // an example using both the ':' and '?.' variants of the notation println sql.rows('select * from PROJECT where name=:foo and id=?.bar', [foo:'Gradle', bar:40]) // as above but using Groovy's named arguments instead of an explicit map println sql.rows('select * from PROJECT where name=:foo and id=?.bar', foo:'Gradle', bar:40) // an example showing rows() with a domain object instead of a map class MyDomainClass { def baz = 'Griffon' } println sql.rows('select * from PROJECT where name=?.baz', new MyDomainClass()) // as above for eachRow() with the domain object supplied in a list sql.eachRow('select * from PROJECT where name=?.baz', [new MyDomainClass()]) { // process row }Named ordinal parameter queries have multiple model objects with the index number (starting at 1) also supplied in the placeholder. Only the question mark variation of placeholder is supported. Here are some examples:
// an example showing the model objects as vararg style parameters (since rows() has an Object[] variant) println sql.rows("select * from PROJECT where name=?1.baz and id=?2.num", new MyDomainClass(), [num:30]) // an example showing the model objects (one domain class and one map) provided in a list sql.eachRow("select * from PROJECT where name=?1.baz and id=?2.num", [new MyDomainClass(), [num:30]]) { // do something with row }
More details
See the method and constructor JavaDoc for more details.For advanced usage, the class provides numerous extension points for overriding the facade behavior associated with the various aspects of managing the interaction with the underlying database.
This class is not thread-safe.
-
Nested Class Summary
-
Field Summary
Modifier and TypeFieldDescriptionstatic final int
static final OutParameter
static final OutParameter
static final OutParameter
static final OutParameter
static final OutParameter
static final OutParameter
static final OutParameter
static final OutParameter
static final OutParameter
static final OutParameter
static final OutParameter
static final OutParameter
static final OutParameter
static final int
static final OutParameter
static final OutParameter
static final OutParameter
protected static final Logger
Hook to allow derived classes to access the logstatic final OutParameter
static final OutParameter
static final int
static final OutParameter
static final OutParameter
static final OutParameter
static final OutParameter
static final OutParameter
static final OutParameter
static final OutParameter
static final OutParameter
static final OutParameter
static final OutParameter
static final OutParameter
static final OutParameter
-
Constructor Summary
ConstructorDescriptionSql
(Connection connection) Constructs an SQL instance using the given Connection.Sql
(DataSource dataSource) Constructs an SQL instance using the given DataSource. -
Method Summary
Modifier and TypeMethodDescriptionstatic InParameter
protected List<GroovyRowResult>
Hook to allow derived classes to override list of result collection behavior.protected List<GroovyRowResult>
protected List<GroovyRowResult>
Hook to allow derived classes to override list of result collection behavior.protected String
Hook to allow derived classes to override sql generation from GStrings.static InParameter
static InParameter
static InParameter
static InParameter
static InParameter
protected SqlWithParams
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
Performs a stored procedure call with the given embedded parameters.void
Performs a stored procedure call with the given parameters, calling the closure once with all result objects.int
Performs a stored procedure call.int
Performs a stored procedure call with the given parameters.int
Performs a stored procedure call with the given parameters.void
Performs a stored procedure call with the given parameters.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).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).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.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.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.static InParameter
checkForNamedParams
(String sql, List<Object> params) protected void
Provides a hook for derived classes to be able to examine JDBC statements before cleanup/closing.static InParameter
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
Provides a hook for derived classes to be able to configure JDBC statements.protected Connection
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
static InParameter
static InParameter
static InParameter
static InParameter
void
Performs the given SQL query calling the givenclosure
with each row of the result set starting at the providedoffset
, and including up tomaxRows
number of rows.void
Performs the given SQL query calling the given Closure with each row of the result set.void
Performs the given SQL query calling the givenclosure
with each row of the result set starting at the providedoffset
, and including up tomaxRows
number of rows.void
Performs the given SQL query calling the given Closure with each row of the result set.void
Performs the given SQL query calling the givenclosure
with each row of the result set starting at the providedoffset
, and including up tomaxRows
number of rows.void
Performs the given SQL query calling the given Closure with each row of the result set.void
Performs the given SQL query calling the givenrowClosure
with each row of the result set starting at the providedoffset
, and including up tomaxRows
number of rows.void
Performs the given SQL query calling the givenrowClosure
with each row of the result set.void
Performs the given SQL query calling the givenclosure
with each row of the result set starting at the providedoffset
, and including up tomaxRows
number of rows.void
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 givenrowClosure
with each row of the result set starting at the providedoffset
, and including up tomaxRows
number of rows.void
Performs the given SQL query calling the given Closure with each row of the result set.void
A variant ofeachRow(String, java.util.List, int, int, groovy.lang.Closure)
useful when providing the named parameters as a map.void
A variant ofeachRow(String, java.util.List, groovy.lang.Closure)
useful when providing the named parameters as a map.void
A variant ofeachRow(String, java.util.List, groovy.lang.Closure, int, int, groovy.lang.Closure)
allowing the named parameters to be supplied in a map.void
A variant ofeachRow(String, java.util.List, groovy.lang.Closure, groovy.lang.Closure)
useful when providing the named parameters as a map.void
A variant ofeachRow(String, java.util.List, int, int, groovy.lang.Closure)
useful when providing the named parameters as named arguments.void
A variant ofeachRow(String, java.util.List, groovy.lang.Closure)
useful when providing the named parameters as named arguments.void
A variant ofeachRow(String, java.util.List, groovy.lang.Closure, int, int, groovy.lang.Closure)
allowing the named parameters to be supplied as named arguments.void
A variant ofeachRow(String, java.util.List, groovy.lang.Closure, groovy.lang.Closure)
useful when providing the named parameters as named arguments.boolean
Executes the given SQL with embedded expressions inside.void
Executes the given SQL with embedded expressions inside.boolean
Executes the given piece of SQL.void
Executes the given piece of SQL.boolean
Executes the given piece of SQL with parameters.void
Executes the given piece of SQL with parameters.boolean
Executes the given piece of SQL with parameters.void
Executes the given piece of SQL with parameters.boolean
A variant ofexecute(String, java.util.List)
useful when providing the named parameters as named arguments.void
A variant ofexecute(String, java.util.List, Closure)
useful when providing the named parameters as named arguments.executeInsert
(GString gstring) Executes the given SQL statement (typically an INSERT statement).executeInsert
(GString gstring, List<String> keyColumnNames) Executes the given SQL statement (typically an INSERT statement).executeInsert
(String sql) Executes the given SQL statement (typically an INSERT statement).executeInsert
(String sql, Object[] params) Executes the given SQL statement (typically an INSERT statement).executeInsert
(String sql, String[] keyColumnNames) Executes the given SQL statement (typically an INSERT statement).executeInsert
(String sql, String[] keyColumnNames, Object[] params) Executes the given SQL statement (typically an INSERT statement).executeInsert
(String sql, List<Object> params) Executes the given SQL statement (typically an INSERT statement).Executes the given SQL statement (typically an INSERT statement).executeInsert
(Map params, String sql) A variant ofexecuteInsert(String, java.util.List)
useful when providing the named parameters as named arguments.executeInsert
(Map params, String sql, List<String> keyColumnNames) A variant ofexecuteInsert(String, List, List)
useful when providing the named parameters as named arguments.protected final 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 final 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
(String sql) Executes the given SQL update.int
executeUpdate
(String sql, Object[] params) Executes the given SQL update with parameters.int
executeUpdate
(String sql, List<Object> params) Executes the given SQL update with parameters.int
executeUpdate
(Map params, String sql) A variant ofexecuteUpdate(String, java.util.List)
useful when providing the named parameters as named arguments.static ExpandedVariable
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.Performs the given SQL query and return the first row of the result set.Performs the given SQL query and return the first row of the result set.Performs the given SQL query and return the first row of the result set.Performs the given SQL query and return the first row of the result set.A variant offirstRow(String, java.util.List)
useful when providing the named parameters as named arguments.static InParameter
If this instance was created with a single Connection then the connection is returned.getParameters
(GString gstring) Hook to allow derived classes to override behavior associated with extracting params from a GString.int
Gets the resultSetConcurrency for statements created using the connection.int
Gets the resultSetHoldability for statements created using the connection.int
Gets the resultSetType for statements created using the connection.int
getUpdatedParams
(List<Object> params, List<Tuple> indexPropList) static InParameter
Create a new InParameterstatic InOutParameter
inout
(InParameter in) Create an inout parameter using this in parameter.static InParameter
boolean
boolean
boolean
boolean
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 loadersstatic InParameter
LONGVARBINARY
(Object value) static InParameter
LONGVARCHAR
(Object value) static Sql
newInstance
(String url) Creates a new Sql instance given a JDBC connection URL.static Sql
newInstance
(String url, 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 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
(Map<String, Object> args) Creates a new Sql instance given parameters in a Map.static InParameter
protected String
Hook to allow derived classes to override null handling.static InParameter
static InParameter
static OutParameter
out
(int type) Create a new OutParameterDeprecated.void
Performs the given SQL query, which should return a singleResultSet
object.void
Performs the given SQL query, which should return a singleResultSet
object.void
Performs the given SQL query, which should return a singleResultSet
object.void
A variant ofquery(String, java.util.List, groovy.lang.Closure)
useful when providing the named parameters as a map.void
A variant ofquery(String, java.util.List, groovy.lang.Closure)
useful when providing the named parameters as named arguments.static InParameter
static InParameter
static ResultSetOutParameter
resultSet
(int type) Create a new ResultSetOutParametervoid
rollback()
If this SQL object was created with a Connection then this method rolls back the connection.Performs the given SQL query and return the rows of the result set.Performs the given SQL query and return a "page" of rows from the result set.Performs the given SQL query and return a "page" of rows from the result set.Performs the given SQL query and return the rows of the result set.Performs the given SQL query and return the rows of the result set.Performs the given SQL query and return a "page" of rows from the result set.Performs the given SQL query and return a "page" of rows from the result set.Performs the given SQL query and return the rows of the result set.Performs the given SQL query and return the rows of the result set.Performs the given SQL query and return the rows of the result set.Performs the given SQL query and return the rows of the result set.Performs the given SQL query and return a "page" of rows from the result set.Performs the given SQL query and return a "page" of rows from the result set.Performs the given SQL query and return the rows of the result set.A variant ofrows(String, java.util.List, int, int)
useful when providing the named parameters as a map.A variant ofrows(String, java.util.List, int, int, groovy.lang.Closure)
useful when providing the named parameters as a map.A variant ofrows(String, java.util.List, groovy.lang.Closure)
useful when providing the named parameters as a map.A variant ofrows(String, java.util.List)
useful when providing the named parameters as named arguments.A variant ofrows(String, java.util.List, int, int)
useful when providing the named parameters as named arguments.A variant ofrows(String, java.util.List, int, int, groovy.lang.Closure)
useful when providing the named parameters as named arguments.A variant ofrows(String, java.util.List, groovy.lang.Closure)
useful when providing the named parameters as named arguments.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
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
static InParameter
static InParameter
static InParameter
static InParameter
static InParameter
static InParameter
int[]
Performs the closure (containing batch operations) within a batch using a given batch size.int[]
Performs the closure (containing batch operations specific to an associated prepared statement) within a batch using a given batch size.int[]
Performs the closure (containing batch operations) within a batch.int[]
Performs the closure (containing batch operations specific to an associated prepared statement) within a batch.void
withCleanupStatement
(Closure cleanupStatement) Allows a closure to be passed in which acts as a hook for JDBC statements before they are closed.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, 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
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
(Map<String, Object> args, Closure c) Invokes a closure passing it a new Sql instance created from the given map of arguments.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.
-
Field Details
-
LOG
Hook to allow derived classes to access the log -
ARRAY
-
BIGINT
-
BINARY
-
BIT
-
BLOB
-
BOOLEAN
-
CHAR
-
CLOB
-
DATALINK
-
DATE
-
DECIMAL
-
DISTINCT
-
DOUBLE
-
FLOAT
-
INTEGER
-
JAVA_OBJECT
-
LONGVARBINARY
-
LONGVARCHAR
-
NULL
-
NUMERIC
-
OTHER
-
REAL
-
REF
-
SMALLINT
-
STRUCT
-
TIME
-
TIMESTAMP
-
TINYINT
-
VARBINARY
-
VARCHAR
-
NO_RESULT_SETS
public static final int NO_RESULT_SETS- See Also:
-
FIRST_RESULT_SET
public static final int FIRST_RESULT_SET- See Also:
-
ALL_RESULT_SETS
public static final int ALL_RESULT_SETS- See Also:
-
-
Constructor Details
-
Sql
Constructs an SQL instance using the given DataSource. Each operation will use a Connection from the DataSource pool and close it when the operation is completed putting it back into the pool.- Parameters:
dataSource
- the DataSource to use
-
Sql
Constructs an SQL instance using the given Connection. It is the caller's responsibility to close the Connection after the Sql instance has been used. Depending on which features you are using, you may be able to do this on the connection object directly but the preferred approach is to call theclose()
method which will close the connection but also free any cached resources.- Parameters:
connection
- the Connection to use
-
Sql
-
-
Method Details
-
newInstance
Creates a new Sql instance given a JDBC connection URL.- Parameters:
url
- a database url of the formjdbc:subprotocol:subname
- Returns:
- a new Sql instance with a connection
- Throws:
SQLException
- if a database access error occurs
-
withInstance
public static void withInstance(String url, @ClosureParams(value=SimpleType.class,options="groovy.sql.Sql") Closure c) throws SQLException Invokes a closure passing it a new Sql instance created from the given JDBC connection URL. The created connection will be closed if required.- Parameters:
url
- a database url of the formjdbc:subprotocol:subname
c
- the Closure to call- Throws:
SQLException
- if a database access error occurs- See Also:
-
newInstance
Creates a new Sql instance given a JDBC connection URL and some properties.- Parameters:
url
- a database url of the formjdbc:subprotocol:subname
properties
- a list of arbitrary string tag/value pairs as connection arguments; normally at least a "user" and "password" property should be included- Returns:
- a new Sql instance with a connection
- Throws:
SQLException
- if a database access error occurs
-
withInstance
public static void withInstance(String url, Properties properties, @ClosureParams(value=SimpleType.class,options="groovy.sql.Sql") Closure c) throws SQLException Invokes a closure passing it a new Sql instance created from the given JDBC connection URL and properties. The created connection will be closed if required.- Parameters:
url
- a database url of the formjdbc: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 call- Throws:
SQLException
- if a database access error occurs- See Also:
-
newInstance
public static Sql newInstance(String url, Properties properties, String driverClassName) throws SQLException, ClassNotFoundException Creates a new Sql instance given a JDBC connection URL, some properties and a driver class name.- Parameters:
url
- a database url of the formjdbc: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 class- Returns:
- a new Sql instance with a connection
- Throws:
SQLException
- if a database access error occursClassNotFoundException
- if the driver class cannot be found or loaded
-
withInstance
public static void withInstance(String url, Properties properties, String driverClassName, @ClosureParams(value=SimpleType.class,options="groovy.sql.Sql") Closure c) throws SQLException, ClassNotFoundException Invokes a closure passing it a new Sql instance created from the given JDBC connection URL, properties and driver classname. The created connection will be closed if required.- Parameters:
url
- a database url of the formjdbc: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 call- Throws:
SQLException
- if a database access error occursClassNotFoundException
- if the driver class cannot be found or loaded- See Also:
-
newInstance
Creates a new Sql instance given a JDBC connection URL, a username and a password.- Parameters:
url
- a database url of the formjdbc:subprotocol:subname
user
- the database user on whose behalf the connection is being madepassword
- the user's password- Returns:
- a new Sql instance with a connection
- Throws:
SQLException
- if a database access error occurs
-
withInstance
public static void withInstance(String url, String user, String password, @ClosureParams(value=SimpleType.class,options="groovy.sql.Sql") Closure c) throws SQLException Invokes a closure passing it a new Sql instance created from the given JDBC connection URL, user and password. The created connection will be closed if required.- Parameters:
url
- a database url of the formjdbc:subprotocol:subname
user
- the database user on whose behalf the connection is being madepassword
- the user's passwordc
- the Closure to call- Throws:
SQLException
- if a database access error occurs- See Also:
-
newInstance
public static Sql newInstance(String url, String user, String password, String driverClassName) throws SQLException, ClassNotFoundException Creates a new Sql instance given a JDBC connection URL, a username, a password and a driver class name.- Parameters:
url
- a database url of the formjdbc: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 class- Returns:
- a new Sql instance with a connection
- Throws:
SQLException
- if a database access error occursClassNotFoundException
- if the driver class cannot be found or loaded
-
withInstance
public static void withInstance(String url, String user, String password, String driverClassName, @ClosureParams(value=SimpleType.class,options="groovy.sql.Sql") Closure c) throws SQLException, ClassNotFoundException Invokes a closure passing it a new Sql instance created from the given JDBC connection URL. The created connection will be closed if required.- Parameters:
url
- a database url of the formjdbc: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 call- Throws:
SQLException
- if a database access error occursClassNotFoundException
- if the driver class cannot be found or loaded- See Also:
-
newInstance
public static Sql newInstance(String url, String driverClassName) throws SQLException, ClassNotFoundException Creates a new Sql instance given a JDBC connection URL and a driver class name.- Parameters:
url
- a database url of the formjdbc:subprotocol:subname
driverClassName
- the fully qualified class name of the driver class- Returns:
- a new Sql instance with a connection
- Throws:
SQLException
- if a database access error occursClassNotFoundException
- if the driver class cannot be found or loaded
-
withInstance
public static void withInstance(String url, String driverClassName, @ClosureParams(value=SimpleType.class,options="groovy.sql.Sql") Closure c) throws SQLException, ClassNotFoundException Invokes a closure passing it a new Sql instance created from the given JDBC connection URL. The created connection will be closed if required.- Parameters:
url
- a database url of the formjdbc:subprotocol:subname
driverClassName
- the fully qualified class name of the driver classc
- the Closure to call- Throws:
SQLException
- if a database access error occursClassNotFoundException
- if the driver class cannot be found or loaded- See Also:
-
newInstance
Creates a new Sql instance given parameters in a Map. Recognized keys for the Map include:driverClassName the fully qualified class name of the driver class driver a synonym for driverClassName url a database url of the form:
Of these, '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_ONLYurl
' is required. Others may be needed depending on your database.
If 'properties
' is supplied, neither 'user
' nor 'password
' should be supplied.
If one of 'user
' or 'password
' is supplied, both should be supplied.Example usage:
import groovy.sql.Sql import static java.sql.ResultSet.* def sql = Sql.newInstance( url:'jdbc:hsqldb:mem:testDB', user:'sa', password:'', driver:'org.hsqldb.jdbc.JDBCDriver', cacheStatements: true, resultSetConcurrency: CONCUR_READ_ONLY )
- Parameters:
args
- a Map contain further arguments- Returns:
- a new Sql instance with a connection
- Throws:
SQLException
- if a database access error occursClassNotFoundException
- if the driver class cannot be found or loaded
-
withInstance
public static void withInstance(Map<String, Object> args, @ClosureParams(value=SimpleType.class,options="groovy.sql.Sql") Closure c) throws SQLException, ClassNotFoundExceptionInvokes a closure passing it a new Sql instance created from the given map of arguments. The created connection will be closed if required.- Parameters:
args
- a Map contain further argumentsc
- the Closure to call- Throws:
SQLException
- if a database access error occursClassNotFoundException
- if the driver class cannot be found or loaded- See Also:
-
getResultSetType
public int getResultSetType()Gets the resultSetType for statements created using the connection.- Returns:
- the current resultSetType value
- Since:
- 1.5.2
-
setResultSetType
public void setResultSetType(int resultSetType) Sets the resultSetType for statements created using the connection. May cause SQLFeatureNotSupportedException exceptions to occur if the underlying database doesn't support the requested type value.- Parameters:
resultSetType
- one of the followingResultSet
constants:ResultSet.TYPE_FORWARD_ONLY
,ResultSet.TYPE_SCROLL_INSENSITIVE
, orResultSet.TYPE_SCROLL_SENSITIVE
- Since:
- 1.5.2
-
getResultSetConcurrency
public int getResultSetConcurrency()Gets the resultSetConcurrency for statements created using the connection.- Returns:
- the current resultSetConcurrency value
- Since:
- 1.5.2
-
setResultSetConcurrency
public void setResultSetConcurrency(int resultSetConcurrency) Sets the resultSetConcurrency for statements created using the connection. May cause SQLFeatureNotSupportedException exceptions to occur if the underlying database doesn't support the requested concurrency value.- Parameters:
resultSetConcurrency
- one of the followingResultSet
constants:ResultSet.CONCUR_READ_ONLY
orResultSet.CONCUR_UPDATABLE
- Since:
- 1.5.2
-
getResultSetHoldability
public int getResultSetHoldability()Gets the resultSetHoldability for statements created using the connection.- Returns:
- the current resultSetHoldability value or -1 if not set
- Since:
- 1.5.2
-
setResultSetHoldability
public void setResultSetHoldability(int resultSetHoldability) Sets the resultSetHoldability for statements created using the connection. May cause SQLFeatureNotSupportedException exceptions to occur if the underlying database doesn't support the requested holdability value.- Parameters:
resultSetHoldability
- one of the followingResultSet
constants:ResultSet.HOLD_CURSORS_OVER_COMMIT
orResultSet.CLOSE_CURSORS_AT_COMMIT
- Since:
- 1.5.2
-
loadDriver
Attempts to load the JDBC driver on the thread, current or system class loaders- Parameters:
driverClassName
- the fully qualified class name of the driver class- Throws:
ClassNotFoundException
- if the class cannot be found or loaded
-
ARRAY
-
BIGINT
-
BINARY
-
BIT
-
BLOB
-
BOOLEAN
-
CHAR
-
CLOB
-
DATALINK
-
DATE
-
DECIMAL
-
DISTINCT
-
DOUBLE
-
FLOAT
-
INTEGER
-
JAVA_OBJECT
-
LONGVARBINARY
-
LONGVARCHAR
-
NULL
-
NUMERIC
-
OTHER
-
REAL
-
REF
-
SMALLINT
-
STRUCT
-
TIME
-
TIMESTAMP
-
TINYINT
-
VARBINARY
-
VARCHAR
-
in
Create a new InParameter- Parameters:
type
- the JDBC data typevalue
- the object value- Returns:
- an InParameter
-
out
Create a new OutParameter- Parameters:
type
- the JDBC data type.- Returns:
- an OutParameter
-
inout
Create an inout parameter using this in parameter.- Parameters:
in
- the InParameter of interest- Returns:
- the resulting InOutParameter
-
resultSet
Create a new ResultSetOutParameter- Parameters:
type
- the JDBC data type.- Returns:
- a ResultSetOutParameter
-
expand
When using GString SQL queries, allows a variable to be expanded in the Sql string rather than representing an sql parameter.Example usage:
def fieldName = 'firstname' def fieldOp = Sql.expand('like') def fieldVal = '%a%' sql.query "select * from PERSON where ${Sql.expand(fieldName)} $fieldOp ${fieldVal}", { ResultSet rs
->
while (rs.next()) println rs.getString('firstname') } // query will be 'select * from PERSON where firstname like ?' // params will be [fieldVal]- Parameters:
object
- the object of interest- Returns:
- the expanded variable
- See Also:
-
dataSet
-
dataSet
-
query
public void query(String sql, @ClosureParams(value=SimpleType.class,options="java.sql.ResultSet") Closure closure) throws SQLException Performs the given SQL query, which should return a singleResultSet
object. The given closure is called with theResultSet
as its argument.Example usages:
sql.query("select * from PERSON where firstname like 'S%'") { ResultSet rs
->
while (rs.next()) println rs.getString('firstname') + ' ' + rs.getString(3) } sql.query("call get_people_places()") { ResultSet rs->
while (rs.next()) println rs.toRowResult().firstname }All resources including the ResultSet are closed automatically after the closure is called.
- Parameters:
sql
- the sql statementclosure
- called for each row with aResultSet
- Throws:
SQLException
- if a database access error occurs
-
query
public void query(String sql, List<Object> params, @ClosureParams(value=SimpleType.class,options="java.sql.ResultSet") Closure closure) throws SQLException Performs the given SQL query, which should return a singleResultSet
object. The given closure is called with theResultSet
as its argument. The query may contain placeholder question marks which match the given list of parameters.Example usage:
sql.query('select * from PERSON where lastname like ?', ['%a%']) { ResultSet rs
->
while (rs.next()) println rs.getString('lastname') }This method supports named and named ordinal parameters. See the class Javadoc for more details.
All resources including the ResultSet are closed automatically after the closure is called.
- Parameters:
sql
- the sql statementparams
- a list of parametersclosure
- called for each row with aResultSet
- Throws:
SQLException
- if a database access error occurs
-
query
public void query(String sql, Map map, @ClosureParams(value=SimpleType.class,options="java.sql.ResultSet") Closure closure) throws SQLException A variant ofquery(String, java.util.List, groovy.lang.Closure)
useful when providing the named parameters as a map.- Parameters:
sql
- the sql statementmap
- a map containing the named parametersclosure
- called for each row with aResultSet
- Throws:
SQLException
- if a database access error occurs- Since:
- 1.8.7
-
query
public void query(Map map, String sql, @ClosureParams(value=SimpleType.class,options="java.sql.ResultSet") Closure closure) throws SQLException A variant ofquery(String, java.util.List, groovy.lang.Closure)
useful when providing the named parameters as named arguments.- Parameters:
map
- a map containing the named parameterssql
- the sql statementclosure
- called for each row with aResultSet
- Throws:
SQLException
- if a database access error occurs- Since:
- 1.8.7
-
query
public void query(GString gstring, @ClosureParams(value=SimpleType.class,options="java.sql.ResultSet") Closure closure) throws SQLException Performs the given SQL query, which should return a singleResultSet
object. The given closure is called with theResultSet
as its argument. The query may contain GString expressions.Example usage:
def location = 25 sql.query "select * from PERSON where location_id
<
$location", { ResultSet rs->
while (rs.next()) println rs.getString('firstname') }All resources including the ResultSet are closed automatically after the closure is called.
- Parameters:
gstring
- a GString containing the SQL query with embedded paramsclosure
- called for each row with aResultSet
- Throws:
SQLException
- if a database access error occurs- See Also:
-
eachRow
public void eachRow(String sql, @ClosureParams(value=SimpleType.class,options="groovy.sql.GroovyResultSet") Closure closure) throws SQLException Performs the given SQL query calling the given Closure with each row of the result set. The row will be aGroovyResultSet
which is aResultSet
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.
- Parameters:
sql
- the sql statementclosure
- called for each row with a GroovyResultSet- Throws:
SQLException
- if a database access error occurs
-
eachRow
public void eachRow(String sql, int offset, int maxRows, @ClosureParams(value=SimpleType.class,options="groovy.sql.GroovyResultSet") Closure closure) throws SQLException Performs the given SQL query calling the givenclosure
with each row of the result set starting at the providedoffset
, and including up tomaxRows
number of rows. The row will be aGroovyResultSet
which is aResultSet
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 isResultSet.TYPE_FORWARD_ONLY
, theResultSet.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.
- Parameters:
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 GroovyResultSet- Throws:
SQLException
- if a database access error occurs
-
eachRow
public void eachRow(String sql, @ClosureParams(value=SimpleType.class,options="java.sql.ResultSetMetaData") Closure metaClosure, @ClosureParams(value=SimpleType.class,options="groovy.sql.GroovyResultSet") Closure rowClosure) throws SQLException Performs the given SQL query calling the givenrowClosure
with each row of the result set. The row will be aGroovyResultSet
which is aResultSet
that supports accessing the fields using property style notation and ordinal index values. In addition, themetaClosure
will be called once passing in theResultSetMetaData
as argument.Example usage:
def printColNames = { meta
->
(1..meta.columnCount).each { print meta.getColumnLabel(it).padRight(20) } println() } def printRow = { row->
row.toRowResult().values().each{ print it.toString().padRight(20) } println() } sql.eachRow("select * from PERSON", printColNames, printRow)Resource handling is performed automatically where appropriate.
- Parameters:
sql
- the sql statementmetaClosure
- called for metadata (only once after sql execution)rowClosure
- called for each row with a GroovyResultSet- Throws:
SQLException
- if a database access error occurs
-
eachRow
public void eachRow(String sql, @ClosureParams(value=SimpleType.class,options="java.sql.ResultSetMetaData") Closure metaClosure, int offset, int maxRows, @ClosureParams(value=SimpleType.class,options="groovy.sql.GroovyResultSet") Closure rowClosure) throws SQLException Performs the given SQL query calling the givenrowClosure
with each row of the result set starting at the providedoffset
, and including up tomaxRows
number of rows. The row will be aGroovyResultSet
which is aResultSet
that supports accessing the fields using property style notation and ordinal index values.In addition, the
metaClosure
will be called once passing in theResultSetMetaData
as argument.Note that the underlying implementation is based on either invoking
ResultSet.absolute()
, or if the ResultSet type isResultSet.TYPE_FORWARD_ONLY
, theResultSet.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.
- Parameters:
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 metadata (only once after sql execution)rowClosure
- called for each row with a GroovyResultSet- Throws:
SQLException
- if a database access error occurs
-
eachRow
public void eachRow(String sql, List<Object> params, @ClosureParams(value=SimpleType.class,options="java.sql.ResultSetMetaData") Closure metaClosure, int offset, int maxRows, @ClosureParams(value=SimpleType.class,options="groovy.sql.GroovyResultSet") Closure rowClosure) throws SQLException Performs the given SQL query calling the givenrowClosure
with each row of the result set starting at the providedoffset
, and including up tomaxRows
number of rows. The row will be aGroovyResultSet
which is aResultSet
that supports accessing the fields using property style notation and ordinal index values.In addition, the
metaClosure
will be called once passing in theResultSetMetaData
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 isResultSet.TYPE_FORWARD_ONLY
, theResultSet.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.- Parameters:
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 metadata (only once after sql execution)rowClosure
- called for each row with a GroovyResultSet- Throws:
SQLException
- if a database access error occurs
-
eachRow
public void eachRow(String sql, Map map, @ClosureParams(value=SimpleType.class,options="java.sql.ResultSetMetaData") Closure metaClosure, int offset, int maxRows, @ClosureParams(value=SimpleType.class,options="groovy.sql.GroovyResultSet") Closure rowClosure) throws SQLException A variant ofeachRow(String, java.util.List, groovy.lang.Closure, int, int, groovy.lang.Closure)
allowing the named parameters to be supplied in a map.- Parameters:
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 metadata (only once after sql execution)rowClosure
- called for each row with a GroovyResultSet- Throws:
SQLException
- if a database access error occurs- Since:
- 1.8.7
-
eachRow
public void eachRow(Map map, String sql, @ClosureParams(value=SimpleType.class,options="java.sql.ResultSetMetaData") Closure metaClosure, int offset, int maxRows, @ClosureParams(value=SimpleType.class,options="groovy.sql.GroovyResultSet") Closure rowClosure) throws SQLException A variant ofeachRow(String, java.util.List, groovy.lang.Closure, int, int, groovy.lang.Closure)
allowing the named parameters to be supplied as named arguments.- Parameters:
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 metadata (only once after sql execution)rowClosure
- called for each row with a GroovyResultSet- Throws:
SQLException
- if a database access error occurs- Since:
- 1.8.7
-
eachRow
public void eachRow(String sql, List<Object> params, @ClosureParams(value=SimpleType.class,options="java.sql.ResultSetMetaData") Closure metaClosure, @ClosureParams(value=SimpleType.class,options="groovy.sql.GroovyResultSet") Closure rowClosure) throws SQLException Performs the given SQL query calling the given Closure with each row of the result set. The row will be aGroovyResultSet
which is aResultSet
that supports accessing the fields using property style notation and ordinal index values. In addition, themetaClosure
will be called once passing in theResultSetMetaData
as argument. The query may contain placeholder question marks which match the given list of parameters.Example usage:
def printColNames = { meta
->
(1..meta.columnCount).each { print meta.getColumnLabel(it).padRight(20) } println() } def printRow = { row->
row.toRowResult().values().each{ print it.toString().padRight(20) } println() } sql.eachRow("select * from PERSON where lastname like ?", ['%a%'], printColNames, printRow)This method supports named and named ordinal parameters. See the class Javadoc for more details.
Resource handling is performed automatically where appropriate.
- Parameters:
sql
- the sql statementparams
- a list of parametersmetaClosure
- called for metadata (only once after sql execution)rowClosure
- called for each row with a GroovyResultSet- Throws:
SQLException
- if a database access error occurs
-
eachRow
public void eachRow(String sql, Map params, @ClosureParams(value=SimpleType.class,options="java.sql.ResultSetMetaData") Closure metaClosure, @ClosureParams(value=SimpleType.class,options="groovy.sql.GroovyResultSet") Closure rowClosure) throws SQLException A variant ofeachRow(String, java.util.List, groovy.lang.Closure, groovy.lang.Closure)
useful when providing the named parameters as a map.- Parameters:
sql
- the sql statementparams
- a map of named parametersmetaClosure
- called for metadata (only once after sql execution)rowClosure
- called for each row with a GroovyResultSet- Throws:
SQLException
- if a database access error occurs- Since:
- 1.8.7
-
eachRow
public void eachRow(Map params, String sql, @ClosureParams(value=SimpleType.class,options="java.sql.ResultSetMetaData") Closure metaClosure, @ClosureParams(value=SimpleType.class,options="groovy.sql.GroovyResultSet") Closure rowClosure) throws SQLException A variant ofeachRow(String, java.util.List, groovy.lang.Closure, groovy.lang.Closure)
useful when providing the named parameters as named arguments.- Parameters:
params
- a map of named parameterssql
- the sql statementmetaClosure
- called for metadata (only once after sql execution)rowClosure
- called for each row with a GroovyResultSet- Throws:
SQLException
- if a database access error occurs- Since:
- 1.8.7
-
eachRow
public void eachRow(String sql, List<Object> params, @ClosureParams(value=SimpleType.class,options="groovy.sql.GroovyResultSet") Closure closure) throws SQLException Performs the given SQL query calling the given Closure with each row of the result set. The row will be aGroovyResultSet
which is aResultSet
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.
- Parameters:
sql
- the sql statementparams
- a list of parametersclosure
- called for each row with a GroovyResultSet- Throws:
SQLException
- if a database access error occurs
-
eachRow
public void eachRow(String sql, Map params, @ClosureParams(value=SimpleType.class,options="groovy.sql.GroovyResultSet") Closure closure) throws SQLException A variant ofeachRow(String, java.util.List, groovy.lang.Closure)
useful when providing the named parameters as a map.- Parameters:
sql
- the sql statementparams
- a map of named parametersclosure
- called for each row with a GroovyResultSet- Throws:
SQLException
- if a database access error occurs- Since:
- 1.8.7
-
eachRow
public void eachRow(Map params, String sql, @ClosureParams(value=SimpleType.class,options="groovy.sql.GroovyResultSet") Closure closure) throws SQLException A variant ofeachRow(String, java.util.List, groovy.lang.Closure)
useful when providing the named parameters as named arguments.- Parameters:
params
- a map of named parameterssql
- the sql statementclosure
- called for each row with a GroovyResultSet- Throws:
SQLException
- if a database access error occurs- Since:
- 1.8.7
-
eachRow
public void eachRow(String sql, List<Object> params, int offset, int maxRows, @ClosureParams(value=SimpleType.class,options="groovy.sql.GroovyResultSet") Closure closure) throws SQLException Performs the given SQL query calling the givenclosure
with each row of the result set starting at the providedoffset
, and including up tomaxRows
number of rows. The row will be aGroovyResultSet
which is aResultSet
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 isResultSet.TYPE_FORWARD_ONLY
, theResultSet.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.- Parameters:
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 GroovyResultSet- Throws:
SQLException
- if a database access error occurs
-
eachRow
public void eachRow(String sql, Map params, int offset, int maxRows, @ClosureParams(value=SimpleType.class,options="groovy.sql.GroovyResultSet") Closure closure) throws SQLException A variant ofeachRow(String, java.util.List, int, int, groovy.lang.Closure)
useful when providing the named parameters as a map.- Parameters:
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 GroovyResultSet- Throws:
SQLException
- if a database access error occurs- Since:
- 1.8.7
-
eachRow
public void eachRow(Map params, String sql, int offset, int maxRows, @ClosureParams(value=SimpleType.class,options="groovy.sql.GroovyResultSet") Closure closure) throws SQLException A variant ofeachRow(String, java.util.List, int, int, groovy.lang.Closure)
useful when providing the named parameters as named arguments.- Parameters:
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 GroovyResultSet- Throws:
SQLException
- if a database access error occurs- Since:
- 1.8.7
-
eachRow
public void eachRow(GString gstring, @ClosureParams(value=SimpleType.class,options="java.sql.ResultSetMetaData") Closure metaClosure, @ClosureParams(value=SimpleType.class,options="groovy.sql.GroovyResultSet") Closure rowClosure) throws SQLException Performs the given SQL query calling the given Closure with each row of the result set. The row will be aGroovyResultSet
which is aResultSet
that supports accessing the fields using property style notation and ordinal index values.In addition, the
metaClosure
will be called once passing in theResultSetMetaData
as argument. The query may contain GString expressions.Example usage:
def location = 25 def printColNames = { meta
->
(1..meta.columnCount).each { print meta.getColumnLabel(it).padRight(20) } println() } def printRow = { row->
row.toRowResult().values().each{ print it.toString().padRight(20) } println() } sql.eachRow("select * from PERSON where location_id<
$location", printColNames, printRow)Resource handling is performed automatically where appropriate.
- Parameters:
gstring
- a GString containing the SQL query with embedded paramsmetaClosure
- called for metadata (only once after sql execution)rowClosure
- called for each row with a GroovyResultSet- Throws:
SQLException
- if a database access error occurs- See Also:
-
eachRow
public void eachRow(GString gstring, @ClosureParams(value=SimpleType.class,options="java.sql.ResultSetMetaData") Closure metaClosure, int offset, int maxRows, @ClosureParams(value=SimpleType.class,options="groovy.sql.GroovyResultSet") Closure rowClosure) throws SQLException Performs the given SQL query calling the givenclosure
with each row of the result set starting at the providedoffset
, and including up tomaxRows
number of rows. The row will be aGroovyResultSet
which is aResultSet
that supports accessing the fields using property style notation and ordinal index values. In addition, themetaClosure
will be called once passing in theResultSetMetaData
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 isResultSet.TYPE_FORWARD_ONLY
, theResultSet.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.- Parameters:
gstring
- a GString containing the SQL query with embedded paramsmetaClosure
- called for metadata (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 GroovyResultSet- Throws:
SQLException
- if a database access error occurs
-
eachRow
public void eachRow(GString gstring, int offset, int maxRows, @ClosureParams(value=SimpleType.class,options="groovy.sql.GroovyResultSet") Closure closure) throws SQLException Performs the given SQL query calling the givenclosure
with each row of the result set starting at the providedoffset
, and including up tomaxRows
number of rows. The row will be aGroovyResultSet
which is aResultSet
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 isResultSet.TYPE_FORWARD_ONLY
, theResultSet.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.- Parameters:
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 GroovyResultSet- Throws:
SQLException
- if a database access error occurs
-
eachRow
public void eachRow(GString gstring, @ClosureParams(value=SimpleType.class,options="groovy.sql.GroovyResultSet") Closure closure) throws SQLException Performs the given SQL query calling the given Closure with each row of the result set. The row will be aGroovyResultSet
which is aResultSet
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.
- Parameters:
gstring
- a GString containing the SQL query with embedded paramsclosure
- called for each row with a GroovyResultSet- Throws:
SQLException
- if a database access error occurs- See Also:
-
rows
Performs the given SQL query and return the rows of the result set.Example usage:
def ans = sql.rows("select * from PERSON where firstname like 'S%'") println "Found ${ans.size()} rows"
Resource handling is performed automatically where appropriate.
- Parameters:
sql
- the SQL statement- Returns:
- a list of GroovyRowResult objects
- Throws:
SQLException
- if a database access error occurs
-
rows
Performs the given SQL query and return a "page" of rows from the result set. A page is defined as starting at a 1-based offset, and containing a maximum number of rows.Note that the underlying implementation is based on either invoking
ResultSet.absolute()
, or if the ResultSet type isResultSet.TYPE_FORWARD_ONLY
, theResultSet.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.
- Parameters:
sql
- the SQL statementoffset
- the 1-based offset for the first row to be processedmaxRows
- the maximum number of rows to be processed- Returns:
- a list of GroovyRowResult objects
- Throws:
SQLException
- if a database access error occurs
-
rows
public List<GroovyRowResult> rows(String sql, @ClosureParams(value=SimpleType.class,options="java.sql.ResultSetMetaData") Closure metaClosure) throws SQLException Performs the given SQL query and return the rows of the result set. In addition, themetaClosure
will be called once passing in theResultSetMetaData
as argument.Example usage:
def printNumCols = { meta
->
println "Found $meta.columnCount columns" } def ans = sql.rows("select * from PERSON", printNumCols) println "Found ${ans.size()} rows"Resource handling is performed automatically where appropriate.
- Parameters:
sql
- the SQL statementmetaClosure
- called with metadata of the ResultSet- Returns:
- a list of GroovyRowResult objects
- Throws:
SQLException
- if a database access error occurs
-
rows
public List<GroovyRowResult> rows(String sql, int offset, int maxRows, @ClosureParams(value=SimpleType.class,options="java.sql.ResultSetMetaData") Closure metaClosure) throws SQLException Performs the given SQL query and return a "page" of rows from the result set. A page is defined as starting at a 1-based offset, and containing a maximum number of rows. In addition, themetaClosure
will be called once passing in theResultSetMetaData
as argument.Note that the underlying implementation is based on either invoking
ResultSet.absolute()
, or if the ResultSet type isResultSet.TYPE_FORWARD_ONLY
, theResultSet.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.
- Parameters:
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 metadata (only once after sql execution)- Returns:
- a list of GroovyRowResult objects
- Throws:
SQLException
- if a database access error occurs
-
rows
Performs the given SQL query and return the rows of the result set. The query may contain placeholder question marks which match the given list of parameters.Example usage:
def ans = sql.rows("select * from PERSON where lastname like ?", ['%a%']) println "Found ${ans.size()} rows"
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.
- Parameters:
sql
- the SQL statementparams
- a list of parameters- Returns:
- a list of GroovyRowResult objects
- Throws:
SQLException
- if a database access error occurs
-
rows
A variant ofrows(String, java.util.List)
useful when providing the named parameters as named arguments.- Parameters:
params
- a map containing the named parameterssql
- the SQL statement- Returns:
- a list of GroovyRowResult objects
- Throws:
SQLException
- if a database access error occurs- Since:
- 1.8.7
-
rows
public List<GroovyRowResult> rows(String sql, List<Object> params, int offset, int maxRows) throws SQLException Performs the given SQL query and return a "page" of rows from the result set. A page is defined as starting at a 1-based offset, and containing a maximum number of rows. 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 isResultSet.TYPE_FORWARD_ONLY
, theResultSet.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.
- Parameters:
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 processed- Returns:
- a list of GroovyRowResult objects
- Throws:
SQLException
- if a database access error occurs
-
rows
public List<GroovyRowResult> rows(String sql, Map params, int offset, int maxRows) throws SQLException A variant ofrows(String, java.util.List, int, int)
useful when providing the named parameters as a map.- Parameters:
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 processed- Returns:
- a list of GroovyRowResult objects
- Throws:
SQLException
- if a database access error occurs- Since:
- 1.8.7
-
rows
public List<GroovyRowResult> rows(Map params, String sql, int offset, int maxRows) throws SQLException A variant ofrows(String, java.util.List, int, int)
useful when providing the named parameters as named arguments.- Parameters:
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 processed- Returns:
- a list of GroovyRowResult objects
- Throws:
SQLException
- if a database access error occurs- Since:
- 1.8.7
-
rows
Performs the given SQL query and return the rows of the result set.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)
.- Parameters:
sql
- the SQL statementparams
- an array of parameters- Returns:
- a list of GroovyRowResult objects
- Throws:
SQLException
- if a database access error occurs
-
rows
public List<GroovyRowResult> rows(String sql, Object[] params, int offset, int maxRows) throws SQLException Performs the given SQL query and return the rows of the result set.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)
.- Parameters:
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 processed- Returns:
- a list of GroovyRowResult objects
- Throws:
SQLException
- if a database access error occurs
-
rows
public List<GroovyRowResult> rows(String sql, List<Object> params, @ClosureParams(value=SimpleType.class,options="java.sql.ResultSetMetaData") Closure metaClosure) throws SQLException Performs the given SQL query and return the rows of the result set. In addition, themetaClosure
will be called once passing in theResultSetMetaData
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
See the class Javadoc for more details.->
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"Resource handling is performed automatically where appropriate.
- Parameters:
sql
- the SQL statementparams
- a list of parametersmetaClosure
- called for metadata (only once after sql execution)- Returns:
- a list of GroovyRowResult objects
- Throws:
SQLException
- if a database access error occurs
-
rows
public List<GroovyRowResult> rows(String sql, Map params, @ClosureParams(value=SimpleType.class,options="java.sql.ResultSetMetaData") Closure metaClosure) throws SQLException A variant ofrows(String, java.util.List, groovy.lang.Closure)
useful when providing the named parameters as a map.- Parameters:
sql
- the SQL statementparams
- a map of named parametersmetaClosure
- called for metadata (only once after sql execution)- Returns:
- a list of GroovyRowResult objects
- Throws:
SQLException
- if a database access error occurs- Since:
- 1.8.7
-
rows
public List<GroovyRowResult> rows(Map params, String sql, @ClosureParams(value=SimpleType.class,options="java.sql.ResultSetMetaData") Closure metaClosure) throws SQLException A variant ofrows(String, java.util.List, groovy.lang.Closure)
useful when providing the named parameters as named arguments.- Parameters:
params
- a map of named parameterssql
- the SQL statementmetaClosure
- called for metadata (only once after sql execution)- Returns:
- a list of GroovyRowResult objects
- Throws:
SQLException
- if a database access error occurs- Since:
- 1.8.7
-
rows
public List<GroovyRowResult> rows(String sql, List<Object> params, int offset, int maxRows, @ClosureParams(value=SimpleType.class,options="java.sql.ResultSetMetaData") Closure metaClosure) throws SQLException Performs the given SQL query and return a "page" of rows from the result set. A page is defined as starting at a 1-based offset, and containing a maximum number of rows. In addition, themetaClosure
will be called once passing in theResultSetMetaData
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 isResultSet.TYPE_FORWARD_ONLY
, theResultSet.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.
- Parameters:
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 metadata (only once after sql execution)- Returns:
- a list of GroovyRowResult objects
- Throws:
SQLException
- if a database access error occurs
-
rows
public List<GroovyRowResult> rows(String sql, Map params, int offset, int maxRows, @ClosureParams(value=SimpleType.class,options="java.sql.ResultSetMetaData") Closure metaClosure) throws SQLException A variant ofrows(String, java.util.List, int, int, groovy.lang.Closure)
useful when providing the named parameters as a map.- Parameters:
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 metadata (only once after sql execution)- Returns:
- a list of GroovyRowResult objects
- Throws:
SQLException
- if a database access error occurs- Since:
- 1.8.7
-
rows
public List<GroovyRowResult> rows(Map params, String sql, int offset, int maxRows, @ClosureParams(value=SimpleType.class,options="java.sql.ResultSetMetaData") Closure metaClosure) throws SQLException A variant ofrows(String, java.util.List, int, int, groovy.lang.Closure)
useful when providing the named parameters as named arguments.- Parameters:
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 metadata (only once after sql execution)- Returns:
- a list of GroovyRowResult objects
- Throws:
SQLException
- if a database access error occurs- Since:
- 1.8.7
-
rows
Performs the given SQL query and return a "page" of rows from the result set. A page is defined as starting at a 1-based offset, and containing a maximum number of rows. The query may contain GString expressions.Note that the underlying implementation is based on either invoking
ResultSet.absolute()
, or if the ResultSet type isResultSet.TYPE_FORWARD_ONLY
, theResultSet.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.
- Parameters:
sql
- the SQL statementoffset
- the 1-based offset for the first row to be processedmaxRows
- the maximum number of rows to be processed- Returns:
- a list of GroovyRowResult objects
- Throws:
SQLException
- if a database access error occurs
-
rows
Performs the given SQL query and return the rows of the result set. The query may contain GString expressions.Example usage:
def location = 25 def ans = sql.rows("select * from PERSON where location_id
<
$location") println "Found ${ans.size()} rows"Resource handling is performed automatically where appropriate.
- Parameters:
gstring
- a GString containing the SQL query with embedded params- Returns:
- a list of GroovyRowResult objects
- Throws:
SQLException
- if a database access error occurs- See Also:
-
rows
public List<GroovyRowResult> rows(GString gstring, @ClosureParams(value=SimpleType.class,options="java.sql.ResultSetMetaData") Closure metaClosure) throws SQLException Performs the given SQL query and return the rows of the result set. In addition, themetaClosure
will be called once passing in theResultSetMetaData
as argument. The query may contain GString expressions.Example usage:
def location = 25 def printNumCols = { meta
->
println "Found $meta.columnCount columns" } def ans = sql.rows("select * from PERSON where location_id<
$location", printNumCols) println "Found ${ans.size()} rows"Resource handling is performed automatically where appropriate.
- Parameters:
gstring
- a GString containing the SQL query with embedded paramsmetaClosure
- called with metadata of the ResultSet- Returns:
- a list of GroovyRowResult objects
- Throws:
SQLException
- if a database access error occurs- See Also:
-
rows
public List<GroovyRowResult> rows(GString gstring, int offset, int maxRows, @ClosureParams(value=SimpleType.class,options="java.sql.ResultSetMetaData") Closure metaClosure) throws SQLException Performs the given SQL query and return a "page" of rows from the result set. A page is defined as starting at a 1-based offset, and containing a maximum number of rows. In addition, themetaClosure
will be called once passing in theResultSetMetaData
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 isResultSet.TYPE_FORWARD_ONLY
, theResultSet.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.
- Parameters:
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 metadata (only once after sql execution)- Returns:
- a list of GroovyRowResult objects
- Throws:
SQLException
- if a database access error occurs
-
firstRow
Performs the given SQL query and return the first row of the result set.Example usage:
def ans = sql.firstRow("select * from PERSON where firstname like 'S%'") println ans.firstname
Resource handling is performed automatically where appropriate.
- Parameters:
sql
- the SQL statement- Returns:
- a GroovyRowResult object or
null
if no row is found - Throws:
SQLException
- if a database access error occurs
-
firstRow
Performs the given SQL query and return the first row of the result set. The query may contain GString expressions.Example usage:
def location = 25 def ans = sql.firstRow("select * from PERSON where location_id
<
$location") println ans.firstnameResource handling is performed automatically where appropriate.
- Parameters:
gstring
- a GString containing the SQL query with embedded params- Returns:
- a GroovyRowResult object or
null
if no row is found - Throws:
SQLException
- if a database access error occurs- See Also:
-
firstRow
Performs the given SQL query and return the first row of the result set. The query may contain placeholder question marks which match the given list of parameters.Example usages:
def ans = sql.firstRow("select * from PERSON where lastname like ?", ['%a%']) println ans.firstname
If your database returns scalar functions as ResultSets, you can also use firstRow to gain access to stored procedure results, e.g. using hsqldb 1.9 RC4:sql.execute """ create function FullName(p_firstname VARCHAR(40)) returns VARCHAR(80) BEGIN atomic DECLARE ans VARCHAR(80); SET ans = (SELECT firstname || ' ' || lastname FROM PERSON WHERE firstname = p_firstname); RETURN ans; END """ assert sql.firstRow("{call FullName(?)}", ['Sam'])[0] == 'Sam Pullara'
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.
- Parameters:
sql
- the SQL statementparams
- a list of parameters- Returns:
- a GroovyRowResult object or
null
if no row is found - Throws:
SQLException
- if a database access error occurs
-
firstRow
A variant offirstRow(String, java.util.List)
useful when providing the named parameters as named arguments.- Parameters:
params
- a map containing the named parameterssql
- the SQL statement- Returns:
- a GroovyRowResult object or
null
if no row is found - Throws:
SQLException
- if a database access error occurs- Since:
- 1.8.7
-
firstRow
Performs the given SQL query and return the first row of the result set.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.- Parameters:
sql
- the SQL statementparams
- an array of parameters- Returns:
- a GroovyRowResult object or
null
if no row is found - Throws:
SQLException
- if a database access error occurs
-
execute
Executes the given piece of SQL. Also saves the updateCount, if any, for subsequent examination.Example usages:
sql.execute "DROP TABLE IF EXISTS person" sql.execute """ CREATE TABLE person ( id INTEGER NOT NULL, firstname VARCHAR(100), lastname VARCHAR(100), location_id INTEGER ) """ sql.execute """ INSERT INTO person (id, firstname, lastname, location_id) VALUES (4, 'Paul', 'King', 40) """ assert sql.updateCount == 1
Resource handling is performed automatically where appropriate.
- Parameters:
sql
- the SQL to execute- Returns:
true
if the first result is aResultSet
object;false
if it is an update count or there are no results- Throws:
SQLException
- if a database access error occurs
-
execute
public void execute(String sql, @ClosureParams(value=FromString.class,options={"boolean,java.util.List<groovy.sql.GroovyRowResult>","boolean,int"}) Closure processResults) throws SQLException Executes the given piece of SQL. Also calls the provided processResults Closure to process any ResultSet or UpdateCount results that executing the SQL might produce.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.
- Parameters:
sql
- the SQL to executeprocessResults
- a Closure which will be passed two parameters: eithertrue
plus a list of GroovyRowResult values derived fromstatement.getResultSet()
orfalse
plus the update count fromstatement.getUpdateCount()
. The closure will be called for each result produced from executing the SQL.- Throws:
SQLException
- if a database access error occurs- Since:
- 2.3.2
-
execute
Executes the given piece of SQL with parameters. Also saves the updateCount, if any, for subsequent examination.Example usage:
sql.execute """ insert into PERSON (id, firstname, lastname, location_id) values (?, ?, ?, ?) """, [1, "Guillaume", "Laforge", 10] assert sql.updateCount == 1
This method supports named and named ordinal parameters. See the class Javadoc for more details.
Resource handling is performed automatically where appropriate.
- Parameters:
sql
- the SQL statementparams
- a list of parameters- Returns:
true
if the first result is aResultSet
object;false
if it is an update count or there are no results- Throws:
SQLException
- if a database access error occurs
-
execute
public void execute(String sql, List<Object> params, @ClosureParams(value=FromString.class,options={"boolean,java.util.List<groovy.sql.GroovyRowResult>","boolean,int"}) Closure processResults) throws SQLException Executes the given piece of SQL with parameters. Also calls the provided processResults Closure to process any ResultSet or UpdateCount results that executing the SQL might produce.This method supports named and named ordinal parameters. See the class Javadoc for more details.
Resource handling is performed automatically where appropriate.
- Parameters:
sql
- the SQL statementparams
- a list of parametersprocessResults
- a Closure which will be passed two parameters: eithertrue
plus a list of GroovyRowResult values derived fromstatement.getResultSet()
orfalse
plus the update count fromstatement.getUpdateCount()
. The closure will be called for each result produced from executing the SQL.- Throws:
SQLException
- if a database access error occurs- Since:
- 2.3.2
- See Also:
-
execute
A variant ofexecute(String, java.util.List)
useful when providing the named parameters as named arguments.- Parameters:
params
- a map containing the named parameterssql
- the SQL statement- Returns:
true
if the first result is aResultSet
object;false
if it is an update count or there are no results- Throws:
SQLException
- if a database access error occurs- Since:
- 1.8.7
-
execute
public void execute(Map params, String sql, @ClosureParams(value=FromString.class,options={"boolean,java.util.List<groovy.sql.GroovyRowResult>","boolean,int"}) Closure processResults) throws SQLException A variant ofexecute(String, java.util.List, Closure)
useful when providing the named parameters as named arguments.- Parameters:
params
- a map containing the named parameterssql
- the SQL statementprocessResults
- a Closure which will be passed two parameters: eithertrue
plus a list of GroovyRowResult values derived fromstatement.getResultSet()
orfalse
plus the update count fromstatement.getUpdateCount()
. The closure will be called for each result produced from executing the SQL.- Throws:
SQLException
- if a database access error occurs- Since:
- 2.3.2
-
execute
Executes the given piece of SQL with parameters.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.- Parameters:
sql
- the SQL statementparams
- an array of parameters- Returns:
true
if the first result is aResultSet
object;false
if it is an update count or there are no results- Throws:
SQLException
- if a database access error occurs
-
execute
public void execute(String sql, Object[] params, @ClosureParams(value=FromString.class,options={"boolean,java.util.List<groovy.sql.GroovyRowResult>","boolean,int"}) Closure processResults) throws SQLException Executes the given piece of SQL with parameters.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.- Parameters:
sql
- the SQL statementparams
- an array of parametersprocessResults
- a Closure which will be passed two parameters: eithertrue
plus a list of GroovyRowResult values derived fromstatement.getResultSet()
orfalse
plus the update count fromstatement.getUpdateCount()
. The closure will be called for each result produced from executing the SQL.- Throws:
SQLException
- if a database access error occurs- Since:
- 2.3.2
- See Also:
-
execute
Executes the given SQL with embedded expressions inside. Also saves the updateCount, if any, for subsequent examination.Example usage:
def scott = [firstname: "Scott", lastname: "Davis", id: 5, location_id: 50] sql.execute """ insert into PERSON (id, firstname, lastname, location_id) values ($scott.id, $scott.firstname, $scott.lastname, $scott.location_id) """ assert sql.updateCount == 1
Resource handling is performed automatically where appropriate.
- Parameters:
gstring
- a GString containing the SQL query with embedded params- Returns:
true
if the first result is aResultSet
object;false
if it is an update count or there are no results- Throws:
SQLException
- if a database access error occurs- See Also:
-
execute
public void execute(GString gstring, @ClosureParams(value=FromString.class,options={"boolean,java.util.List<groovy.sql.GroovyRowResult>","boolean,int"}) Closure processResults) throws SQLException Executes the given SQL with embedded expressions inside. Also calls the provided processResults Closure to process any ResultSet or UpdateCount results that executing the SQL might produce. Resource handling is performed automatically where appropriate.- Parameters:
gstring
- a GString containing the SQL query with embedded paramsprocessResults
- a Closure which will be passed two parameters: eithertrue
plus a list of GroovyRowResult values derived fromstatement.getResultSet()
orfalse
plus the update count fromstatement.getUpdateCount()
. The closure will be called for each result produced from executing the SQL.- Throws:
SQLException
- if a database access error occurs- Since:
- 2.3.2
- See Also:
-
executeInsert
Executes the given SQL statement (typically an INSERT statement). Use this variant when you want to receive the values of any auto-generated columns, such as an autoincrement ID field. SeeexecuteInsert(GString)
for more details.Resource handling is performed automatically where appropriate.
- Parameters:
sql
- The SQL statement to execute- Returns:
- A list of the auto-generated column values for each inserted row (typically auto-generated keys)
- Throws:
SQLException
- if a database access error occurs
-
executeInsert
Executes the given SQL statement (typically an INSERT statement). Use this variant when you want to receive the values of any auto-generated columns, such as an autoincrement ID field. The query may contain placeholder question marks which match the given list of parameters. SeeexecuteInsert(GString)
for more details.This method supports named and named ordinal parameters. See the class Javadoc for more details.
Resource handling is performed automatically where appropriate.
- Parameters:
sql
- The SQL statement to executeparams
- The parameter values that will be substituted into the SQL statement's parameter slots- Returns:
- A list of the auto-generated column values for each inserted row (typically auto-generated keys)
- Throws:
SQLException
- if a database access error occurs
-
executeInsert
public List<GroovyRowResult> executeInsert(String sql, List<Object> params, List<String> keyColumnNames) throws SQLException Executes the given SQL statement (typically an INSERT statement). Use this variant when you want to receive the values of any auto-generated columns, such as an autoincrement ID field (or fields) and you know the column name(s) of the ID field(s). The query may contain placeholder question marks which match the given list of parameters. SeeexecuteInsert(GString)
for more details.This method supports named and named ordinal parameters. See the class Javadoc for more details.
Resource handling is performed automatically where appropriate.
- Parameters:
sql
- The SQL statement to 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)- Returns:
- A list of the auto-generated row results for each inserted row (typically auto-generated keys)
- Throws:
SQLException
- if a database access error occurs- Since:
- 2.3.2
- See Also:
-
executeInsert
A variant ofexecuteInsert(String, java.util.List)
useful when providing the named parameters as named arguments.- Parameters:
params
- a map containing the named parameterssql
- The SQL statement to execute- Returns:
- A list of the auto-generated column values for each inserted row (typically auto-generated keys)
- Throws:
SQLException
- if a database access error occurs- Since:
- 1.8.7
-
executeInsert
public List<GroovyRowResult> executeInsert(Map params, String sql, List<String> keyColumnNames) throws SQLException A variant ofexecuteInsert(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).- Parameters:
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)- Returns:
- A list of the auto-generated row results for each inserted row (typically auto-generated keys)
- Throws:
SQLException
- if a database access error occurs- Since:
- 2.3.2
- See Also:
-
executeInsert
Executes the given SQL statement (typically an INSERT statement).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.- Parameters:
sql
- The SQL statement to executeparams
- The parameter values that will be substituted into the SQL statement's parameter slots- Returns:
- A list of the auto-generated column values for each inserted row (typically auto-generated keys)
- Throws:
SQLException
- if a database access error occurs
-
executeInsert
Executes the given SQL statement (typically an INSERT statement). 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).This method supports named and named ordinal parameters by supplying such parameters in the
params
array. See the class Javadoc for more details.- Parameters:
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)- Returns:
- A list of the auto-generated row results for each inserted row (typically auto-generated keys)
- Throws:
SQLException
- if a database access error occurs- Since:
- 2.3.2
-
executeInsert
public List<GroovyRowResult> executeInsert(String sql, String[] keyColumnNames, Object[] params) throws SQLException Executes the given SQL statement (typically an INSERT statement). 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).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.- Parameters:
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 slots- Returns:
- A list of the auto-generated row results for each inserted row (typically auto-generated keys)
- Throws:
SQLException
- if a database access error occurs- Since:
- 2.3.2
-
executeInsert
Executes the given SQL statement (typically an INSERT statement). Use this variant when you want to receive the values of any auto-generated columns, such as an autoincrement ID field. The query may contain GString expressions.Generated key values can be accessed using array notation. For example, to return the second auto-generated column value of the third row, use
keys[3][1]
. The method is designed to be used with SQL INSERT statements, but is not limited to them.The standard use for this method is when a table has an autoincrement ID column and you want to know what the ID is for a newly inserted row. In this example, we insert a single row into a table in which the first column contains the autoincrement ID:
def sql = Sql.newInstance("jdbc:mysql://localhost:3306/groovy", "user", "password", "com.mysql.jdbc.Driver") def keys = sql.executeInsert("insert into test_table (INT_DATA, STRING_DATA) " + "VALUES (1, 'Key Largo')") def id = keys[0][0] // 'id' now contains the value of the new row's ID column. // It can be used to update an object representation's // id attribute for example. ...
Resource handling is performed automatically where appropriate.
- Parameters:
gstring
- a GString containing the SQL query with embedded params- Returns:
- A list of the auto-generated column values for each inserted row (typically auto-generated keys)
- Throws:
SQLException
- if a database access error occurs- See Also:
-
executeInsert
public List<GroovyRowResult> executeInsert(GString gstring, List<String> keyColumnNames) throws SQLException Executes the given SQL statement (typically an INSERT statement). Use this variant when you want to receive the values of any auto-generated columns, such as an autoincrement ID field (or fields) and you know the column name(s) of the ID field(s).Resource handling is performed automatically where appropriate.
- Parameters:
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)- Returns:
- A list of the auto-generated row results for each inserted row (typically auto-generated keys)
- Throws:
SQLException
- if a database access error occurs- Since:
- 2.3.2
- See Also:
-
executeUpdate
Executes the given SQL update.Resource handling is performed automatically where appropriate.
- Parameters:
sql
- the SQL to execute- Returns:
- the number of rows updated or 0 for SQL statements that return nothing
- Throws:
SQLException
- if a database access error occurs
-
executeUpdate
Executes the given SQL update with parameters.This method supports named and named ordinal parameters. See the class Javadoc for more details.
Resource handling is performed automatically where appropriate.
- Parameters:
sql
- the SQL statementparams
- a list of parameters- Returns:
- the number of rows updated or 0 for SQL statements that return nothing
- Throws:
SQLException
- if a database access error occurs
-
executeUpdate
A variant ofexecuteUpdate(String, java.util.List)
useful when providing the named parameters as named arguments.- Parameters:
params
- a map containing the named parameterssql
- the SQL statement- Returns:
- the number of rows updated or 0 for SQL statements that return nothing
- Throws:
SQLException
- if a database access error occurs- Since:
- 1.8.7
-
executeUpdate
Executes the given SQL update with parameters.An Object array variant of
executeUpdate(String, List)
.- Parameters:
sql
- the SQL statementparams
- an array of parameters- Returns:
- the number of rows updated or 0 for SQL statements that return nothing
- Throws:
SQLException
- if a database access error occurs
-
executeUpdate
Executes the given SQL update with embedded expressions inside.Resource handling is performed automatically where appropriate.
- Parameters:
gstring
- a GString containing the SQL query with embedded params- Returns:
- the number of rows updated or 0 for SQL statements that return nothing
- Throws:
SQLException
- if a database access error occurs- See Also:
-
call
Performs a stored procedure call.Example usage (tested with MySQL) - suppose we have the following stored procedure:
sql.execute """ CREATE PROCEDURE HouseSwap(_first1 VARCHAR(50), _first2 VARCHAR(50)) BEGIN DECLARE _loc1 INT; DECLARE _loc2 INT; SELECT location_id into _loc1 FROM PERSON where firstname = _first1; SELECT location_id into _loc2 FROM PERSON where firstname = _first2; UPDATE PERSON set location_id = case firstname when _first1 then _loc2 when _first2 then _loc1 end where (firstname = _first1 OR firstname = _first2); END """
then you can invoke the procedure as follows:def rowsChanged = sql.call("{call HouseSwap('Guillaume', 'Paul')}") assert rowsChanged == 2
- Parameters:
sql
- the SQL statement- Returns:
- the number of rows updated or 0 for SQL statements that return nothing
- Throws:
SQLException
- if a database access error occurs
-
call
Performs a stored procedure call with the given embedded parameters.Example usage - see
call(String)
for more details about creating aHouseSwap(IN name1, IN name2)
stored procedure. Once created, it can be called like this:def p1 = 'Paul' def p2 = 'Guillaume' def rowsChanged = sql.call("{call HouseSwap($p1, $p2)}") assert rowsChanged == 2
Resource handling is performed automatically where appropriate.
- Parameters:
gstring
- a GString containing the SQL query with embedded params- Returns:
- the number of rows updated or 0 for SQL statements that return nothing
- Throws:
SQLException
- if a database access error occurs- See Also:
-
call
Performs a stored procedure call with the given parameters.Example usage - see
call(String)
for more details about creating aHouseSwap(IN name1, IN name2)
stored procedure. Once created, it can be called like this:def rowsChanged = sql.call("{call HouseSwap(?, ?)}", ['Guillaume', 'Paul']) assert rowsChanged == 2
Resource handling is performed automatically where appropriate.
- Parameters:
sql
- the SQL statementparams
- a list of parameters- Returns:
- the number of rows updated or 0 for SQL statements that return nothing
- Throws:
SQLException
- if a database access error occurs- See Also:
-
call
Performs a stored procedure call with the given parameters.An Object array variant of
call(String, List)
.- Parameters:
sql
- the SQL statementparams
- an array of parameters- Returns:
- the number of rows updated or 0 for SQL statements that return nothing
- Throws:
SQLException
- if a database access error occurs- See Also:
-
call
public void call(String sql, List<Object> params, @ClosureParams(value=SimpleType.class,options="java.lang.Object[]") Closure closure) throws SQLException Performs a stored procedure call with the given parameters. The closure is called once with all the out parameters.Example usage - suppose we create a stored procedure (ignore its simplistic implementation):
// Tested with MySql 5.0.75 sql.execute """ CREATE PROCEDURE Hemisphere( IN p_firstname VARCHAR(50), IN p_lastname VARCHAR(50), OUT ans VARCHAR(50)) BEGIN DECLARE loc INT; SELECT location_id into loc FROM PERSON where firstname = p_firstname and lastname = p_lastname; CASE loc WHEN 40 THEN SET ans = 'Southern Hemisphere'; ELSE SET ans = 'Northern Hemisphere'; END CASE; END; """
we can now call the stored procedure as follows:sql.call '{call Hemisphere(?, ?, ?)}', ['Guillaume', 'Laforge', Sql.VARCHAR], { dwells
which will output '->
println dwells }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.
- Parameters:
sql
- the sql statementparams
- a list of parametersclosure
- called for each row with a GroovyResultSet- Throws:
SQLException
- if a database access error occurs
-
call
public void call(GString gstring, @ClosureParams(value=SimpleType.class,options="java.lang.Object[]") Closure closure) throws SQLException Performs a stored procedure call with the given parameters, calling the closure once with all result objects.See
call(String, List, Closure)
for more details about creating aHemisphere(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 aFullName(IN first)
stored function. Once created, it can be called like this:def first = 'Sam' sql.call("{$Sql.VARCHAR = call FullName($first)}") { name
->
assert name == 'Sam Pullara' }Resource handling is performed automatically where appropriate.
- Parameters:
gstring
- a GString containing the SQL query with embedded paramsclosure
- called for each row with a GroovyResultSet- Throws:
SQLException
- if a database access error occurs- See Also:
-
callWithRows
public List<GroovyRowResult> callWithRows(GString gstring, @ClosureParams(value=SimpleType.class,options="java.lang.Object[]") 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.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.
- Parameters:
gstring
- a GString containing the SQL query with embedded paramsclosure
- called once with all out parameter results- Returns:
- a list of GroovyRowResult objects
- Throws:
SQLException
- if a database access error occurs- See Also:
-
callWithRows
public List<GroovyRowResult> callWithRows(String sql, List<Object> params, @ClosureParams(value=SimpleType.class,options="java.lang.Object[]") 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.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.
- Parameters:
sql
- the sql statementparams
- a list of parametersclosure
- called once with all out parameter results- Returns:
- a list of GroovyRowResult objects
- Throws:
SQLException
- if a database access error occurs- See Also:
-
callWithAllRows
public List<List<GroovyRowResult>> callWithAllRows(GString gstring, @ClosureParams(value=SimpleType.class,options="java.lang.Object[]") Closure closure) throws SQLException 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).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.
- Parameters:
gstring
- a GString containing the SQL query with embedded paramsclosure
- called once with all out parameter results- Returns:
- a list containing lists of GroovyRowResult objects
- Throws:
SQLException
- if a database access error occurs- See Also:
-
callWithAllRows
public List<List<GroovyRowResult>> callWithAllRows(String sql, List<Object> params, @ClosureParams(value=SimpleType.class,options="java.lang.Object[]") Closure closure) throws SQLException 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).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.
- Parameters:
sql
- the sql statementparams
- a list of parametersclosure
- called once with all out parameter results- Returns:
- a list containing lists of GroovyRowResult objects
- Throws:
SQLException
- if a database access error occurs- See Also:
-
close
public void close()If this SQL object was created with a Connection then this method closes the connection. If this SQL object was created from a DataSource then this method only frees any cached objects (statements in particular).- Specified by:
close
in interfaceAutoCloseable
-
getDataSource
-
commit
If this SQL object was created with a Connection then this method commits the connection. If this SQL object was created from a DataSource then this method does nothing.- Throws:
SQLException
- if a database access error occurs
-
rollback
If this SQL object was created with a Connection then this method rolls back the connection. If this SQL object was created from a DataSource then this method does nothing.- Throws:
SQLException
- if a database access error occurs
-
getUpdateCount
public int getUpdateCount()- Returns:
- Returns the updateCount.
-
getConnection
If this instance was created with a single Connection then the connection is returned. Otherwise if this instance was created with a DataSource then this method returns null- Returns:
- the connection wired into this object, or null if this object uses a DataSource
-
withStatement
public void withStatement(@ClosureParams(value=SimpleType.class,options="java.sql.Statement") Closure configureStatement) Allows a closure to be passed in to configure the JDBC statements before they are executed. It can be used to do things like set the query size etc. When this method is invoked, the supplied closure is saved. Statements subsequently created from other methods will then be configured using this closure. The statement being configured is passed into the closure as its single argument, e.g.:sql.withStatement{ stmt
->
stmt.maxRows = 10 } def firstTenRows = sql.rows("select * from table")- Parameters:
configureStatement
- the closure
-
withCleanupStatement
public void withCleanupStatement(@ClosureParams(value=SimpleType.class,options="java.sql.Statement") Closure cleanupStatement) Allows a closure to be passed in which acts as a hook for JDBC statements before they are closed. It can be used to do things like check SQL warnings, e.g.:sql.withCleanupStatement{ stmt
->
assert !stmt.warnings } def rows = sql.rows("select * from table")- Parameters:
cleanupStatement
- the closure- Since:
- 4.0.1
-
setCacheStatements
public void setCacheStatements(boolean cacheStatements) Enables statement caching.
if cacheStatements is true, cache is created and all created prepared statements will be cached. if cacheStatements is false, all cached statements will be properly closed.- Parameters:
cacheStatements
- the new value
-
isCacheStatements
public boolean isCacheStatements()- Returns:
- boolean true if cache is enabled (default is false)
-
cacheConnection
public void cacheConnection(@ClosureParams(value=SimpleType.class,options="java.sql.Connection") Closure closure) throws SQLException Caches the connection used while the closure is active. If the closure takes a single argument, it will be called with the connection, otherwise it will be called with no arguments.- Parameters:
closure
- the given closure- Throws:
SQLException
- if a database error occurs
-
withTransaction
public void withTransaction(@ClosureParams(value=SimpleType.class,options="java.sql.Connection") Closure closure) throws SQLException Performs the closure within a transaction using a cached connection. If the closure takes a single argument, it will be called with the connection, otherwise it will be called with no arguments.- Parameters:
closure
- the given closure- Throws:
SQLException
- if a database error occurs
-
isWithinBatch
public boolean isWithinBatch()Returns true if the current Sql object is currently executing a withBatch method call.- Returns:
- true if a withBatch call is currently being executed.
-
withBatch
public int[] withBatch(@ClosureParams(value=SimpleType.class,options="groovy.sql.BatchingStatementWrapper") Closure closure) throws SQLException Performs the closure (containing batch operations) within a batch. Uses a batch size of zero, i.e. no automatic partitioning of batches.This means that
executeBatch()
will be called automatically after thewithBatch
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
For integrity and performance reasons, you may wish to consider executing your batch command(s) within a transaction:->
stmt.addBatch("insert into TABLENAME ...") stmt.addBatch("insert into TABLENAME ...") stmt.addBatch("insert into TABLENAME ...") ... }sql.withTransaction { def result1 = sql.withBatch { ... } ... }
- Parameters:
closure
- the closure containing batch and optionally other statements- Returns:
- an array of update counts containing one element for each command in the batch. The elements of the array are ordered according to the order in which commands were added to the batch.
- Throws:
SQLException
- if a database access error occurs, or this method is called on a closedStatement
, or the driver does not support batch statements. ThrowsBatchUpdateException
(a subclass ofSQLException
) if one of the commands sent to the database fails to execute properly or attempts to return a result set.- See Also:
-
withBatch
public int[] withBatch(int batchSize, @ClosureParams(value=SimpleType.class,options="groovy.sql.BatchingStatementWrapper") Closure closure) throws SQLException Performs the closure (containing batch operations) within a batch using a given batch size.After every
batchSize
addBatch(sqlBatchOperation)
operations, automatically calls anexecuteBatch()
operation to "chunk" up the database operations into partitions. Though not normally needed, you can also explicitly callexecuteBatch()
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
For integrity and performance reasons, you may wish to consider executing your batch command(s) within a transaction:->
stmt.addBatch("insert into TABLENAME ...") stmt.addBatch("insert into TABLENAME ...") stmt.addBatch("insert into TABLENAME ...") ... }sql.withTransaction { def result1 = sql.withBatch { ... } ... }
- Parameters:
batchSize
- partition the batch into batchSize pieces, i.e. after batchSizeaddBatch()
invocations, callexecuteBatch()
automatically; 0 means manual calls to executeBatch are requiredclosure
- the closure containing batch and optionally other statements- Returns:
- an array of update counts containing one element for each command in the batch. The elements of the array are ordered according to the order in which commands were added to the batch.
- Throws:
SQLException
- if a database access error occurs, or this method is called on a closedStatement
, or the driver does not support batch statements. ThrowsBatchUpdateException
(a subclass ofSQLException
) if one of the commands sent to the database fails to execute properly or attempts to return a result set.- See Also:
-
withBatch
public int[] withBatch(String sql, @ClosureParams(value=SimpleType.class,options="groovy.sql.BatchingPreparedStatementWrapper") Closure closure) throws SQLException Performs the closure (containing batch operations specific to an associated prepared statement) within a batch. Uses a batch size of zero, i.e. no automatic partitioning of batches.This means that
executeBatch()
will be called automatically after thewithBatch
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
For integrity and performance reasons, you may wish to consider executing your batch command(s) within a transaction:->
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) ... }sql.withTransaction { def result1 = sql.withBatch { ... } ... }
- Parameters:
sql
- batch update statementclosure
- the closure containing batch statements (to bind parameters) and optionally other statements- Returns:
- an array of update counts containing one element for each binding in the batch. The elements of the array are ordered according to the order in which commands were executed.
- Throws:
SQLException
- if a database access error occurs, or this method is called on a closedStatement
, or the driver does not support batch statements. ThrowsBatchUpdateException
(a subclass ofSQLException
) if one of the commands sent to the database fails to execute properly or attempts to return a result set.- See Also:
-
withBatch
public int[] withBatch(int batchSize, String sql, @ClosureParams(value=SimpleType.class,options="groovy.sql.BatchingPreparedStatementWrapper") Closure closure) throws SQLException Performs the closure (containing batch operations specific to an associated prepared statement) within a batch using a given batch size.After every
batchSize
addBatch(params)
operations, automatically calls anexecuteBatch()
operation to "chunk" up the database operations into partitions. Though not normally needed, you can also explicitly callexecuteBatch()
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
Named parameters (into maps or domain objects) are also supported:->
ps.addBatch(10, 12, 5) // varargs style ps.addBatch([7, 3, 98]) // list ps.addBatch([22, 67, 11]) ... }def updateCounts = sql.withBatch(20, 'insert into TABLENAME(a, b, c) values (:foo, :bar, :baz)') { ps
Named ordinal parameters (into maps or domain objects) are also supported:->
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 ... }def updateCounts = sql.withBatch(20, 'insert into TABLENAME(a, b, c) values (?1.foo, ?2.bar, ?2.baz)') { ps
For integrity and performance reasons, you may wish to consider executing your batch command(s) within a transaction:->
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')) ... }sql.withTransaction { def result1 = sql.withBatch { ... } ... }
- Parameters:
batchSize
- partition the batch into batchSize pieces, i.e. after batchSizeaddBatch()
invocations, callexecuteBatch()
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 statements- Returns:
- an array of update counts containing one element for each binding in the batch. The elements of the array are ordered according to the order in which commands were executed.
- Throws:
SQLException
- if a database access error occurs, or this method is called on a closedStatement
, or the driver does not support batch statements. ThrowsBatchUpdateException
(a subclass ofSQLException
) if one of the commands sent to the database fails to execute properly or attempts to return a result set.- See Also:
-
cacheStatements
public void cacheStatements(@ClosureParams(value=SimpleType.class,options="java.sql.Connection") Closure closure) throws SQLException Caches every created preparedStatement in Closure closure Every cached preparedStatement is closed after closure has been called. If the closure takes a single argument, it will be called with the connection, otherwise it will be called with no arguments.- Parameters:
closure
- the given closure- Throws:
SQLException
- if a database error occurs- See Also:
-
callWithRows
protected List<List<GroovyRowResult>> callWithRows(String sql, List<Object> params, int processResultsSets, @ClosureParams(value=SimpleType.class,options="java.lang.Object[]") Closure closure) throws SQLException Base internal method for call(), callWithRows(), and callWithAllRows() style of methods.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.
- Parameters:
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 results- Returns:
- a list of GroovyRowResult objects
- Throws:
SQLException
- if a database access error occurs- See Also:
-
executeQuery
Useful helper method which handles resource management when executing a query which returns a result set. Derived classes of Sql can override "createQueryCommand" and then call this method to access the ResultSet returned from the provided query or alternatively can use the higher-level method of Sql which return result sets which are funnelled through this method, e.g. eachRow, query.- Parameters:
sql
- query to execute- Returns:
- the resulting ResultSet
- Throws:
SQLException
- if a database error occurs
-
executePreparedQuery
Useful helper method which handles resource management when executing a prepared query which returns a result set. Derived classes of Sql can override "createPreparedQueryCommand" and then call this method to access the ResultSet returned from the provided query.- Parameters:
sql
- query to executeparams
- parameters matching question mark placeholders in the query- Returns:
- the resulting ResultSet
- Throws:
SQLException
- if a database error occurs
-
asList
Hook to allow derived classes to override list of result collection behavior. The default behavior is to return a list of GroovyRowResult objects corresponding to each row in the ResultSet.- Parameters:
sql
- query to executers
- the ResultSet to process- Returns:
- the resulting list of rows
- Throws:
SQLException
- if a database error occurs
-
asList
protected List<GroovyRowResult> asList(String sql, ResultSet rs, @ClosureParams(value=SimpleType.class,options="java.sql.ResultSetMetaData") Closure metaClosure) throws SQLException Hook to allow derived classes to override list of result collection behavior. The default behavior is to return a list of GroovyRowResult objects corresponding to each row in the ResultSet.- Parameters:
sql
- query to executers
- the ResultSet to processmetaClosure
- called for metadata (only once after sql execution)- Returns:
- the resulting list of rows
- Throws:
SQLException
- if a database error occurs
-
asList
protected List<GroovyRowResult> asList(String sql, ResultSet rs, int offset, int maxRows, @ClosureParams(value=SimpleType.class,options="java.sql.ResultSetMetaData") Closure metaClosure) throws SQLException - Throws:
SQLException
-
asSql
Hook to allow derived classes to override sql generation from GStrings.- Parameters:
gstring
- a GString containing the SQL query with embedded paramsvalues
- the values to embed- Returns:
- the SQL version of the given query using ? instead of any parameter
- See Also:
-
nullify
Hook to allow derived classes to override null handling. Default behavior is to replace ?'"? references with NULLish- Parameters:
sql
- the SQL statement- Returns:
- the modified SQL String
-
findWhereKeyword
Hook to allow derived classes to override where clause sniffing. Default behavior is to find the first 'where' keyword in the sql doing simple avoidance of the word 'where' within quotes.- Parameters:
sql
- the SQL statement- Returns:
- the index of the found keyword or -1 if not found
-
getParameters
Hook to allow derived classes to override behavior associated with extracting params from a GString.- Parameters:
gstring
- a GString containing the SQL query with embedded params- Returns:
- extracts the parameters from the expression as a List
- See Also:
-
setParameters
Hook to allow derived classes to override behavior associated with setting params for a prepared statement. Default behavior is to append the parameters to the given statement usingsetObject
.- Parameters:
params
- the parameters to appendstatement
- the statement- Throws:
SQLException
- if a database access error occurs
-
setObject
Strategy method allowing derived classes to handle types differently such as for CLOBs etc.- Parameters:
statement
- the statement of interesti
- the index of the object of interestvalue
- the new object value- Throws:
SQLException
- if a database access error occurs
-
createConnection
An extension point allowing derived classes to change the behavior of connection creation. The default behavior is to either use the supplied connection or obtain it from the supplied datasource.- Returns:
- the connection associated with this Sql
- Throws:
SQLException
- if a SQL error occurs
-
closeResources
An extension point allowing derived classes to change the behavior of resource closing.- Parameters:
connection
- the connection to closestatement
- the statement to closeresults
- the results to close
-
closeResources
An extension point allowing the behavior of resource closing to be overridden in derived classes.- Parameters:
connection
- the connection to closestatement
- the statement to close
-
closeResources
An extension point allowing the behavior of resource closing to be overridden in derived classes.- Parameters:
connection
- the connection to close
-
configure
Provides a hook for derived classes to be able to configure JDBC statements. Default behavior is to call a previously saved closure, if any, using the statement as a parameter.- Parameters:
statement
- the statement to configure
-
cleanup
Provides a hook for derived classes to be able to examine JDBC statements before cleanup/closing. Default behavior is to call a previously saved closure, if any, using the statement as a parameter.- Parameters:
statement
- the statement to clean up- Since:
- 4.0.1
-
checkForNamedParams
-
preCheckForNamedParams
Deprecated.UsebuildSqlWithIndexedProps(String)
instead -
buildSqlWithIndexedProps
Hook to allow derived classes to override behavior associated with the parsing and indexing of parameters from a given sql statement.- Parameters:
sql
- the sql statement to process- Returns:
- a
SqlWithParams
instance containing the parsed sql and parameters containing the indexed location and property name of parameters ornull
if no parsing of the sql was performed.
-
getUpdatedParams
-
isCacheNamedQueries
public boolean isCacheNamedQueries()- Returns:
- boolean true if caching is enabled (the default is true)
-
setCacheNamedQueries
public void setCacheNamedQueries(boolean cacheNamedQueries) Enables named query caching.
if cacheNamedQueries is true, cache is created and processed named queries will be cached. if cacheNamedQueries is false, no caching will occur saving memory at the cost of additional processing time.- Parameters:
cacheNamedQueries
- the new value
-
isEnableNamedQueries
public boolean isEnableNamedQueries()- Returns:
- boolean true if named query processing is enabled (the default is true)
-
setEnableNamedQueries
public void setEnableNamedQueries(boolean enableNamedQueries) Enables named query support:- if enableNamedQueries is true, queries with ':propname' and '?1.propname' style placeholders will be processed.
- if enableNamedQueries is false, this feature will be turned off.
- Parameters:
enableNamedQueries
- the new value
-
createQueryCommand
Factory for the QueryCommand command pattern object allows subclasses to supply implementations of the command class. The factory will be used in a pattern similar to:AbstractQueryCommand q = createQueryCommand("update TABLE set count = 0) where count is null"); try { ResultSet rs = q.execute(); return asList(rs); } finally { q.closeResources(); }
- Parameters:
sql
- statement to be executed- Returns:
- a command - invoke its execute() and closeResource() methods
-
createPreparedQueryCommand
Factory for the PreparedQueryCommand command pattern object allows subclass to supply implementations of the command class.- Parameters:
sql
- statement to be executed, including optional parameter placeholders (?)queryParams
- List of parameter values corresponding to parameter placeholders- Returns:
- a command - invoke its execute() and closeResource() methods
- See Also:
-
setInternalConnection
Stub needed for testing. Called when a connection is opened by one of the command-pattern classes so that a test case can monitor the state of the connection through its subclass.- Parameters:
conn
- the connection that is about to be used by a command
-
buildSqlWithIndexedProps(String)
instead