0

How to create Spring JPA CriteriaQuery for the below HQL. The outer query SELECT COUNT(t) FROM (...) counts the number of rows returned by the subquery, which is given the alias t.

SELECT COUNT(t) FROM (select count(entity1_.id)
        from entity1 entity1_
             inner join entity1_.entity2 entity2_
             inner join entity2_.entity3 entity3_
        group by entity3_.name, entity2.name, entity1_.description
) t

I tried the below code but was not giving the exact result and not generating the above query

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
Root<Entity1> entity1Root = countQuery.from(Entity1.class);

Join<Entity1, Entity2> entity2Join = entity1Root.join("entity2");
Join<Entity2, Entity3> entity3Join = entity2Join.join("entity3");

countQuery.select(cb.countDistinct(entity1Root.get("id")))
    .groupBy(entity3Join.get("name"), entity2Join.get("name"), entity1Root.get("description"));

Long count = entityManager.createQuery(countQuery).getSingleResult();
Alex Man
  • 4,746
  • 17
  • 93
  • 178

0 Answers0