Hibernate 6中JPQL的ORDER BY子句使用Collation的问题

问题背景 #

Hibernate 6支持Collation功能，官方文档提及了collate函数，但未提供完整的JPQL查询示例。需在JPQL的ORDER BY子句中使用已通过SQL创建的自定义排序规则display_id_collation，尝试三种写法均报错：

• 使用AS关键字的写法触发JPQL语法错误
• 直接传入排序规则名称触发语义异常
• 传入带引号的规则名称提示需要COLLATION类型参数，但无法获取该类型对象

解决方法 #

方法1：正确使用JPQL的collate函数 #

确保数据库中display_id_collation已正确创建，且Hibernate方言适配目标数据库（如MySQL、PostgreSQL等），使用如下格式的JPQL查询：

SELECT e FROM YourEntity e ORDER BY collate(e.displayId, 'display_id_collation') ASC

注意：部分数据库对排序规则名称的大小写有要求，需与数据库中创建的名称完全一致。

方法2：通过@Formula预定义排序字段 #

在实体类中添加带排序规则的计算字段，后续直接在JPQL中对该字段排序：

import jakarta.persistence.Formula;

@Entity
public class YourEntity {
    // 其他字段...
    private String displayId;

    @Formula("display_id COLLATE display_id_collation")
    private String displayIdWithCollation;

    // 对应的getter、setter方法
}

对应的JPQL查询：

SELECT e FROM YourEntity e ORDER BY e.displayIdWithCollation ASC

方法3：使用原生SQL查询 #

如果JPQL的collate函数仍无法适配，可直接使用原生SQL：

SELECT * FROM your_entity ORDER BY display_id COLLATE display_id_collation ASC

通过Hibernate的createNativeQuery方法执行该查询。

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