Hi all,

I'm having some trouble figuring out just how to get a total record count using a query with parameters and joining multiple tables. I've been through the forum hoping to find a solution but I can't put it all together.

I'm currently running Hibernate 2.1.2 and MySQL 4.0.15

I have a somewhat complex query where I'm joining multiple tables via assocation tables. The part that needs some background is in the pet.attributes table. This is an association table of pets to their attributes and I'm selecting all pets who have match to three attributes (reference_id's).

An example of this association table might look like:


My query is defined as


and via hibernate I use


Now the query returns the results as expected however, I want to know how many total records are in the result for paging purposes and I don't want to access that via


since there could be a substantial number of records and the memory/performance hit would be way too costly.

I've tried using


which returned a java.lang.ClassCastException, and


which returned

Hibernate ExceptionThe collection was unreferenced
java.lang.NullPointerException

What am I missing?

You realize, that query.list() returns a fully loaded list, right? If you have the List already from query.list(), calling size() and that list is not gonna hardly any "memory/performance hit".

If you are talking about trying to get the count prior to calling query.list(), then thats a different issue (and if you are doing pagination you most likely want query.scrol() instead of query.list()).


You are querying for pets. Why would iterate() return an Integer?


createFilter() is used to narrow (filter) the results in an association collection http://www.hibernate.org/hib_docs/api/net/sf/hibernate/Session.html#createFilter(java.lang.Object,%20java.lang.String)

Unfortunately, the only way to do this currently is to either 1) do the list() and call its size; 2) perform an additional query selecting count(*) based on the same FROM and WHERE clauses.

Steve,

Thanks for the response.

Yes I do realize that query.list() returns the actual list and I would like to know the size prior to doing that. I showed the code for that (and pagination) simply as an example of what I was doing. I don't want to load the entire list and then call size() because there could be 1000+ records, all of which I do not want to load into memory. I thought that perhaps hibernate had a way to take a query and then return a count based on the query but it sounds like an additional query that only returns the count is the way to go.

Again, many thanks for pointing out my ignorance and explaining things.