Package groovy.sql
Class DataSet
java.lang.Object
groovy.sql.Sql
groovy.sql.DataSet
- All Implemented Interfaces:
- java.lang.AutoCloseable
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 SummaryNested classes/interfaces inherited from class groovy.sql.SqlSql.AbstractQueryCommand
- 
Field SummaryFields inherited from class groovy.sql.SqlALL_RESULT_SETS, ARRAY, BIGINT, BINARY, BIT, BLOB, BOOLEAN, CHAR, CLOB, DATALINK, DATE, DECIMAL, DISTINCT, DOUBLE, FIRST_RESULT_SET, FLOAT, INTEGER, JAVA_OBJECT, LOG, LONGVARBINARY, LONGVARCHAR, NO_RESULT_SETS, NULL, NUMERIC, OTHER, REAL, REF, SMALLINT, STRUCT, TIME, TIMESTAMP, TINYINT, VARBINARY, VARCHAR
- 
Constructor Summary
- 
Method SummaryModifier and Type Method Description voidadd(java.util.Map<java.lang.String,java.lang.Object> map)Adds the provided map of key-value pairs as a new row in the table represented by this DataSet.voidcacheConnection(Closure closure)Caches the connection used while the closure is active.voidclose()If this SQL object was created with a Connection then this method closes the connection.protected voidcloseResources(java.sql.Connection connection, java.sql.Statement statement)An extension point allowing the behavior of resource closing to be overridden in derived classes.protected voidcloseResources(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.voidcommit()If this SQL object was created with a Connection then this method commits the connection.protected java.sql.ConnectioncreateConnection()An extension point allowing derived classes to change the behavior of connection creation.DataSetcreateView(Closure criteria)voideach(int offset, int maxRows, Closure closure)Calls the provided closure for a "page" of rows from the table represented by this DataSet.voideach(Closure closure)Calls the provided closure for each of the rows of the table represented by this DataSet.DataSetfindAll(Closure where)Return a lazy-implemented filtered view of this DataSet.java.lang.ObjectfirstRow()Returns the first row from a DataSet's underlying tablejava.util.List<java.lang.Object>getParameters()java.lang.StringgetSql()protected SqlOrderByVisitorgetSqlOrderByVisitor()protected SqlWhereVisitorgetSqlWhereVisitor()DataSetreverse()Return a lazy-implemented reverse-ordered view of this DataSet.voidrollback()If this SQL object was created with a Connection then this method rolls back the connection.java.util.Listrows()Returns a List of all of the rows from the table a DataSet represents.java.util.Listrows(int offset, int maxRows)Returns a "page" of the rows from the table a DataSet represents.DataSetsort(Closure sort)Return a lazy-implemented re-ordered view of this DataSet.int[]withBatch(int batchSize, Closure closure)Performs the closure (containing batch operations) within a batch.int[]withBatch(Closure closure)Performs the closure (containing batch operations) within a batch.voidwithTransaction(Closure closure)Performs the closure within a transaction using a cached connection.Methods inherited from class groovy.sql.SqlARRAY, asList, asList, asList, asSql, BIGINT, BINARY, BIT, BLOB, BOOLEAN, buildSqlWithIndexedProps, cacheStatements, call, call, call, call, call, call, callWithAllRows, callWithAllRows, callWithRows, callWithRows, callWithRows, 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, execute, execute, execute, execute, execute, executeInsert, executeInsert, executeInsert, executeInsert, executeInsert, 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, withInstance, withInstance, withInstance, withInstance, withInstance, withInstance, withInstance, withStatementMethods inherited from class java.lang.Objectclone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait
- 
Constructor Details- 
DataSet
- 
DataSet
 
- 
- 
Method Details- 
createConnectionprotected java.sql.Connection createConnection() throws java.sql.SQLExceptionDescription copied from class:SqlAn 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:
- createConnectionin class- Sql
- Returns:
- the connection associated with this Sql
- Throws:
- java.sql.SQLException- if a SQL error occurs
 
- 
closeResourcesprotected void closeResources(java.sql.Connection connection, java.sql.Statement statement, java.sql.ResultSet results)Description copied from class:SqlAn extension point allowing derived classes to change the behavior of resource closing.- Overrides:
- closeResourcesin class- Sql
- Parameters:
- connection- the connection to close
- statement- the statement to close
- results- the results to close
 
- 
closeResourcesprotected void closeResources(java.sql.Connection connection, java.sql.Statement statement)Description copied from class:SqlAn extension point allowing the behavior of resource closing to be overridden in derived classes.- Overrides:
- closeResourcesin class- Sql
- Parameters:
- connection- the connection to close
- statement- the statement to close
 
- 
cacheConnectionDescription copied from class:SqlCaches 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:
- cacheConnectionin class- Sql
- Parameters:
- closure- the given closure
- Throws:
- java.sql.SQLException- if a database error occurs
 
- 
withTransactionDescription copied from class:SqlPerforms 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:
- withTransactionin class- Sql
- Parameters:
- closure- the given closure
- Throws:
- java.sql.SQLException- if a database error occurs
 
- 
commitpublic void commit() throws java.sql.SQLExceptionDescription copied from class:SqlIf 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.
- 
rollbackpublic void rollback() throws java.sql.SQLExceptionDescription copied from class:SqlIf 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.
- 
withBatchPerforms the closure (containing batch operations) within a batch. Uses a batch size of zero, i.e. no automatic partitioning of batches.- Overrides:
- withBatchin class- Sql
- Parameters:
- closure- the closure containing batch and optionally other statements
- Returns:
- an array of update counts containing one element for each command in the batch. The elements of the array are ordered according to the order in which commands were added to the batch.
- Throws:
- java.sql.SQLException- if a database access error occurs, or this method is called on a closed- Statement, or the driver does not support batch statements. Throws- BatchUpdateException(a subclass of- SQLException) if one of the commands sent to the database fails to execute properly or attempts to return a result set.
- See Also:
- Sql.withBatch(int, Closure)
 
- 
withBatchPerforms the closure (containing batch operations) within a batch. For example:dataSet.withBatch(3) { add(anint: 1, astring: "Groovy") add(anint: 2, astring: "rocks") add(anint: 3, astring: "the") add(anint: 4, astring: "casbah") }- Overrides:
- withBatchin class- Sql
- Parameters:
- batchSize- partition the batch into batchSize pieces, i.e. after batchSize- addBatch()invocations, call- executeBatch()automatically; 0 means manual calls to executeBatch are required
- closure- the closure containing batch and optionally other statements
- Returns:
- an array of update counts containing one element for each command in the batch. The elements of the array are ordered according to the order in which commands were added to the batch.
- Throws:
- java.sql.SQLException- if a database access error occurs, or the driver does not support batch statements. Throws- BatchUpdateException(a subclass of- SQLException) if one of the commands sent to the database fails to execute properly.
- See Also:
- Sql.withBatch(Closure),- BatchingStatementWrapper,- Statement
 
- 
addpublic void add(java.util.Map<java.lang.String,java.lang.Object> map) throws java.sql.SQLExceptionAdds the provided map of key-value pairs as a new row in the table represented by this DataSet.- Parameters:
- map- the key (column-name), value pairs to add as a new row
- Throws:
- java.sql.SQLException- if a database error occurs
 
- 
findAllReturn a lazy-implemented filtered view of this DataSet.- Parameters:
- where- the filtering Closure
- Returns:
- the view DataSet
 
- 
sortReturn a lazy-implemented re-ordered view of this DataSet.- Parameters:
- sort- the ordering Closure
- Returns:
- the view DataSet
 
- 
reverseReturn a lazy-implemented reverse-ordered view of this DataSet.- Returns:
- the view DataSet
 
- 
eachCalls 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)
 
- 
eachCalls 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)
 
- 
getSqlpublic java.lang.String getSql()
- 
getParameterspublic java.util.List<java.lang.Object> getParameters()
- 
getSqlWhereVisitor
- 
getSqlOrderByVisitor
- 
createView
- 
rowspublic java.util.List rows() throws java.sql.SQLExceptionReturns 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
 
- 
rowspublic java.util.List rows(int offset, int maxRows) throws java.sql.SQLExceptionReturns 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
 
- 
firstRowpublic java.lang.Object firstRow() throws java.sql.SQLExceptionReturns 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
 
- 
closepublic void close()Description copied from class:SqlIf 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).
 
-