Spring-Data-JPA Specification with Aggregate function

Question

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 · Accepted Answer · 2018-12-19 08:34:02Z

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

answered Dec 19, 2018 at 8:34

Vaibhav Sawant

3611 gold badge10 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dchang Over a year ago

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.

Collectives™ on Stack Overflow

Spring-Data-JPA Specification with Aggregate function

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related