Problem when using count with distinct

MaximeM · **Joined:** Tue May 02, 2006 6:06 am **Posts:** 2

i have a problem with hibernate; I want to get the row count of this named query:

Code:

select distinct table.a, table.b from MyTable table where table.a = :val

where a and b are primary key columns of MyTable.

So, as i usually do with sql, I tried

Code:

select count(*) from 
 (select distinct table.a, table.b from MyTable table 
where table.a = :val)

but hibernate refuse to execute this query, saying :

org.hibernate.hql.ast.QuerySyntaxError: unexpected token: ( near line 3, column 46)

I tried a lot of tricks (as select count(distinct table.a, table.b). for example), but none did satisfy me. The only way to do that on hql seems something like :

Code:

Query myQuery = session.createQuery("select distinct table.a, table.b from MyTable table where table.a = :val").setParameter("val", "...");
int count = myQuery.list().size;

but i'm afraid hibernate map the whole list when getting its size, which may be huge, whereas i'm only interest by its size.

Hibernate version:
3.1

Name and version of the database you are using:
Oracle 9i

ggeorges · **Joined:** Tue May 02, 2006 3:17 pm **Posts:** 3

I have exactly the same problem. This is an easy query for SQL, surprised that I cannot do this in HQL. For now I must list the results and use the size() method.

bkmr_77 · **Joined:** Tue Apr 25, 2006 12:04 pm **Posts:** 260

Maybe the trick in the link will help you http://www.hibernate.org/118.html#A2

bonnyr · **Posted:** Tue May 02, 2006 9:48 pm

If your database or JDBC driver supports this, you could use
ScrollableResults. This does not actually iterate over the whole
result set when you request the last page and get the row number.

I think this will allow you to run the distinct query and get the size of
the result set without costing too much time and without mapping the
results back into objects.

See the code fragment below:

Code:

                    ScrollableResults sr = q.scroll();
                    sr.last();
                    int size = sr.getRowNumber();

This fragment is typically used in pagination, but the principal would be
the same, I think.

MaximeM · **Joined:** Tue May 02, 2006 6:06 am **Posts:** 2

bkmr_77 wrote:

Maybe the trick in the link will help you http://www.hibernate.org/118.html#A2

You know, looking at the doc is the first thing i'v done (the first request i wrote is what it is said in the documentation); If it had solved my problem i wouldn't have post any message on this board

bonnyr wrote:

If your database or JDBC driver supports this, you could use
ScrollableResults. This does not actually iterate over the whole
result set when you request the last page and get the row number.

I think this will allow you to run the distinct query and get the size of
the result set without costing too much time and without mapping the
results back into objects.

See the code fragment below:

Code:

                    ScrollableResults sr = q.scroll();
                    sr.last();
                    int size = sr.getRowNumber();

This fragment is typically used in pagination, but the principal would be
the same, I think.

i'll will try what you said, it looks better than what i did, at least until this query will be supported by hibernate.