Package 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 invalid input: '&'invalid input: '&' 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.
Author:
Chris Stevenson, Paul King, James Strachan
  • Constructor Details

    • DataSet

      public DataSet(Sql sql, Class type)
    • DataSet

      public DataSet(Sql sql, String table)
  • Method Details

    • createConnection

      protected Connection createConnection() throws 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:
      SQLException - if a SQL error occurs
    • closeResources

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

      public void withTransaction(Closure closure) throws 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:
      SQLException - if a database error occurs
    • commit

      public void commit() throws 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:
      SQLException - if a database access error occurs
    • rollback

      public void rollback() throws 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:
      SQLException - if a database access error occurs
    • add

      public void add(Map<String,Object> map) throws SQLException
      Throws:
      SQLException
    • findAll

      public DataSet findAll(Closure where)
    • sort

      public DataSet sort(Closure sort)
    • reverse

      public DataSet reverse()
    • each

      public void each(Closure closure) throws 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:
      SQLException - if a database access error occurs
      See Also:
    • each

      public void each(int offset, int maxRows, Closure closure) throws 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:
      SQLException - if a database access error occurs
      See Also:
    • getSql

      public String getSql()
    • getParameters

      public List<Object> getParameters()
    • getSqlWhereVisitor

      protected SqlWhereVisitor getSqlWhereVisitor()
    • getSqlOrderByVisitor

      protected SqlOrderByVisitor getSqlOrderByVisitor()
    • createView

      public DataSet createView(Closure criteria)
    • rows

      public List rows() throws 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:
      SQLException - if a database error occurs
    • rows

      public List rows(int offset, int maxRows) throws 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:
      Returns a list of GroovyRowResult objects from the dataset
      Throws:
      SQLException - if a database error occurs
    • firstRow

      public Object firstRow() throws SQLException
      Returns the first row from a DataSet's underlying table
      Returns:
      Returns the first GroovyRowResult object from the dataset
      Throws:
      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