java - 动态 JPA 标准构建器

Question

我正在使用 Spring boot JPA 在下面执行下面的查询

select DELTA_TYPE,OPERATION_ID,COUNT(*) from ACTIVE_DISCREPANCIES ad group by DELTA_TYPE,OPERATION_ID

DELTA_TYPE、OPERATION_ID等可能来自外部系统，在存储库类中我尝试执行 native 查询

    @Query(value="select OPERATION_ID,DELTA_TYPE,count(*) from ACTIVE_DISCREPANCIES ad group by ?1",nativeQuery = true)
    public List<Object[]> groupByQuery(@Param("reconType") String recGroupColumns);

其中 recGroupColumns="DELTA_TYPE,OPERATION_ID"但不起作用，因为 @param 将拆分 ','

对我来说第二个选择是条件查询

public List<Object[]> getReconGroupList() {
        String recGroupColumns = "OPERATION_ID,DELTA_TYPE";
        String[] arrStr = recGroupColumns.split(",");
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Object[]> query = criteriaBuilder.createQuery(Object[].class);
        Root<ActiveDiscrepancies> adr = query.from(ActiveDiscrepancies.class);
        query.groupBy(adr.get("operationId"), adr.get("deltaType"));

    // query.groupBy(adr.get("deltaType"));
    query.multiselect(adr.get("operationId"), adr.get("deltaType"), criteriaBuilder.count(adr));
    TypedQuery<Object[]> typedQuery = entityManager.createQuery(query);
    List<Object[]> resultList = typedQuery.getResultList();
    return resultList;
}

这里如何动态传递groupBy和multiselect？

Answer 1

使用投影我们可以解决这个场景，下面是代码

     Criteria criteria = getSession().createCriteria(ActiveDiscrepancies.class);

      ProjectionList projectionList = Projections.projectionList();

      for(String str : colList) {
        projectionList.add(Projections.groupProperty(str));
      }
        projectionList.add(Projections.rowCount());
        criteria.setProjection(projectionList);
        List results = criteria.list();

        getSession().close();