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

Question

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:

1. 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.

1. 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:

1. shall support order by multiple fields;
2. given a query, it will return the record position or the containing record page offset;

A good solution would:

1. be purely JPA;
2. be Ok if one additional query is performed in database just to find out the record position;
3. be Ok if hibernate is used in some point (as Hibernate is behind JPA in this case).

Answer 1

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.

Answer 2

Given that the bounty is still unassigned, I'll add another answer. (This is quite similar to Tomasz Nurkiewicz's answer, but talks a little bit more about what I think is the tricky part: making the WHERE clause right. I'll delete this if it's considered impolite.)

You don't need a native query to find the record's position, just a carefully crafted SELECT COUNT(r).

For the sake of making it concrete, let's pretend that the Record type has properties foo and bar that you're using to sort (i.e. ORDER BY foo, bar). Then a quick-and-dirty version of the method you want looks like this (untested):

List<Record> getRecordsPage(Record record, int pageSize) {
    // Note this is NOT a native query
    Query query = entityManager.createQuery(
        "SELECT COUNT(r) " +
        "FROM Record r " +
        "WHERE (r.foo < :foo) OR ((r.foo = :foo) AND (r.bar < :bar))");
    query.setParameter("foo", record.getFoo());
    query.setParameter("bar", record.getBar());
    int zeroBasedPosition = ((Number) query.getSingleResult()).intValue();
    int pageIndex = zeroBasedPosition / pageSize;
    return getRecordsPage(pageIndex, pageSize);
}

There are two tricky considerations: getting the WHERE clause exactly right, and dealing with "ties" in all of your sort columns.

Regarding the WHERE clause, the goal is to count the records that sort "lower" than the given Record. With one sort column it's easy: it's just the records with r.foo < :foo, for example.

For two sort columns it's marginally harder, since there are potentially "ties" in the first column that have to be broken with the second column. So the "lower" records either have r.foo < :foo, or r.foo = :foo AND r.bar < :bar. (If there were three, then you'd need three OR conditions-- something like (r.foo < :foo) OR ((r.foo = :foo) AND (r.bar < :bar)) OR ((r.foo = :foo) AND (r.bar = :bar) AND (r.baz < :baz)).)

And then there's the possibility of "ties" in all of your sort columns. If this happens, then your pager function (getRecordsPage(long,int)) could potentially get different results each time if it's naively implemented (since the database has leeway to return "equal" rows in a different order every time a query is performed).

One easy way to get around this is to add a sort by the record ID to make sure that the ties are broken the same way every time. And you'd want to put corresponding logic in BOTH of the functions mentioned in your question. That means having adding id onto the end of your query, e.g. ORDER BY foo,bar,id, in the pager function, and adding another OR condition to the WHERE in getRecordsPage(Record,int).)