使用Criteria Builder实现金额求和与GROUP_CONCAT报错排查

问题场景 #

需展示分组ID对应的日期-总价求和键值对（单日仅一条数据显示原值，多条则求和），使用Criteria Builder的sum、concat和GROUP_CONCAT方法时触发SQL语法错误。

错误信息 #

org.hibernate.exception.GenericJDBCException: 执行SQL时发生JDBC异常 [select gs1_0.storage_location_name,concat('[',concat(GROUP_CONCAT(concat(concat('{"x": "',concat(g1_0.grnCreatedAt,'", "y": ')),concat(cast(sum(i1_0.totalAmount) as char),'}'))),']')),cast(sum(i1_0.totalAmount) as char) from erp_grn g1_0 join erp_grn_goods i1_0 on g1_0.grnId=i1_0.grnId join erp_storage_location gs1_0 on gs1_0.storage_location_id=g1_0.functional_area where g1_0.companyId=? and g1_0.branchId=? and g1_0.locationId=? and g1_0.grnCreatedAt between ? and ? and g1_0.grnType=? group by gs1_0.storage_location_code,1 order by 3 desc limit ?] [无效使用分组函数] [无额外信息]

原实现代码 #

Expression<String> grnCreatedAt = root.get("grnCreatedAt");
Expression<BigDecimal> itemPriceSum = criteriaBuilder.sum(grnItemJoin.get("totalAmount"));
Expression<String> itemPriceSumAsString = itemPriceSum.as(String.class);
// Construct the JSON structure
Expression<String> dataJson = criteriaBuilder.concat(criteriaBuilder.literal("{\"x\": \""),
         criteriaBuilder.concat(grnCreatedAt, criteriaBuilder.literal("\", \"y\": ")));
dataJson = criteriaBuilder.concat(dataJson,
               criteriaBuilder.concat(itemPriceSumAsString, criteriaBuilder.literal("}")));
Expression<String> groupConcat = criteriaBuilder.function("GROUP_CONCAT", String.class, dataJson);
Expression<String> data = criteriaBuilder.concat(criteriaBuilder.literal("["),
                    criteriaBuilder.concat(groupConcat, criteriaBuilder.literal("]")));


if (bumpRequest.getType().equalsIgnoreCase("grn")) {
               query.multiselect(grnStorageName.alias("grnStorageName"), data.alias("data"),
                    itemPriceSumAsString.alias("item_price"));
} else {
               query.multiselect(grnCostCenterDesc.alias("grnCostCenterDesc"), data.alias("data"),
                    itemPriceSumAsString.alias("item_price"));}

           query.where(criteriaBuilder.and(companyPredicate, branchPredicate, locationPredicate, datePredicate,
                    grnTypePredicate));
if (bumpRequest.getType.equalsIgnoreCase("grn")) {
               query.groupBy(groupByField, grnStorageName);
           } else {
               query.groupBy(groupByField, grnCostCenterDesc);
           }
           query.orderBy(criteriaBuilder.desc(itemPriceSumAsString));

错误原因 #

SQL语法不允许在分组聚合函数（GROUP_CONCAT）内部直接嵌套另一个聚合函数（sum）。原代码试图在GROUP_CONCAT中直接使用sum计算每日总价，违反了SQL的分组函数使用规则。

修正方案 #

先通过子查询完成按日期+分组字段的每日总价聚合，生成日期-总价的JSON字符串；再在主查询中对每个分组字段，GROUP_CONCAT子查询得到的所有每日数据，避免聚合函数嵌套。

修正后代码示例 #

// 1. 构建子查询：按日期和分组字段聚合每日总价
Subquery<Tuple> subQuery = query.subquery(Tuple.class);
Root<Grn> subRoot = subQuery.from(Grn.class);
Join<Grn, GrnGoods> subItemJoin = subRoot.join("grnGoods");
Expression<String> subDate = subRoot.get("grnCreatedAt");
Expression<BigDecimal> subDailySum = criteriaBuilder.sum(subItemJoin.get("totalAmount"));

// 生成单条日期-总价JSON
Expression<String> dailyJson = criteriaBuilder.concat(
    criteriaBuilder.literal("{\"x\": \""),
    criteriaBuilder.concat(subDate, criteriaBuilder.concat("\", \"y\": ", subDailySum.as(String.class), "}"))
);

subQuery.multiselect(
    subRoot.get(groupByField).alias("groupField"),
    dailyJson.alias("dailyData")
);
subQuery.where(criteriaBuilder.and(companyPredicate, branchPredicate, locationPredicate, datePredicate, grnTypePredicate));
subQuery.groupBy(subRoot.get(groupByField), subDate);

// 2. 构建主查询：按分组字段GROUP_CONCAT所有每日数据
Root<Grn> mainRoot = query.from(Grn.class);
Expression<String> groupField;
if (bumpRequest.getType().equalsIgnoreCase("grn")) {
    Join<Grn, StorageLocation> storageJoin = mainRoot.join("functionalArea");
    groupField = storageJoin.get("storageLocationName").alias("grnStorageName");
} else {
    groupField = mainRoot.get("grnCostCenterDesc").alias("grnCostCenterDesc");
}

// 聚合每日JSON为数组格式
Expression<String> groupConcatData = criteriaBuilder.function(
    "GROUP_CONCAT", String.class, subQuery.getSelection().get("dailyData")
);
Expression<String> data = criteriaBuilder.concat("[", criteriaBuilder.concat(groupConcatData, "]"));

// 计算分组总金额
Expression<BigDecimal> totalSum = criteriaBuilder.sum(mainRoot.join("grnGoods").get("totalAmount"));
Expression<String> totalSumStr = totalSum.as(String.class);

// 设置查询选择项与分组规则
if (bumpRequest.getType().equalsIgnoreCase("grn")) {
    query.multiselect(groupField, data.alias("data"), totalSumStr.alias("item_price"));
    query.groupBy(mainRoot.get(groupByField), groupField);
} else {
    query.multiselect(groupField, data.alias("data"), totalSumStr.alias("item_price"));
    query.groupBy(mainRoot.get(groupByField), groupField);
}

query.where(criteriaBuilder.and(companyPredicate, branchPredicate, locationPredicate, datePredicate, grnTypePredicate));
query.orderBy(criteriaBuilder.desc(totalSumStr));

说明 #

• 子查询先完成按日期的聚合，确保sum函数在正确的分组层级（日期+分组字段）中执行
• 主查询仅负责对分组字段聚合，将每日JSON字符串拼接为数组格式，避免了聚合函数嵌套的语法错误
• 最终结果满足需求：单日数据显示对应总价，多日数据按日期汇总后展示

内容的提问来源于stack exchange，提问作者Sayan Das