如何在JPA Criteria API中实现ROW_NUMBER行索引查询功能

前置修改 #

首先调整你的MyTupleContainer实体类：

• 新增Long类型的listIndex属性
• 调整全参数构造方法，参数顺序和后续multiselect的字段顺序保持一致

完整修改后代码 #

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
Metamodel m = entityManager.getMetamodel();
EntityType<Offer> offerMeta = m.entity(Offer.class);

CriteriaQuery<MyTupleContainer> criteriaQuery = criteriaBuilder.createQuery(MyTupleContainer.class);
Root<Offer> offer = criteriaQuery.from(Offer.class);
// 改为左连接，和原生SQL的LEFT JOIN逻辑保持一致
Join<Offer, Item> item = offer.leftJoin(offerMeta.getSingularAttribute("item", Item.class));

// 构造行索引窗口函数，以Hibernate作为JPA实现为例（最常用的JPA实现）
HibernateCriteriaBuilder hibernateCb = criteriaBuilder.unwrap(HibernateCriteriaBuilder.class);
Expression<Long> listIndex = hibernateCb.rowNumber()
        .over()
        .partitionBy(criteriaBuilder.literal(1)) // PARTITION BY 1 表示全表不分区
        .orderBy(criteriaBuilder.asc(offer.get("offerValidUntil"))); // 默认排序规则

criteriaQuery.multiselect(
        offer.get("id"),
        item.get("name"),
        offer.get("offerValidUntil"),
        item.get("regularPrice"),
        offer.get("discountInPercent"),
        listIndex // 新增行索引字段
);

兼容其他JPA实现的通用写法 #

如果不使用Hibernate，可以直接用function方法调用原生SQL函数，注意此处排序字段要写数据库实际列名：

Expression<Long> listIndex = criteriaBuilder.function(
    "ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY offer_valid_until ASC)",
    Long.class
);

注意事项 #

• 窗口函数是SQL标准功能，Oracle、MySQL 8.0+、PostgreSQL等主流数据库均支持，低版本数据库无法使用
• SQL执行顺序中窗口函数运行在JOIN、WHERE条件之后，ORDER BY、分页逻辑之前，完全符合你的需求：先按默认规则生成全量数据的行索引，再执行后续的自定义过滤、排序、分页逻辑，不会打乱索引和原始记录的对应关系
• 若你使用静态元模型，可把offer.get("offerValidUntil")替换为offer.get(Offer_.offerValidUntil)，避免硬编码字段名错误

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