1

I am trying to paginate results with the JPA criteria API. After looking through this answer: Total row count for pagination using JPA Criteria API

I see that the way to do it is to make a separate query that counts the number of rows returned by that specific criteria query. (They do this by making an array of predicates and using the same list for both the count query and the actual paginated query).

However, my criteria query cannot be replicated by a simple predicate list because it involves many joins with other tables and predicates involving comparisons with results returned from sub-queries.

I was wondering if there is a convenient way to get the row count for a particular criteria query without duplicating the query. (Or if there is an easy way to obtain a deep copy of a criteria query by passing in a reference)

Thanks in advance!

Solace
  • 2,161
  • 1
  • 16
  • 33

1 Answers1

2

The "convenient way" to do this is to resort to native SQL and use window functions. You can easily add a window function to your top level SELECT that simply counts all the rows as follows:

SELECT x, y, ..., COUNT(*) OVER()
FROM ...

By using SQL instead of JPQL / Criteria API, you'll have access to a variety of other very useful features, like the above.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509