public class DataSet extends Sql
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.
Sql.AbstractQueryCommand
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
Constructor and Description |
---|
DataSet(Sql sql,
Class type) |
DataSet(Sql sql,
String table) |
Modifier and Type | Method and Description |
---|---|
void |
add(Map<String,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(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)
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)
Return a lazy-implemented filtered view of this DataSet.
|
Object |
firstRow()
Returns the first row from a DataSet's underlying table
|
List<Object> |
getParameters() |
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.
|
List |
rows()
Returns a List of all of the rows from the table a DataSet
represents.
|
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(Closure closure)
Performs the closure (containing batch operations) within a batch.
|
int[] |
withBatch(int batchSize,
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.
|
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
protected Connection createConnection() throws SQLException
Sql
createConnection
in class Sql
SQLException
- if a SQL error occursprotected void closeResources(Connection connection, Statement statement, ResultSet results)
Sql
closeResources
in class Sql
connection
- the connection to closestatement
- the statement to closeresults
- the results to closeprotected void closeResources(Connection connection, Statement statement)
Sql
closeResources
in class Sql
connection
- the connection to closestatement
- the statement to closepublic void cacheConnection(Closure closure) throws SQLException
Sql
cacheConnection
in class Sql
closure
- the given closureSQLException
- if a database error occurspublic void withTransaction(Closure closure) throws SQLException
Sql
withTransaction
in class Sql
closure
- the given closureSQLException
- if a database error occurspublic void commit() throws SQLException
Sql
commit
in class Sql
SQLException
- if a database access error occurspublic void rollback() throws SQLException
Sql
rollback
in class Sql
SQLException
- if a database access error occurspublic int[] withBatch(Closure closure) throws SQLException
withBatch
in class Sql
closure
- the closure containing batch and optionally other statementsSQLException
- 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.Sql.withBatch(int, Closure)
public int[] withBatch(int batchSize, Closure closure) throws SQLException
dataSet.withBatch(3) { add(anint: 1, astring: "Groovy") add(anint: 2, astring: "rocks") add(anint: 3, astring: "the") add(anint: 4, astring: "casbah") }
withBatch
in class Sql
batchSize
- partition the batch into batchSize pieces, i.e. after batchSize
addBatch()
invocations, call executeBatch()
automatically;
0 means manual calls to executeBatch are requiredclosure
- the closure containing batch and optionally other statementsSQLException
- 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.Sql.withBatch(Closure)
,
BatchingStatementWrapper
,
Statement
public void add(Map<String,Object> map) throws SQLException
map
- the key (column-name), value pairs to add as a new rowSQLException
- if a database error occurspublic DataSet findAll(Closure where)
where
- the filtering Closurepublic DataSet sort(Closure sort)
sort
- the ordering Closurepublic DataSet reverse()
public void each(Closure closure) throws SQLException
closure
- called for each row with a GroovyResultSetSQLException
- if a database access error occursSql.eachRow(String, java.util.List, groovy.lang.Closure)
public void each(int offset, int maxRows, Closure closure) throws SQLException
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 GroovyResultSetSQLException
- if a database access error occursSql.eachRow(String, java.util.List, int, int, groovy.lang.Closure)
public String getSql()
protected SqlWhereVisitor getSqlWhereVisitor()
protected SqlOrderByVisitor getSqlOrderByVisitor()
public List rows() throws SQLException
SQLException
- if a database error occurspublic List rows(int offset, int maxRows) throws SQLException
offset
- the 1-based offset for the first row to be processedmaxRows
- the maximum number of rows to be processedSQLException
- if a database error occurspublic Object firstRow() throws SQLException
SQLException
- if a database error occurspublic void close()
Sql