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

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

Navigating the joins

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.