Spring调用存储过程分页时TOP语法错误问题求助

问题原因 #

Spring Data JPA在处理带Pageable的原生SQL查询时，会自动尝试添加分页语法（比如SQL Server的TOP），但存储过程的exec语句结构特殊，Hibernate的SQL解析器错误地将TOP(?)插入到了存储过程名称中间，最终导致SQL语法错误。JPA的自动分页机制并不适合直接用于原生存储过程查询，需要手动调整实现方式。

解决方案 #

方案1：手动实现分页（适合小数据量场景） #

如果数据量不大，可以先查询全量结果，再在业务层手动切割分页数据，绕过JPA的自动分页改造。

1. 修改Repo接口，去掉Pageable，查询全量数据：

public interface ITestProcedureData extends JpaRepository<TestProcedureData, String> {
    @Query(nativeQuery=true,value="exec sp_TEST_PROCEDURE 'param1','param2'")
    List<TestProcedureData> getAllProcedureData();
}

2. 在Service层手动处理分页：

@Service
public class TestProcedureServices {

    ITestProcedureData testProcedureData;

    public TestProcedureServices(ITestProcedureData testProcedureData) { 
        this.testProcedureData = testProcedureData;
    }
    
    public Page<TestProcedureData> getProcedureData(Pageable pageable){
        List<TestProcedureData> allData = testProcedureData.getAllProcedureData();
        // 计算分页起始和结束下标
        int start = (int) pageable.getOffset();
        int end = Math.min((start + pageable.getPageSize()), allData.size());
        List<TestProcedureData> pageData = allData.subList(start, end);
        // 封装为Page对象返回
        return new PageImpl<>(pageData, pageable, allData.size());
    }
}

方案2：改造存储过程支持分页（推荐，性能友好） #

针对大数据量场景，推荐直接修改存储过程，添加分页参数，通过ROW_NUMBER()实现数据库端分页，避免全量查询的性能损耗。

1. 修改存储过程，新增分页参数并实现分页逻辑：

PROCEDURE [dbo].[sp_TEST_PROCEDURE]
    @param1    VARCHAR(20),
    @param2      VARCHAR(20),
    @pageSize INT,
    @pageIndex INT
AS
BEGIN 
    -- 分页数据查询
    WITH PaginatedData AS (
        SELECT 
            AQA.[A_COLUMN],
            AQA.[B_COLUMN],
            Q.NAME AS C_COLUMN,
            A.NAME AS D_COLUMN,
            ROW_NUMBER() OVER (ORDER BY AQA.TIME) AS RowNum
        FROM [rpt_ACTIVITY] AQA, TENANT A, QUEUE Q
        WHERE Q.QID = AQA.SERVED_QID 
          AND A.AID = AQA.AID 
          AND (@param1 = '0' OR AQA.SERVED_QID IN (SELECT * FROM GET_IDS(@param1)) )
          AND (@param2 = '-1' OR (@param2 = '-2' AND A.DELETION_ORDER != 0) OR(@param2 = '0' AND A.DELETION_ORDER = 0) OR A.AID IN (select * from dbo.GET_IDS(@param2)))
    )
    SELECT A_COLUMN, B_COLUMN, C_COLUMN, D_COLUMN
    FROM PaginatedData
    WHERE RowNum BETWEEN (@pageIndex * @pageSize) + 1 AND (@pageIndex + 1) * @pageSize
    ORDER BY RowNum;

    -- 查询总条数，用于构建Page对象
    SELECT COUNT(*) AS TotalCount
    FROM [rpt_ACTIVITY] AQA, TENANT A, QUEUE Q
    WHERE Q.QID = AQA.SERVED_QID 
      AND A.AID = AQA.AID 
      AND (@param1 = '0' OR AQA.SERVED_QID IN (SELECT * FROM GET_IDS(@param1)) )
      AND (@param2 = '-1' OR (@param2 = '-2' AND A.DELETION_ORDER != 0) OR(@param2 = '0' AND A.DELETION_ORDER = 0) OR A.AID IN (select * from dbo.GET_IDS(@param2)));
END

2. 修改Repo接口，新增分页查询和总条数查询方法：

public interface ITestProcedureData extends JpaRepository<TestProcedureData, String> {
    @Query(nativeQuery=true,value="exec sp_TEST_PROCEDURE :param1,:param2,:pageSize,:pageIndex")
    List<TestProcedureData> getProcedureDataPage(
        @Param("param1") String param1, 
        @Param("param2") String param2, 
        @Param("pageSize") int pageSize, 
        @Param("pageIndex") int pageIndex
    );

    @Query(nativeQuery=true,value="SELECT COUNT(*) FROM [rpt_ACTIVITY] AQA, TENANT A, QUEUE Q WHERE Q.QID = AQA.SERVED_QID AND A.AID = AQA.AID AND (:param1 = '0' OR AQA.SERVED_QID IN (SELECT * FROM GET_IDS(:param1)) ) AND (:param2 = '-1' OR (:param2 = '-2' AND A.DELETION_ORDER != 0) OR(:param2 = '0' AND A.DELETION_ORDER = 0) OR A.AID IN (select * from dbo.GET_IDS(:param2)))")
    long getTotalCount(@Param("param1") String param1, @Param("param2") String param2);
}

3. 调整Service层逻辑：

@Service
public class TestProcedureServices {

    ITestProcedureData testProcedureData;

    public TestProcedureServices(ITestProcedureData testProcedureData) { 
        this.testProcedureData = testProcedureData;
    }
    
    public Page<TestProcedureData> getProcedureData(Pageable pageable){
        String param1 = "param1"; // 替换为实际业务参数值
        String param2 = "param2";
        List<TestProcedureData> pageData = testProcedureData.getProcedureDataPage(param1, param2, pageable.getPageSize(), pageable.getPageNumber());
        long total = testProcedureData.getTotalCount(param1, param2);
        return new PageImpl<>(pageData, pageable, total);
    }
}

方案3：使用@NamedStoredProcedureQuery注解调用存储过程 #

通过实体类上的命名存储过程注解，直接调用存储过程，再手动处理分页。

1. 在Entity类上添加命名存储过程注解：

@Entity
@Data
@NamedStoredProcedureQuery(
    name = "sp_TEST_PROCEDURE",
    procedureName = "sp_TEST_PROCEDURE",
    parameters = {
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "param1", type = String.class),
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "param2", type = String.class)
    },
    resultClasses = TestProcedureData.class
)
public class TestProcedureData {
        
        @Id 
        @Column(name="A_COLUMN")
        String aColumn;
        
        @Column(name="B_COLUMN")
        String bColumn;
        
        @Column(name="C_COLUMN")
        String cColumn;
        
        @Column(name="D_COLUMN")
        String dColumn;
}

2. 在Service层通过EntityManager调用并分页：

@Service
public class TestProcedureServices {

    @PersistenceContext
    private EntityManager entityManager;

    public Page<TestProcedureData> getProcedureData(Pageable pageable){
        StoredProcedureQuery query = entityManager.createNamedStoredProcedureQuery("sp_TEST_PROCEDURE");
        query.setParameter("param1", "param1"); // 替换为实际参数
        query.setParameter("param2", "param2");
        List<TestProcedureData> allData = query.getResultList();
        
        int start = (int) pageable.getOffset();
        int end = Math.min((start + pageable.getPageSize()), allData.size());
        List<TestProcedureData> pageData = allData.subList(start, end);
        return new PageImpl<>(pageData, pageable, allData.size());
    }
}

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