java - 当页面大小小于记录大小时，带有分页的 native 查询会引发内部服务器错误

Question

我在 Spring Boot 中使用带有分页的 native 查询，问题是当我运行页面大小小于记录数的查询时，我收到内部服务器错误，但它工作正常或页面大小更大比记录的数量。下面是我的 native 查询

  @Query(value = "SELECT * FROM (SELECT * from transactions t where t.book_id=?1 OR t.reflect_book_id=?2  AND t.is_active = true) a inner join " +
            "(select  id, display_name from customers) b on a.customer_id = b.id order by display_name",
            nativeQuery = true)
    Page<Transaction> findAllTransactionsByName(String bookId, String reflect_book_id, Pageable pageable);

下面是我遇到的错误

    ... 110 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM (SELECT *) from transactions t where t.book_id='bid-5778a8af-6bd0-4f3e-87ac' at line 1
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120) ~[mysql-connector-java-8.0.18.jar:8.0.18]

我打印了sql查询并手动运行它，它工作得很好。下面是打印在日志上的sql查询。

Hibernate: SELECT * FROM (SELECT * from transactions t where t.book_id=? OR t.reflect_book_id=?  AND t.is_active = true) a inner join (select  id, display_name from customers) b on a.customer_id = b.id order by display_name limit ?

希望您遇到这个问题，需要您在这个问题上的所有专业知识。谢谢。

Answer 1

• 您可以尝试指定 countQuery

@Query(value = "SELECT * FROM (SELECT * from transactions t where t.book_id=?1 OR t.reflect_book_id=?2  AND t.is_active = true) a inner join " +
            "(select  id, display_name from customers) b on a.customer_id = b.id order by display_name",
    countQuery="Your count query goes here",
            nativeQuery = true)
    Page<Transaction> findAllTransactionsByName(String bookId, String reflect_book_id, Pageable pageable);