Spring Data JPA迁移PostgreSQL时LIMIT参数报错<expression> expected, got ':'

问题描述 #

我有如下Oracle SQL查询语句：

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

尝试改写为适配PostgreSQL的Spring Data JPA查询：

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

但LIMIT :count处报错：<expression> expected, got ':'，请问该如何解决？

问题根源 #

1. JPQL（JPA查询语言）不支持PostgreSQL原生的LIMIT语法，这是数据库专属关键字，不属于JPQL标准规范。
2. 语法错误：LIMIT是结果集限制条件，不属于WHERE子句的一部分，不能跟在AND后面，需放在查询语句末尾。

解决方法 #

方法1：使用JPQL标准的FETCH FIRST语法（推荐，跨数据库兼容）

JPQL支持FETCH FIRST n ROWS ONLY的标准写法，可替代Oracle的ROWNUM和PostgreSQL的LIMIT，同时注意避免使用JPQL保留关键字user作为别名：

@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);

方法2：使用Spring Data JPA命名查询特性

无需手动编写@Query，通过方法名自动生成查询逻辑，调用时传入分页参数即可限制结果数量：

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

调用示例：findTopByStatusAndDescriptionIsNotNullAndUpdatedGoodsDateBetween(status, start, end, PageRequest.of(0, count))

方法3：使用原生SQL查询（仅适用于固定PostgreSQL场景）

开启原生SQL模式，修正LIMIT的位置（去掉前置AND）：

@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