DataSet (Groovy 4.0.13)

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.

Inherited fields
Fields inherited from class	Fields
`class Sql`	`ALL_RESULT_SETS, ARRAY, BIGINT, BINARY, BIT, BLOB, BOOLEAN, CHAR, CLOB, DATALINK, DATE, DECIMAL, DISTINCT, DOUBLE, FIRST_RESULT_SET, FLOAT, INTEGER, JAVA_OBJECT, LOG, LONGVARBINARY, LONGVARCHAR, NO_RESULT_SETS, 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)`

Methods Summary

Methods
Type Params	Return Type	Name and description
	`public void`	`add(Map<String, Object> map)` Adds the provided map of key-value pairs as a new row in the table represented by this DataSet.
	`public void`	`cacheConnection(Closure closure)`
	`public void`	`close()`
	`protected void`	`closeResources(Connection connection, Statement statement, ResultSet results)`
	`protected void`	`closeResources(Connection connection, Statement statement)`
	`public void`	`commit()`
	`protected Connection`	`createConnection()`
	`public DataSet`	`createView(Closure criteria)`
	`public void`	`doCall(BatchingPreparedStatementWrapper stmt)`
	`public void`	`each(Closure closure)` Calls the provided closure for each of the rows of the table represented by this DataSet.
	`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.
	`public DataSet`	`findAll(Closure where)` Return a lazy-implemented filtered view of this DataSet.
	`public Object`	`firstRow()` Returns the first row from a DataSet's underlying table
	`public List<Object>`	`getParameters()`
	`public String`	`getSql()`
	`protected SqlOrderByVisitor`	`getSqlOrderByVisitor()`
	`protected SqlWhereVisitor`	`getSqlWhereVisitor()`
	`public DataSet`	`reverse()` Return a lazy-implemented reverse-ordered view of this DataSet.
	`public void`	`rollback()`
	`public List`	`rows()` Returns a List of all the rows from the DataSet.
	`public List`	`rows(int offset, int maxRows)` Returns a "page" of the rows from the table a DataSet represents.
	`public DataSet`	`sort(Closure sort)` Return a lazy-implemented re-ordered view of this DataSet.
	`public int[]`	`withBatch(Closure closure)` Performs the closure (containing batch operations) within a batch.
	`public int[]`	`withBatch(int batchSize, Closure closure)` Performs the closure (containing batch operations) within a batch.
	`public void`	`withTransaction(Closure closure)`

Inherited Methods Summary

Inherited Methods
Methods inherited from class	Name
`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, buildSqlWithIndexedProps, cacheConnection, cacheStatements, call, call, call, call, call, call, callWithAllRows, callWithAllRows, callWithRows, callWithRows, callWithRows, checkForNamedParams, cleanup, 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, execute, execute, execute, execute, execute, executeInsert, executeInsert, executeInsert, executeInsert, executeInsert, executeInsert, executeInsert, executeInsert, executeInsert, executeInsert, executePreparedQuery, executeQuery, executeUpdate, executeUpdate, executeUpdate, executeUpdate, executeUpdate, expand, findWhereKeyword, firstRow, firstRow, firstRow, firstRow, firstRow, getConnection, getDataSource, getObject, getParameters, getResultSetConcurrency, getResultSetHoldability, getResultSetType, getType, getType, getType, getType, getType, getType, getType, getType, getType, getType, getType, getType, getType, getType, getType, getType, getType, getType, getType, getType, getType, getType, getType, getType, getType, getType, getType, getType, getType, getType, getType, getType, getType, getType, getUpdateCount, getUpdatedParams, getValue, getValue, 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, run, setCacheNamedQueries, setCacheStatements, setEnableNamedQueries, setInternalConnection, setObject, setParameters, setResultSetConcurrency, setResultSetHoldability, setResultSetType, withBatch, withBatch, withBatch, withBatch, withCleanupStatement, withInstance, withInstance, withInstance, withInstance, withInstance, withInstance, withInstance, withStatement, withTransaction

- Constructor Detail
  - public DataSet(Sql sql, Class type)
  - public DataSet(Sql sql, String table)
- Method Detail
  - public void add(Map<String, Object> map)
    
    Adds the provided map of key-value pairs as a new row in the table represented by this DataSet.
    throws:
    SQLException if a database error occurs
    
    Parameters:
    map - the key (column-name), value pairs to add as a new row
  - @Override
    public void cacheConnection(Closure closure)
  - @Override
    public void close()
  - @Override
    protected void closeResources(Connection connection, Statement statement, ResultSet results)
  - @Override
    protected void closeResources(Connection connection, Statement statement)
  - @Override
    public void commit()
  - @Override
    protected Connection createConnection()
  - public DataSet createView(Closure criteria)
  - public void doCall(BatchingPreparedStatementWrapper stmt)
  - public void each(@ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet")
    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
  - public void each(int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet")
    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
  - public DataSet findAll(Closure where)
    
    Return a lazy-implemented filtered view of this DataSet.
    Parameters:
    where - the filtering Closure
    Returns:
    the view DataSet
  - public 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
  - public List<Object> getParameters()
  - public String getSql()
  - protected SqlOrderByVisitor getSqlOrderByVisitor()
  - protected SqlWhereVisitor getSqlWhereVisitor()
  - public DataSet reverse()
    
    Return a lazy-implemented reverse-ordered view of this DataSet.
    Returns:
    the view DataSet
  - @Override
    public void rollback()
  - public List rows()
    
    Returns a List of all the rows from the DataSet.
    throws:
    SQLException if a database error occurs
    
    Returns:
    Returns a list of GroovyRowResult objects from the dataset
  - public 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
  - public DataSet sort(Closure sort)
    
    Return a lazy-implemented re-ordered view of this DataSet.
    Parameters:
    sort - the ordering Closure
    Returns:
    the view DataSet
  - @Override
    public int[] withBatch(Closure closure)
    
    Performs the closure (containing batch operations) within a batch. Uses a batch size of zero, i.e. no automatic partitioning of batches.
    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.
  - @Override
    public int[] withBatch(int batchSize, Closure closure)
    
    Performs the closure (containing batch operations) within a batch. For example:
```
 dataSet.withBatch(3) {
     add(anint: 1, astring: "Groovy")
     add(anint: 2, astring: "rocks")
     add(anint: 3, astring: "the")
     add(anint: 4, astring: "casbah")
 }
 
```
    throws:
    SQLException if a database access error occurs, 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.
    
    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.
  - @Override
    public void withTransaction(Closure closure)

Summary:
Constructor
Method

| Detail:
Constructor
Method

[Java] Class DataSet

Constructor Summary

Methods Summary

Inherited Methods Summary

Constructor Detail

public DataSet(Sql sql, Class type)

public DataSet(Sql sql, String table)

Method Detail

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

@Override
public void cacheConnection(Closure closure)

@Override
public void close()

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

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

@Override
public void commit()

@Override
protected Connection createConnection()

public DataSet createView(Closure criteria)

public void doCall(BatchingPreparedStatementWrapper stmt)

public void each(@ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet")
Closure closure)

public void each(int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet")
Closure closure)

public DataSet findAll(Closure where)

public Object firstRow()

public List<Object> getParameters()

public String getSql()

protected SqlOrderByVisitor getSqlOrderByVisitor()

protected SqlWhereVisitor getSqlWhereVisitor()

public DataSet reverse()

@Override
public void rollback()

public List rows()

public List rows(int offset, int maxRows)

public DataSet sort(Closure sort)

@Override
public int[] withBatch(Closure closure)

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

@Override
public void withTransaction(Closure closure)

[Java] Class DataSet

Constructor Summary

Methods Summary

Inherited Methods Summary

Constructor Detail

public DataSet(Sql sql, Class type)

public DataSet(Sql sql, String table)

Method Detail

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

@Overridepublic void cacheConnection(Closure closure)

@Overridepublic void close()

@Overrideprotected void closeResources(Connection connection, Statement statement, ResultSet results)

@Overrideprotected void closeResources(Connection connection, Statement statement)

@Overridepublic void commit()

@Overrideprotected Connection createConnection()

public DataSet createView(Closure criteria)

public void doCall(BatchingPreparedStatementWrapper stmt)

public void each(@ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet")Closure closure)

public void each(int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet")Closure closure)

public DataSet findAll(Closure where)

public Object firstRow()

public List<Object> getParameters()

public String getSql()

protected SqlOrderByVisitor getSqlOrderByVisitor()

protected SqlWhereVisitor getSqlWhereVisitor()

public DataSet reverse()

@Overridepublic void rollback()

public List rows()

public List rows(int offset, int maxRows)

public DataSet sort(Closure sort)

@Overridepublic int[] withBatch(Closure closure)

@Overridepublic int[] withBatch(int batchSize, Closure closure)

@Overridepublic void withTransaction(Closure closure)

@Override
public void cacheConnection(Closure closure)

@Override
public void close()

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

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

@Override
public void commit()

@Override
protected Connection createConnection()

public void each(@ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet")
Closure closure)

public void each(int offset, int maxRows, @ClosureParams(value=SimpleType.class, options="groovy.sql.GroovyResultSet")
Closure closure)

@Override
public void rollback()

@Override
public int[] withBatch(Closure closure)

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

@Override
public void withTransaction(Closure closure)