JPA自定义sql实现分页查询及关于Spring JPA @query 方法添加 Pageable 的问题记录

由于需要在一个自定义sql语句下添加分页功能,想用直接用JpaRepository 的 Pageable 来直接实现(数据库 oracle) 于是参考文档[Example 51. Declare native count queries for pagination at the query method using @Query](https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.at-query)

public interface UserRepository extends JpaRepository<User, Long> {

  @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1",
    countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
    nativeQuery = true)
  Page<User> findByLastname(String lastname, Pageable pageable);
}

但实际使用后, 启动工程发生异常:

org.springframework.data.jpa.repository.query.InvalidJpaQueryMethodException: Cannot use native queries with dynamic sorting and/or pagination in method

然后上网搜索了下异常, 解决方案是 添加 #pageable 或 /n#pageable/n ,工程可以正常启动,但运行到该处时,报出另外一个数据库异常:

java.sql.SQLSyntaxErrorException: ORA-00911: 无效字符
o.h.e.jdbc.spi.SqlExceptionHelper - SQL Error: 911, SQLState: 22019

由此可以判断是数据库报出的异常, 于是查询log的sql文:

org.hibernate.SQL - select * from ( select * From Pencil where id in (select distinct pencilid from Touser where touser = ? ) #pageable ) where rownum <= ?

由此可以判断,无效字符应该是#pageable, 到目前为止,可以推测到JPA可能需要pageable字符来使工程正常启动,而由于使用#pageable导致sql文执行时报出无效字符, 由此推测,尝试使用注释来避免sql文报错,修改sql文如下:

     1.这个是mysql,注意前缀 #{#pageable}

@Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1 ORDER BY ?#{#pageable}",
    countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
    nativeQuery = true)
  Page<User> findByLastname(String lastname, Pageable pageable);

 

这样写应该就不会出现我的那种报错。

    2.这个是Oracle ,注意前缀 /#pageable/

1. @Query(value = "SELECT * FROM T_PURCHASE_ORDER_ITEM ORDER BY /*#pageable*/",
              countQuery = "SELECT count(*) FROM T_PURCHASE_ORDER_ITEM",
              nativeQuery = true)
       Page<PurchaseOrderItem> findFf(String ff, Pageable pageable);

2. ==========================================================

关于order by 的问题可以通过给表设置变量名来实现

@Query(value = "select p.* From Pencil p where p.id in (select distinct pencilid from Touser where touser = ?1 )  /* #pageable# */",
            countQuery = "select count(*) From Pencil p where p.id in (select distinct pencilid from Touser where touser = ?1 )",
            nativeQuery = true)