File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Object Relational Mapping and the fly likes JPA Criteria query and pagination Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "JPA Criteria query and pagination" Watch "JPA Criteria query and pagination" New topic

JPA Criteria query and pagination

Thomas Kruse

Joined: Dec 26, 2005
Posts: 14

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_.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.)
I agree. Here's the link:
subject: JPA Criteria query and pagination
It's not a secret anymore!