• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

JPA Criteria query and pagination

 
Greenhorn
Posts: 14
Netbeans IDE Firefox Browser Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.)
 
Creativity is allowing yourself to make mistakes; art is knowing which ones to keep. Keep this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic