Spring Data JPA规范groupBy

Question

首先，很抱歉我的英语不够好。

我希望使用JPA进行分组统计，例如： select scrip, dustup, count(*) from data flow group by scrip, dstip。

因此，这里提供一段相应的代码：

public class DataflowSpec {
    public static Specification<Dataflow> search(final String[] group, final String[] sort, final String[] desc) {
        return new Specification<Dataflow>() {
            @Override
            public Predicate toPredicate(Root<Dataflow> root1, CriteriaQuery<?> query1, CriteriaBuilder builder) {
                // TODO Auto-generated method stub

                CriteriaQuery<Tuple> query = builder.createQuery(Tuple.class);

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


                query.multiselect(root.get("srcip"), root.get("dstip"), builder.count(root));

                query.groupBy(root.get("srcip"), root.get("dstip"));

                query.orderBy(builder.desc(root.get("srcip").as(BigInteger.class)));
                return query.getRestriction();
            }
        };
    }
}

但是，SQL日志是： Hibernate:

select
    count(dataflow0_.id) as col_0_0_ 
from
    Dataflow dataflow0_

Hibernate：

select
    dataflow0_.id as id1_2_,
    dataflow0_.byteall as byteall2_2_,
    dataflow0_.bytedn as bytedn3_2_,
    dataflow0_.byteup as byteup4_2_,
    dataflow0_.dstip as dstip5_2_,
    dataflow0_.dstport as dstport6_2_,
    dataflow0_.engieid as engieid7_2_,
    dataflow0_.flag as flag8_2_,
    dataflow0_.netid as netid9_2_,
    dataflow0_.pkgall as pkgall10_2_,
    dataflow0_.pkgdn as pkgdn11_2_,
    dataflow0_.pkgup as pkgup12_2_,
    dataflow0_.protocolid as protoco17_2_,
    dataflow0_.rtt as rtt13_2_,
    dataflow0_.srcip as srcip14_2_,
    dataflow0_.srcport as srcport15_2_,
    dataflow0_.updatetime as updatet16_2_ 
from
    Dataflow dataflow0_ limit ?

那么，如何解决呢？谢谢！

- hi bomb

---

你尝试使用JPQL了吗？ - 11thdimension

那么您会返回用于 WHERE 子句的 Predicate（到某个地方）... 那么 SELECT 子句部分会发生什么？ - Neil Stockton

不，我只想使用规范。 - hi bomb

Stockton。我想使用SELECT查询两个字段：srcip和dstip。但它返回了所有字段。 - hi bomb

Answer 1

对于仍在寻找如何在Spring jpa Specification中应用“group by”的人，您可以使用以下代码片段：

...
private Dataflow dataflowFilter;

@Override
public Predicate toPredicate(Root&ltDataflow> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {
    Predicate predicate = cb.conjunction();
    predicate.getExpressions().add(cb.equal(root.get("id"), dataflowFilter.getId()));
    ...
    <b>cq.groupBy(root.get("id"));</b>
    ...
    return predicate;
}

- razvanone

Answer 2

你可以通过specification实现Spring数据的分组，只需遵循[第2.6节][1]或[第3.6节][2]（适用于2.0版本之前或之后）。对于单个存储库操作，两个版本具有相同的解决方案。对于*全部*存储库解决方案，在2.0之前使用[自定义工厂bean][3]，而在2.0之后，此工厂bean操作被省略。

public Map<AlarmMsg.AlarmLevel, Long> testSpecification(String neId) {

    SingularAttribute attribute = AlarmData_.isClear;
    Specification<Object> where = Specification.where(
        (root, query, cb) -> cb.equal(root.get(attribute), false)
    );

    final Map<AlarmMsg.AlarmLevel, Long> result = alarmDataRepository.groupAndCount(AlarmData_.alarmLevel, where );
    return result;
}

代码库：

public interface AlarmDataRepository extends JpaRepository<AlarmData, Long>, JpaSpecificationExecutor<AlarmData>, CustomizedGroupCountRepository {

碎片仓库及其实现：

public interface CustomizedGroupCountRepository {
    Map<AlarmMsg.AlarmLevel, Long> groupAndCount(SingularAttribute singularAttribute, Specification where);
}

public class CustomizedGroupCountRepositoryImpl implements CustomizedGroupCountRepository {
    private final EntityManager entityManager;


    public CustomizedGroupCountRepositoryImpl(EntityManager entityManager) {
        Assert.notNull(entityManager, "EntityManager must not be null!");
        this.entityManager = entityManager;
    }

    @Override
    public Map<AlarmMsg.AlarmLevel, Long> groupAndCount(SingularAttribute singularAttribute, Specification where) {
        final CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        final CriteriaQuery<Tuple> query = criteriaBuilder.createQuery(Tuple.class);
        final Root<AlarmData> root = query.from(AlarmData.class);
        final Path<AlarmMsg.AlarmLevel> expression = root.get(singularAttribute);
        query.multiselect(expression, criteriaBuilder.count(root));
        query.select(criteriaBuilder.tuple(expression, criteriaBuilder.count(root)));
        query.where(where.toPredicate(root, query, criteriaBuilder));
        query.groupBy(expression);
        final List<Tuple> resultList = entityManager.createQuery(query).getResultList();
        return resultList.stream()
            .collect(toMap(
                t -> t.get(0, AlarmMsg.AlarmLevel.class),
                t -> t.get(1, Long.class))
            );
    }
}

在一个单一的存储库和一个通用存储库之间的主要区别在于，在一个单一的存储库中，它可以访问真正的实体类，比如spring参考文档中的User。因此，您不需要使用泛型类型来引用任何类型的实体，而在一个通用存储库中，自定义方法的实现使用泛型类型，并且必须从注入的JpaEntityInformation中获取其类信息，如3.6节所述。 [1]: https://docs.spring.io/spring-data/jpa/docs/1.8.0.RELEASE/reference/html/#repositories.single-repository-behaviour [2]: https://docs.spring.io/spring-data/jpa/docs/2.0.5.RELEASE/reference/html/#repositories.single-repository-behavior [3]: https://jeroenbellen.com/spring-data-extending-the-jpa-specification-executor/

- Tiina

---

"query.multiselect(expression, criteriaBuilder.count(root));" 及 "query.select(criteriaBuilder.tuple(expression, criteriaBuilder.count(root)));" 这两行代码的作用是什么？为什么不只用一行就可以了呢？ - shivarajan

Answer 3

规范不支持 groupBy。SimpleJpaRepository 用 query.select(root) 替换了 query.select/multiselect。

- radekolejarski

---

Specification is used for where part. CriteriaQuery is used for select - Tiina