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

 println sql.rows('select * from PROJECT where name=:foo', [foo:'Gradle'])
 println sql.rows('select * from PROJECT where name=:foo and id=?.bar', [foo:'Gradle', bar:40])
 class MyDomainClass { def baz = 'Griffon' }
 println sql.rows('select * from PROJECT where name=?.baz', new MyDomainClass())
 

 println sql.rows("select * from PROJECT where name=?1.baz and id=?2.num", new MyDomainClass(), [num:30])
 

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.

Authors:

Chris Stevenson

James Strachan

Paul King

Marc DeXeT

John Bito

John Hurst

Version:

\$Revision: 21090 \$

ARRAY

public static final OutParameter ARRAY

BIGINT

public static final OutParameter BIGINT

BINARY

public static final OutParameter BINARY

BIT

public static final OutParameter BIT

BLOB

public static final OutParameter BLOB

BOOLEAN

public static final OutParameter BOOLEAN

CHAR

public static final OutParameter CHAR

CLOB

public static final OutParameter CLOB

DATALINK

public static final OutParameter DATALINK

DATE

public static final OutParameter DATE

DECIMAL

public static final OutParameter DECIMAL

DISTINCT

public static final OutParameter DISTINCT

DOUBLE

public static final OutParameter DOUBLE

FLOAT

public static final OutParameter FLOAT

INTEGER

public static final OutParameter INTEGER

JAVA_OBJECT

public static final OutParameter JAVA_OBJECT

LOG

protected static final Logger LOG

Hook to allow derived classes to access the log

LONGVARBINARY

public static final OutParameter LONGVARBINARY

LONGVARCHAR

public static final OutParameter LONGVARCHAR

NULL

public static final OutParameter NULL

NUMERIC

public static final OutParameter NUMERIC

OTHER

public static final OutParameter OTHER

REAL

public static final OutParameter REAL

REF

public static final OutParameter REF

SMALLINT

public static final OutParameter SMALLINT

STRUCT

public static final OutParameter STRUCT

TIME

public static final OutParameter TIME

TIMESTAMP

public static final OutParameter TIMESTAMP

TINYINT

public static final OutParameter TINYINT

VARBINARY

public static final OutParameter VARBINARY

VARCHAR

public static final OutParameter VARCHAR

Sql

public Sql(DataSource dataSource)

Constructs an SQL instance using the given DataSource. Each operation will use a Connection from the DataSource pool and close it when the operation is completed putting it back into the pool.

Parameters:

dataSource - the DataSource to use

Sql

public Sql(Connection connection)

Constructs an SQL instance using the given Connection. It is the caller's responsibility to close the Connection after the Sql instance has been used. 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 the close() method which will close the connection but also free any caches resources.

Parameters:

connection - the Connection to use

Sql

public Sql(Sql parent)

ARRAY

public static InParameter ARRAY(Object value)

BIGINT

public static InParameter BIGINT(Object value)

BINARY

public static InParameter BINARY(Object value)

BIT

public static InParameter BIT(Object value)

BLOB

public static InParameter BLOB(Object value)

BOOLEAN

public static InParameter BOOLEAN(Object value)

CHAR

public static InParameter CHAR(Object value)

CLOB

public static InParameter CLOB(Object value)

DATALINK

public static InParameter DATALINK(Object value)

DATE

public static InParameter DATE(Object value)

DECIMAL

public static InParameter DECIMAL(Object value)

DISTINCT

public static InParameter DISTINCT(Object value)

DOUBLE

public static InParameter DOUBLE(Object value)

FLOAT

public static InParameter FLOAT(Object value)

INTEGER

public static InParameter INTEGER(Object value)

JAVA_OBJECT

public static InParameter JAVA_OBJECT(Object value)

LONGVARBINARY

public static InParameter LONGVARBINARY(Object value)

LONGVARCHAR

public static InParameter LONGVARCHAR(Object value)

NULL

public static InParameter NULL(Object value)

NUMERIC

public static InParameter NUMERIC(Object value)

OTHER

public static InParameter OTHER(Object value)

REAL

public static InParameter REAL(Object value)

REF

public static InParameter REF(Object value)

SMALLINT

public static InParameter SMALLINT(Object value)

STRUCT

public static InParameter STRUCT(Object value)

TIME

public static InParameter TIME(Object value)

TIMESTAMP

public static InParameter TIMESTAMP(Object value)

TINYINT

public static InParameter TINYINT(Object value)

VARBINARY

public static InParameter VARBINARY(Object value)

VARCHAR

public static InParameter VARCHAR(Object value)

asList

protected List asList(String sql, ResultSet rs)

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.

throws:

SQLException if a database error occurs

Parameters:

sql - query to execute

rs - the ResultSet to process

Returns:

the resulting list of rows

asList

protected List asList(String sql, ResultSet rs, Closure metaClosure)

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.

throws:

SQLException if a database error occurs

Parameters:

sql - query to execute

rs - the ResultSet to process

metaClosure - called for meta data (only once after sql execution)

Returns:

the resulting list of rows

asSql

protected String asSql(GString gstring, List values)

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

Parameters:

gstring - a GString containing the SQL query with embedded params

values - the values to embed

Returns:

the SQL version of the given query using ? instead of any parameter

See Also:

expand(Object)

cacheConnection

public void cacheConnection(Closure closure)

Caches the connection used while the closure is active. If the closure takes a single argument, it will be called with the connection, otherwise it will be called with no arguments.

throws:

SQLException if a database error occurs

Parameters:

closure - the given closure

cacheStatements

public void cacheStatements(Closure closure)

Caches every created preparedStatement in Closure closure
Every cached preparedStatement is closed after closure has been called. If the closure takes a single argument, it will be called with the connection, otherwise it will be called with no arguments.

throws:

SQLException if a database error occurs

Parameters:

closure - the given closure

See Also:

setCacheStatements(boolean)

call

public int call(String sql)

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
 

throws:

SQLException if a database access error occurs

Parameters:

sql - the SQL statement

Returns:

the number of rows updated or 0 for SQL statements that return nothing

call

public int call(GString gstring)

Performs a stored procedure call with the given embedded parameters.

Example usage - see call(String) for more details about creating a HouseSwap(IN name1, IN name2) stored procedure. Once created, it can be called like this:

 def p1 = 'Paul'
 def p2 = 'Guillaume'
 def rowsChanged = sql.call("{call HouseSwap($p1, $p2)}")
 assert rowsChanged == 2
 

Resource handling is performed automatically where appropriate.

throws:

SQLException if a database access error occurs

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

See Also:

expand(Object)

call(String)

call

public int call(String sql, List params)

Performs a stored procedure call with the given parameters.

Example usage - see call(String) for more details about creating a HouseSwap(IN name1, IN name2) stored procedure. Once created, it can be called like this:

 def rowsChanged = sql.call("{call HouseSwap(?, ?)}", ['Guillaume', 'Paul'])
 assert rowsChanged == 2
 

Resource handling is performed automatically where appropriate.

throws:

SQLException if a database access error occurs

Parameters:

sql - the SQL statement

params - a list of parameters

Returns:

the number of rows updated or 0 for SQL statements that return nothing

See Also:

call(String)

call

public int call(String sql, Object[] params)

Performs a stored procedure call with the given parameters.

An Object array variant of call(String, List).

throws:

SQLException if a database access error occurs

Parameters:

sql - the SQL statement

params - an array of parameters

Returns:

the number of rows updated or 0 for SQL statements that return nothing

See Also:

call(String)

call

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

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

Example usage - suppose we create a stored procedure (ignore its simplistic implementation):

 // Tested with MySql 5.0.75
 sql.execute """
     CREATE PROCEDURE Hemisphere(
         IN p_firstname VARCHAR(50),
         IN p_lastname VARCHAR(50),
         OUT ans VARCHAR(50))
     BEGIN
     DECLARE loc INT;
     SELECT location_id into loc FROM PERSON where firstname = p_firstname and lastname = p_lastname;
     CASE loc
         WHEN 40 THEN
             SET ans = 'Southern Hemisphere';
         ELSE
             SET ans = 'Northern Hemisphere';
     END CASE;
     END;
 """
 

we can now call the stored procedure as follows:

 sql.call '{call Hemisphere(?, ?, ?)}', ['Guillaume', 'Laforge', Sql.VARCHAR], { dwells ->
     println dwells
 }
 

which will output 'Northern Hemisphere'.

We can also access stored functions with scalar return values where the return value will be treated as an OUT parameter. Here are examples for various databases for creating such a procedure:

 // Tested with MySql 5.0.75
 sql.execute """
     create function FullName(p_firstname VARCHAR(40)) returns VARCHAR(80)
     begin
         declare ans VARCHAR(80);
         SELECT CONCAT(firstname, ' ', lastname) INTO ans FROM PERSON WHERE firstname = p_firstname;
         return ans;
     end
 """

 // Tested with MS SQLServer Express 2008
 sql.execute """
     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;
 """
 

and here is how you access the stored function for all databases:

 sql.call("{? = call FullName(?)}", [Sql.VARCHAR, 'Sam']) { name ->
     assert name == 'Sam Pullara'
 }
 

Resource handling is performed automatically where appropriate.

throws:

SQLException if a database access error occurs

Parameters:

sql - the sql statement

params - a list of parameters

closure - called for each row with a GroovyResultSet

call

public void call(GString gstring, Closure closure)

Performs a stored procedure call with the given parameters, calling the closure once with all result objects.

See call(String, List, Closure) for more details about creating a Hemisphere(IN first, IN last, OUT dwells) stored procedure. Once created, it can be called like this:

 def first = 'Scott'
 def last = 'Davis'
 sql.call "{call Hemisphere($first, $last, ${Sql.VARCHAR})}", { dwells ->
     println dwells
 }
 

As another example, see call(String, List, Closure) for more details about creating a FullName(IN first) stored function. Once created, it can be called like this:

 def first = 'Sam'
 sql.call("{$Sql.VARCHAR = call FullName($first)}") { name ->
     assert name == 'Sam Pullara'
 }
 

Resource handling is performed automatically where appropriate.

throws:

SQLException if a database access error occurs

Parameters:

gstring - a GString containing the SQL query with embedded params

closure - called for each row with a GroovyResultSet

See Also:

call(String, List, Closure)

expand(Object)

checkForNamedParams

public SqlWithParams checkForNamedParams(String sql, List params)

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

closeResources

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

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

Parameters:

connection - the connection to close

statement - the statement to close

results - the results to close

closeResources

protected void closeResources(Connection connection, Statement statement)

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

Parameters:

connection - the connection to close

statement - the statement to close

closeResources

protected void closeResources(Connection connection)

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

Parameters:

connection - the connection to close

commit

public void commit()

If this SQL object was created with a Connection then this method commits the connection. If this SQL object was created from a DataSource then this method does nothing.

throws:

SQLException if a database access error occurs

configure

protected void configure(Statement statement)

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

Parameters:

statement - the statement to configure

createConnection

protected Connection createConnection()

An extension point allowing derived classes to change the behavior of connection creation. The default behavior is to either use the supplied connection or obtain it from the supplied datasource.

throws:

java.sql.SQLException if a SQL error occurs

Returns:

the connection associated with this Sql

createPreparedQueryCommand

protected Sql.AbstractQueryCommand createPreparedQueryCommand(String sql, List queryParams)

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:

createQueryCommand(String)

createQueryCommand

protected Sql.AbstractQueryCommand createQueryCommand(String sql)

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

dataSet

public DataSet dataSet(String table)

dataSet

public DataSet dataSet(Class type)

eachRow

public void eachRow(String sql, Closure closure)

Performs the given SQL query calling the given Closure with each row of the result set. The row will be a GroovyRowResult which is a Map that also supports accessing the fields using 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.

throws:

SQLException if a database access error occurs

Parameters:

sql - the sql statement

closure - called for each row with a GroovyResultSet

eachRow

public void eachRow(String sql, Closure metaClosure, Closure rowClosure)

Performs the given SQL query calling the given rowClosure with each row of the result set. The row will be a GroovyRowResult which is a Map that also supports accessing the fields using ordinal index values. In addition, the metaClosure will be called once passing in the ResultSetMetaData as argument.

Example usage:

 def printColNames = { meta ->
     (1..meta.columnCount).each {
         print meta.getColumnLabel(it).padRight(20)
     }
     println()
 }
 def printRow = { row ->
     row.toRowResult().values().each{ print it.toString().padRight(20) }
     println()
 }
 sql.eachRow("select * from PERSON", printColNames, printRow)
 

Resource handling is performed automatically where appropriate.

throws:

SQLException if a database access error occurs

Parameters:

sql - the sql statement

metaClosure - called for meta data (only once after sql execution)

rowClosure - called for each row with a GroovyResultSet

eachRow

public void eachRow(String sql, List params, Closure metaClosure, Closure closure)

Performs the given SQL query calling the given Closure with each row of the result set. The row will be a GroovyRowResult which is a Map that also supports accessing the fields using ordinal index values. In addition, the metaClosure will be called once passing in the ResultSetMetaData as argument. The query may contain placeholder question marks which match the given list of parameters.

Example usage:

 def printColNames = { meta ->
     (1..meta.columnCount).each {
         print meta.getColumnLabel(it).padRight(20)
     }
     println()
 }
 def printRow = { row ->
     row.toRowResult().values().each{ print it.toString().padRight(20) }
     println()
 }
 sql.eachRow("select * from PERSON where lastname like ?", ['%a%'], printColNames, printRow)
 

This method supports named and named ordinal parameters. See the class Javadoc for more details.

Resource handling is performed automatically where appropriate.

throws:

SQLException if a database access error occurs

Parameters:

sql - the sql statement

params - a list of parameters

metaClosure - called for meta data (only once after sql execution)

closure - called for each row with a GroovyResultSet

eachRow

public void eachRow(String sql, List params, Closure closure)

Performs the given SQL query calling the given Closure with each row of the result set. The row will be a GroovyRowResult which is a Map that also supports accessing the fields using 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.

throws:

SQLException if a database access error occurs

Parameters:

sql - the sql statement

params - a list of parameters

closure - called for each row with a GroovyResultSet

eachRow

public void eachRow(GString gstring, Closure metaClosure, Closure closure)

Performs the given SQL query calling the given Closure with each row of the result set. The row will be a GroovyRowResult which is a Map that also supports accessing the fields using ordinal index values. In addition, the metaClosure will be called once passing in the ResultSetMetaData as argument. The query may contain GString expressions.

Example usage:

 def location = 25
 def printColNames = { meta ->
     (1..meta.columnCount).each {
         print meta.getColumnLabel(it).padRight(20)
     }
     println()
 }
 def printRow = { row ->
     row.toRowResult().values().each{ print it.toString().padRight(20) }
     println()
 }
 sql.eachRow("select * from PERSON where location_id < $location", printColNames, printRow)
 

Resource handling is performed automatically where appropriate.

throws:

SQLException if a database access error occurs

Parameters:

gstring - a GString containing the SQL query with embedded params

metaClosure - called for meta data (only once after sql execution)

closure - called for each row with a GroovyResultSet

See Also:

expand(Object)

eachRow

public void eachRow(GString gstring, Closure closure)

Performs the given SQL query calling the given Closure with each row of the result set. The row will be a GroovyRowResult which is a Map that also supports accessing the fields using 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.

throws:

SQLException if a database access error occurs

Parameters:

gstring - a GString containing the SQL query with embedded params

closure - called for each row with a GroovyResultSet

See Also:

expand(Object)

execute

public boolean execute(String sql)

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.

throws:

SQLException if a database access error occurs

Parameters:

sql - the SQL to execute

Returns:

true if the first result is a ResultSet object; false if it is an update count or there are no results

execute

public boolean execute(String sql, List params)

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.

throws:

SQLException if a database access error occurs

Parameters:

sql - the SQL statement

params - a list of parameters

Returns:

true if the first result is a ResultSet object; false if it is an update count or there are no results

execute

public boolean execute(String sql, Object[] params)

Executes the given piece of SQL with parameters. An Object array variant of execute(String, List).

throws:

SQLException if a database access error occurs

Parameters:

sql - the SQL statement

params - an array of parameters

Returns:

true if the first result is a ResultSet object; false if it is an update count or there are no results

execute

public boolean execute(GString gstring)

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.

throws:

SQLException if a database access error occurs

Parameters:

gstring - a GString containing the SQL query with embedded params

Returns:

true if the first result is a ResultSet object; false if it is an update count or there are no results

See Also:

expand(Object)

executeInsert

public List executeInsert(String sql)

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. See executeInsert(GString) for more details. Resource handling is performed automatically where appropriate.

throws:

SQLException if a database access error occurs

Parameters:

sql - The SQL statement to execute

Returns:

A list of the auto-generated column values for each inserted row (typically auto-generated keys)

executeInsert

public List executeInsert(String sql, List params)

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. See executeInsert(GString) for more details. This method supports named and named ordinal parameters. See the class Javadoc for more details.

Resource handling is performed automatically where appropriate.

throws:

SQLException if a database access error occurs

Parameters:

sql - The SQL statement to execute

params - The parameter values that will be substituted into the SQL statement's parameter slots

Returns:

A list of the auto-generated column values for each inserted row (typically auto-generated keys)

executeInsert

public List executeInsert(String sql, Object[] params)

Executes the given SQL statement (typically an INSERT statement). An Object array variant of executeInsert(String, List).

throws:

SQLException if a database access error occurs

Parameters:

sql - The SQL statement to execute

params - The parameter values that will be substituted into the SQL statement's parameter slots

Returns:

A list of the auto-generated column values for each inserted row (typically auto-generated keys)

executeInsert

public List executeInsert(GString gstring)

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.

throws:

SQLException if a database access error occurs

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)

See Also:

expand(Object)

executePreparedQuery

protected final ResultSet executePreparedQuery(String sql, List params)

Hook to allow derived classes to access ResultSet returned from query.

throws:

SQLException if a database error occurs

Parameters:

sql - query to execute

params - parameters matching question mark placeholders in the query

Returns:

the resulting ResultSet

executeQuery

protected final ResultSet executeQuery(String sql)

Hook to allow derived classes to access ResultSet returned from query.

throws:

SQLException if a database error occurs

Parameters:

sql - query to execute

Returns:

the resulting ResultSet

executeUpdate

public int executeUpdate(String sql)

Executes the given SQL update. Resource handling is performed automatically where appropriate.

throws:

SQLException if a database access error occurs

Parameters:

sql - the SQL to execute

Returns:

the number of rows updated or 0 for SQL statements that return nothing

executeUpdate

public int executeUpdate(String sql, List params)

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.

throws:

SQLException if a database access error occurs

Parameters:

sql - the SQL statement

params - a list of parameters

Returns:

the number of rows updated or 0 for SQL statements that return nothing

executeUpdate

public int executeUpdate(String sql, Object[] params)

Executes the given SQL update with parameters. An Object array variant of executeUpdate(String, List).

throws:

SQLException if a database access error occurs

Parameters:

sql - the SQL statement

params - an array of parameters

Returns:

the number of rows updated or 0 for SQL statements that return nothing

executeUpdate

public int executeUpdate(GString gstring)

Executes the given SQL update with embedded expressions inside. Resource handling is performed automatically where appropriate.

throws:

SQLException if a database access error occurs

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

See Also:

expand(Object)

expand

public static ExpandedVariable expand(Object object)

When using GString SQL queries, allows a variable to be expanded in the Sql string rather than representing an sql parameter.

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:

expand(Object)

findWhereKeyword

protected int findWhereKeyword(String sql)

Hook to allow derived classes to override where clause sniffing. Default behavior is to find the first 'where' keyword in the sql doing simple avoidance of the word 'where' within quotes.

Parameters:

sql - the SQL statement

Returns:

the index of the found keyword or -1 if not found

firstRow

public Object firstRow(String sql)

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

Example usage:

 def ans = sql.firstRow("select * from PERSON where firstname like 'S%'")
 println ans.firstname
 

Resource handling is performed automatically where appropriate.

throws:

SQLException if a database access error occurs

Parameters:

sql - the SQL statement

Returns:

a GroovyRowResult object or null if no row is found

firstRow

public Object firstRow(GString gstring)

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

Resource handling is performed automatically where appropriate.

throws:

SQLException if a database access error occurs

Parameters:

gstring - a GString containing the SQL query with embedded params

Returns:

a GroovyRowResult object or null if no row is found

See Also:

expand(Object)

firstRow

public Object firstRow(String sql, List params)

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'
 

Resource handling is performed automatically where appropriate.

throws:

SQLException if a database access error occurs

Parameters:

sql - the SQL statement

params - a list of parameters

Returns:

a GroovyRowResult object or null if no row is found

firstRow

public Object firstRow(String sql, Object[] params)

Performs the given SQL query and return the first row of the result set. An Object array variant of firstRow(String, List).

throws:

SQLException if a database access error occurs

Parameters:

sql - the SQL statement

params - an array of parameters

Returns:

a GroovyRowResult object or null if no row is found

getConnection

public Connection getConnection()

If this instance was created with a single Connection then the connection is returned. Otherwise if this instance was created with a DataSource then this method returns null

Returns:

the connection wired into this object, or null if this object uses a DataSource

getDataSource

public DataSource getDataSource()

getParameters

protected List getParameters(GString gstring)

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

Parameters:

gstring - a GString containing the SQL query with embedded params

Returns:

extracts the parameters from the expression as a List

See Also:

expand(Object)

getResultSetConcurrency

public int getResultSetConcurrency()

Gets the resultSetConcurrency for statements created using the connection.

Returns:

the current resultSetConcurrency value

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

getResultSetType

public int getResultSetType()

Gets the resultSetType for statements created using the connection.

Returns:

the current resultSetType value

Since:

1.5.2

getUpdateCount

public int getUpdateCount()

Returns:

Returns the updateCount.

in

public static InParameter in(int type, Object value)

Create a new InParameter

Parameters:

type - the JDBC data type

value - the object value

Returns:

an InParameter

inout

public static InOutParameter inout(InParameter in)

Create an inout parameter using this in parameter.

Parameters:

in - the InParameter of interest

Returns:

the resulting InOutParameter

isCacheNamedQueries

public boolean isCacheNamedQueries()

Returns:

boolean true if caching is enabled (the default is true)

isCacheStatements

public boolean isCacheStatements()

Returns:

boolean true if cache is enabled (default is false)

isEnableNamedQueries

public boolean isEnableNamedQueries()

Returns:

boolean true if named query processing is enabled (the default is true)

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.

loadDriver

public static void loadDriver(String driverClassName)

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

throws:

ClassNotFoundException if the class cannot be found or loaded

Parameters:

driverClassName - the fully qualified class name of the driver class

newInstance

public static Sql newInstance(String url)

Creates a new Sql instance given a JDBC connection URL.

throws:

SQLException if a database access error occurs

Parameters:

url - a database url of the form jdbc:subprotocol:subname

Returns:

a new Sql instance with a connection

newInstance

public static Sql newInstance(String url, Properties properties)

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

throws:

SQLException if a database access error occurs

Parameters:

url - a database url of the form jdbc:subprotocol:subname

properties - a list of arbitrary string tag/value pairs as connection arguments; normally at least a "user" and "password" property should be included

Returns:

a new Sql instance with a connection

newInstance

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

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

throws:

SQLException if a database access error occurs

throws:

ClassNotFoundException if the class cannot be found or loaded

Parameters:

url - a database url of the form jdbc:subprotocol:subname

properties - a list of arbitrary string tag/value pairs as connection arguments; normally at least a "user" and "password" property should be included

driverClassName - the fully qualified class name of the driver class

Returns:

a new Sql instance with a connection

newInstance

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

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

throws:

SQLException if a database access error occurs

Parameters:

url - a database url of the form jdbc:subprotocol:subname

user - the database user on whose behalf the connection is being made

password - the user's password

Returns:

a new Sql instance with a connection

newInstance

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

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

throws:

SQLException if a database access error occurs

throws:

ClassNotFoundException if the class cannot be found or loaded

Parameters:

url - a database url of the form jdbc:subprotocol:subname

user - the database user on whose behalf the connection is being made

password - the user's password

driverClassName - the fully qualified class name of the driver class

Returns:

a new Sql instance with a connection

newInstance

public static Sql newInstance(String url, String driverClassName)

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

throws:

SQLException if a database access error occurs

throws:

ClassNotFoundException if the class cannot be found or loaded

Parameters:

url - a database url of the form jdbc:subprotocol:subname

driverClassName - the fully qualified class name of the driver class

Returns:

a new Sql instance with a connection

newInstance

public static Sql newInstance(Map args)

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: jdbc:subprotocol:subname
 user            the database user on whose behalf the connection is being made
 password        the user's password
 properties      a list of arbitrary string tag/value pairs as connection arguments;
                 normally at least a "user" and "password" property should be included
 other           any of the public setter methods of this class may be used with property notation
                 e.g. cacheStatements: true, resultSetConcurrency: ResultSet.CONCUR_READ_ONLY
 

Of these, 'url' is required. Others may be needed depending on your database.
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.jdbcDriver',
     cacheStatements: true,
     resultSetConcurrency: CONCUR_READ_ONLY
 )
 

throws:

SQLException if a database access error occurs

throws:

ClassNotFoundException if the class cannot be found or loaded

Parameters:

args - a Map contain further arguments

Returns:

a new Sql instance with a connection

nullify

protected String nullify(String sql)

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

out

public static OutParameter out(int type)

Create a new OutParameter

Parameters:

type - the JDBC data type.

Returns:

an OutParameter

query

public void query(String sql, Closure closure)

Performs the given SQL query, which should return a single ResultSet object. The given closure is called with the ResultSet as its argument.

Example usages:

 sql.query("select * from PERSON where firstname like 'S%'") { ResultSet rs ->
     while (rs.next()) println rs.getString('firstname') + ' ' + rs.getString(3)
 }

 sql.query("call get_people_places()") { ResultSet rs ->
     while (rs.next()) println rs.toRowResult().firstname
 }
 

All resources including the ResultSet are closed automatically after the closure is called.

throws:

SQLException if a database access error occurs

Parameters:

sql - the sql statement

closure - called for each row with a GroovyResultSet

query

public void query(String sql, List params, Closure closure)

Performs the given SQL query, which should return a single ResultSet object. The given closure is called with the ResultSet as its argument. The query may contain placeholder question marks which match the given list of parameters.

Example usage:

 sql.query('select * from PERSON where lastname like ?', ['%a%']) { ResultSet rs ->
     while (rs.next()) println rs.getString('lastname')
 }
 

This method supports named and named ordinal parameters. See the class Javadoc for more details.

All resources including the ResultSet are closed automatically after the closure is called.

throws:

SQLException if a database access error occurs

Parameters:

sql - the sql statement

params - a list of parameters

closure - called for each row with a GroovyResultSet

query

public void query(GString gstring, Closure closure)

Performs the given SQL query, which should return a single ResultSet object. The given closure is called with the ResultSet as its argument. The query may contain GString expressions.

Example usage:

 def location = 25
 sql.query "select * from PERSON where location_id < $location", { ResultSet rs ->
     while (rs.next()) println rs.getString('firstname')
 }
 

All resources including the ResultSet are closed automatically after the closure is called.

throws:

SQLException if a database access error occurs

Parameters:

gstring - a GString containing the SQL query with embedded params

closure - called for each row with a GroovyResultSet

See Also:

expand(Object)

resultSet

public static ResultSetOutParameter resultSet(int type)

Create a new ResultSetOutParameter

Parameters:

type - the JDBC data type.

Returns:

a ResultSetOutParameter

rollback

public void rollback()

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

throws:

SQLException if a database access error occurs

rows

public List rows(String sql)

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

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.

throws:

SQLException if a database access error occurs

Parameters:

sql - the SQL statement

Returns:

a list of GroovyRowResult objects

rows

public List rows(String sql, Closure metaClosure)

Performs the given SQL query and return the rows of the result set. In addition, the metaClosure will be called once passing in the ResultSetMetaData as argument.

Example usage:

 def printNumCols = { meta -> println "Found $meta.columnCount columns" }
 def ans = sql.rows("select * from PERSON", printNumCols)
 println "Found ${ans.size()} rows"
 

Resource handling is performed automatically where appropriate.

throws:

SQLException if a database access error occurs

Parameters:

sql - the SQL statement

metaClosure - called with meta data of the ResultSet

Returns:

a list of GroovyRowResult objects

rows

public List rows(String sql, List params)

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"
 

Resource handling is performed automatically where appropriate.

throws:

SQLException if a database access error occurs

Parameters:

sql - the SQL statement

params - a list of parameters

Returns:

a list of GroovyRowResult objects

rows

public List rows(String sql, Object[] params)

Performs the given SQL query and return the rows of the result set. An Object array variant of rows(String, List).

throws:

SQLException if a database access error occurs

Parameters:

sql - the SQL statement

params - an array of parameters

Returns:

a list of GroovyRowResult objects

rows

public List rows(String sql, List params, Closure metaClosure)

Performs the given SQL query and return the rows of the result set. In addition, the metaClosure will be called once passing in the ResultSetMetaData as argument. The query may contain placeholder question marks which match the given list of parameters.

Example usage:

 def printNumCols = { meta -> println "Found $meta.columnCount columns" }
 def ans = sql.rows("select * from PERSON where lastname like ?", ['%a%'], printNumCols)
 println "Found ${ans.size()} rows"
 

This method supports named and named ordinal parameters by supplying such parameters in the params list. Here is an example:

 def printNumCols = { meta -> println "Found $meta.columnCount columns" }

 def mapParam = [foo: 'Smith']
 def domainParam = new MyDomainClass(bar: 'John')
 def qry = 'select * from PERSON where lastname=?1.foo and firstname=?2.bar'
 def ans = sql.rows(qry, [mapParam, domainParam], printNumCols)
 println "Found ${ans.size()} rows"

 def qry2 = 'select * from PERSON where firstname=:first and lastname=:last'
 def ans2 = sql.rows(qry2, [[last:'Smith', first:'John']], printNumCols)
 println "Found ${ans2.size()} rows"
 

See the class Javadoc for more details.

Resource handling is performed automatically where appropriate.

throws:

SQLException if a database access error occurs

Parameters:

sql - the SQL statement

params - a list of parameters

metaClosure - called for meta data (only once after sql execution)

Returns:

a list of GroovyRowResult objects

rows

public List rows(GString gstring)

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.

throws:

SQLException if a database access error occurs

Parameters:

gstring - a GString containing the SQL query with embedded params

Returns:

a list of GroovyRowResult objects

See Also:

expand(Object)

rows

public List rows(GString gstring, Closure metaClosure)

Performs the given SQL query and return the rows of the result set. In addition, the metaClosure will be called once passing in the ResultSetMetaData as argument. The query may contain GString expressions.

Example usage:

 def location = 25
 def 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.

throws:

SQLException if a database access error occurs

Parameters:

gstring - a GString containing the SQL query with embedded params

metaClosure - called with meta data of the ResultSet

Returns:

a list of GroovyRowResult objects

See Also:

expand(Object)

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

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

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

setInternalConnection

protected void setInternalConnection(Connection conn)

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

setObject

protected void setObject(PreparedStatement statement, int i, Object value)

Strategy method allowing derived classes to handle types differently such as for CLOBs etc.

throws:

SQLException if a database access error occurs

Parameters:

statement - the statement of interest

i - the index of the object of interest

value - the new object value

setParameters

protected void setParameters(List params, PreparedStatement statement)

Hook to allow derived classes to override behavior associated with setting params for a prepared statement. Default behavior is to append the parameters to the given statement using setObject.

throws:

SQLException if a database access error occurs

Parameters:

params - the parameters to append

statement - the statement

setResultSetConcurrency

public void setResultSetConcurrency(int resultSetConcurrency)

Sets the resultSetConcurrency for statements created using the connection. May cause SQLFeatureNotSupportedException exceptions to occur if the underlying database doesn't support the requested concurrency value.

Parameters:

resultSetConcurrency - one of the following ResultSet constants: ResultSet.CONCUR_READ_ONLY or ResultSet.CONCUR_UPDATABLE

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 following ResultSet constants: ResultSet.HOLD_CURSORS_OVER_COMMIT or ResultSet.CLOSE_CURSORS_AT_COMMIT

Since:

1.5.2

setResultSetType

public void setResultSetType(int resultSetType)

Sets the resultSetType for statements created using the connection. May cause SQLFeatureNotSupportedException exceptions to occur if the underlying database doesn't support the requested type value.

Parameters:

resultSetType - one of the following ResultSet constants: ResultSet.TYPE_FORWARD_ONLY, ResultSet.TYPE_SCROLL_INSENSITIVE, or ResultSet.TYPE_SCROLL_SENSITIVE

Since:

1.5.2

withBatch

public int[] withBatch(Closure closure)

Performs the closure within a batch using a cached connection. Uses a batch size of zero, i.e. no automatic partitioning of batches. Use it like this:

 def updateCounts = sql.withBatch { stmt ->
     stmt.addBatch("insert into TABLENAME ...")
     stmt.addBatch("insert into TABLENAME ...")
     stmt.addBatch("insert into TABLENAME ...")
     ...
 }
 

throws:

SQLException if a database access error occurs, or this method is called on a closed Statement, or the driver does not support batch statements. Throws BatchUpdateException (a subclass of SQLException) if one of the commands sent to the database fails to execute properly or attempts to return a result set.

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.

See Also:

withBatch(int, Closure)

withBatch

public int[] withBatch(int batchSize, Closure closure)

Performs the closure within a batch using a cached connection. The closure will be called with a single argument; the statement associated with this batch. Use it like this for batchSize of 20:

 def updateCounts = sql.withBatch(20) { stmt ->
     stmt.addBatch("insert into TABLENAME ...")
     stmt.addBatch("insert into TABLENAME ...")
     stmt.addBatch("insert into TABLENAME ...")
     ...
 }
 

throws:

SQLException if a database access error occurs, or this method is called on a closed Statement, or the driver does not support batch statements. Throws BatchUpdateException (a subclass of SQLException) if one of the commands sent to the database fails to execute properly or attempts to return a result set.

Parameters:

batchSize - partition the batch into batchSize pieces, i.e. after batchSize addBatch() invocations, call executeBatch() automatically; 0 means manual calls to executeBatch are required

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.

See Also:

withBatch(Closure)

withStatement

public void withStatement(Closure configureStatement)

Allows a closure to be passed in to configure the JDBC statements before they are executed. It can be used to do things like set the query size etc. When this method is invoked, the supplied closure is saved. Statements 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

withTransaction

public void withTransaction(Closure closure)

Performs the closure within a transaction using a cached connection. If the closure takes a single argument, it will be called with the connection, otherwise it will be called with no arguments.

throws:

SQLException if a database error occurs

Parameters:

closure - the given closure

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