By craftsman

2009-09-03 09:07:59 8 Comments

For example, if we have a table Books, how would we count total number of book records with hibernate?


@xrcwrn 2015-03-16 05:53:10

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

@rajadilipkolli 2017-07-03 07:26:25

It should be``` Long count = (Long) session.createQuery("select count(1) from Book").uniqueResult();``` it will improve performance

@Salandur 2009-09-03 10:34:05

For older versions of Hibernate (<5.2):

Assuming the class name is Book:

return (Number) session.createCriteria("Book")

It is at least a Number, most likely a Long.

@dj_segfault 2011-11-15 18:48:31

It returns a long.

@Jerry Tian 2012-02-22 03:28:21

As @Salandur suggests, "It is at least a Number", and Number type has "intValue()", "longValue()" methods, so we can easily get the desired primitive type we want: ((Number) criteria.uniqueResult()).intValue()

@Lion 2012-07-27 03:20:50

It returns a type of Object.

@Tobias M 2012-09-17 01:27:16

If the entity mapping is unable to be found using a string parameter to the create criteria method, session.createCriteria(Book.class) can also be used

@bcmoney 2013-04-04 18:36:42

Like @MontyBongo said, I actually had to refer to the class like this: return (Number) session.createCriteria(Book.class).setProjection(Projections‌​.rowCount()).uniqueR‌​esult();

@Salandur 2013-05-08 08:55:42

Actualy, Hibernate also maps the class name (Book in this case) as a entity name. So you can both use the entity name ("Book") as the class name (Book.class). But if you have 2 entity mappings on the same class than you should use the enityname, otherwise both entity mappings will be loaded.

@nikel 2014-10-02 02:19:59

But If the number of rows in the table is more than what Long can hold , then what do we cast to?

@Salandur 2014-10-03 13:56:00

Then you should not use a rational database ;). Max value of long is 9,223372037×10¹⁸, which is laaaaaaaaaarge

@hram908 2016-12-16 16:06:21

how can you get the row count with connection?

@Lluis Martinez 2018-04-18 09:34:41

@dj_segfault are you sure it returns a long always? It can depend on the driver and the DBMS flavour I guess.

@Capn Sparrow 2018-10-14 00:38:41

createCriteria is deprecated since 5.2.

@Jon Spokes 2009-09-03 09:32:58

You could try count(*)

Integer count = (Integer) session.createQuery("select count(*) from Books").uniqueResult();

Where Books is the name off the class - not the table in the database.

@craftsman 2009-09-03 09:55:25

sorry but its not working with Java and Hibernate :( ( I did replace int with Integer, as it is in Java for type casting. )

@Jon Spokes 2009-09-03 12:21:50

It should work - with Integer instead of int ? You need to put the class name in the HQL, not the table name - is the only thing I can think that may be wrong

@Matt Sidesinger 2009-09-04 13:10:48

I believe the post directly below this is more in line with the core Hibernate principles.

@rParvathi 2016-07-01 11:10:29

for me its not working with java and hibernate. what to do instead?

@Vlad Mihalcea 2018-06-27 06:11:23

It's very easy, just run the following JPQL query:

int count = (
        "select count(b) " +
        "from Book b")

The reason we are casting to Number is that some databases will return Long while others will return BigInteger, so for portability sake you are better off casting to a Number and getting an int or a long, depending on how many rows you are expecting to be counted.

@LucianoDemuru 2018-03-14 10:48:26

This works in Hibernate 4(Tested).

String hql="select count(*) from  Book";
Query query= getCurrentSession().createQuery(hql);
Long count=(Long) query.uniqueResult();
return count;

Where getCurrentSession() is:

private SessionFactory sessionFactory;

private Session getCurrentSession(){
return sessionFactory.getCurrentSession();

@rajadilipkolli 2017-02-14 20:40:31

If you are using Hibernate 5+, then query will be modified as

Long count = session.createQuery("select count(1) from  Book")

Or if you Need TypedQuery

Long count = session.createQuery("select count(1) from  Book",Long.class)

@Antonio 2011-02-19 12:10:06

Here is what official hibernate docs tell us about this:

You can count the number of query results without returning them:

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

However, it doesn't always return Integer instance, so it is better to use java.lang.Number for safety.

@Tom 2013-05-23 02:05:42

+1 for an answer that gives the Hibernate team recommended method.

@rogerdpack 2013-09-17 20:17:43

For me this gave "java.lang.ClassCastException: java.lang.Long cannot be cast to java.lang.Integer" but casting to a Long instead works...

@machinery 2014-02-26 12:02:40

@rogerdpack this is because Hibernate changed the returned type in 3.5 to Long:

@Guillaume Husta 2018-02-06 09:55:36

The return type for the count function can be found in org.hibernate.dialect.function.StandardAnsiSqlAggregationFun‌​ctions.CountFunction (StandardBasicTypes.LONG)

@marioosh 2010-11-27 08:06:09

In Java i usually need to return int and use this form:

int count = ((Long)getSession().createQuery("select count(*) from Book").uniqueResult()).intValue();

@Jason Nichols 2012-01-18 14:59:08

The accepted answer for this question didn't work for me, but yours did. Thanks!

@kommradHomer 2012-03-16 10:28:37

is this the fastest and cheapest way for getting count of a query? i mean hibernate-wise

@thermz 2012-04-17 15:35:55

What's the point of using an ORM if we end up coding SQL anyway?

@Pramod 2012-09-21 05:37:42

That's my main concern (using SQL instead of HQL). I have to use nested SELECT just to count number of rows that comes after left outer join (I did not find proper implementation of left outer join in hibernate).

@BrunoJCM 2012-12-28 20:22:16

First off, this solution doesn't use SQL, it's HQL. And using count(*) instead of 'select count(e) from E e' or criteria works with @EmbeddedId and databases that don't support tuple count (eg. MySQL, where queries like 'select count((a,b)) from table1' doesn't work).

@another 2017-01-17 08:57:46

Why don't just cast directly to Integer, and let the unboxing do its work?? Please, is there an explaination?

@Jerry Chin 2017-08-14 07:03:34

The latest version of Hibernate ( at the time of writing) makes it very hard to do a simple conditional querying, so I'd prefer this method.

@Lluis Martinez 2018-04-18 09:35:46

@thermz That's why ORM are leaky abstractions. Very clear in this case.

Related Questions

Sponsored Content

24 Answered Questions

[SOLVED] How can I count the occurrences of a list item?

  • 2010-04-08 13:30:00
  • weakish
  • 1712668 View
  • 1539 Score
  • 24 Answer
  • Tags:   python list count

58 Answered Questions

[SOLVED] How to create a memory leak in Java?

44 Answered Questions

[SOLVED] How do I convert a String to an int in Java?

51 Answered Questions

59 Answered Questions

[SOLVED] How do I read / convert an InputStream into a String in Java?

27 Answered Questions

[SOLVED] How to get an enum value from a string value in Java?

  • 2009-03-02 22:56:34
  • Malachi
  • 1137309 View
  • 1996 Score
  • 27 Answer
  • Tags:   java enums

21 Answered Questions

13 Answered Questions

[SOLVED] How do you find the row count for all your tables in Postgres

11 Answered Questions

[SOLVED] jQuery: count number of rows in a table

  • 2009-07-19 14:02:41
  • danjan
  • 698261 View
  • 493 Score
  • 11 Answer
  • Tags:   jquery count row

Sponsored Content