Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JPA Criteria query and pagination

 
Thomas Kruse
Greenhorn
Posts: 14
Firefox Browser Netbeans IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.)
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic