How to Count the rows in Hibernate Query Language?

Question

I am trying to get just the count of the rows returned rather than all the results from the table.

I saw that this can be done like this:

( (Integer) session.createQuery("select count(*) from ....").iterate().next() ).intValue()

But when trying to store this query in an integer format(it says cannot convert from Query to Integer)

I am using a dynamic query where the values will be mentioned below the query like this

theQuery = "select count(*) from THM as thm " + 
                "join thm.TMC as tmc " +
                "join tmc.TIMCC as timcc " +
                "where thm.Qid = :Qid and thm.Cv = :Cv and timcc.Did = :Did and timcc.Cv= :Cv";

Query query = session.createQuery(theQuery);
query.setInteger("Qid", Integer.parseInt(Qid));
query.setInteger("Did", Did);
query.setInteger("Cv",cV);

Now, how can i get a count of all the rows returned by using Hibernate query in a variable without using list.size but directly from the query?

adreide · Accepted Answer · 2011-12-30 12:58:27Z

14

Have you tried the query.uniqueResult(); ? As your Select count(*) will give you only one number, you should be able to retrieve it with this like int count = (Integer)query.uniqueResult();

To count based on a Criteria you can do this:

Criteria criteria = currentSession().createCriteria(type);
criteria.setProjection(Projections.rowCount());
criteria.uniqueResult();

I'm using the Criteria right now so I know for sure that it works. I saw the uniqueResult() solution on a website here: http://www.jroller.com/RickHigh/entry/hibernate_pagination_jsf_datagrid_prototype1

answered Dec 30, 2011 at 12:58

adreide

1923 silver badges9 bronze badges

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

3 Comments

user1002782 Over a year ago

query.uniqueResult(); is working great & so does .iterate().next() ).intValue()

Jerry Over a year ago

What if my query is not Select count(*) instead it starts like "from THM"

blackwolf Over a year ago

The method createCriteria(String) from the type SharedSessionContract is deprecated.

Mark Hall · Accepted Answer · 2012-01-01 00:39:40Z

12

you can do this

long count = (long)session.createQuery("SELECT COUNT(e) FROM Employees e").getSingleResult();

edited Jan 1, 2012 at 0:39

Mark Hall

54.7k9 gold badges101 silver badges116 bronze badges

answered Jan 1, 2012 at 0:08

skyblue m.

1211 silver badge2 bronze badges

Comments

ndmhta · Accepted Answer · 2015-07-14 06:25:47Z

6

Try it.

Long count = ((Long) session.createQuery("select count(*) from Book").uniqueResult());
Integer totalBooks = count.intValue();

edited Jul 14, 2015 at 6:25

ndmhta

5261 gold badge8 silver badges22 bronze badges

answered Feb 11, 2015 at 17:32

Emiliano Dueñas

811 silver badge3 bronze badges

Comments

Neetz · Accepted Answer · 2019-08-14 09:08:09Z

3

Work for me

int list=(int) 
sessionFactory.getCurrentSession().createSQLQuery("select count(*) as count from 
                         Book").addScalar("count",IntegerType.INSTANCE).uniqueResult();

answered Aug 14, 2019 at 9:08

Neetz

4915 silver badges9 bronze badges

Collectives™ on Stack Overflow

How to Count the rows in Hibernate Query Language?

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related