How do we count rows using older versions of Hibernate (~2009)?

Question

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

score 310 · Accepted Answer · edited Nov 04 '18 at 18:58

310

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.

edited Nov 04 '18 at 18:58

Alex R

11,364
15
100
180

answered Sep 03 '09 at 10:34

Salandur

6,409
2
22
23

10

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() – Jerry Tian Feb 22 '12 at 03:28
It returns a type of `Object`. – Lion Jul 27 '12 at 03:20
5

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 – Tobias M Sep 17 '12 at 01:27
5

Like @MontyBongo said, I actually had to refer to the class like this: `return (Number) session.createCriteria(Book.class).setProjection(Projections.rowCount()).uniqueResult();` – bcmoney Apr 04 '13 at 18:36
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. – Salandur May 08 '13 at 08:55
But If the number of rows in the table is more than what Long can hold , then what do we cast to? – nikel Oct 02 '14 at 02:19
2

Then you should not use a rational database ;). Max value of long is 9,223372037×10¹⁸, which is laaaaaaaaaarge – Salandur Oct 03 '14 at 13:56
how can you get the row count with connection? – hram908 Dec 16 '16 at 16:06
@dj_segfault are you sure it returns a long always? It can depend on the driver and the DBMS flavour I guess. – Lluis Martinez Apr 18 '18 at 09:34
createCriteria is deprecated since 5.2. – Capn Sparrow Oct 14 '18 at 00:38

score 102 · Answer 2 · answered Nov 27 '10 at 08:06

102

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

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

answered Nov 27 '10 at 08:06

marioosh

27,328
49
143
192

1

The accepted answer for this question didn't work for me, but yours did. Thanks! – Jason Nichols Jan 18 '12 at 14:59
is this the fastest and cheapest way for getting count of a query? i mean hibernate-wise – kommradHomer Mar 16 '12 at 10:28
59

What's the point of using an ORM if we end up coding SQL anyway? – thermz Apr 17 '12 at 15:35
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). – Pramod Sep 21 '12 at 05:37
15

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). – Bruno Medeiros Dec 28 '12 at 20:22
Why don't just cast directly to Integer, and let the unboxing do its work?? Please, is there an explaination? – another Jan 17 '17 at 08:57
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. – Jerry Chin Aug 14 '17 at 07:03
@thermz That's why ORM are leaky abstractions. Very clear in this case. – Lluis Martinez Apr 18 '18 at 09:35

score 42 · Answer 3 · edited Feb 23 '16 at 09:27

42

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.

edited Feb 23 '16 at 09:27

Frederik Struck-Schøning

12,981
8
59
68

answered Feb 19 '11 at 12:10

Anthony

12,407
12
64
88

1

+1 for an answer that gives the Hibernate team recommended method. – Tom May 23 '13 at 02:05
3

For me this gave "java.lang.ClassCastException: java.lang.Long cannot be cast to java.lang.Integer" but casting to a Long instead works... – rogerdpack Sep 17 '13 at 20:17
2

@rogerdpack this is because Hibernate changed the returned type in 3.5 to Long: https://community.jboss.org/wiki/HibernateCoreMigrationGuide35 – machinery Feb 26 '14 at 12:02
1

The return type for the count function can be found in `org.hibernate.dialect.function.StandardAnsiSqlAggregationFunctions.CountFunction` (_StandardBasicTypes.LONG_) – Guillaume Husta Feb 06 '18 at 09:55

score 12 · Answer 4 · edited Aug 13 '18 at 00:09

12

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.

edited Aug 13 '18 at 00:09

Unmitigated

76,500
11
62
80

answered Sep 03 '09 at 09:32

Jon Spokes

2,599
2
18
21

sorry but its not working with Java and Hibernate :( ( I did replace int with Integer, as it is in Java for type casting. ) – craftsman Sep 03 '09 at 09:55
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 – Jon Spokes Sep 03 '09 at 12:21
1

I believe the post directly below this is more in line with the core Hibernate principles. – Matt Sidesinger Sep 04 '09 at 13:10
for me its not working with java and hibernate. what to do instead? – rParvathi Jul 01 '16 at 11:10

score 6 · Answer 5 · edited Jan 23 '19 at 12:56

6

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

edited Jan 23 '19 at 12:56

akash

22,664
11
59
87

answered Mar 16 '15 at 05:53

xrcwrn

5,339
17
68
129

It should be``` Long count = (Long) session.createQuery("select count(1) from Book").uniqueResult();``` it will improve performance – rajadilipkolli Jul 03 '17 at 07:26

rajadilipkolli · Answer 6 · 2017-06-11T07:41:33.850

6

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();

edited Jun 11 '17 at 07:41

answered Feb 14 '17 at 20:40

rajadilipkolli

3,475
2
26
49

score 1 · Answer 7 · answered Mar 14 '18 at 10:48

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();
}

score 1 · Answer 8 · answered Jun 27 '18 at 06:11

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.

How do we count rows using older versions of Hibernate (~2009)?

8 Answers8

Linked

Related