Repository: incubator-groovy Updated Branches: refs/heads/master a54e59dd5 -> 01609605a
groovy-sql doco: pagination Project: http://git-wip-us.apache.org/repos/asf/incubator-groovy/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-groovy/commit/01609605 Tree: http://git-wip-us.apache.org/repos/asf/incubator-groovy/tree/01609605 Diff: http://git-wip-us.apache.org/repos/asf/incubator-groovy/diff/01609605 Branch: refs/heads/master Commit: 01609605a368e386b19c3df2f18f9c5f1e275b4b Parents: a54e59d Author: Paul King <[email protected]> Authored: Mon Apr 20 09:21:26 2015 +1000 Committer: Paul King <[email protected]> Committed: Mon Apr 20 09:21:26 2015 +1000 ---------------------------------------------------------------------- .../groovy-sql/src/spec/doc/sql-userguide.adoc | 13 +++++-- .../groovy-sql/src/spec/test/SqlTest.groovy | 37 ++++++++++++++++++++ 2 files changed, 48 insertions(+), 2 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-groovy/blob/01609605/subprojects/groovy-sql/src/spec/doc/sql-userguide.adoc ---------------------------------------------------------------------- diff --git a/subprojects/groovy-sql/src/spec/doc/sql-userguide.adoc b/subprojects/groovy-sql/src/spec/doc/sql-userguide.adoc index d0e4ff0..c371412 100644 --- a/subprojects/groovy-sql/src/spec/doc/sql-userguide.adoc +++ b/subprojects/groovy-sql/src/spec/doc/sql-userguide.adoc @@ -249,7 +249,7 @@ Even though the first sql execute succeeds initially, it will be rolled back and === Using batches -When dealing with large volumes of data, it can be more efficient to chunk the data into batches. This is done +When dealing with large volumes of data, particularly when inserting such data, it can be more efficient to chunk the data into batches. This is done using the `withBatch` statement as shown in the following example: [source,groovy] @@ -297,7 +297,16 @@ to the one table. === Performing pagination -(TBD) +When presenting large tables of data to a user, it is often convenient to present information a page at +a time. Many of Groovy's SQL retrieval methods have extra parameters which can be used to select a particular +page of interest. The starting position and page size are specified as integers as shown in the following example +using `rows`: + +[source,groovy] +.Retrieving pages of data +---- +include::{rootProjectDir}/subprojects/groovy-sql/src/spec/test/SqlTest.groovy[tags=sql_pagination,indent=0] +---- === Fetching metadata http://git-wip-us.apache.org/repos/asf/incubator-groovy/blob/01609605/subprojects/groovy-sql/src/spec/test/SqlTest.groovy ---------------------------------------------------------------------- diff --git a/subprojects/groovy-sql/src/spec/test/SqlTest.groovy b/subprojects/groovy-sql/src/spec/test/SqlTest.groovy index f5b5c9b..dc16d91 100644 --- a/subprojects/groovy-sql/src/spec/test/SqlTest.groovy +++ b/subprojects/groovy-sql/src/spec/test/SqlTest.groovy @@ -502,6 +502,43 @@ class SqlTest extends GroovyTestCase { ''' } + void testPagination() { + assertScript ''' + import groovy.sql.Sql + + def url = 'jdbc:hsqldb:mem:yourDB' + def user = 'sa' + def password = '' + def driver = 'org.hsqldb.jdbcDriver' + Sql.withInstance(url, user, password, driver) { sql -> + sql.execute """ + DROP TABLE Author IF EXISTS + """ + sql.execute """ + CREATE TABLE Author ( + id INTEGER GENERATED BY DEFAULT AS IDENTITY, + firstname VARCHAR(64), + lastname VARCHAR(64) + ) + """ + + sql.execute "INSERT INTO Author (firstname, lastname) VALUES ('Dierk', 'Koenig')" + sql.execute "INSERT INTO Author (firstname, lastname) VALUES ('Paul', 'King')" + sql.execute "INSERT INTO Author (firstname, lastname) VALUES ('Guillaume', 'Laforge')" + sql.execute "INSERT INTO Author (firstname, lastname) VALUES ('Hamlet', 'D''Arcy')" + sql.execute "INSERT INTO Author (firstname, lastname) VALUES ('Cedric', 'Champeau')" + sql.execute "INSERT INTO Author (firstname, lastname) VALUES ('Erik', 'Pragt')" + sql.execute "INSERT INTO Author (firstname, lastname) VALUES ('Jon', 'Skeet')" + // tag::sql_pagination[] + def qry = 'SELECT * FROM Author' + assert sql.rows(qry, 1, 3)*.firstname == ['Dierk', 'Paul', 'Guillaume'] + assert sql.rows(qry, 4, 3)*.firstname == ['Hamlet', 'Cedric', 'Erik'] + assert sql.rows(qry, 7, 3)*.firstname == ['Jon'] + // end::sql_pagination[] + } + ''' + } + void testDeletingRows() { assertScript ''' import groovy.sql.Sql
