For example, if we have a table Books, how would we count total number of book records with hibernate?
Add a comment
|
8 Answers
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.
10 Comments
Jerry Tian
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
It returns a type of
Object.
Tobias M
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
Like @MontyBongo said, I actually had to refer to the class like this:
return (Number) session.createCriteria(Book.class).setProjection(Projections.rowCount()).uniqueResult();
Salandur
Then you should not use a rational database ;). Max value of long is 9,223372037×10¹⁸, which is laaaaaaaaaarge
|
In Java i usually need to return int and use this form:
int count = ((Long)getSession().createQuery("select count(*) from Book").uniqueResult()).intValue();
8 Comments
Jason Nichols
The accepted answer for this question didn't work for me, but yours did. Thanks!
kommradHomer
is this the fastest and cheapest way for getting count of a query? i mean hibernate-wise
thermz
What's the point of using an ORM if we end up coding SQL anyway?
Pramod
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).
Bruno Medeiros
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).
|
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.
4 Comments
Tom
+1 for an answer that gives the Hibernate team recommended method.
rogerdpack
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
@rogerdpack this is because Hibernate changed the returned type in 3.5 to Long: community.jboss.org/wiki/HibernateCoreMigrationGuide35
Guillaume Husta
The return type for the count function can be found in
org.hibernate.dialect.function.StandardAnsiSqlAggregationFunctions.CountFunction (StandardBasicTypes.LONG)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.
4 Comments
craftsman
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
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
I believe the post directly below this is more in line with the core Hibernate principles.
rParvathi
for me its not working with java and hibernate. what to do instead?
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();
Comments
Long count = (Long) session.createQuery("select count(*) from Book").uniqueResult();
1 Comment
rajadilipkolli
It should be``` Long count = (Long) session.createQuery("select count(1) from Book").uniqueResult();``` it will improve performance
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();
}
Comments
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.
