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

java

hibernate

hql

query-language

byNikita Barsukov·Oct 4, 2024

Eager to get back to your Netflix? Use Criteria and Projections.rowCount() to quickly count rows with Hibernate:

// Creating a Criteria for counting. Homer Simpson would say "D'oh!" If he knew it was that easy.
Criteria criteria = sessionFactory.openSession().createCriteria(YourEntity.class);

// Setting projection to count rows. Again, who knew?!
Long count = (Long) criteria.setProjection(Projections.rowCount()).uniqueResult();

This code generates a criteria query for YourEntity and counts the rows, releasing the result as a Long. Short, sweet and straight to the point!

Under the hood: a deeper dive into counting rows

Buckle up! Let's take a ride through the HQL (Hibernate Query Language) route for counting records:

// Good Ol’ HQL stepping in for a record count. Yep, the SQL got a Hibernate part now.
Long count = (Long) getSession().createQuery("SELECT COUNT(*) FROM Book").uniqueResult();

Here, the createQuery method leverages HQL to execute the query. Replace "Book" with your entity class name, because "naming truly matters"!

Safety first: the casting chronicle

To keep the potential for heartburn-inducing ClassCastException at bay, we'll do a careful type conversion:

// Do you believe in Numbers? Because Hibernate does. So, let's meet halfway.
Number countResult = (Number) criteria.setProjection(Projections.rowCount()).uniqueResult();

// And ... Ta Da! an integer from a Number. Voila! Safety manifested.
int count = countResult != null ? countResult.intValue() : 0;

Remember, java.lang.Number is Hibernate's BFF. It's flexible enough to handle different return types.

The Unhorcrux way: createQuery meets `iterate()`

A wizard way when createQuery collaborates with iterate():

// Hello potterheads, let's unleash the Elder Wand now, shall we?
Iterator countIterator = session.createQuery("SELECT COUNT(*) FROM Book").iterate();
if (countIterator.hasNext()) {
    Long count = (Long) countIterator.next();
}

Slashing through records like Dumbledore with the Elder Wand! Can it get any cooler?

Beware of the dark arts: common pitfalls

Potterheads know, every spell comes with its consequence. Here are some magic misfires and ways to avoid them:

🚮 What happens in the session, stays in the session! Don't forget to close sessions.
💻 It's the entity, not the table! Be mindful of the HQL table reference.
🪄 Don't get spellbound! Type casting can sometimes lead to ClassCastException if overlooked.

Tactics for winning the Triwizard Tournament: optimization

Diving into the misty waters of optimization:

🏞️ Did you know? Criteria queries can be cached! Use setCacheable(true).
⏳ Less is more! Consider restrictions if you require subset of data.
⌛ Do you fear the bogart of lazy loading? Don't! Just design your entities wisely.