Finding out the page containing a given record using JPA (Hibernate)

criteria, hibernate, java, jpa

Solution

To be sure I understand correctly: you are displaying a `Record` and want to display a paged list of all records preselecting the page containing your item?

First of all you must know that relational databases do not offer any implicit sorting of records in the database. Although they seem to be sorted from first to last added, this is not portable and reliable.

So your paged list/grid must be sorted explicitly by some column. For simplicity say your grid is sorted by `id`. You know the id of the record being currently displayed (say: `X`). You first need to figure out at which position in your table your record is with regards to this sorting order:

SELECT COUNT(r)
FROM Record r
WHERE r.id < :X

This query will return the number of records before your record. Now it's simple:

int page = count / pageSize

`page` is 0-based.

Unfortunately if your sort column is not unique, this might not work in all cases. But if the column is not unique, the sorting itself is not stable (records having the same value might appear in random order), so consider ordering by extra unique column:

...
ORDER BY r.sex, r.id

In this case the records are first sorted by `sex` (lots of duplicates) and by id. The solution with counting records before current record will still work.

Problem

How can I know the position of a record in a JPA query? I have a service that returns paged results implementing more or less a method with this signature: ``` List<Record> getRecordsPage(long page, int pageSize); ``` When this is invoked I just create a query and configure like this: ``` TypedQuery<Record> query = entityManager.createQuery(criteriaQuery); query.setFirstResult(page * pageSize); query.setMaxResults(pageSize); ``` This pages the result. And this is working as expected, and is quite simple. The Problem Another requirement I have is to implement a method that would retrieve the page that contains a specific record. Implementing a method with the following signature: ``` List<Record> getRecordsPage(Record record, int pageSize); ``` This method needs to result the right page in which the record is. For instance, for `getRecordsPage(RECORD4, 2)` invocation, considering database state: ``` 1. RECORD1 2. RECORD2 3. RECORD3 4. RECORD4 5. RECORD5 ``` The returned page should be 2 containing `[RECORD3, RECORD4]`. The `ORDER BY` parameter is always set, and could be more than one field. Solution Until Now Until now I have a few solutions that are: - Not good at all but it solves the problem: Using the query provided I select just the id without paging and perform just a `indexOf` in order to find its position and based on the position I can find out the page the record is and then perform the regular process using `getRecordsPage(long page, int pageSize)` already implemented. - Not so good because is high coupled with database: As I'm using mySQL, I could perform an sql like : `select r from (select rownum r, id from t order by x,y) z where z.id = :id`, what would return the position of the record and I could use it in order to invoke `getRecordsPage(long page, int pageSize)`. Good Solution Requirements: - shall support order by multiple fields; - given a query, it will return the record position or the containing record page offset; A good solution would: - be purely JPA; - be Ok if one additional query is performed in database just to find out the record position; - be Ok if hibernate is used in some point (as Hibernate is behind JPA in this case).

Original source