Cross database pagination problem and how we solved it

When we are developing a webapp with any type of listing, pagination is a must have feature. From the UI perspective we can use infinite scrolling or pages approach.

If you are familiar with google search this is how google uses the second approach,

Now the problem is how we generate listing for a particular page?
Lets take a hypothetical library application where we need to list down existing books, assume we need to list 10 books per page.

This is how we query books to generate the 1st page,

SELECT * FROM BOOKS LIMIT 10;

to generate the 2nd page,

SELECT * FROM BOOKS LIMIT 10 OFFSET 10;

So easy,  by providing the offset value we can dynamically generate our pages.

BUT can we use the same across database types?

If you know the exact backend DB type you gonna use in production, lucky you!
But if you are developing a product which needs to support multiple DB types (MySQL, Oracle, Postgres, H2 ....) above query will fail in some of them. For example above will work in MySQL, H2 and Postgres but will not work in Oracle.

In Oracle 11g you can run following but not in 12c

SELECT *
FROM (
  SELECT b.*, ROWNUM RN
  FROM (
    SELECT *
    FROM BOOKS
    ORDER BY ID ASC
  ) b
  WHERE ROWNUM <= 10
)
WHERE RN > 10

Now let's discuss how we solved this problem. Following is high-level architecture of the solution.







In this scenario we have exposed all CRUD operations as OSGI services to the outside world. This component may or may not have DB specific logic. But for sure we don't have any DB type specific logic within our main OSGI component. We have delegated all those DB type specific logic to the DB Adapter.

What are the DB type specific things we can have?

1. Above pagination related stuff (LIMIT, OFFSET)
2. Auto generate key usage(getGenaratedKeys)

How to load correct DB Adapter class through reflection

We can maintain a configuration file where we state the DB Adapter class. Then read that class name within our main component and use/call through reflection.

Reference

http://www.jooq.org/doc/3.5/manual/sql-building/sql-statements/select-statement/limit-clause/