自定义JPQL中如何结合Pageable实现排序功能？

问题根源是：Spring Data JPA在处理带有Pageable参数的@Query时，若JPQL中已定义ORDER BY，当Pageable携带Sort参数时，会自动拼接两个排序规则，导致SQL出现重复的ORDER BY关键字，引发语法错误。

以下是几种可行的解决方案：

方法1：使用SpEL动态添加固定排序（推荐） #

通过Spring表达式语言（SpEL）判断Pageable的Sort是否为空，仅当无外部排序参数时，才添加自定义的CASE排序规则，同时指定独立的count查询避免排序逻辑影响计数性能：

@Query(value = "SELECT b FROM Booking b WHERE "
        + "(:officeId is null OR b.officeId = :officeId) AND "
        + "(:status is null OR b.status = :status) AND "
        + "(:type is null OR b.officeType = :type) "
        + "#{#pageable.sort.isEmpty() ? 'ORDER BY CASE WHEN b.guestId in (SELECT userId FROM MeetingRoomQuotaRequest) THEN 0 ELSE 1 END' : ''}",
        countQuery = "SELECT COUNT(b) FROM Booking b WHERE "
                + "(:officeId is null OR b.officeId = :officeId) AND "
                + "(:status is null OR b.status = :status) AND "
                + "(:type is null OR b.officeType = :type)")
Page<Booking> findAndOrderByQuotaRequestExists(
        @Param("officeId") String officeId,
        @Param("status") Status status,
        @Param("type") OfficeType type,
        Pageable pageable);

方法2：移除JPQL中的ORDER BY，通过Pageable传递排序规则 #

将自定义排序逻辑整合到Pageable中，JPQL仅保留筛选条件，实现默认排序与外部传入排序的灵活组合：

第一步：修改Repository方法 #

@Query("SELECT b FROM Booking b WHERE "
        + "(:officeId is null OR b.officeId = :officeId) AND "
        + "(:status is null OR b.status = :status) AND "
        + "(:type is null OR b.officeType = :type)")
Page<Booking> findAndOrderByQuotaRequestExists(
        @Param("officeId") String officeId,
        @Param("status") Status status,
        @Param("type") OfficeType type,
        Pageable pageable);

第二步：调用时构建带默认排序的Pageable #

当没有传入外部排序参数时，构造包含自定义CASE排序的Pageable；若有外部排序参数，直接传入或叠加即可：

// 构建默认排序规则：优先显示存在MeetingRoomQuotaRequest的Booking
Sort defaultSort = Sort.by(
        new Sort.Order(Sort.Direction.ASC, 
            "CASE WHEN guestId in (SELECT userId FROM MeetingRoomQuotaRequest) THEN 0 ELSE 1 END")
);
// 如需叠加其他排序，可使用and()方法扩展
// defaultSort = defaultSort.and(Sort.by("createTime").descending());

Pageable pageable = PageRequest.of(pageNum, pageSize, defaultSort);
// 调用查询方法
bookingRepository.findAndOrderByQuotaRequestExists(officeId, status, type, pageable);

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