## 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
Modifier and Type | Class and Description
--- | ---

Nested classes/interfaces inherited from class groovy.sql.Sql
`Sql.AbstractQueryCommand, Sql.PreparedQueryCommand, Sql.QueryCommand`

Field Summary
Modifier and Type | Field and Description
--- | ---

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
Constructor and Description
`DataSet(Sql sql, Class type)`
`DataSet(Sql sql, String table)`

Method Summary
Modifier and Type | Method and Description
--- | ---
`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

Copyright © 2003-2010 The Codehaus. All rights reserved.