users@jpa-spec.java.net

[jpa-spec users] Re: Paginating (paging/sorting/filtering) support in JPA 2.2 MR

From: Oliver Gierke <ogierke_at_pivotal.io>
Date: Wed, 16 Dec 2015 22:22:06 +0100

It might have not crossed your radar, but support for pagination on top of JPA has been happily served in Spring Data JPA (and other store modules) [0] since 2011 and its predecessor since 2008 :).

There's even a slightly optimized concept of a slice which in contrast to a page only knows about a "has next" state and can avoid the potentially very costly additional count query at the expense of not knowing about the overall total of elements.

Having built the implementation of that I agree that esp. translating more complex sort expressions is a quite a bit of a challenge (as I experienced mostly due to ambiguities in the metamodel API). So any improvements and clarifications in that area would be highly appreciated.

Cheers,
Ollie

[0] http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.special-parameters

> Am 16.12.2015 um 22:01 schrieb arjan tijms <arjan.tijms_at_gmail.com>:
>
> Hi,
>
> I noticed the following items were placed on the provisional/initial JPA 2.2 short list:
>
> * JPQL/SQL string from Query
> * Creating criteria from query
>
>
> Very happy to see those, as those can all help implementing a very common case of {paging/sorting/filtering}. I wrote an article about this here: http://jdevelopment.nl/counting-rows-returned-jpa-query
>
> To mention the gist here: this is used in a lot of applications where the result from a query is shown in a table. The user can scroll through the results one page at a time, and the result can be sorted by clicking on table column headers. There is a search field present to search in the result.
>
> This typically requires the following:
>
> 1. The total number of rows (or entities) in the full result must be known
> 2. There should be support for an offset in the full result and a limit for the amount of rows that will be obtained
> 3. The column (attribute) on which to sort must be dynamically added to the query
> 4. Search expressions must be dynamically added to the query
>
> Currently this is rather hard to implement in a straightforward and maintainable way in JPA.
>
> With "Creating criteria from query", it will be possible to define a base query using the easier to read and maintain JPQL syntax. If there are search expressions, the JPQL based query can be converted to a criteria, and then those expressions can be dynamically added. Same thing for adding the column on which to sort.
>
> With "JPQL/SQL string from Query", specifically the SQL part, the now impossible (without resorting to elaborate amounts of proprietary code) requirement of counting the results can be done by adding a "select count(*) from ()" to the SQL.
>
> Actually "Support subqueries in SELECT lists" (https://java.net/jira/browse/JPA_SPEC-107) would be a somewhat nicer way to do this, but for this use case just having "JPQL/SQL string from Query" would be a truly great improvement.
>
> Kind regards,
> Arjan
>
>
>
>
>
>
>
>

--
/**
 * @author Oliver Gierke - Senior Software Engineer
 *
 * @param email ogierke_at_pivotal.io
 * @param phone +49-151-50465477
 * @param fax   +49-351-418898439
 * @param skype einsdreizehn
 * @see http://www.olivergierke.de
 */