The doc explains how to count records for a trivial Hibernate query:

select count(*)
from eg.Cat as cat
where cat.name='Fritz'

I am trying to have a generic "algorithm" returning the needing HQL for counting the number of records for whatever any Hibernate query:

public String getHqlCount(String any_hql_query) {
...
}

As the original HQL may already contain the "select" keyword, I tried to count using a sub-select:

select count(cat)
from(
select cat from eg.Cat as cat where cat.name='Fritz'
)

which does not work (tested in hibern8ide) !
What should be the exact query in such case ??

Now, what about multiple-classes resultsets ??
Can anynone give me the HQL for counting records of the following initial query:

select mother, offspr, mate.name
from eg.DomesticCat as mother
inner join mother.mate as mate
left outer join mother.kittens as offspr


Thanks in advance.

I'm trying to solve the same problem (modifying an HQL query to find the number of rows) with an additional twist: i want it to work with paginated results (via Query.setFirst/MaxResults).

I discovered today that in Oracle one can write SQL like:



and then each resulting row contains the total number of rows. So I'd like to just prepend a "count(*) over ()" pseudo-column to the HQL select clause. However, I can't see a way to embed arbitrary SQL in the select clause. (I'm using 2.0.2) I'm willing to do this in a non-portable way because this is much more efficient than running two queries to get the total count.

The RoadMap indicates that 2.1b1 added "support for dialect-specific SQL functions in the HQL select clause" which may be relevant, but I've yet to find a explanation or example of this in the 2.1 reference.

Or am I going about this the hard way? Is there a way to get the total count when using Query.setFirstResult/setMaxResults ??