jqpage
JOOQ
The jqpage package uses jooq. APIs are expressed
in terms of jooq query structures.
Sakila
This documentation is written using example data from the sakila database.
Offset Pagination
Offset pagination is provided by the JQOffsetPagination class. Offset
pagination is generally considered inferior to
keyset pagination but may be necessary when executing
queries that are too complex to use keyset pagination. The JQOffsetPagination
class implements pagination in a single database round trip by calculating
page boundary offsets using window functions. See
Calculating Pagination Metadata
for the general technique.
Assuming a base query that selects everything from an ACTOR table:
DSLContext context = ...
final var baseQuery =
context.selectFrom(ACTOR)
.orderBy(ACTOR.ACTOR_ID);
The JQOffsetPagination.paginate() method can paginate the query. The method
takes a base query, a list of fields used in the ORDER BY clause, a number
specifying the desired number of results per page, an offset value, and
a function from Record values to values of your application's domain
types. The method returns a JQPage value that contains a list of results,
a page number, and a count of the total number of pages that could be returned.
record Person(
int id,
String nameFirst,
String nameLast)
{
}
Person toActor(
final Record record)
{
return new Person(
record.getValue(ACTOR.ACTOR_ID, Integer.class).intValue(),
record.getValue(ACTOR.FIRST_NAME, String.class),
record.getValue(ACTOR.LAST_NAME, String.class)
);
}
final JQPage<Person> page =
JQOffsetPagination.paginate(
context,
baseQuery,
List.of(ACTOR.ACTOR_ID),
75L,
0L,
this::toActor
);
Keyset Pagination
Keyset pagination is provided by the JQKeysetRandomAccessPagination class.
The JQKeysetRandomAccessPagination.createPageDefinitions() function takes
an instance of the immutable JQKeysetRandomAccessPaginationParameters class.
The JQKeysetRandomAccessPaginationParameters class has the following
properties:
| Name | Description |
|---|---|
| context | The jooq DSL context used to execute queries. |
| table | A table expression. Typically the name of a table, or a set of table joins. All columns referenced elsewhere must be present in this table expression. |
| sortFields | The list of fields by which to sort the resulting rows |
| whereConditions | The list of conditions by which to filter rows. An empty list means no WHERE clause. |
| groupBy | A list of fields to use in a GROUP BY clause. An empty list means no GROUP BY. |
| pageSize | The maximum desired size of a page. |
| distinct | Whether to use SELECT DISTINCT for the processed rows. |
Due to the large number of parameters, the
JQKeysetRandomAccessPaginationParameters class provides a mutable builder
for constructing instances of the class.
The method returns a list of
JQKeysetRandomAccessPageDefinition structures that individually contain
all the information required to seek directly to any page of the executed
query in more or less constant time. The JQKeysetRandomAccessPagination
class assumes the use of a database that supports window functions.
See Faster SQL Pagination with Keysets for the general technique.
Person toCustomer(
final Record record)
{
return new Person(
record.getValue(CUSTOMER.CUSTOMER_ID, Integer.class).intValue(),
record.getValue(CUSTOMER.FIRST_NAME, String.class),
record.getValue(CUSTOMER.LAST_NAME, String.class)
);
}
final List<JQField> orderBy =
List.of(
new JQField(CUSTOMER.FIRST_NAME, ASCENDING),
new JQField(CUSTOMER.LAST_NAME, DESCENDING),
);
final JQKeysetRandomAccessPaginationParameters parameters =
JQKeysetRandomAccessPaginationParameters.forTable(CUSTOMER)
.addWhereCondition(CUSTOMER.FIRST_NAME.like("%I%"))
.addSortField(new JQField(CUSTOMER.FIRST_NAME, ASCENDING))
.addSortField(new JQField(CUSTOMER.LAST_NAME, DESCENDING))
.setPageSize(75L)
.build();
final List<JQKeysetRandomAccessPageDefinition> pages =
JQKeysetRandomAccessPagination.createPageDefinitions(context, parameters);
final JQKeysetRandomAccessPageDefinition page = pages.get(1);
final List<Person> records =
page.query(context)
.fetch()
.map(this::toCustomer);
// Or, equivalently:
final List<Person> records =
context.selectFrom(CUSTOMER)
.where(CUSTOMER.FIRST_NAME.like("%I%"))
.orderBy(page.orderBy())
.seek(page.seek())
.limit(page.limit())
.fetch()
.map(this::toCustomer);
Releases & Development Snapshots
Releases
You can subscribe to the atom feed to be notified of project releases.
The most recently released version of the package is 1.0.0.
1.0.0 Release (2024-05-11Z)
- Initial public release.
The compiled artifacts for the release (and all previous releases) are available on Maven Central.
Maven Modules
<dependency> <group>com.io7m.jqpage</group> <artifactId>com.io7m.jqpage.core</artifactId> <version>1.0.0</version> </dependency><dependency> <group>com.io7m.jqpage</group> <artifactId>com.io7m.jqpage.tests</artifactId> <version>1.0.0</version> </dependency>
Development Snapshots
At the time of writing, the current unstable development version of the package is 1.0.1-SNAPSHOT.
Development snapshots may be available in the Central Portal Snapshots repository. Snapshots are published to this repository every time the project is built by the project's continuous integration system, but snapshots do expire after around ninety days and so may or may not be available depending on when a build of the package was last triggered.
Manual
This project does not have any user manuals or other documentation beyond what might be present on the page above.
Sources
This project uses Git to manage source code.
Repository: https://www.github.com/io7m/jqpage
$ git clone --recursive https://www.github.com/io7m/jqpage
Issues
This project uses GitHub Issues to track issues.
License
Copyright © 2023 Mark Raynsford <code@io7m.com> https://www.io7m.com Permission to use, copy, modify, and/or distribute this software for any purpose with or without fee is hereby granted, provided that the above copyright notice and this permission notice appear in all copies. THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.