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/96737065 Tree: http://git-wip-us.apache.org/repos/asf/incubator-groovy/tree/96737065 Diff: http://git-wip-us.apache.org/repos/asf/incubator-groovy/diff/96737065 Branch: refs/heads/GROOVY_2_4_X Commit: 96737065e2e8f7f0b804453c694d337cc36053b4 Parents: 58ba4de Author: Paul King <pa...@asert.com.au> Authored: Mon Apr 20 09:21:26 2015 +1000 Committer: Paul King <pa...@asert.com.au> Committed: Mon Apr 20 17:23:32 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/96737065/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/96737065/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
