DataSet (Groovy 6.0.0-alpha-2)

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. The expressions (or nested expressions) can contain references to fields of the POGO, literals (i.e. constant Strings or numbers), and variables captured from the enclosing scope. Method calls, arithmetic, and other complex expressions are not currently supported.
SQL identifiers and untrusted input
The table name and column names handled by a DataSet are SQL identifiers: they are included directly in the generated SQL because JDBC cannot bind identifiers as ? parameters. Row values are always bound as parameters and so are safe against SQL injection, but identifiers are not. Treat the table name (the constructor argument) and the column names (the keys of any Map passed to add(Map)) as trusted, developer-controlled values. If such an identifier originates from less-trusted input, validate or quote it yourself first — for example with the driver-aware JDBC methods Statement.isSimpleIdentifier or Statement.enquoteIdentifier (Java 9+), which you can reach via Sql.cacheConnection:
```
 db.cacheConnection { con ->
     assert con.createStatement().isSimpleIdentifier(userSuppliedName)
 
 }
 
```
Passing untrusted input as an identifier is a SQL-injection risk, exactly as for any hand-built SQL string (see the "Avoiding SQL injection" notes on Sql).

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, INJECTION_LENIENT, 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)` Creates a `DataSet` whose table name is derived from the supplied type.
`DataSet(Sql sql, String table)` Creates a `DataSet` for the supplied 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)` * 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 *
	`public void`	`close()` Closes this `DataSet` and its backing Sql resources.
	`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
	`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
	`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
	`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 *
	`public DataSet`	`createView(Closure criteria)` create a subset of the original dataset.
	`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()` Returns the positional parameter values corresponding to getSql().
	`public String`	`getSql()` Returns the lazily assembled SQL query represented by this `DataSet`.
	`protected SqlOrderByVisitor`	`getSqlOrderByVisitor()` Returns the lazily initialized visitor used to derive the SQL `ORDER BY` clause.
	`protected SqlWhereVisitor`	`getSqlWhereVisitor()` Returns the lazily initialized visitor used to derive the SQL `WHERE` clause.
	`public DataSet`	`reverse()` Return a lazy-implemented reverse-ordered view of this DataSet.
	`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
	`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)` * 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 *

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, call, call, call, call, callWithAllRows, callWithAllRows, callWithAllRows, callWithAllRows, callWithRows, callWithRows, 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, 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, getParameters, getResultSetConcurrency, getResultSetHoldability, getResultSetType, getUpdateCount, getUpdatedParams, in, inList, 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, withCleanupStatement, withInstance, withInstance, withInstance, withInstance, withInstance, withInstance, withInstance, withStatement, withTransaction

- Constructor Detail
  - public DataSet(Sql sql, Class type)
    
    Creates a DataSet whose table name is derived from the supplied type.
    The table name is the lower-cased simple name of type, i.e. a trusted, compile-time-controlled SQL identifier.
    Parameters:
    sql - the backing Sql instance
    type - the type whose simple name identifies the table
  - public DataSet(Sql sql, String table)
    
    Creates a DataSet for the supplied table.
    The table name is a trusted SQL identifier included directly in the generated SQL; do not pass untrusted input (see the class-level note on SQL identifiers and untrusted input).
    Parameters:
    sql - the backing Sql instance
    table - the trusted table name to query and update
- 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.
    The map values are bound as JDBC parameters, but the map keys are used as column names and included directly in the generated SQL. Treat the keys as trusted SQL identifiers; do not use keys taken from untrusted input (see the class-level note on SQL identifiers and untrusted input).
    throws:
    SQLException if a database error occurs
    
    Parameters:
    map - the key (column-name), value pairs to add as a new row; keys are treated as trusted identifiers
  - 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 *
    
    Delegates connection caching to the backing Sql instance.
  - public void close()
    
    Closes this DataSet and its backing Sql resources.
  - 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
    
    Delegates resource cleanup to the backing Sql instance.
  - 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
    
    Delegates resource cleanup to the backing Sql instance.
  - 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
    
    Delegates commit handling to the backing Sql instance.
  - 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 *
    
    Delegates connection creation to the backing Sql instance.
  - public DataSet createView(Closure criteria)
    
    create a subset of the original dataset.
    Parameters:
    criteria - the filter closure describing the subset
    Returns:
    a new DataSet view
  - 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()
    
    Returns the positional parameter values corresponding to getSql().
    Returns:
    the parameters for the current chained view
  - public String getSql()
    
    Returns the lazily assembled SQL query represented by this DataSet.
    Returns:
    the SQL query for the current chained view
  - protected SqlOrderByVisitor getSqlOrderByVisitor()
    
    Returns the lazily initialized visitor used to derive the SQL ORDER BY clause.
    Returns:
    the visitor for the current sort closure
  - protected SqlWhereVisitor getSqlWhereVisitor()
    
    Returns the lazily initialized visitor used to derive the SQL WHERE clause.
    Returns:
    the visitor for the current filter closure
  - public DataSet reverse()
    
    Return a lazy-implemented reverse-ordered view of this DataSet.
    Returns:
    the view DataSet
  - 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
    
    Delegates rollback handling to the backing Sql instance.
  - 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
  - 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.
  - 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.
  - 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 *
    
    Delegates transaction handling to the backing Sql instance.

Summary:
Constructor
Method

| Detail:
Constructor
Method

[Java] Class DataSet

SQL identifiers and untrusted input

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)

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

public void rollback()

public List rows()

public List rows(int offset, int maxRows)

public DataSet sort(Closure sort)

public int[] withBatch(Closure closure)

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

public void withTransaction(Closure closure)

[Java] Class DataSet

SQL identifiers and untrusted input

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)

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

public void rollback()

public List rows()

public List rows(int offset, int maxRows)

public DataSet sort(Closure sort)

public int[] withBatch(Closure closure)

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

public void withTransaction(Closure closure)

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)