Skip to content

io7m-com/jqpage

Repository files navigation

jqpage

Maven Central Maven Central (snapshot) Codecov Java Version

com.io7m.jqpage

JVM Platform Status
OpenJDK (Temurin) Current Linux Build (OpenJDK (Temurin) Current, Linux)
OpenJDK (Temurin) LTS Linux Build (OpenJDK (Temurin) LTS, Linux)
OpenJDK (Temurin) Current Windows Build (OpenJDK (Temurin) Current, Windows)
OpenJDK (Temurin) LTS Windows Build (OpenJDK (Temurin) LTS, Windows)

Usage

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);