Spring Boot中CriteriaQuery别名排序失效问题求助

方法一：直接使用已定义的Selection对象排序 #

你的代码错误在于试图通过root.get(别名)获取排序字段，但该别名是查询生成的计算列，并非实体的固有属性。正确做法是直接将你定义的expressionAlias作为排序表达式传入：

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Tuple> query = builder.createTupleQuery();
Root<Purchase> root = query.from(Purchase.class);    

final Path<BigInteger> pathInt = root.get(Purchase_.cashAmount);
final Path<Integer> pathDec = root.join(Purchase_.currency, JoinType.LEFT)
        .get(CurrencyEntity_.numberOfDecimals);

final Expression<BigDecimal> expression = builder.function("FN100_AMOUNT", BigDecimal.class,
        builder.abs(pathInt), pathDec)
        .as(BigDecimal.class);

Selection<BigDecimal> expressionAlias = expression.alias("SORTBY_AMOUNT");

// ... 添加where子句

// 选择列
final List<Selection<?>> selectList = ...

selectList.add(expressionAlias);
query.multiselect(selectList);
// 直接使用expressionAlias作为排序依据
query.orderBy(builder.desc(expressionAlias)); 

List<Tuple> resultList = em.createQuery(query).setMaxResults(10).getResultList();

如果此方法仍报错，可能是Hibernate 5.2版本对TupleQuery中引用Selection排序的支持问题，可尝试以下方法。

方法二：通过子查询封装计算列，外层引用别名排序 #

将包含计算列的查询作为子查询，外层查询直接引用子查询的别名进行排序，避免Hibernate重复生成存储过程调用：

CriteriaBuilder builder = em.getCriteriaBuilder();

// 构建子查询，包含所有需要的字段和计算列
CriteriaQuery<Tuple> subQuery = builder.createTupleQuery();
Root<Purchase> subRoot = subQuery.from(Purchase.class);

Path<BigInteger> pathInt = subRoot.get(Purchase_.cashAmount);
Path<Integer> pathDec = subRoot.join(Purchase_.currency, JoinType.LEFT)
        .get(CurrencyEntity_.numberOfDecimals);

Expression<BigDecimal> expression = builder.function("FN100_AMOUNT", BigDecimal.class,
        builder.abs(pathInt), pathDec)
        .as(BigDecimal.class);
Selection<BigDecimal> expressionAlias = expression.alias("SORTBY_AMOUNT");

// 组装子查询的选择列
List<Selection<?>> subSelectList = new ArrayList<>();
subSelectList.add(subRoot.get(Purchase_.purchaseId));
subSelectList.add(subRoot.get(Purchase_.user));
subSelectList.add(subRoot.get(Purchase_.cashAmount));
// 添加其他需要的字段...
subSelectList.add(expressionAlias);

// 添加where条件
subQuery.where(
        builder.equal(subRoot.get(Purchase_.user), yourUserParam),
        builder.equal(subRoot.get(Purchase_.purchaseDate), yourDateParam)
);
subQuery.multiselect(subSelectList);

// 构建外层查询，引用子查询的别名排序
CriteriaQuery<Tuple> query = builder.createTupleQuery();
Root<Tuple> root = query.from(subQuery);
query.select(root);
// 直接引用子查询中的别名
query.orderBy(builder.desc(root.get("SORTBY_AMOUNT")));

// 分页
List<Tuple> resultList = em.createQuery(query)
        .setMaxResults(10)
        .getResultList();

方法三：通过@Formula注解将计算列映射为实体属性 #

在Purchase实体类中添加一个用@Formula注解标记的属性，直接映射存储过程计算逻辑，之后即可像普通实体属性一样排序：

@Entity
@Table(name = "VG100_PURCHASES")
public class Purchase {
    // 其他实体属性...

    @Formula("FN100_AMOUNT(ABS(CASH_AMT), (SELECT CUR_NM_DECIMALS FROM VG205_CURRENCIES WHERE CURRENCY_ID = CURRENCY))")
    private BigDecimal sortByAmount;

    // getter方法
    public BigDecimal getSortByAmount() {
        return sortByAmount;
    }
}

然后在CriteriaQuery中直接使用该属性排序：

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Purchase> query = builder.createQuery(Purchase.class);
Root<Purchase> root = query.from(Purchase.class);

// 添加where条件
query.where(
        builder.equal(root.get(Purchase_.user), yourUserParam),
        builder.equal(root.get(Purchase_.purchaseDate), yourDateParam)
);

// 排序并分页
query.orderBy(builder.desc(root.get(Purchase_.sortByAmount)));
List<Purchase> resultList = em.createQuery(query)
        .setMaxResults(10)
        .getResultList();

此方法更简洁，且符合JPA规范，推荐优先尝试。

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