Example using countDistinct in a JPA Criteria API query

Question

I'm having trouble figuring out how to represent the following JPQL query:

SELECT count(e) FROM Foo e

using Criteria API. What I'm trying is:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Foo> c = cb.createQuery(Foo.class);
Root<Foo> f = c.from(Foo.class);
c.select(cb.count(f));

but this is not working. I also tried:

c.select(cb.count(f.get("id"));

This is for JPA2, Eclipselink.

score 11 · Accepted Answer · edited Jul 13 '10 at 18:24

11

try this, this is working with hibernate 3.5.1:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Long> c = cb.createQuery(Long.class);
Root<Foo> f = c.from(Foo.class);
c.select(cb.count(f));
int count = em.createQuery(c).getSingleResult().intValue();

edited Jul 13 '10 at 18:24

cdmckay

31,832
25
83
114

answered Apr 16 '10 at 11:04

Buchi

126
1
3

Arghhh. I new it was a simple mistake. – Tim Apr 17 '10 at 14:21

score 5 · Answer 2 · answered Feb 28 '12 at 14:56

This is a pretty old question but for completness here's a simple addition:

The title said something about "using countDistinct", so countDistinct should be mentioned here:

CriteriaBuilder critBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Long> critQuery = criteriaBuilder.createQuery(Long.class);
Root<Foo> root = critQuery.from(Foo.class);

critQuery.select(critBuilder.countDistinct(root));
int count = entityManager.createQuery(critQuery).getSingleResult().intValue();

This is important if you don't want to count rows that are double. If you want to avoid doule rows in your ResultList, you'd had to use:

CriteriaBuilder critBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Long> critQuery = criteriaBuilder.createQuery(Long.class);
Root<Foo> root = critQuery.from(Foo.class);

critQuery.select(root).distinct(true);
List<Foo> result = entityManager.createQuery(critQuery).getResultList();

Example using countDistinct in a JPA Criteria API query

2 Answers2