README.in

## Usage

### JOOQ

The `jqpage` package uses [jooq](https://www.jooq.org/). APIs are expressed
in terms of `jooq` query structures.

### Sakila

This documentation is written using example data from the [sakila](https://github.com/jOOQ/sakila)
database.

### Offset Pagination

Offset pagination is provided by the `JQOffsetPagination` class. Offset
pagination is generally considered inferior to 
[keyset pagination](#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](https://blog.jooq.org/calculating-pagination-metadata-without-extra-roundtrips-in-sql/)
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](https://blog.jooq.org/faster-sql-pagination-with-keysets-continued/)
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);
```