ChatGPT解决这个技术问题 Extra ChatGPT

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

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


A
Alex R

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.


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()
It returns a type of Object.
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
Like @MontyBongo said, I actually had to refer to the class like this: return (Number) session.createCriteria(Book.class).setProjection(Projections.rowCount()).uniqueResult();
Then you should not use a rational database ;). Max value of long is 9,223372037×10¹⁸, which is laaaaaaaaaarge
m
marioosh

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

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

The accepted answer for this question didn't work for me, but yours did. Thanks!
is this the fastest and cheapest way for getting count of a query? i mean hibernate-wise
What's the point of using an ORM if we end up coding SQL anyway?
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).
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).
F
Frederik Struck-Schøning

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.


+1 for an answer that gives the Hibernate team recommended method.
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 this is because Hibernate changed the returned type in 3.5 to Long: community.jboss.org/wiki/HibernateCoreMigrationGuide35
The return type for the count function can be found in org.hibernate.dialect.function.StandardAnsiSqlAggregationFunctions.CountFunction (StandardBasicTypes.LONG)
U
Unmitigated

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.


sorry but its not working with Java and Hibernate :( ( I did replace int with Integer, as it is in Java for type casting. )
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
I believe the post directly below this is more in line with the core Hibernate principles.
for me its not working with java and hibernate. what to do instead?
r
rajadilipkolli

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

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

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

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

V
Vlad Mihalcea

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.