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?

8 comments

@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")
                  .setProjection(Projections.rowCount())
                  .uniqueResult();

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 = (
(Number)
    entityManager
    .createQuery(
        "select count(b) " +
        "from Book b")
    .getSingleResult()
).intValue();

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:

@Autowired
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")
                    .getSingleResult();

Or if you Need TypedQuery

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

@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: community.jboss.org/wiki/HibernateCoreMigrationGuide35

@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 (5.1.9.final 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

58 Answered Questions

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

55 Answered Questions

[SOLVED] How to create a memory leak in Java

42 Answered Questions

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

22 Answered Questions

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

  • 2010-04-08 13:30:00
  • weakish
  • 1522208 View
  • 1422 Score
  • 22 Answer
  • Tags:   python list count

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
  • 664176 View
  • 473 Score
  • 11 Answer
  • Tags:   jquery count row

48 Answered Questions

26 Answered Questions

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

  • 2009-03-02 22:56:34
  • Malachi
  • 1067495 View
  • 1896 Score
  • 26 Answer
  • Tags:   java enums

22 Answered Questions

[SOLVED] What's the difference between JPA and Hibernate?

Sponsored Content