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.

Version:
  $Revision: 19572 $
Author:
  Chris Stevenson, Paul King, James Strachan

### Nested Class Summary

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

`Sql.AbstractQueryCommand, Sql.PreparedQueryCommand, Sql.QueryCommand`

### 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
`void` `add(Map<String,Object> map)`
`void` `cacheConnection(Closure closure)` Caches the connection used while the closure is active.
`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)`
`DataSet` `findAll(Closure where)`
`Object` `firstRow()` Returns the first row from a DataSet's underlying table
`List` `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
`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, asSql, BIGINT, BINARY, BIT, BLOB, BOOLEAN, cacheStatements, call, call, call, call, call, call, CHAR, checkForNamedParams, CLOB, close, closeResources, configure, createPreparedQueryCommand, createQueryCommand, DATALINK, dataSet, dataSet, DATE, DECIMAL, DISTINCT, DOUBLE, eachRow, eachRow, eachRow, eachRow, eachRow, eachRow, execute, execute, execute, execute, executeInsert, executeInsert, executeInsert, executeInsert, executePreparedQuery, executeQuery, executeUpdate, executeUpdate, executeUpdate, executeUpdate, expand, findWhereKeyword, firstRow, firstRow, firstRow, firstRow, FLOAT, getConnection, getDataSource, getParameters, getResultSetConcurrency, getResultSetHoldability, getResultSetType, getUpdateCount, in, inout, INTEGER, isCacheNamedQueries, isCacheStatements, isEnableNamedQueries, isWithinBatch, JAVA_OBJECT, loadDriver, LONGVARBINARY, LONGVARCHAR, newInstance, newInstance, newInstance, newInstance, newInstance, newInstance, newInstance, NULL, nullify, NUMERIC, OTHER, out, query, query, query, REAL, REF, resultSet, 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, 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

Throws:
  `SQLException`

#### getSql

    public String getSql()

#### getParameters

    public List 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

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