Hibernate Criteria按查询分组的行数

我试图通过查询以hibernate标准获取组的结果 . 正如您在下面的代码中看到的，我在ProjectionList中添加了许多投影 . 我在前端有一个分页，所以如果客户端想要获取20个结果，我将 firstResult 设置为 0 ，将 maxResults 设置为 20 .

我也需要发送此查询的总行数 . 但是，如果我使用 setProjection(Projections.rowCount) ，它将覆盖我的 groupby ， sum 和 max 投影 .

我怎么能写像 select count(*) from ( myCriteria ) 这样的东西？

@Override
public SearchResult<ServiceIncomeSO> findServiceIncomesBySearch(IncomeQuery query, Pager pager) {
    Criteria criteria = createCriteria(ServiceIncome.class, "sin");
    criteria.createAlias("sin.partner", "ptr", JoinType.LEFT_OUTER_JOIN);

    if (StringUtils.isNotBlank(query.getPartnerKey())) {
        criteria.add(eq("ptr.key", query.getPartnerKey()));
    }

    if (StringUtils.isNotBlank(query.getPartnerTxt())) {
        criteria.add(disjunction() //
            .add(ilike("ptr.key", query.getPartnerTxt(), ANYWHERE)) //
            .add(ilike("ptr.name", query.getPartnerTxt(), ANYWHERE)) //
            .add(ilike("ptr.title", query.getPartnerTxt(), ANYWHERE))); //
    }

    if (CollectionUtils.isNotEmpty(query.getPartnerTypes())) {
        criteria.createAlias("ptr.partnerTypes", "partnerType", JoinType.LEFT_OUTER_JOIN);
        criteria.add(in("partnerType." + COLLECTION_ELEMENTS, query.getPartnerTypes()));
    }

    if (CollectionUtils.isNotEmpty(query.getCategories())) {
        criteria.createAlias("ptr.categories", "cat", JoinType.LEFT_OUTER_JOIN);
        criteria.add(in("cat.key", query.getCategories()));
    }

    if (query.getPeriodFromMonth() != null && query.getPeriodFromYear() != null) {
        addPeriodFrom(criteria, "sin.year", "sin.month", query.getPeriodFromYear(), query.getPeriodFromMonth());
    }

    if (query.getPeriodToMonth() != null && query.getPeriodToYear() != null) {
        addPeriodTo(criteria, "sin.year", "sin.month", query.getPeriodToYear(), query.getPeriodToMonth());
    }

    String[] fields;
    ProjectionList projection;
    if (query.getBreakout() == QueryBreakout.PARTNER) {
        projection = Projections.projectionList() //
            .add(Projections.groupProperty("sin.partner.key")) //
            .add(Projections.groupProperty("sin.year")) //
            .add(Projections.groupProperty("sin.month")) //
            .add(Projections.sum("sin.subscriberCount")) //
            .add(Projections.sum("sin.income"));//
        fields = new String[] { "partnerKey", "year", "month", "subscriberCount", "income" };
    } else {
        projection = Projections.projectionList() //
            .add(Projections.groupProperty("sin.partner.key")) //
            .add(Projections.groupProperty("sin.service.id")) //
            .add(Projections.groupProperty("sin.year")) //
            .add(Projections.groupProperty("sin.month")) //
            .add(Projections.max("sin.shortNumber")) //
            .add(Projections.sum("sin.subscriberCount")) //
            .add(Projections.sum("sin.income")) //
        ; //
        fields = new String[] { "partnerKey", "serviceId", "year", "month", "shortNumber", "subscriberCount", "income" };
    }

    criteria.setProjection(projection);
    criteria.addOrder(desc("sin.year")).addOrder(desc("sin.month"));

    criteria.setFirstResult(pager.getOffset());
    criteria.setMaxResults(pager.getMax());

    List<ServiceIncomeSO> list = ((List<Object[]>) criteria.list()).stream() //
        .map(values -> JavaLangUtils.setProperties(new ServiceIncomeSO(), fields, values)) //
        .collect(Collectors.toList()); //

    // TODO How to get count without retrieving all the data?
    int totalResults = 0;

    return new SearchResult<>(list, totalResults, pager.getMax(), pager.getOffset());
}

private void addPeriodFrom(Criteria criteria, String yearColumn, String periodColumn, int yearFrom, int periodFrom) {
    criteria.add(disjunction() // OR
        .add(and(eq(yearColumn, yearFrom), ge(periodColumn, periodFrom))) // year == yearFrom && period >= periodFrom
        .add(gt(yearColumn, yearFrom))); // year > yearFrom
}

private void addPeriodTo(Criteria criteria, String yearColumn, String periodColumn, int yearFrom, int periodFrom) {
    criteria.add(disjunction() // OR
        .add(and(eq(yearColumn, yearFrom), le(periodColumn, periodFrom))) // year == yearFrom && period <= periodFrom
        .add(lt(yearColumn, yearFrom))); // year < yearFrom
}

我可以在纯SQL中编写相同的查询，但不能在Hibernate Criteria API中执行 .

选择查询：

SELECT 
    PARTNER_KEY, 
    SERVICE_ID, 
    INCOME_YEAR, 
    INCOME_MONTH, 
    SUM(SUBSCRIBER_COUNT), 
    SUM(INCOME) 
FROM 
    PP_SERVICE_INCOME 
WHERE 
    PARTNER_KEY = 'PART32143' 
GROUP BY 
    PARTNER_KEY, 
    SERVICE_ID, 
    INCOME_YEAR, 
    INCOME_MONTH;

选择结果：

PARTNER_KEY SERVICE_ID INCOME_YEAR INCOME_MONTH SUM(SUBSCRIBER_COUNT) SUM(INCOME) 
----------- ---------- ----------- ------------ --------------------- ----------- 
PART32143   1          2016        1            1234                  175000      
PART32143   1          2017        1            1234                  175390      
PART32143   1          2016        6            1234                  151100      
PART32143   1          2017        0            1234                  157800      
PART32143   1          2016        7            1234                  175220      
PART32143   1          2016        2            1234                  143000      
PART32143   1          2016        0            1234                  150000      
PART32143   1          2017        2            1234                  143012      
PART32143   1          2016        8            1234                  143330

计数查询：

SELECT 
    COUNT(*) 
FROM (
    SELECT 
        PARTNER_KEY, 
        SERVICE_ID, 
        INCOME_YEAR, 
        INCOME_MONTH, 
        SUM(SUBSCRIBER_COUNT), 
        SUM(INCOME) 
    FROM 
        PP_SERVICE_INCOME 
    WHERE 
        PARTNER_KEY = 'PART32143' 
    GROUP BY 
        PARTNER_KEY, 
        SERVICE_ID, 
        INCOME_YEAR, 
        INCOME_MONTH
    );

计数结果：9