Package groovy.sql

# Class DataSet

java.lang.Object
groovy.sql.Sql
groovy.sql.DataSet

All Implemented Interfaces:
  `AutoCloseable`

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(String)` or `Statement.enquoteIdentifier(String, boolean)` (Java 9+), which you can reach via `Sql.cacheConnection(groovy.lang.Closure)`:

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

- ## Nested Class Summary

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

  `Sql.AbstractQueryCommand`

- ## Field Summary

  ### Fields inherited from class groovy.sql.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
  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.

- ## Method Summary

  Modifier and Type
  Method
  Description
  `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.
  `void`
  `cacheConnection(Closure closure)`
  Caches the connection used while the closure is active.
  `void`
  `close()`
  Closes this `DataSet` and its backing `Sql` resources.
  `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)`
  create a subset of the original 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.
  `void`
  `each(Closure closure)`
  Calls the provided closure for each of the rows of the table represented by this DataSet.
  `DataSet`
  `findAll(Closure where)`
  Return a lazy-implemented filtered view of this DataSet.
  `Object`
  `firstRow()`
  Returns the first row from a DataSet's underlying table
  `List<Object>`
  `getParameters()`
  Returns the positional parameter values corresponding to `getSql()`.
  `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.
  `DataSet`
  `reverse()`
  Return a lazy-implemented reverse-ordered view of this DataSet.
  `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 the rows from the DataSet.
  `List`
  `rows(int offset, int maxRows)`
  Returns a "page" of the rows from the table a DataSet represents.
  `DataSet`
  `sort(Closure sort)`
  Return a lazy-implemented re-ordered view of this DataSet.
  `int[]`
  `withBatch(int batchSize, Closure closure)`
  Performs the closure (containing batch operations) within a batch.
  `int[]`
  `withBatch(Closure closure)`
  Performs the closure (containing batch operations) within a batch.
  `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, call, call, call, call, callWithAllRows, callWithAllRows, callWithAllRows, callWithAllRows, callWithRows, callWithRows, callWithRows, callWithRows, callWithRows, CHAR, checkForNamedParams, cleanup, 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, 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, FLOAT, getConnection, getDataSource, getParameters, getResultSetConcurrency, getResultSetHoldability, getResultSetType, getUpdateCount, getUpdatedParams, in, inList, 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, withCleanupStatement, 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 Details

  - ### DataSet

    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

  - ### DataSet

    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 Details

  - ### createConnection

    protected Connection createConnection() throws SQLException
    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.

    Delegates connection creation to the backing `Sql` instance.

    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)
    An extension point allowing derived classes to change the behavior of resource closing.

    Delegates resource cleanup to the backing `Sql` instance.

    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)
    An extension point allowing the behavior of resource closing to be overridden in derived classes.

    Delegates resource cleanup to the backing `Sql` instance.

    Overrides:
      `closeResources` in class `Sql`
    Parameters:
      `connection` - the connection to close
      `statement` - the statement to close

  - ### cacheConnection

    public void cacheConnection(Closure closure) throws SQLException
    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.

    Delegates connection caching to the backing `Sql` instance.

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

    Delegates transaction handling to the backing `Sql` instance.

    Overrides:
      `withTransaction` in class `Sql`
    Parameters:
      `closure` - the given closure
    Throws:
      `SQLException` - if a database error occurs

  - ### commit

    public void commit() throws SQLException
    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.

    Delegates commit handling to the backing `Sql` instance.

    Overrides:
      `commit` in class `Sql`
    Throws:
      `SQLException` - if a database access error occurs

  - ### rollback

    public void rollback() throws SQLException
    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.

    Delegates rollback handling to the backing `Sql` instance.

    Overrides:
      `rollback` in class `Sql`
    Throws:
      `SQLException` - if a database access error occurs

  - ### withBatch

    public int[] withBatch(Closure closure) throws SQLException
    Performs the closure (containing batch operations) within a batch. Uses a batch size of zero, i.e. no automatic partitioning of batches.
    Overrides:
      `withBatch` in class `Sql`
    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.
    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.
    See Also:
      - `Sql.withBatch(int, Closure)`

  - ### withBatch

    public int[] withBatch(int batchSize, Closure closure) throws SQLException
    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")
         }

    Overrides:
      `withBatch` in class `Sql`
    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.
    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.
    See Also:
      - `Sql.withBatch(Closure)`
      - `BatchingStatementWrapper`
      - `Statement`

  - ### add

    public void add(Map<String,Object> map) throws SQLException
    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).

    Parameters:
      `map` - the key (column-name), value pairs to add as a new row; keys are treated as trusted identifiers
    Throws:
      `SQLException` - if a database error occurs

  - ### findAll

    public DataSet findAll(Closure where)
    Return a lazy-implemented filtered view of this DataSet.
    Parameters:
      `where` - the filtering Closure
    Returns:
      the view DataSet

  - ### sort

    public DataSet sort(Closure sort)
    Return a lazy-implemented re-ordered view of this DataSet.
    Parameters:
      `sort` - the ordering Closure
    Returns:
      the view DataSet

  - ### reverse

    public DataSet reverse()
    Return a lazy-implemented reverse-ordered view of this DataSet.
    Returns:
      the view DataSet

  - ### each

    public void each(@ClosureParams(value=SimpleType.class,options="groovy.sql.GroovyResultSet") 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, @ClosureParams(value=SimpleType.class,options="groovy.sql.GroovyResultSet") 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()
    Returns the lazily assembled SQL query represented by this `DataSet`.
    Returns:
      the SQL query for the current chained view

  - ### getParameters

    public List<Object> getParameters()
    Returns the positional parameter values corresponding to `getSql()`.
    Returns:
      the parameters for the current chained view

  - ### getSqlWhereVisitor

    protected SqlWhereVisitor getSqlWhereVisitor()
    Returns the lazily initialized visitor used to derive the SQL `WHERE` clause.
    Returns:
      the visitor for the current filter closure

  - ### getSqlOrderByVisitor

    protected SqlOrderByVisitor getSqlOrderByVisitor()
    Returns the lazily initialized visitor used to derive the SQL `ORDER BY` clause.
    Returns:
      the visitor for the current sort closure

  - ### createView

    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

  - ### rows

    public List rows() throws SQLException
    Returns a List of all the rows from the DataSet.
    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()
    Closes this `DataSet` and its backing `Sql` resources.
    Specified by:
      `close` in interface `AutoCloseable`
    Overrides:
      `close` in class `Sql`