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

- - ### Nested Class Summary

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

      `Sql.AbstractQueryCommand`

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

  - ### Method Summary

    Methods
    Modifier and Type | Method and Description
    --- | ---
    `void` | `add(java.util.Map<java.lang.String,java.lang.Object> map)`
    `void` | `cacheConnection(Closure closure)` Caches the connection used while the closure is active.
    `void` | `close()` If this SQL object was created with a Connection then this method closes the connection.
    `protected void` | `closeResources(java.sql.Connection connection, java.sql.Statement statement)` An extension point allowing the behavior of resource closing to be overridden in derived classes.
    `protected void` | `closeResources(java.sql.Connection connection, java.sql.Statement statement, java.sql.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 java.sql.Connection` | `createConnection()` An extension point allowing derived classes to change the behavior of connection creation.
    `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<java.lang.Object>` | `getParameters()`
    `java.lang.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.
    `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)` 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, cacheStatements, call, call, call, call, call, call, CHAR, checkForNamedParams, 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, 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, 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, withBatch, withBatch, 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 Detail

    - #### DataSet

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

    - #### DataSet

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

  - ### Method Detail

    - #### createConnection

          protected java.sql.Connection createConnection()
                                                  throws java.sql.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:
        `java.sql.SQLException` - if a SQL error occurs

    - #### closeResources

          protected void closeResources(java.sql.Connection connection,
                            java.sql.Statement statement,
                            java.sql.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(java.sql.Connection connection,
                            java.sql.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 java.sql.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:
        `java.sql.SQLException` - if a database error occurs

    - #### withTransaction

          public void withTransaction(Closure closure)
                               throws java.sql.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:
        `java.sql.SQLException` - if a database error occurs

    - #### commit

          public void commit()
                      throws java.sql.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:
        `java.sql.SQLException` - if a database access error occurs

    - #### rollback

          public void rollback()
                        throws java.sql.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:
        `java.sql.SQLException` - if a database access error occurs

    - #### add

          public void add(java.util.Map<java.lang.String,java.lang.Object> map)
                   throws java.sql.SQLException

      Throws:
        `java.sql.SQLException`

    - #### findAll

          public DataSet findAll(Closure where)

    - #### sort

          public DataSet sort(Closure sort)

    - #### reverse

          public DataSet reverse()

    - #### each

          public void each(Closure closure)
                    throws java.sql.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:
        `java.sql.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,
                  Closure closure)
                    throws java.sql.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:
        `java.sql.SQLException` - if a database access error occurs
      See Also:
        `Sql.eachRow(String, java.util.List, int, int, groovy.lang.Closure)`

    - #### getSql

          public java.lang.String getSql()

    - #### getParameters

          public java.util.List<java.lang.Object> getParameters()

    - #### getSqlWhereVisitor

          protected SqlWhereVisitor getSqlWhereVisitor()

    - #### getSqlOrderByVisitor

          protected SqlOrderByVisitor getSqlOrderByVisitor()

    - #### createView

          public DataSet createView(Closure criteria)

    - #### rows

          public java.util.List rows()
                              throws java.sql.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:
        `java.sql.SQLException` - if a database error occurs

    - #### rows

          public java.util.List rows(int offset,
                            int maxRows)
                              throws java.sql.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:
        `java.sql.SQLException` - if a database error occurs

    - #### firstRow

          public java.lang.Object firstRow()
                                    throws java.sql.SQLException

      Returns the first row from a DataSet's underlying table
      Returns:
        Returns the first GroovyRowResult object from the dataset
      Throws:
        `java.sql.SQLException` - if a database error occurs

    - #### close

          public void close()

      Description copied from class: `Sql`
      If this SQL object was created with a Connection then this method closes the connection. If this SQL object was created from a DataSource then this method only frees any cached objects (statements in particular).
      Overrides:
        `close` in class `Sql`