1

How can we write below query using Specification

SELECT e.id, e.name, count(e.id) 
FROM Employee e INNER JOIN Department d
WHERE e.id = d.empId;

When we are using Specification, it is not selecting count() even it is mentioned in multiselect.

Any idea, how to include count column in Specification when we are using JPA findAll method.

Tried with multiselect, but when it passed to specification then it query only on non-aggregated columns.

Expression<Long> countExp = cb.count(root.get("id"));
 CriteriaQuery<Employee> select =
 criteriaQuery.multiselect(root.get("id"), root.get("name"), countExp);

But it generates query like :

SELECT e.id, e.name
FROM Employee e INNER JOIN Department d
WHERE e.id = d.empId;

Seems having same issue as: JpaSpecificationExecutor : complex queries with specifications

Why the multiselect method in JPA does not work

Vaibhav Sawant
  • 341
  • 1
  • 7
  • 22

1 Answers1

1

To solve above problem, created own JPARepository implementation and override getQuery method from SimpleJpaRepository

private TypedQuery<Employee> getQuery(Specification<Employee> spec, Sort sort) {
        CriteriaBuilder builder = this.em.getCriteriaBuilder();
        CriteriaQuery<Employee> query = builder.createQuery(Employee.class);
        Root<Employee> root = this.applySpecificationToCriteria(spec, query);

        query.multiselect(root.get("id"), root.get("name"),
                builder.count(root.get("id")).alias("count"));

        if (sort != null) {
            query.orderBy(QueryUtils.toOrders(sort, root, builder));
        }

        return this.em.createQuery(query);
    }
Vaibhav Sawant
  • 341
  • 1
  • 7
  • 22
  • 1
    I'm using spring boot 1.5.22 and at least in this version the method applySpecificationToCriteria is private, so I can't use it. – dchang Oct 24 '19 at 10:32