groovy.sql

Class DataSet

java.lang.Object

groovy.sql.Sql

groovy.sql.DataSet

public class DataSet
extends Sql

An enhancement of Groovy's Sql class providing support for accessing and querying databases using POGO fields and operators rather than JDBC-level API calls and RDBMS column names. So, instead of a query like:

 def db = // an instance of groovy.sql.Sql
 def sql = '''select * from Person
     where (purchaseCount > ? and birthMonth = ?)
     and (lastName < ? or lastName > ?)
     and age < ? and age > ? and firstName != ?
     order by firstName DESC, age'''
 def params = [10, "January", "Zulu", "Alpha", 99, 5, "Bert"]
 def sortedPeopleOfInterest = db.rows(sql, params)
 

You can write code like this:

 def person = new DataSet(db, 'Person') // or db.dataSet('Person'), or db.dataSet(Person)
 def janFrequentBuyers = person.findAll { it.purchaseCount > 10 && it.lastName == "January" }
 def sortedPeopleOfInterest = janFrequentBuyers.
     findAll{ it.lastName < 'Zulu' || it.lastName > 'Alpha' }.
     findAll{ it.age < 99 }.
     findAll{ it.age > 5 }.
     sort{ it.firstName }.reverse().
     findAll{ it.firstName != 'Bert' }.
     sort{ it.age }
 

Currently, the Groovy source code for any accessed POGO must be on the classpath at runtime. Also, at the moment, the expressions (or nested expressions) can only contain references to fields of the POGO or literals (i.e. constant Strings or numbers). This limitation may be removed in a future version of Groovy.

Author:

Chris Stevenson, Paul King, James Strachan

Nested Class Summary

Nested classes/interfaces inherited from class groovy.sql.Sql

Sql.AbstractQueryCommand

Field Summary

Fields inherited from class groovy.sql.Sql

ARRAY, BIGINT, BINARY, BIT, BLOB, BOOLEAN, CHAR, CLOB, DATALINK, DATE, DECIMAL, DISTINCT, DOUBLE, FLOAT, INTEGER, JAVA_OBJECT, LOG, LONGVARBINARY, LONGVARCHAR, NULL, NUMERIC, OTHER, REAL, REF, SMALLINT, STRUCT, TIME, TIMESTAMP, TINYINT, VARBINARY, VARCHAR

Constructor Summary

Constructors

Constructor and Description
DataSet(Sql sql, Class type)
DataSet(Sql sql, String table)

Method Summary

Methods

Modifier and Type	Method and Description
void	add(Map<String,Object> map)
void	cacheConnection(Closure closure) Caches the connection used while the closure is active.
void	close() If this SQL object was created with a Connection then this method closes the connection.
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 Connection	createConnection() An extension point allowing derived classes to change the behavior of connection creation.
DataSet	createView(Closure criteria)
void	each(Closure closure) Calls the provided closure for each of the rows of the table represented by this DataSet.
void	each(int offset, int maxRows, Closure closure) Calls the provided closure for a "page" of rows from the table represented by this DataSet.
DataSet	findAll(Closure where)
Object	firstRow() Returns the first row from a DataSet's underlying table
List<Object>	getParameters()
String	getSql()
protected SqlOrderByVisitor	getSqlOrderByVisitor()
protected SqlWhereVisitor	getSqlWhereVisitor()
DataSet	reverse()
void	rollback() If this SQL object was created with a Connection then this method rolls back the connection.
List	rows() Returns a List of all of the rows from the table a DataSet represents.
List	rows(int offset, int maxRows) Returns a "page" of the rows from the table a DataSet represents.
DataSet	sort(Closure sort)
void	withTransaction(Closure closure) Performs the closure within a transaction using a cached connection.

Methods inherited from class groovy.sql.Sql

ARRAY, asList, asList, asList, asSql, BIGINT, BINARY, BIT, BLOB, BOOLEAN, buildSqlWithIndexedProps, cacheStatements, call, call, call, call, call, call, callWithRows, callWithRows, callWithRows, CHAR, checkForNamedParams, CLOB, closeResources, configure, createPreparedQueryCommand, createQueryCommand, DATALINK, dataSet, dataSet, DATE, DECIMAL, DISTINCT, DOUBLE, eachRow, eachRow, eachRow, eachRow, eachRow, eachRow, eachRow, eachRow, eachRow, eachRow, eachRow, eachRow, eachRow, eachRow, eachRow, eachRow, eachRow, eachRow, eachRow, eachRow, execute, execute, execute, execute, execute, executeInsert, executeInsert, executeInsert, executeInsert, executeInsert, executePreparedQuery, executeQuery, executeUpdate, executeUpdate, executeUpdate, executeUpdate, executeUpdate, expand, findWhereKeyword, firstRow, firstRow, firstRow, firstRow, firstRow, FLOAT, getConnection, getDataSource, getParameters, getResultSetConcurrency, getResultSetHoldability, getResultSetType, getUpdateCount, getUpdatedParams, in, inout, INTEGER, isCacheNamedQueries, isCacheStatements, isEnableNamedQueries, isWithinBatch, JAVA_OBJECT, loadDriver, LONGVARBINARY, LONGVARCHAR, newInstance, newInstance, newInstance, newInstance, newInstance, newInstance, newInstance, NULL, nullify, NUMERIC, OTHER, out, preCheckForNamedParams, query, query, query, query, query, REAL, REF, resultSet, rows, rows, rows, rows, rows, rows, rows, rows, rows, rows, rows, rows, rows, rows, rows, rows, rows, rows, rows, rows, rows, setCacheNamedQueries, setCacheStatements, setEnableNamedQueries, setInternalConnection, setObject, setParameters, setResultSetConcurrency, setResultSetHoldability, setResultSetType, SMALLINT, STRUCT, TIME, TIMESTAMP, TINYINT, VARBINARY, VARCHAR, withBatch, withBatch, withBatch, withBatch, withInstance, withInstance, withInstance, withInstance, withInstance, withInstance, withInstance, withStatement

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

DataSet

public DataSet(Sql sql,
       Class type)

DataSet

public DataSet(Sql sql,
       String table)

Method Detail

createConnection

protected Connection createConnection()
                               throws SQLException

Description copied from class: Sql

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.

Overrides:

createConnection in class Sql

Returns:

the connection associated with this Sql

Throws:

SQLException - if a SQL error occurs

closeResources

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

Description copied from class: Sql

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

Overrides:

closeResources in class Sql

Parameters:

connection - the connection to close

statement - the statement to close

results - the results to close

closeResources

protected void closeResources(Connection connection,
                  Statement statement)

Description copied from class: Sql

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

Overrides:

closeResources in class Sql

Parameters:

connection - the connection to close

statement - the statement to close

cacheConnection

public void cacheConnection(Closure closure)
                     throws SQLException

Description copied from class: Sql

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.

Overrides:

cacheConnection in class Sql

Parameters:

closure - the given closure

Throws:

SQLException - if a database error occurs

withTransaction

public void withTransaction(Closure closure)
                     throws SQLException

Description copied from class: Sql

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.

Overrides:

withTransaction in class Sql

Parameters:

closure - the given closure

Throws:

SQLException - if a database error occurs

commit

public void commit()
            throws SQLException

Description copied from class: Sql

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.

Overrides:

commit in class Sql

Throws:

SQLException - if a database access error occurs

rollback

public void rollback()
              throws SQLException

Description copied from class: Sql

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.

Overrides:

rollback in class Sql

Throws:

SQLException - if a database access error occurs

add

public void add(Map<String,Object> map)
         throws SQLException

Throws:

SQLException

findAll

public DataSet findAll(Closure where)

sort

public DataSet sort(Closure sort)

reverse

public DataSet reverse()

each

public void each(Closure closure)
          throws SQLException

Calls the provided closure for each of the rows of the table represented by this DataSet.

Parameters:

closure - called for each row with a GroovyResultSet

Throws:

SQLException - if a database access error occurs

See Also:

Sql.eachRow(String, java.util.List, groovy.lang.Closure)

each

public void each(int offset,
        int maxRows,
        Closure closure)
          throws SQLException

Calls the provided closure for a "page" of rows from the table represented by this DataSet. A page is defined as starting at a 1-based offset, and containing a maximum number of rows.

Parameters:

offset - the 1-based offset for the first row to be processed

maxRows - the maximum number of rows to be processed

closure - called for each row with a GroovyResultSet

Throws:

SQLException - if a database access error occurs

See Also:

Sql.eachRow(String, java.util.List, int, int, groovy.lang.Closure)

getSql

public String getSql()

getParameters

public List<Object> getParameters()

getSqlWhereVisitor

protected SqlWhereVisitor getSqlWhereVisitor()

getSqlOrderByVisitor

protected SqlOrderByVisitor getSqlOrderByVisitor()

createView

public DataSet createView(Closure criteria)

rows

public List rows()
          throws SQLException

Returns a List of all of the rows from the table a DataSet represents.

Returns:

Returns a list of GroovyRowResult objects from the dataset

Throws:

SQLException - if a database error occurs

rows

public List rows(int offset,
        int maxRows)
          throws SQLException

Returns a "page" of the rows from the table a DataSet represents. A page is defined as starting at a 1-based offset, and containing a maximum number of rows.

Parameters:

offset - the 1-based offset for the first row to be processed

maxRows - the maximum number of rows to be processed

Returns:

a list of GroovyRowResult objects from the dataset

Throws:

SQLException - if a database error occurs

firstRow

public Object firstRow()
                throws SQLException

Returns the first row from a DataSet's underlying table

Returns:

Returns the first GroovyRowResult object from the dataset

Throws:

SQLException - if a database error occurs

close

public void close()

Description copied from class: Sql

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

Overrides:

close in class Sql