Hi, I need to add paginator for my Hibernate application. I applied it to some of my database operations which I perform using Criteria by setting Projection.count().This is working fine.
But when I use hql to query, I can't seem to get and efficient method to get the result count.
If I do query.list().size() it takes lot of time and I think hibernate does load all the objects in memory.
Can anyone please suggest an efficient method to retrieve the result count when using hql?
I've done similar things in the past. If you want to get a total count of records for the paginator, then I'd suggest a separate query that you do first. This query should just do a count and return the total.
As you suspect, in order to count your records on your main query, hibernate does have to load up all the records, although it will do it's best to not load all the data for each record. But still this takes time.
If you can get away with it, because even a count query can take time if your where clauses are inefficient, just check that you got a full page of records back, and put up an indicator of some sort to show that there could be more results on the next page. That's the fastest method because you are only queries for each page as you need it.
You'll have to use another query and Query#iterate(). See section 14.16. Tips & Tricks:
You can count the number of query results without returning them:
( (Integer) session.createQuery("select count(*) from ....").iterate().next() ).intValue()