getting count(*) using createSQLQuery in hibernate?

Question

I have several sql queries that I simply want to fire at the database. I am using hibernate throughout the whole application, so i would prefer to use hibernate to call this sql queries.

In the example below i want to get count + name, but cant figure out how to get that info when i use createSQLQuery().

I have seen workarounds where people only need to get out a single "count()" from the result, but in this case I am using count() + a column as ouput

SELECT count(*), a.name as count FROM user a
WHERE a.user_id IN (SELECT b.user_id FROM user b)
GROUP BY a.name
HAVING COUNT(*) BETWEEN 2 AND 5;

fyi, the above query would deliver a result like this if i call it directly on the database:

1, John
2, Donald
1, Ralph
...

Answer 1

Hi John

If your SQL statement looks like this SELECT count(*) as count, a.name as name... you could use setResultTransformer(new AliasToBeanResultTransformer(YourSimpleBean.class)) on your Query.
Where YourSimpleBean has the fields Integer count and String name respectively the setters setCount and setName.
On execution of the query with query.list() hibernate will return a List of YourSimpleBeans.

Cheers
Thomas

Answer 2

cheers for the info Thomas, worked wonderful for generating objects

the problem i had with my initial query was that "count" was a reserved word :P when i changed the name to something else it worked.