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