CODEWITHSUNDEEP
javadatabasehibernatehql
user1002782
user1002782

Reputation: 299

How to Count the rows in Hibernate Query Language?

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?

Upvotes: 10

Views: 44264

Answers (4)

Neetz
Neetz

Reputation: 481

Work for me

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

Upvotes: 3

Emiliano Dueñas
Emiliano Dueñas

Reputation: 81

Try it.

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

Upvotes: 6

skyblue m.
skyblue m.

Reputation: 121

you can do this

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

Upvotes: 12

adreide
adreide

Reputation: 192

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

Upvotes: 14

Related Questions