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
...

Kefik · Accepted Answer · 2012-08-21 07:53:45Z

3

Alternatively, you can use

SQLQuery query = session.createSQLQuery("SELECT count(*) as num, a.name as name 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;";

query.addScalar("num", Hibernate.INTEGER).addScalar("name", Hibernate.STRING); 
// you might need to use org.hibernate.type.StandardBasicTypes.INTEGER / STRING 
// for Hibernate v3.6+, 
// see https://hibernate.onjira.com/browse/HHH-5138

List<Object> result = query.list();
// result.get(2*i + 0) -> i-th row num
// result.get(2*i + 1) -> i-th row name

I'm using this in case of time-pressure, imo much faster to code then creating your own beans & transformers.

Cheers!

Jakub

answered Aug 21, 2012 at 7:53

Kefik

1016 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

JohnSmith · Accepted Answer · 2010-04-08 10:09:05Z

0

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.

answered Apr 8, 2010 at 10:09

JohnSmith

4,6889 gold badges27 silver badges25 bronze badges

Comments

Thomas Rawyler · Accepted Answer · 2013-12-04 22:58:31Z

0

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.

edited Dec 4, 2013 at 22:58

answered Apr 1, 2010 at 11:50

Thomas Rawyler

1,0957 silver badges16 bronze badges

Collectives™ on Stack Overflow

getting count(*) using createSQLQuery in hibernate?

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related