Groovy Documentation

groovy.sql
[Java] 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.
Authors:
Chris Stevenson
Paul King
James Strachan


Field Summary
 
Fields inherited from class 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
DataSet(Sql sql, java.lang.Class type)

DataSet(Sql sql, java.lang.String table)

 
Method Summary
void add(java.util.Map map)

void cacheConnection(Closure closure)

void close()

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

protected void closeResources(java.sql.Connection connection, java.sql.Statement statement)

void commit()

protected java.sql.Connection createConnection()

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)

java.lang.Object firstRow()

Returns the first row from a DataSet's underlying table

java.util.List getParameters()

java.lang.String getSql()

protected SqlOrderByVisitor getSqlOrderByVisitor()

protected SqlWhereVisitor getSqlWhereVisitor()

DataSet reverse()

void rollback()

java.util.List rows()

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

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

 
Methods inherited from class Sql
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, asList, asList, asList, asSql, cacheConnection, cacheStatements, call, call, call, call, call, call, checkForNamedParams, close, closeResources, closeResources, closeResources, commit, configure, createConnection, createPreparedQueryCommand, createQueryCommand, dataSet, dataSet, 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, getConnection, getDataSource, getParameters, getResultSetConcurrency, getResultSetHoldability, getResultSetType, getUpdateCount, getUpdatedParams, in, inout, isCacheNamedQueries, isCacheStatements, isEnableNamedQueries, isWithinBatch, loadDriver, newInstance, newInstance, newInstance, newInstance, newInstance, newInstance, newInstance, nullify, out, preCheckForNamedParams, query, query, query, query, query, resultSet, rollback, 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, withBatch, withBatch, withBatch, withBatch, withInstance, withInstance, withInstance, withInstance, withInstance, withInstance, withInstance, withStatement, withTransaction
 

Constructor Detail

DataSet

public DataSet(Sql sql, java.lang.Class type)


DataSet

public DataSet(Sql sql, java.lang.String table)


 
Method Detail

add

public void add(java.util.Map map)


cacheConnection

@Override
public void cacheConnection(Closure closure)


close

@Override
public void close()


closeResources

@Override
protected void closeResources(java.sql.Connection connection, java.sql.Statement statement, java.sql.ResultSet results)


closeResources

@Override
protected void closeResources(java.sql.Connection connection, java.sql.Statement statement)


commit

@Override
public void commit()


createConnection

@Override
protected java.sql.Connection createConnection()


createView

public DataSet createView(Closure criteria)


each

public void each(Closure closure)
Calls the provided closure for each of the rows of the table represented by this DataSet.
throws:
SQLException if a database access error occurs
Parameters:
closure - called for each row with a GroovyResultSet
See Also:
Sql.eachRow


each

public void each(int offset, int maxRows, Closure closure)
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.
throws:
SQLException if a database access error occurs
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
See Also:
Sql.eachRow


findAll

public DataSet findAll(Closure where)


firstRow

public java.lang.Object firstRow()
Returns the first row from a DataSet's underlying table
throws:
SQLException if a database error occurs
Returns:
Returns the first GroovyRowResult object from the dataset


getParameters

public java.util.List getParameters()


getSql

public java.lang.String getSql()


getSqlOrderByVisitor

protected SqlOrderByVisitor getSqlOrderByVisitor()


getSqlWhereVisitor

protected SqlWhereVisitor getSqlWhereVisitor()


reverse

public DataSet reverse()


rollback

@Override
public void rollback()


rows

public java.util.List rows()
Returns a List of all of the rows from the table a DataSet represents.
throws:
SQLException if a database error occurs
Returns:
Returns a list of GroovyRowResult objects from the dataset


rows

public java.util.List rows(int offset, int maxRows)
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.
throws:
SQLException if a database error occurs
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


sort

public DataSet sort(Closure sort)


withTransaction

@Override
public void withTransaction(Closure closure)


 

Groovy Documentation