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

Question

I am rather new to JPA 2 and it's CriteriaBuilder / CriteriaQuery API:

CriteriaQuery javadoc

CriteriaQuery in the Java EE 6 tutorial

I would like to count the results of a CriteriaQuery without actually retrieving them. Is that possible, I did not find any such method, the only way would be to do this:

CriteriaBuilder cb = entityManager.getCriteriaBuilder();

CriteriaQuery<MyEntity> cq = cb
        .createQuery(MyEntityclass);

// initialize predicates here

return entityManager.createQuery(cq).getResultList().size();

And that can't be the proper way to do it...

Is there a solution?

It would be very useful if someone can help or include in answers below. How to achieve following count query using JPA criteria API? select count(distinct col1, col2, col3) from my_table; — Bhavesh, Feb 09 '19 at 07:47
looking the answer below but instead of qb.count use qb.distinctCount @Bhavesh — Tonino, Apr 22 '20 at 14:43

score 268 · Accepted Answer · edited Sep 21 '16 at 13:28

268

A query of type MyEntity is going to return MyEntity. You want a query for a Long.

CriteriaBuilder qb = entityManager.getCriteriaBuilder();
CriteriaQuery<Long> cq = qb.createQuery(Long.class);
cq.select(qb.count(cq.from(MyEntity.class)));
cq.where(/*your stuff*/);
return entityManager.createQuery(cq).getSingleResult();

Obviously you will want to build up your expression with whatever restrictions and groupings etc you skipped in the example.

edited Sep 21 '16 at 13:28

dur

15,689
25
79
125

answered May 21 '10 at 17:58

Affe

47,174
11
83
83

3

That's what I had figured myself, thanks. But that means I can't use the same query instance to query for for the number of results and the actual results which I know is analogous to SQL, but which would make this API a lot more OOP-like. Well, at least I can reuse some of the predicates, I guess. – Sean Patrick Floyd May 21 '10 at 18:34
6

@Barett if it's a rather large count you probably don't want to load a list of hundreds or thousands of entities into memory just to find out how many there are! – Affe Dec 10 '12 at 06:31
@Barett this is used in case of pagination a lot. Hence the need for a total number and only a subset of the actual rows. – gkephorus Nov 27 '13 at 13:35
4

Mind you that the `qb.count` is done over the `Root` of your query (`Root` myEntity = cq.from(MyEntity.class)) and this is often already in your normal select code and when you forget you end up with a join to self. – gkephorus Nov 27 '13 at 13:38
6

To reuse the same criteria for the retrieval of objects and the count you may need to use aliases on the root, see https://forum.hibernate.org/viewtopic.php?p=2471522#p2471522 for an example. – Pool Apr 07 '14 at 07:58
this code was okay with me when I had one primary key, when I have a composite primary key (using Id class or embeddable ) I got an exception, any adea why? – Basheer AL-MOMANI Jan 13 '19 at 11:58
2

Not work for me with where condition. It gives me total count of records from the table. Ignoring where condition, any reason for that? – S_K Mar 27 '19 at 06:13
1

@S_K Probably you are using no the same Root> for cb.count() and your predicates (eq cb.less() ). I had the same problem. – Mateusz Jul 16 '21 at 09:36
Shouldn't it be `query.getResultList().stream().reduce(0L, Long::sum)` ? – cnmuc Jun 10 '22 at 13:42
@cnmuc this answer is 12 years old. feel free to add an answer using modern features? – Affe Jun 10 '22 at 15:50
@Affe it's not about using modern features, it's about correctness. because count-statements can also generate multiple results, which is why it is better to use `getResultList` instead of `getSingleResult`, and then sum the long values – cnmuc Jun 10 '22 at 20:53

score 36 · Answer 2 · answered May 11 '12 at 18:51

I've sorted this out using the cb.createQuery() (without the result type parameter):

public class Blah() {

    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    CriteriaQuery query = criteriaBuilder.createQuery();
    Root<Entity> root;
    Predicate whereClause;
    EntityManager entityManager;
    Class<Entity> domainClass;

    ... Methods to create where clause ...

    public Blah(EntityManager entityManager, Class<Entity> domainClass) {
        this.entityManager = entityManager;
        this.domainClass = domainClass;
        criteriaBuilder = entityManager.getCriteriaBuilder();
        query = criteriaBuilder.createQuery();
        whereClause = criteriaBuilder.equal(criteriaBuilder.literal(1), 1);
        root = query.from(domainClass);
    }

    public CriteriaQuery<Entity> getQuery() {
        query.select(root);
        query.where(whereClause);
        return query;
    }

    public CriteriaQuery<Long> getQueryForCount() {
        query.select(criteriaBuilder.count(root));
        query.where(whereClause);
        return query;
    }

    public List<Entity> list() {
        TypedQuery<Entity> q = this.entityManager.createQuery(this.getQuery());
        return q.getResultList();
    }

    public Long count() {
        TypedQuery<Long> q = this.entityManager.createQuery(this.getQueryForCount());
        return q.getSingleResult();
    }
}

Hope it helps :)

score 27 · Answer 3 · answered May 21 '10 at 17:56

27

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Long> cq = cb.createQuery(Long.class);
cq.select(cb.count(cq.from(MyEntity.class)));

return em.createQuery(cq).getSingleResult();

answered May 21 '10 at 17:56

axtavt

239,438
41
511
482

Shouldn't it be `em.createQuery(cq).getResultList().stream().reduce(0L, Long::sum)` ? – cnmuc Jun 10 '22 at 13:42

score 17 · Answer 4 · edited May 23 '17 at 12:18

17

As others answers are correct, but too simple, so for completeness I'm presenting below code snippet to perform SELECT COUNT on a sophisticated JPA Criteria query (with multiple joins, fetches, conditions).

It is slightly modified this answer.

public <T> long count(final CriteriaBuilder cb, final CriteriaQuery<T> selectQuery,
        Root<T> root) {
    CriteriaQuery<Long> query = createCountQuery(cb, selectQuery, root);
    return this.entityManager.createQuery(query).getSingleResult();
}

private <T> CriteriaQuery<Long> createCountQuery(final CriteriaBuilder cb,
        final CriteriaQuery<T> criteria, final Root<T> root) {

    final CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
    final Root<T> countRoot = countQuery.from(criteria.getResultType());

    doJoins(root.getJoins(), countRoot);
    doJoinsOnFetches(root.getFetches(), countRoot);

    countQuery.select(cb.count(countRoot));
    countQuery.where(criteria.getRestriction());

    countRoot.alias(root.getAlias());

    return countQuery.distinct(criteria.isDistinct());
}

@SuppressWarnings("unchecked")
private void doJoinsOnFetches(Set<? extends Fetch<?, ?>> joins, Root<?> root) {
    doJoins((Set<? extends Join<?, ?>>) joins, root);
}

private void doJoins(Set<? extends Join<?, ?>> joins, Root<?> root) {
    for (Join<?, ?> join : joins) {
        Join<?, ?> joined = root.join(join.getAttribute().getName(), join.getJoinType());
        joined.alias(join.getAlias());
        doJoins(join.getJoins(), joined);
    }
}

private void doJoins(Set<? extends Join<?, ?>> joins, Join<?, ?> root) {
    for (Join<?, ?> join : joins) {
        Join<?, ?> joined = root.join(join.getAttribute().getName(), join.getJoinType());
        joined.alias(join.getAlias());
        doJoins(join.getJoins(), joined);
    }
}

Hope it saves somebody's time.

Because IMHO JPA Criteria API is not intuitive nor quite readable.

edited May 23 '17 at 12:18

Community

1
1

answered May 22 '15 at 07:08

G. Demecki

10,145
3
58
58

3

@specializt of course it is not perfect - e.g. above solution is still missing a recursive joins on fetches. But do you think that just because of this I shouldn't share my thoughts? IMHO sharing knowledge is the main idea behind StackOverfow. – G. Demecki Sep 14 '15 at 06:30
Recursion on databases are always the worst possible solution imaginable ... thats a beginners mistake. – specializt Sep 14 '15 at 07:58
@specializt `recursion on databases`? I was talking about recursion on the API level. Do not confuse these concepts :-) JPA comes with very _powerfull/ complex_ API that allows you to do multiple joins/ fetches/ aggregations/ aliases etc in a single query. You have to deal with it while counting. – G. Demecki Sep 14 '15 at 08:16
1

Apparently you havent yet understood how JPA works - the vast majority of you criterions will be mapped onto appropriate database queries, including these (extremely weird) joins. Activate SQL output and observe your mistake - there is no "API layer", JPA is a ABSTRACTION layer – specializt Sep 14 '15 at 08:20
most likely, you will see many cascaded JOINs - because JPA cant yet create SQL functions automatically; but that will change sometime ... probably with JPA 3, i recall discussions about these things – specializt Sep 14 '15 at 08:27
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/89562/discussion-between-g-demecki-and-specializt). – G. Demecki Sep 14 '15 at 08:57
That is a good one, but still not so, better to have not "case like" method on fetch joins, not all JPA providers will allow that cast at all actually :) – wtsiamruk Mar 26 '20 at 16:40

Guido Medina · Answer 5 · 2018-11-26T19:14:36.500

It is a bit tricky, depending on the JPA 2 implementation you use, this one works for EclipseLink 2.4.1, but doesn't for Hibernate, here a generic CriteriaQuery count for EclipseLink:

public static Long count(final EntityManager em, final CriteriaQuery<?> criteria)
  {
    final CriteriaBuilder builder=em.getCriteriaBuilder();
    final CriteriaQuery<Long> countCriteria=builder.createQuery(Long.class);
    countCriteria.select(builder.count(criteria.getRoots().iterator().next()));
    final Predicate
            groupRestriction=criteria.getGroupRestriction(),
            fromRestriction=criteria.getRestriction();
    if(groupRestriction != null){
      countCriteria.having(groupRestriction);
    }
    if(fromRestriction != null){
      countCriteria.where(fromRestriction);
    }
    countCriteria.groupBy(criteria.getGroupList());
    countCriteria.distinct(criteria.isDistinct());
    return em.createQuery(countCriteria).getSingleResult();
  }

The other day I migrated from EclipseLink to Hibernate and had to change my count function to the following, so feel free to use either as this is a hard problem to solve, it might not work for your case, it has been in use since Hibernate 4.x, notice that I don't try to guess which is the root, instead I pass it from the query so problem solved, too many ambiguous corner cases to try to guess:

  public static <T> long count(EntityManager em,Root<T> root,CriteriaQuery<T> criteria)
  {
    final CriteriaBuilder builder=em.getCriteriaBuilder();
    final CriteriaQuery<Long> countCriteria=builder.createQuery(Long.class);

    countCriteria.select(builder.count(root));

    for(Root<?> fromRoot : criteria.getRoots()){
      countCriteria.getRoots().add(fromRoot);
    }

    final Predicate whereRestriction=criteria.getRestriction();
    if(whereRestriction!=null){
      countCriteria.where(whereRestriction);
    }

    final Predicate groupRestriction=criteria.getGroupRestriction();
    if(groupRestriction!=null){
      countCriteria.having(groupRestriction);
    }

    countCriteria.groupBy(criteria.getGroupList());
    countCriteria.distinct(criteria.isDistinct());
    return em.createQuery(countCriteria).getSingleResult();
  }

I think the only case that would be dangerous is when you have a left join and the picked root is not the main entity. Otherwise it doesn't matter, because the count will be the same regardless the picked entity. As for left join entities, I'm quite sure the first entity in the select is the reference one, for example, if you have students left join courses then picking student should be the natural thing because there could be courses that the student is not enrolled. — Guido Medina, Jun 08 '14 at 12:00
If the original query is groupBy query, the result would be one count for each group. If we can make a CriteriaQuery into a SubQuery, then count the subquery, it would work in all cases. Can we do that? — Dave, Jul 06 '14 at 01:21
Hi @Dave, I've come to the same conclusion as you, the real solution would be to be able to transform queries into subqueries, that would work for all cases, even for counting rows after a groupBy. Actually I can't seem to find a reason as to why the different clases for CriteriaQuery and Subquery, or at leas the fact that the common interface they share, AbstractQuery, does not define a select method. Because of this, there's no way to reuse almost anything. Have you find a clean solution for reusing a groupped by query for counting the rows? — Amanda Tarafa Mas, May 27 '15 at 12:57

score 0 · Answer 6 · answered Feb 09 '17 at 08:27

0

You can also use Projections:

ProjectionList projection = Projections.projectionList();
projection.add(Projections.rowCount());
criteria.setProjection(projection);

Long totalRows = (Long) criteria.list().get(0);

answered Feb 09 '17 at 08:27

Pavel Evstigneev

4,918
31
21

1

I am afraid Projections API is Hibernate specific but the question is asking about JPA 2. – gersonZaragocin Mar 02 '18 at 21:39
Still, I find it a useful addition, but maybe it should have been a comment. Can you expand your answer to include the full Hibernate-specific answer? – Benny Bottema Nov 05 '18 at 11:53
gersonZaragocin agree, but there is no code blocks in comments – Pavel Evstigneev Nov 05 '18 at 16:37

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

6 Answers6

Linked

Related