aspose file tools*
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
Author

JPA Criteria query and pagination

Thomas Kruse
Greenhorn

Joined: Dec 26, 2005
Posts: 14

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.)
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: JPA Criteria query and pagination