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

by

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.

More Related Contents:

  1. JPA 2.0, Criteria API, Subqueries, In Expressions
  2. JPQL Constructor Expression – org.hibernate.hql.ast.QuerySyntaxException:Table is not mapped
  3. Using the JPA Criteria API, can you do a fetch join that results in only one join?
  4. JPA CriteriaBuilder – How to use “IN” comparison operator
  5. How do I do a “deep” fetch join in JPQL?
  6. Really dynamic JPA CriteriaBuilder
  7. JPA Select latest instance for each item
  8. How can I validate two or more fields in combination?
  9. JPQL Create new Object In Select Statement – avoid or embrace?
  10. JPA 2.0 many-to-many with extra column
  11. JPA: unidirectional many-to-one and cascading delete
  12. JPA & Criteria API – Select only specific columns
  13. JPA Criteria API – How to add JOIN clause (as general sentence as possible)
  14. How do I properly cascade save a one-to-one, bidirectional relationship on primary key in Hibernate 3.6
  15. Spring batch jpaPagingItemReader why some rows are not read?
  16. JPA Criteria builder IN clause query
  17. Doing an “IN” query with Hibernate
  18. JPQL: Receiving a Collection in a Constructor Expression
  19. How can I avoid the Warning “firstResult/maxResults specified with collection fetch; applying in memory!” when using Hibernate?
  20. kotlin data class + bean validation jsr 303
  21. JPA transient information lost on create
  22. JPA Native Query select and cast object
  23. JPA: JOIN in JPQL
  24. Spring Data JPA and Querydsl to fetch subset of columns using bean/constructor projection
  25. How to create a JPA query with LEFT OUTER JOIN
  26. Escaping the colon character ‘:’ in JPA queries
  27. JPA Query to handle NULL parameter value
  28. JPA Criteria API group_concat usage
  29. JPA Criteria Tutorial [closed]
  30. Persist java.time.Instant (JDK8) with JPA2/Hibernate

Leave a Comment