Spring boot JPA 原生sql 分页查询报错问题

最新推荐文章于 2025-05-24 08:00:00 发布

原创最新推荐文章于 2025-05-24 08:00:00 发布 · 1.4k 阅读

2 ·

CC 4.0 BY-SA版权

spring boot 专栏收录该内容

6 篇文章

订阅专栏

本文介绍了一个使用Jpa遇到的SQL语法错误问题，通过在application.yml配置文件中开启SQL显示，发现统计countSQL存在错乱。最终通过添加countQuery属性解决了问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

@Repository
public interface DreamBookDao extends JpaRepository<DreamBook,Long>
{
   @Query(value ="SELECT * FROM (SELECT * FROM dream_book AS t WHERE t.name LIKE ?1) q WHERE q.name LIKE ?2", nativeQuery = true)
   Page<DreamBook> findByNameAndInput(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<DreamBook,Long>
{

@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)
    Page<DreamBook> findByNameAndInput(String name,String input,Pageable pageable);

}