代码之家 › 专栏 › 技术社区 › Mankind1023

Spring-无法将Pageable设置为小于总结果大小

pageable jpa spring

Mankind1023 · 技术社区 · 4 年前

在我的repo中,在WITH中包含一个巨大的查询,以保持简单,因此应该在那里进行分页(我还重命名了引号中的一些字段,jpa无法找到它们,除非它们嵌套),如下所示:

String QRY_DATA = "WITH result AS (<ton of code>) SELECT * FROM result";

@Query(nativeQuery = true, value = QRY_DATA)
Page<IBusinessDataDto> getData(UUID userId, Pageable pageable);

...
Pageable pageable = PageRequest.of(page - 1, pageSize, Sort.Direction.ASC, "businessId");
Page<IBusinessDataDto> test = repository.getData(userId, pageable);
...

我在代码之外测试了查询,它工作正常并返回2个结果,如果我将“pageSize”设置为3,它在代码中工作正常,但如果我将其设置为2或1,则会出现错误:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "FROM"

我尝试打印flyway SQL,得到以下结果:

Hibernate: WITH result AS (...) SELECT * FROM result order by businessId asc limit ?
Hibernate: WITH result AS (...) SELECT * FROM result
2021-11-11 15:58:48.108  WARN 17088 --- [nio-8091-exec-7] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42601
2021-11-11 15:58:48.108 ERROR 17088 --- [nio-8091-exec-7] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: syntax error at or near "FROM"
  Position: 1006

1 回复 | 直到 4 年前

Andreas 4 年前

分页通过发出计数查询来工作,这在代码中是缺失的。

String QRY_DATA = "WITH result AS (<ton of code>) SELECT * FROM result";
String QRY_DATA_COUNT = "WITH result AS (<ton of code>) SELECT COUNT(*) FROM result";

@Query(nativeQuery = true, value = QRY_DATA, countQuery = QRY_DATA_COUNT)
Page<IBusinessDataDto> getData(UUID userId, Pageable pageable);

https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.at-query.native