Explain Codes LogoExplain Codes Logo

In JPA 2, using a CriteriaQuery, how to count results

java
jpa
criteriaquery
counting
Alex KataevbyAlex Kataev·Feb 23, 2025
TLDR

Sweet CriteriaBuilder, you counting star! Let's meet EntityManager for a quick rude count:

// Create CriteriaBuilder CriteriaBuilder cb = em.getCriteriaBuilder(); // The Beatles: We all live in a CriteriaQuery<Long> 🎶 CriteriaQuery<Long> countQuery = cb.createQuery(Long.class); // The root of all evils, uh, I mean entities Root<MyEntity> entityRoot = countQuery.from(MyEntity.class); // Go on, take a selfie... with a count filter on! countQuery.select(cb.count(entityRoot)); // Grab the result! Long total = em.createQuery(countQuery).getSingleResult();

Replace MyEntity with your entity class. The cb.count(entityRoot) is the paparazzi focusing on the counting operation.

Counting delicacies: Distinct, Joins and a Pinch of Reuse

Counting distinct entities

Fancy counting distinct records? No problemo!

// This time, we want to count elected officials. No more clones! CriteriaQuery<Long> countDistinctQuery = cb.createQuery(Long.class); Root<MyEntity> root = countDistinctQuery.from(MyEntity.class); countDistinctQuery.select(cb.countDistinct(root.get("distinctAttr"))); // Voila, distinct count! Long countDistinct = em.createQuery(countDistinctQuery).getSingleResult();

An optimized join count, saving you from "Oops, I loaded all the data"!

// We ain't gonna load all data, we ain't rookies! CriteriaQuery<Long> countQuery = cb.createQuery(Long.class); Root<MyEntity> root = countQuery.from(MyEntity.class); // Joining the party Join<MyEntity, RelatedEntity> joinedTable = root.join("relatedEntity", JoinType.LEFT); countQuery.select(cb.count(root)).where(cb.equal(joinedTable.get("attribute"), "conditionValue")); // Et voila! Long countWithJoin = em.createQuery(countQuery).getSingleResult();

Reusable criteria pattern

Repetition? Not on my watch! A reusable method:

// DRY is yum! public Long getCountForClass(EntityManager em, Class<MyEntity> domainClass) { CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Long> cq = cb.createQuery(Long.class); cq.select(cb.count(cq.from(domainClass))); return em.createQuery(cq).getSingleResult(); }

Mastering complex counts

Sometimes things get real: more complex queries, special JPA providers, and counting teeth... uh, I mean performance considerations.

Optimizing the counts

Beware, each additional join may cause a performance drain. Steer clear of fetch joins which inflate the entity counts.

Reusable geniuses

Imagine a utility class with EntityManager and domainClass as constructor params generating counts on demand. Einstein-level reusability, eh?

Provider-specific dance

JPA implementations like Hibernate or EclipseLink can have specific quirks. Check their specs and adjust your counts. The devil, as always, is in the details.