You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

如何在现有Ag-Grid后端代码中实现服务端分组与透视功能

avatar
开发者阿泽
2026-7-9
服务端实现Ag-Grid分组与透视功能方案

问题背景

已基于服务端完成Ag-Grid的分页、搜索和排序功能，现需在现有代码基础上实现服务端分组与透视功能，以下是具体修改方案及优化建议。

现有代码 

@Service
public class CompanyService {

    @Autowired
    EntityManager entityManager;

    public Page<CompanyResponse> getCompanies(Pageable pageable, RequestWithFilterAndSort requestWithFilterAndSort){
        CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
        CriteriaQuery<CompanyResponse> companyResponseCriteriaQuery=criteriaBuilder.createQuery(CompanyResponse.class);
        Root<Company> companyRoot=companyResponseCriteriaQuery.from(Company.class);

        companyResponseCriteriaQuery.multiselect(companyRoot.get("id"),
                companyRoot.get("companyName"),
                companyRoot.get("employeeName"),
                companyRoot.get("description"),
                companyRoot.get("leave")
                );

        if(requestWithFilterAndSort.getColId()!=null && requestWithFilterAndSort.getSort().equals("desc")){
            companyResponseCriteriaQuery.orderBy(criteriaBuilder.desc(companyRoot.get(requestWithFilterAndSort.getColId())));
        }
        else if(requestWithFilterAndSort.getColId()!=null && requestWithFilterAndSort.getSort().equals("asc")){
            companyResponseCriteriaQuery.orderBy(criteriaBuilder.asc(companyRoot.get(requestWithFilterAndSort.getColId())));
        }

        List<Predicate> predicateList=new ArrayList<>();
        predicateList.addAll(getFilter(criteriaBuilder,predicateList,companyRoot,requestWithFilterAndSort));
        Predicate predicateArray[]=new Predicate[predicateList.size()];

        companyResponseCriteriaQuery.where(criteriaBuilder.and(predicateList.toArray(predicateArray)));

        List<CompanyResponse> companyResponseList=entityManager.createQuery(companyResponseCriteriaQuery).getResultList();
        List<CompanyResponse> companyResponses=entityManager.createQuery(companyResponseCriteriaQuery).
                setFirstResult((int) pageable.getOffset()).
                setMaxResults(pageable.getPageSize()).
                getResultList();
        return new PageImpl<>(companyResponses,pageable,companyResponseList.size());
    }

    private List<Predicate> getFilter(CriteriaBuilder criteriaBuilder,List<Predicate> predicateList,Root<Company> companyRoot,RequestWithFilterAndSort requestWithFilterAndSort){
        Optional.ofNullable(requestWithFilterAndSort.getFilterModel()).ifPresent(filterModelMap->{
            for(Map.Entry<String, FilterModel> filterModelEntry:filterModelMap.entrySet()){

                String colName=filterModelEntry.getKey();
                FilterModel filterModel=filterModelEntry.getValue();
                System.out.println("colName in filter : "+colName);
                /*if(filterModel.getType().equalsIgnoreCase("NUMBER") && filterModel.getFilterType().equalsIgnoreCase("GREATETHEN")){
                    predicateList.add(criteriaBuilder.ge(companyRoot.get(colName),Long.parseLong(filterModel.getFilter())));
                }
                else if(filterModel.getType().equalsIgnoreCase("NUMBER") && filterModel.getFilterType().equalsIgnoreCase("LESSTHEN")){
                    predicateList.add(criteriaBuilder.lessThan(companyRoot.get(colName),Long.parseLong(filterModel.getFilter())));

                }*/
                if(filterModel.getType().equalsIgnoreCase("equals")){
                    predicateList.add(criteriaBuilder.equal(companyRoot.get(colName),filterModel.getFilter()));
                }
                else if(filterModel.getType().equalsIgnoreCase("notEqual")){
                    predicateList.add(criteriaBuilder.notEqual(companyRoot.get(colName),filterModel.getFilter()));

                }
                else if(filterModel.getType().equalsIgnoreCase("contains")){
                    predicateList.add(criteriaBuilder.like(companyRoot.get(colName),filterModel.getFilter()));

                }
                else if(filterModel.getType().equalsIgnoreCase("notContains")){
                    predicateList.add(criteriaBuilder.notLike(companyRoot.get(colName),filterModel.getFilter()));

                }
                else if(filterModel.getType().equalsIgnoreCase("startsWith")){
                    predicateList.add(criteriaBuilder.like(companyRoot.get(colName),"%"+filterModel.getFilter()));

                }
                else if(filterModel.getType().equalsIgnoreCase("endsWith")){
                    predicateList.add(criteriaBuilder.like(companyRoot.get(colName),filterModel.getFilter()+"%"));
                }
                else if(filterModel.getType().equalsIgnoreCase("blank")){
                    predicateList.add(criteriaBuilder.isNull(companyRoot.get(colName)));
                }
                else if(filterModel.getType().equalsIgnoreCase("notBlank")){
                    predicateList.add(criteriaBuilder.isNotNull(companyRoot.get(colName)));
                }
            }
        });
        return  predicateList;
    }
}

一、服务端分组与透视实现步骤

1. 扩展请求参数类

首先在RequestWithFilterAndSort中添加分组、透视相关参数，接收Ag-Grid前端传递的配置：

public class RequestWithFilterAndSort {
    // 原有字段...
    private List<String> groupColumns; // 分组列名称列表
    private List<String> pivotColumns; // 透视列名称列表
    private List<ValueColumn> valueColumns; // 聚合列（含聚合类型）

    // Getter & Setter

    // 内部类：定义聚合列的列名和聚合类型
    public static class ValueColumn {
        private String field;
        private String aggFunc; // 可选值：sum、count、avg、max、min

        // Getter & Setter
    }
}

2. 修改查询构建逻辑

分组和透视本质是分组查询+聚合计算，透视是多维度分组，需动态构建分组条件和聚合函数：

核心修改点：

  • 新增分组结果DTO，承载分组键和聚合值
  • 根据请求参数判断是否走分组/透视查询
  • 分组查询需调用groupBy()，并添加聚合函数
  • 单独构建count查询统计分组结果总数，避免全表扫描
// 分组结果DTO
public class GroupedCompanyResponse {
    private Map<String, Object> groupKeys; // 分组键（如{companyName: "XX公司"}）
    private Map<String, Object> aggValues; // 聚合值（如{leaveCount: 5, totalLeave: 10}）

    // Getter & Setter
}

// 修改getCompanies方法
public Page<?> getCompanies(Pageable pageable, RequestWithFilterAndSort requestWithFilterAndSort){
    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    Root<Company> companyRoot = criteriaBuilder.createQuery().from(Company.class);

    // 1. 处理过滤条件（复用原有逻辑）
    List<Predicate> predicateList = new ArrayList<>();
    predicateList.addAll(getFilter(criteriaBuilder, predicateList, companyRoot, requestWithFilterAndSort));
    Predicate[] predicateArray = predicateList.toArray(new Predicate[0]);

    // 2. 判断是否需要分组/透视
    boolean hasGroupOrPivot = requestWithFilterAndSort.getGroupColumns() != null || requestWithFilterAndSort.getPivotColumns() != null;
    if(hasGroupOrPivot){
        // 构建分组查询
        CriteriaQuery<Tuple> tupleQuery = criteriaBuilder.createTupleQuery();
        List<Selection<?>> selections = new ArrayList<>();
        List<Expression<?>> groupByExpressions = new ArrayList<>();

        // 添加分组列到查询和分组条件
        if(requestWithFilterAndSort.getGroupColumns() != null){
            for(String col : requestWithFilterAndSort.getGroupColumns()){
                Expression<?> expr = companyRoot.get(col);
                selections.add(expr.alias(col));
                groupByExpressions.add(expr);
            }
        }

        // 添加透视列到查询和分组条件（透视是多维度分组）
        if(requestWithFilterAndSort.getPivotColumns() != null){
            for(String col : requestWithFilterAndSort.getPivotColumns()){
                Expression<?> expr = companyRoot.get(col);
                selections.add(expr.alias(col));
                groupByExpressions.add(expr);
            }
        }

        // 添加聚合列
        if(requestWithFilterAndSort.getValueColumns() != null){
            for(RequestWithFilterAndSort.ValueColumn vc : requestWithFilterAndSort.getValueColumns()){
                Expression<?> aggExpr;
                switch(vc.getAggFunc().toLowerCase()){
                    case "sum":
                        aggExpr = criteriaBuilder.sum(companyRoot.get(vc.getField()));
                        break;
                    case "count":
                        aggExpr = criteriaBuilder.count(companyRoot.get(vc.getField()));
                        break;
                    case "avg":
                        aggExpr = criteriaBuilder.avg(companyRoot.get(vc.getField()));
                        break;
                    case "max":
                        aggExpr = criteriaBuilder.max(companyRoot.get(vc.getField()));
                        break;
                    case "min":
                        aggExpr = criteriaBuilder.min(companyRoot.get(vc.getField()));
                        break;
                    default:
                        throw new IllegalArgumentException("不支持的聚合类型：" + vc.getAggFunc());
                }
                selections.add(aggExpr.alias(vc.getField() + "_" + vc.getAggFunc()));
            }
        }

        // 组装查询
        tupleQuery.multiselect(selections)
                .where(criteriaBuilder.and(predicateArray))
                .groupBy(groupByExpressions);

        // 处理排序（基于分组键或聚合值）
        if(requestWithFilterAndSort.getColId() != null && requestWithFilterAndSort.getSort() != null){
            Order order = "desc".equalsIgnoreCase(requestWithFilterAndSort.getSort())
                    ? criteriaBuilder.desc(criteriaBuilder.tuple(requestWithFilterAndSort.getColId()))
                    : criteriaBuilder.asc(criteriaBuilder.tuple(requestWithFilterAndSort.getColId()));
            tupleQuery.orderBy(order);
        }

        // 分页查询分组结果
        List<Tuple> tupleResults = entityManager.createQuery(tupleQuery)
                .setFirstResult((int) pageable.getOffset())
                .setMaxResults(pageable.getPageSize())
                .getResultList();

        // 转换为GroupedCompanyResponse
        List<GroupedCompanyResponse> groupedResponses = tupleResults.stream().map(tuple -> {
            GroupedCompanyResponse response = new GroupedCompanyResponse();
            Map<String, Object> groupKeys = new HashMap<>();
            Map<String, Object> aggValues = new HashMap<>();

            tuple.getElements().forEach(elem -> {
                String alias = elem.getAlias();
                Object value = tuple.get(elem);
                // 区分分组键和聚合值（聚合值后缀带_aggFunc）
                if(alias.contains("_")){
                    aggValues.put(alias, value);
                } else {
                    groupKeys.put(alias, value);
                }
            });
            response.setGroupKeys(groupKeys);
            response.setAggValues(aggValues);
            return response;
        }).collect(Collectors.toList());

        // 查询分组结果总数
        CriteriaQuery<Long> countQuery = criteriaBuilder.createQuery(Long.class);
        countQuery.select(criteriaBuilder.countDistinct(groupByExpressions.get(0)))
                .from(companyRoot)
                .where(criteriaBuilder.and(predicateArray))
                .groupBy(groupByExpressions);
        Long totalCount = (long) entityManager.createQuery(countQuery).getResultList().size();

        return new PageImpl<>(groupedResponses, pageable, totalCount);
    } else {
        // 原有普通查询逻辑（已优化总数查询）
        CriteriaQuery<CompanyResponse> companyResponseCriteriaQuery = criteriaBuilder.createQuery(CompanyResponse.class);
        companyResponseCriteriaQuery.multiselect(companyRoot.get("id"),
                companyRoot.get("companyName"),
                companyRoot.get("employeeName"),
                companyRoot.get("description"),
                companyRoot.get("leave")
        );

        // 处理排序
        if(requestWithFilterAndSort.getColId() != null && requestWithFilterAndSort.getSort().equals("desc")){
            companyResponseCriteriaQuery.orderBy(criteriaBuilder.desc(companyRoot.get(requestWithFilterAndSort.getColId())));
        } else if(requestWithFilterAndSort.getColId() != null && requestWithFilterAndSort.getSort().equals("asc")){
            companyResponseCriteriaQuery.orderBy(criteriaBuilder.asc(companyRoot.get(requestWithFilterAndSort.getColId())));
        }

        companyResponseCriteriaQuery.where(criteriaBuilder.and(predicateArray));

        // 单独查询总数，避免全表扫描
        CriteriaQuery<Long> countQuery = criteriaBuilder.createQuery(Long.class);
        countQuery.select(criteriaBuilder.count(companyRoot))
                .from(companyRoot)
                .where(criteriaBuilder.and(predicateArray));
        Long totalCount = entityManager.createQuery(countQuery).getSingleResult();

        List<CompanyResponse> companyResponses = entityManager.createQuery(companyResponseCriteriaQuery)
                .setFirstResult((int) pageable.getOffset())
                .setMaxResults(pageable.getPageSize())
                .getResultList();

        return new PageImpl<>(companyResponses, pageable, totalCount);
    }
}

3. 前端适配

确保Ag-Grid开启服务端分组/透视模式：

gridOptions = {
    rowModelType: 'serverSide',
    serverSideGrouping: true,
    serverSidePivot: true,
    // 其他配置...
};

二、代码优化建议

  1. 替换System.out为日志框架
    用SLF4J+Logback替代System.out.println，便于日志管理：

    private static final Logger log = LoggerFactory.getLogger(CompanyService.class);
// 替换为log.info("colName in filter : {}", colName);

  2. 使用Specification简化Predicate构建
    引入Spring Data JPA的Specification，替代手动拼接Predicate，代码更简洁：

    public Specification<Company> buildSpecification(RequestWithFilterAndSort request) {
    return (root, query, cb) -> {
        List<Predicate> predicates = new ArrayList<>();
        // 过滤逻辑迁移到此处
        return cb.and(predicates.toArray(new Predicate[0]));
    };
}

  3. 参数合法性校验

    • 校验请求中的列名是否存在于Company实体字段中
    • 校验聚合类型是否为支持的类型（sum/count/avg等），避免非法参数报错

  4. 拆分复杂方法
    将分组查询、普通查询拆分为独立方法，避免单个方法过于臃肿，提升可读性。

  5. 开启查询缓存
    对重复执行的查询添加缓存提示，提升性能：

    entityManager.createQuery(queryString).setHint("org.hibernate.cacheable", true);

  6. 处理空值边界

    • 分组列为空时返回全量聚合结果
    • 聚合计算时忽略null值（如count使用countDistinct或count(case when ...)）

内容的提问来源于stack exchange，提问作者Manoj Tiwari

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.07.09 23:47:03