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| 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) |
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) |
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() |
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) |
void |
withTransaction(Closure closure)
Performs the closure within a transaction using a cached connection.
|
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, withStatementprotected Connection createConnection() throws SQLException
SqlcreateConnection in class SqlSQLException - if a SQL error occursprotected void closeResources(Connection connection, Statement statement, ResultSet results)
SqlcloseResources in class Sqlconnection - the connection to closestatement - the statement to closeresults - the results to closeprotected void closeResources(Connection connection, Statement statement)
SqlcloseResources in class Sqlconnection - the connection to closestatement - the statement to closepublic void cacheConnection(Closure closure) throws SQLException
SqlcacheConnection in class Sqlclosure - the given closureSQLException - if a database error occurspublic void withTransaction(Closure closure) throws SQLException
SqlwithTransaction in class Sqlclosure - the given closureSQLException - if a database error occurspublic void commit()
throws SQLException
Sqlcommit in class SqlSQLException - if a database access error occurspublic void rollback()
throws SQLException
Sqlrollback in class SqlSQLException - if a database access error occurspublic void add(Map<String,Object> map) throws SQLException
SQLExceptionpublic 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