Package groovy.sql

Class DataSet

  • All Implemented Interfaces:
    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.

    • Constructor Detail

      • DataSet

        public DataSet​(Sql sql,
               Class type)

      • DataSet

        public DataSet​(Sql sql,
               String table)

    • Method Detail

      • 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

      • withBatch

        public int[] withBatch​(Closure closure)
                throws 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 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:
        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)

      • withBatch

        public int[] withBatch​(int batchSize,
                       Closure closure)
                throws 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 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:
        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

      • add

        public void add​(Map<String,​Object> map)
         throws 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:
        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​(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:
        Sql.eachRow(String, java.util.List, int, int, groovy.lang.Closure)

      • getSql

        public String getSql()

      • getParameters

        public List<Object> getParameters()

      • 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:
        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).
        Specified by:
        close in interface AutoCloseable
        Overrides:
        close in class Sql