jpa mysql sql分页查询语句_Spring boot JPA 原生sql 分页查询报错问题

项目使用Jpa，因为具体业务需要，写原生sql

@Repository

public interface DreamBookDao extends JpaRepository{

@Query(value ="SELECT * FROM (SELECT * FROM dream_book AS t WHERE t.name LIKE ?1) q WHERE q.name LIKE ?2", nativeQuery = true)

PagefindByNameAndInput(String name,String input,Pageable pageable);

}

报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 dream_book AS t WHERE t.name LIKE 'B%') q WHERE q.name LIKE' at line 1 错误。各种查找，最后在api 接口 application.yml 文件里配置：

spring:

jpa:

show-sql: true

打印出sql，结果发现 统计count sql 错乱。

添加countQuery 解决：

@Repository

public interface DreamBookDao extends JpaRepository{

@Query(value ="SELECT * FROM (SELECT * FROM dream_book AS t WHERE t.name LIKE ?1) q WHERE q.name LIKE ?2",

countQuery="SELECT count(*) FROM (SELECT * FROM dream_book AS t WHERE t.name LIKE ?1) q WHERE q.name LIKE ?2",

nativeQuery = true)

PagefindByNameAndInput(String name,String input,Pageable pageable);

}