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