含ROWNUM的Oracle SQL迁移至PostgreSQL Spring Data JPA求助

原Oracle查询逻辑 #

原Oracle SQL通过ROWNUM <=13限制返回前13条符合条件的数据：

SELECT user FROM global_users user WHERE user.status = 'ACTIVE'
AND user.description IS NOT NULL AND user.updatedGoodsDate BETWEEN '2024-03-10 20:09:53' AND '2024-03-10 20:09:53'
AND ROWNUM <= 13

解决方案 #

针对Spring Data JPA中无法直接在JPQL内使用PostgreSQL LIMIT的问题，提供三种可行方案：

1. 使用JPQL标准分页语法（推荐）

JPA 2.1及以上版本支持FETCH FIRST :count ROWS ONLY的标准跨库语法，兼容PostgreSQL，直接修改@Query即可：

@Query("SELECT u FROM global_users u WHERE u.status = :status " +
       "AND u.description IS NOT NULL AND u.updatedGoodsDate BETWEEN :startDate AND :endDate " +
       "FETCH FIRST :count ROWS ONLY")
List<Users> findTopUsers(@Param("status") TransactionStatus status,
                         @Param("startDate") OffsetDateTime start,
                         @Param("endDate") OffsetDateTime end,
                         @Param("count") long count);

注意：将别名user改为u，避免与SQL关键字冲突。

2. 利用Spring Data JPA命名查询+Pageable

无需自定义@Query，直接通过方法名规则定义查询，结合Pageable实现分页取数：

List<Users> findTopByStatusAndDescriptionIsNotNullAndUpdatedGoodsDateBetween(
    TransactionStatus status,
    OffsetDateTime startDate,
    OffsetDateTime endDate,
    Pageable pageable
);

调用时传入PageRequest.of(0, count)即可获取前count条数据，示例：

List<Users> result = dataRepository.findTopByStatusAndDescriptionIsNotNullAndUpdatedGoodsDateBetween(
    TransactionStatus.ACTIVE,
    startDateTime,
    endDateTime,
    PageRequest.of(0, 13)
);

3. 使用原生PostgreSQL查询

指定nativeQuery=true，直接编写PostgreSQL原生SQL并使用LIMIT：

@Query(value = "SELECT * FROM global_users u WHERE u.status = :status " +
               "AND u.description IS NOT NULL AND u.updatedGoodsDate BETWEEN :startDate AND :endDate " +
               "LIMIT :count", nativeQuery = true)
List<Users> findTopUsers(@Param("status") TransactionStatus status,
                         @Param("startDate") OffsetDateTime start,
                         @Param("endDate") OffsetDateTime end,
                         @Param("count") long count);

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