Implementing Infinite Scroll in jOOQ

In this article, we cover keyset pagination and infinite scroll via jOOQ. The schema used in the examples is available here.

You may also like "We need tool support for keyset pagination." 

jOOQ Keyset Pagination

Keyset (or seek) pagination doesn't have a default implementation in Spring Boot, but this shouldn't stop you from using it. Simply start by choosing a table's column that should act as the latest visited record/row (for instance, the id column), and use this column in the WHERE and ORDER BY clauses. The idioms relying on the ID column are as follows (sorting by multiple columns follows this same idea):

SELECT ... FROM ...
WHERE id < {last_seen_id}
ORDER BY id DESC
LIMIT {how_many_rows_to_fetch}

SELECT ... FROM ...
WHERE id > {last_seen_id}
ORDER BY id ASC
LIMIT {how_many_rows_to_fetch}

Or, like this:

SELECT ... FROM ...
WHERE ... AND id < {last_seen_id}
ORDER BY id DESC
LIMIT {how_many_rows_to_fetch}

SELECT ... FROM ...
WHERE ... AND id > {last_seen_id}
ORDER BY id ASC
LIMIT {how_many_rows_to_fetch}

Expressing these queries in jOOQ should be a piece of cake. For instance, let's apply the first idiom to the PRODUCT table via PRODUCT_ID:

List<Product> result = ctx.selectFrom(PRODUCT)
   .where(PRODUCT.PRODUCT_ID.lt(productId))
   .orderBy(PRODUCT.PRODUCT_ID.desc())
   .limit(size)
   .fetchInto(Product.class);

In MySQL, the rendered SQL is (where productId = 20 and size = 5) as follows:

SELECT `classicmodels`.`product`.`product_id`,
       `classicmodels`.`product`.`product_name`,
       ...
FROM `classicmodels`.`product`
WHERE `classicmodels`.`product`.`product_id` < 20
ORDER BY `classicmodels`.`product`.`product_id` DESC
LIMIT 5

This was easy! You can practice this case in KeysetPagination for MySQL. In the same place, you can find the approach for PostgreSQL, SQL Server, and Oracle.

However, keyset pagination becomes a little bit trickier if the WHERE clause becomes more complicated. Fortunately, jOOQ saves us from this scenario via a synthetic clause named SEEK. Let's dive into it!

The jOOQ SEEK Clause

The jOOQ synthetic SEEK clause simplifies the implementation of keyset pagination. Among its major advantages, the SEEK clause is type-safe and is capable of generating/emulating the correct/expected WHERE clause (including the emulation of row value expressions).

For instance, the previous keyset pagination example can be expressed using the SEEK clause, as shown here (productId is provided by the client): 

List<Product> result = ctx.selectFrom(PRODUCT)
   .orderBy(PRODUCT.PRODUCT_ID)
   .seek(productId)
   .limit(size)
   .fetchInto(Product.class);

Note that there is no explicit WHERE clause. jOOQ will generate it on our behalf based on the seek() arguments. While this example may not look so impressive, let's consider another one. This time, let's paginate EMPLOYEE using the employee's office code and salary:

List<Employee> result = ctx.selectFrom(EMPLOYEE)
   .orderBy(EMPLOYEE.OFFICE_CODE, EMPLOYEE.SALARY.desc())
   .seek(officeCode, salary)
   .limit(size)
   .fetchInto(Employee.class);

Both officeCode and salary are provided by the client, and they land into the following generated SQL sample (where officeCode = 1, salary = 75000, and size = 10):

SELECT `classicmodels`.`employee`.`employee_number`,
   ...
FROM `classicmodels`.`employee`
WHERE (`classicmodels`.`employee`.`office_code` > '1'
    OR (`classicmodels`.`employee`.`office_code` = '1'
       AND `classicmodels`.`employee`.`salary` < 75000))
ORDER BY `classicmodels`.`employee`.`office_code`,
         `classicmodels`.`employee`.`salary` DESC
LIMIT 10

Check out the generated WHERE clause! I am pretty sure that you don't want to get your hands dirty and explicitly write this clause. How about the following example? 

List<Orderdetail> result = ctx.selectFrom(ORDERDETAIL)
   .orderBy(ORDERDETAIL.ORDER_ID, ORDERDETAIL.PRODUCT_ID.desc(),
            ORDERDETAIL.QUANTITY_ORDERED.desc())
   .seek(orderId, productId, quantityOrdered)
   .limit(size)
   .fetchInto(Orderdetail.class);

And the following code is a sample of the generated SQL (where orderId = 10100, productId = 23, quantityOrdered = 30, and size = 10):

SELECT `classicmodels`.`orderdetail`.`orderdetail_id`,
   ...
FROM `classicmodels`.`orderdetail`
WHERE (`classicmodels`.`orderdetail`.`order_id` > 10100
   OR (`classicmodels`.`orderdetail`.`order_id` = 10100
   AND `classicmodels`.`orderdetail`.`product_id` < 23)
   OR (`classicmodels`.`orderdetail`.`order_id` = 10100
   AND `classicmodels`.`orderdetail`.`product_id` = 23
   AND `classicmodels`.`orderdetail`.`quantity_ordered` < 30))
ORDER BY `classicmodels`.`orderdetail`.`order_id`,
         `classicmodels`.`orderdetail`.`product_id` DESC,
         `classicmodels`.`orderdetail`.`quantity_ordered` DESC
LIMIT 10

After this example, I think it is obvious that you should opt for the SEEK clause and let jOOQ do its job! Look, you can even do this:

List<Product> result = ctx.selectFrom(PRODUCT)
   .orderBy(PRODUCT.BUY_PRICE, PRODUCT.PRODUCT_ID)
   .seek(PRODUCT.MSRP.minus(PRODUCT.MSRP.mul(0.35)), val(productId))
   .limit(size)
   .fetchInto(Product.class);

You can practice these examples in SeekClausePagination, next to the other examples, including using jOOQ-embedded keys as arguments of the SEEK clause.

Implementing Infinite Scroll

Infinite scroll is a classical usage of keyset pagination and is gaining popularity these days. For instance, let's assume that we plan to obtain something, as shown in this figure:

So, we want an infinite scroll over the ORDERDETAIL table. At each scroll, we fetch the next n records via the SEEK clause:

public List<Orderdetail> fetchOrderdetailPageAsc(long orderdetailId, int size) {

  List<Orderdetail> result = ctx.selectFrom(ORDERDETAIL)
     .orderBy(ORDERDETAIL.ORDERDETAIL_ID)
     .seek(orderdetailId)
     .limit(size)
     .fetchInto(Orderdetail.class);
  
  return result;
}

The fetchOrderdetailPageAsc() method gets the last visited ORDERDETAIL_ID and the number of records to fetch (size), and it returns a list of jooq.generated.tables.pojos.Orderdetail, which will be serialized in JSON format via a Spring Boot REST controller endpoint defined as @GetMapping("/orderdetail/{orderdetailId}/{size}").

On the client side, we rely on the JavaScript Fetch API (of course, you can use XMLHttpRequest, jQuery, AngularJS, Vue, React, and so on) to execute an HTTP GET request, as shown here:

const postResponse
   = await fetch('/orderdetail/${start}/${size}');
const data = await postResponse.json();

For fetching exactly three records, we replace ${size} with 3. Moreover, the ${start} placeholder should be replaced by the last visited ORDERDETAIL_ID, so the start variable can be computed as the following:

start = data[size-1].orderdetailId;

While scrolling, your browser will execute an HTTP request at every three records, as shown here:

http://localhost:8080/orderdetail/0/3
http://localhost:8080/orderdetail/3/3
http://localhost:8080/orderdetail/6/3
…

You can check out this example in SeekInfiniteScroll. 

Infinite Scrolling and Dynamic Filters

Now, let's add some filters for ORDERDETAIL that allows a client to choose the price and quantity ordered range, as shown in this figure:

We can easily implement this behavior by fusing the powers of SEEK and SelectQuery:

public List<Orderdetail> fetchOrderdetailPageAsc(
   long orderdetailId, int size, BigDecimal priceEach, Integer quantityOrdered) {
  
  SelectQuery sq = ctx.selectFrom(ORDERDETAIL)
    .orderBy(ORDERDETAIL.ORDERDETAIL_ID)
    .seek(orderdetailId)
    .limit(size)
    .getQuery();
  
  if (priceEach != null) {
    sq.addConditions(ORDERDETAIL.PRICE_EACH.between(
      priceEach.subtract(BigDecimal.valueOf(50)), priceEach));
  }
  
  if (quantityOrdered != null) {
    sq.addConditions(ORDERDETAIL.QUANTITY_ORDERED.between(
      quantityOrdered - 25, quantityOrdered));
  }
  
  return sq.fetchInto(Orderdetail.class);
}

The following example URL involves loading the first page of three records that have prices between 50 and 100 and an order quantity between 50 and 75:

http://localhost:8080/orderdetail/0/3?priceEach=100&quantityOrdered=75

You can find the complete example in SeekInfiniteScrollFilter for MySQL, SQL Server, PostgreSQL, and Oracle.