Overview | Package | Class | Deprecated | Index | Help
| Groovy Documentation
| FRAMES NO FRAMES
SUMMARY: NESTED | FIELD | CONSTR | METHOD | DETAIL: FIELD | CONSTR | METHOD

## groovy.sql
[Java] 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. 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.

Authors:
  Chris Stevenson
  Paul King
  James Strachan

Field Summary

Fields inherited from class 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
`DataSet(Sql sql, java.lang.Class type)`
`DataSet(Sql sql, java.lang.String table)`

Method Summary
`void` | `add(java.util.Map map)`
`void` | `cacheConnection(Closure closure)`
`void` | `close()`
`protected void` | `closeResources(java.sql.Connection connection, java.sql.Statement statement, java.sql.ResultSet results)`
`protected void` | `closeResources(java.sql.Connection connection, java.sql.Statement statement)`
`void` | `commit()`
`protected java.sql.Connection` | `createConnection()`
`DataSet` | `createView(Closure criteria)`
`void` | `each(Closure closure)` Calls the provided closure for each of the rows of the table represented by this 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.
`DataSet` | `findAll(Closure where)`
`java.lang.Object` | `firstRow()` Returns the first row from a DataSet's underlying table
`java.util.List` | `getParameters()`
`java.lang.String` | `getSql()`
`protected SqlOrderByVisitor` | `getSqlOrderByVisitor()`
`protected SqlWhereVisitor` | `getSqlWhereVisitor()`
`DataSet` | `reverse()`
`void` | `rollback()`
`java.util.List` | `rows()` Returns a List of all of the rows from the table a DataSet represents.
`java.util.List` | `rows(int offset, int maxRows)` Returns a "page" of the rows from the table a DataSet represents.
`DataSet` | `sort(Closure sort)`
`void` | `withTransaction(Closure closure)`

Methods inherited from 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, cacheConnection, cacheStatements, call, call, call, call, call, call, checkForNamedParams, 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, 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, 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, withInstance, withInstance, withInstance, withInstance, withInstance, withInstance, withInstance, withStatement, withTransaction

Constructor Detail

### DataSet

    public DataSet(Sql sql, java.lang.Class type)

### DataSet

    public DataSet(Sql sql, java.lang.String table)

Method Detail

### add

    public void add(java.util.Map map)

### cacheConnection

    @Override
    public void cacheConnection(Closure closure)

### close

    @Override
    public void close()

### closeResources

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

### closeResources

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

### commit

    @Override
    public void commit()

### createConnection

    @Override
    protected java.sql.Connection createConnection()

### createView

    public DataSet createView(Closure criteria)

### each

    public void each(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

### each

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

### findAll

    public DataSet findAll(Closure where)

### firstRow

    public java.lang.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

### getParameters

    public java.util.List getParameters()

### getSql

    public java.lang.String getSql()

### getSqlOrderByVisitor

    protected SqlOrderByVisitor getSqlOrderByVisitor()

### getSqlWhereVisitor

    protected SqlWhereVisitor getSqlWhereVisitor()

### reverse

    public DataSet reverse()

### rollback

    @Override
    public void rollback()

### rows

    public java.util.List rows()

  Returns a List of all of the rows from the table a DataSet represents.
  throws:
    SQLException if a database error occurs
  Returns:
    Returns a list of GroovyRowResult objects from the dataset

### rows

    public java.util.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

### sort

    public DataSet sort(Closure sort)

### withTransaction

    @Override
    public void withTransaction(Closure closure)

Groovy Documentation