我可以在一个Spring DataJPA存储库方法上执行@Query带规范的聚合函数吗？

Question

我有非常类似于以前问的问题。除了在我的情况下查询是要找到多个和（）

public interface IQuotaRepository extends JpaRepository<QuotaDao, Long>, JpaSpecificationExecutor<QuotaDao> {

        @Query( "select new ca.quota.kpi.model.dto.TotalDto(sum(q.value1), sum(q.value2), sum(q.value3), sum(q.value4), "
                                                                + "sum(q.value5), sum(q.value6), sum(q.value7), sum(q.value8), "
                                                                + "sum(q.value9), sum(q.value10), sum(q.value11), sum(q.value12)) from QuotaDao q")
        public TotalDto sumQuota();//Specification<QuotaDao> spec

        @Query( "select new ca.quota.kpi.model.dto.TotalDto(sum(q.value1), sum(q.value2), sum(q.value3), sum(q.value4), "
                                                                + "sum(q.value5), sum(q.value6), sum(q.value7), sum(q.value8), "
                                                                + "sum(q.value9), sum(q.value10), sum(q.value11), sum(q.value12)) from QuotaDao q")
        public TotalDto sumQuotaWithSpec(Specification<QuotaDao> spec);
}

• sumQuota（）工作正常

我希望重用“规范规范”来过滤来自网格控制的各种参数的结果。

你能建议任何解决方案或替代方案吗

Answer 1

好我发现解决方案可能比严格的JPA方法更有效

首先，我在存储库接口中声明了方法

public interface IQuotaRepositoryCustom{

    public TotalDto sumQuotaWithFilters(FilterParameterExtJs6[] filters);
    ...
}

然后在存储库实现中实现此方法

@Repository
public class IQuotaRepositoryImpl implements IQuotaRepositoryCustom {
    private static Logger logger = Logger.getLogger(IQuotaRepositoryImpl.class);    
    private final String SELECT_COUNT_FOR_TOTAL = "select count(*)";
    private final String SELECT_COUNT_FOR_TOTAL_QUOTA = SELECT_COUNT_FOR_TOTAL + " from QuotaDao q";
    private final String SELECT_TOTAL = "select new org.avp.quota.kpi.model.dto.TotalDto(sum(q.value1), sum(q.value2), sum(q.value3), sum(q.value4), "
                                                                                    + "sum(q.value5), sum(q.value6), sum(q.value7), sum(q.value8), "
                                                                                    + "sum(q.value9), sum(q.value10), sum(q.value11), sum(q.value12))";

private final String SELECT_TOTAL_QUOTA = SELECT_TOTAL + " from QuotaDao q";
    ...
    public TotalDto sumQuotaWithFilters(FilterParameterExtJs6[] filters) {
        String countHql = SELECT_COUNT_FOR_TOTAL_QUOTA + buildWhereClause(filters);
        Query countQuery = em.createQuery(countHql.toString());
        Long count = (Long)countQuery.getSingleResult();
        if(count.longValue() == 0L)
            return new TotalDto();
        String hql = SELECT_TOTAL_QUOTA+ buildWhereClause(filters);
        Query query = em.createQuery(hql.toString());
        return (TotalDto) query.getSingleResult();
    }
}

TotalDto是普通的数据传输对象

    public class TotalDto {

        private long value1;
        private long value2;
        private long value3;
        private long value4;
        private long value5;
        private long value6;
        private long value7;
        private long value8;
        private long value9;
        private long value10;
        private long value11;
        private long value12;
        private long total;
// constructor and getters/setters removed
}

顾名思义，localhelper函数只是构建where子句字符串（我不发布代码，因为来自UI的非常特定的格式）

private String buildWhereClause(FilterParameterExtJs6[] filterParameters) {
    StringBuffer sb = new StringBuffer();
    // Build where clause string 
    return sb.toString();
}

最后，在服务中调用此存储库方法

public TotalDto getFilteredSummAggregateQuota(final FilterParameterExtJs6[] filterParameters){
    TotalDto aggregateTotalQuota = quotaRepository.sumQuotaWithFilters(filterParameters);
    return aggregateTotalQuota;
}

希望这能帮助你四处走动。