posted 12 years ago
Hi,
I wonder what the correct way is to gather data required for pagination with JPA and the criteria API.
What I need is the total number of rows to calculate the position and total number of pages in the view - in the past I added a 'count' to the select part. (Maybe there is a better way?)
Now I have the problem that I am aggregating data with groupBy, leading to the result that the count is no longer representing the total number of rows, but the number of collapsed rows per row.
For example:
I have a Customer entity and in the criteria query I add a
groupBy(Customer_.city, Customer_.age);
The result will then show something like
City Age Count
London 20 5
London 21 2
London 22 10
...
I came up with the idea to have a subquery - but this is per JPA spec not allowed in the select part.
I could write a second query which uses distinct values to calculate what should be the outcome of the query - this works as long as I am not using a HAVIN in the WHERE part, since then the result will depend on the aggregation.
After having finished reading the complete google and stackoverflow corpus ;) I wonder if I am doing something completely wrong and there is in fact an easy solution for pagination with JPA in general. I would be very happy if someone could point out that solution.
Thank you!
(My current 'solution' is in fact to make a second query which just selects a unique field of the aggregation query and then counting the size of the result set: query.getResultList().size(); -- I really consider this a clumsy workaround, but I want to stick to the criteria query in order to avoid creating queries manually.)