带有聚合函数的Spring-Data-JPA规范

Question

如何使用规范编写下面查询

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

当我们使用Specification时，即使在multiselect中提到了count()，也没有选择count（）。

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

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

为什么JPA中的multiselect方法不起作用

Answer 1

为了解决上述问题，创建了自己的JPARepository实现，并重写了SimpleJpaRepository中的getQuery方法

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