Spring Data结合MSSQL复杂原生查询分页参数位置错误问题

问题背景 #

在使用MSSQL原生查询时，若SQL包含DECLARE变量定义语句，Spring Data JPA的Pageable参数会被错误插入到DECLARE语句之后，而非目标查询的末尾，导致SQL语法错误。

原查询SQL #

DECLARE @IdParam NVARCHAR(50) = (
SELECT TOP 1 PartyID
FROM PartyInfo
WHERE PartyName = ?1
ORDER BY IsPrimary DESC);

SELECT *
FROM TutorialInfo
WHERE UserId = @IdParam 

Repository方法定义 #

@Query(nativeQuery = true)
List<UserConvictedCases> getUserConvictedCasesByQid(String qid, Pageable pageable);

报错的生成SQL #

DECLARE @IdParam NVARCHAR(50) = (
SELECT TOP 1 PartyID
FROM PartyInfo
WHERE PartyName = ?1
ORDER BY IsPrimary DESC) order by @@version offset 0 rows fetch first ? rows only;

可行解决方案 #

1. 将主查询包装为子查询 #

修改原生SQL，把需要分页的SELECT语句包装成子查询，引导Spring Data JPA识别正确的分页位置：

DECLARE @IdParam NVARCHAR(50) = (
SELECT TOP 1 PartyID
FROM PartyInfo
WHERE PartyName = ?1
ORDER BY IsPrimary DESC);

SELECT * FROM (
    SELECT *
    FROM TutorialInfo
    WHERE UserId = @IdParam 
) AS paginated_query

Spring Data会自动将分页相关的ORDER BY、OFFSET和FETCH语句追加到子查询末尾，匹配正确的分页逻辑。

2. 使用CTE（公共表表达式）包装主查询 #

通过CTE封装主查询，同样能让Spring Data将分页参数追加到正确位置：

DECLARE @IdParam NVARCHAR(50) = (
SELECT TOP 1 PartyID
FROM PartyInfo
WHERE PartyName = ?1
ORDER BY IsPrimary DESC);

WITH paginated_cte AS (
    SELECT *
    FROM TutorialInfo
    WHERE UserId = @IdParam 
)
SELECT * FROM paginated_cte

3. 手动指定分页逻辑（不依赖Pageable自动处理） #

若不想修改查询结构，可放弃Pageable参数，手动传入分页偏移量和每页大小，在SQL中直接写分页语句：

DECLARE @IdParam NVARCHAR(50) = (
SELECT TOP 1 PartyID
FROM PartyInfo
WHERE PartyName = ?1
ORDER BY IsPrimary DESC);

SELECT *
FROM TutorialInfo
WHERE UserId = @IdParam 
ORDER BY [Your_Sort_Column] OFFSET ?2 ROWS FETCH NEXT ?3 ROWS ONLY

对应的Repository方法修改为：

@Query(nativeQuery = true, value = "上面的SQL")
List<UserConvictedCases> getUserConvictedCasesByQid(String qid, int offset, int size);

注意：这种方式需要自行计算offset（公式：offset = pageNumber * pageSize），灵活性不如前两种方法。

问题原因说明 #

Spring Data JPA处理原生查询分页时，默认会将分页相关语句追加到整个SQL字符串的末尾。当原SQL包含多个独立语句（如DECLARE和SELECT）时，它会错误地将分页语句追加到第一个语句后，引发语法错误。通过子查询或CTE包装主查询后，SQL末尾部分变为需要分页的目标查询，Spring Data就能正确插入分页参数。

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