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)
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 }
-
-
Nested Class Summary
-
Nested classes/interfaces inherited from class groovy.sql.Sql
Sql.AbstractQueryCommand
-
-
Field Summary
-
Fields inherited from class groovy.sql.Sql
ALL_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
-
-
Method Summary
All Methods Instance Methods Concrete Methods Modifier and Type Method Description void
add(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.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(int offset, int maxRows, Closure closure)
Calls the provided closure for a "page" of rows from the table represented by this DataSet.void
each(Closure closure)
Calls the provided closure for each of the rows of the table represented by this DataSet.DataSet
findAll(Closure where)
Return a lazy-implemented filtered view of this DataSet.java.lang.Object
firstRow()
Returns the first row from a DataSet's underlying tablejava.util.List<java.lang.Object>
getParameters()
java.lang.String
getSql()
protected SqlOrderByVisitor
getSqlOrderByVisitor()
protected SqlWhereVisitor
getSqlWhereVisitor()
DataSet
reverse()
Return a lazy-implemented reverse-ordered view of this DataSet.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)
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.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, 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, withStatement
-
-
-
-
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 classSql
- 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 classSql
- Parameters:
connection
- the connection to closestatement
- the statement to closeresults
- 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 classSql
- Parameters:
connection
- the connection to closestatement
- 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 classSql
- 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 classSql
- 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.
-
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.
-
withBatch
public int[] withBatch(Closure closure) throws java.sql.SQLException
Performs the closure (containing batch operations) within a batch. Uses a batch size of zero, i.e. no automatic partitioning of batches.- Overrides:
withBatch
in classSql
- 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 closedStatement
, or the driver does not support batch statements. ThrowsBatchUpdateException
(a subclass ofSQLException
) 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)
-
withBatch
public int[] withBatch(int batchSize, Closure closure) throws java.sql.SQLException
Performs 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:
withBatch
in classSql
- Parameters:
batchSize
- partition the batch into batchSize pieces, i.e. after batchSizeaddBatch()
invocations, callexecuteBatch()
automatically; 0 means manual calls to executeBatch are requiredclosure
- 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. ThrowsBatchUpdateException
(a subclass ofSQLException
) if one of the commands sent to the database fails to execute properly.- See Also:
Sql.withBatch(Closure)
,BatchingStatementWrapper
,Statement
-
add
public void add(java.util.Map<java.lang.String,java.lang.Object> map) throws java.sql.SQLException
Adds 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
-
findAll
public DataSet findAll(Closure where)
Return a lazy-implemented filtered view of this DataSet.- Parameters:
where
- the filtering Closure- Returns:
- the view DataSet
-
sort
public DataSet sort(Closure sort)
Return a lazy-implemented re-ordered view of this DataSet.- Parameters:
sort
- the ordering Closure- Returns:
- the view DataSet
-
reverse
public DataSet reverse()
Return a lazy-implemented reverse-ordered view of this DataSet.- Returns:
- the view DataSet
-
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 processedmaxRows
- the maximum number of rows to be processedclosure
- 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()
-
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 processedmaxRows
- 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).
-
-