users@jpa-spec.java.net

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

From: arjan tijms <arjan.tijms_at_gmail.com>
Date: Wed, 16 Dec 2015 22:01:46 +0100

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