How do I count the number of rows returned by subquery?

Question

I want to do something like this:

select count(*) from (select ...)

(As it would be in SQL), but in JPA.

Any ideas on how I would do it?

Answer 1

I stumbled upon this issue as well. I would ultimately like to execute the following JPQL:

SELECT COUNT(u)
FROM (
   SELECT DISTINCT u
   FROM User u
   JOIN u.roles r
   WHERE r.id IN (1)
)

But this wasn't possible, also not with criteria API. Research taught that this was just a design limitation in JPA. The JPA spec states that subqueries are only supported in WHERE and HAVING clauses (and thus not in the FROM).

Rewriting the query in the following JPQL form:

SELECT COUNT(u)
FROM User u
WHERE u IN (
   SELECT DISTINCT u
   FROM User u
   JOIN u.roles r
   WHERE r.id IN (1)
)

using the JPA Criteria API like as follows:

CriteriaQuery<Long> query = cb.createQuery(Long.class);
Root<User> u = query.from(User.class);
Subquery<User> subquery = query.subquery(User.class);
Root<User> u_ = subquery.from(User.class);
subquery.select(u_).distinct(true).where(u_.join("roles").get("id").in(Arrays.asList(1L)));
query.select(cb.count(u)).where(cb.in(u).value(subquery));
Long count = entityManager.createQuery(query).getSingleResult();
// ...

has solved the functional requirement for me. This should also give you sufficient insight into solving your particular functional requirement.

Answer 2

This should do the trick (If you want to use JPA criteria API):

CriteriaBuilder cb = getEntityManager().getCriteriaBuilder();  
CriteriaQuery<Long> query = cb.createQuery(Long.class);

Root<Entity> root = query.from(Entity.class);

//Selecting the count
query.select(cb.count(root));

//Create your search criteria
Criteria criteria = ...

//Adding search criteria   
query.where(criteria);

Long count = getEntityManager().createQuery(query).getSingleResult();

On the other hand, if you want to use JP-QL, the following code should do the trick:

//Add the where condition to the end of the query
Query query = getEntityManager().createQuery("select count(*) from Entity entity where...")
Long count = query.getSingleResult();

Answer 3

Use the following snippet to count rows for a given Criteria Query:

public static Query createNativeCountQuery(EntityManager em, CriteriaQuery<?> criteriaQuery) {
  org.hibernate.query.Query<?> hibernateQuery = em.createQuery(criteriaQuery).unwrap(org.hibernate.query.Query.class);
  String hqlQuery = hibernateQuery.getQueryString();

  QueryTranslatorFactory queryTranslatorFactory = new ASTQueryTranslatorFactory();
  QueryTranslator queryTranslator = queryTranslatorFactory.createQueryTranslator(
    hqlQuery,
    hqlQuery,
    Collections.emptyMap(),
    em.getEntityManagerFactory().unwrap(SessionFactoryImplementor.class),
    null
  );
  queryTranslator.compile(Collections.emptyMap(), false);

  String sqlCountQueryTemplate = "select count(*) from (%s)";
  String sqlCountQuery = String.format(sqlCountQueryTemplate, queryTranslator.getSQLString());

  Query nativeCountQuery = em.createNativeQuery(sqlCountQuery);

  Map<Integer, Object> positionalParamBindings = getPositionalParamBindingsFromNamedParams(hibernateQuery);
  positionalParamBindings.forEach(nativeCountQuery::setParameter);

  return nativeCountQuery;
}

private static Map<Integer, Object> getPositionalParamBindingsFromNamedParams(org.hibernate.query.Query<?> hibernateQuery) {
  Map<Integer, Object> bindings = new HashMap<>();

  for (var namedParam : hibernateQuery.getParameterMetadata().getNamedParameters()) {
    for (int location : namedParam.getSourceLocations()) {
      bindings.put(location + 1, hibernateQuery.getParameterValue(namedParam.getName()));
    }
  }

  return bindings;
}