Count results from joining a table based on a condition with Criteria API

Question

I can't figure out what seems like a simplest thing because I couldn't find it anywhere. I found some examples on using count, but I just can't fit them in my current code.

The idea is to count how many entities are linked to another one based on the passed id.

The code is following:

   final EntityManager em = this.entityManager();
   final CriteriaBuilder builder = em.getCriteriaBuilder();
   final CriteriaQuery<Dataset> criteriaQuery = builder.createQuery(Dataset.class);
   final Root<Dataset> resultSetRoot = criteriaQuery.from(Dataset.class);
   final SetJoin<Dataset, Report> join = resultSetRoot.join(Dataset_.reports);
   criteriaQuery.select(resultSetRoot).where(builder.equal(join.get(Report_.id), reportId));

That's how I select, but how could I count the amount of datasets linked to the passed report? I do know that the query root must be Long then, but then it's all messed up in my head again and I can't figure out how all these objects work together and can be wrapped in one another...

Thank you in advance!

MohammadReza Alagheband · Accepted Answer · 2018-09-17T07:14:19.400

Solution:

You could achieve this by adding builder.count(resultSetRoot) inside your criteriaQuery.select and then get the count result back by em.createQuery(criteria).getSingleResult();, here is the complete part:

SourceCode:

final EntityManager em = this.entityManager();
final CriteriaBuilder builder = em.getCriteriaBuilder();
final CriteriaQuery < Dataset > criteriaQuery = builder.createQuery(Dataset.class);
final Root < Dataset > resultSetRoot = criteriaQuery.from(Dataset.class);
final SetJoin < Dataset, Report > join = resultSetRoot.join(Dataset_.reports);

criteriaQuery.select(builder.count(resultSetRoot)).where(builder.equal(join.get(Report_.id), reportId));

Long count = em.createQuery(criteria).getSingleResult();

More Info:

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

https://gist.github.com/ufuk/2ca63e392cc61d5b5277249dc23d4eca // search count() in the webpage

That's a lot of details! Thank you! – Stanislav Codes Sep 17 '18 at 07:20 — Stanislav Codes, Sep 17 '18 at 07:20

score 1 · Answer 2 · answered Apr 15 '21 at 15:11

The trick is to create a CriteriaQuery<Long> (rather than with the Dataset type). From this object, create the root with the Dataset type.

CriteriaQuery<Long> countQuery = builder.createQuery(Long.class);
Root<Dataset> root = countQuery.from(Dataset.class);

Then, add the where part like you normally would:

Join<Dataset, Report> join = root.join(Dataset_.reports);
countQuery.where(builder.equal(join.get(Report_.id), reportId))

Finally, perform a select counting the root:

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

Count results from joining a table based on a condition with Criteria API

2 Answers2