Java spring 数据 jpa @query 和 pageable

spring data jpa @query and pageable

提问by Awakening

I'm using Spring Data JPA, and when I use @Queryto to define a query WITHOUTPageable, it works:

我正在使用 Spring Data JPA，当我@Query用来定义查询WITHOUT 时Pageable，它可以工作：

public interface UrnMappingRepository extends JpaRepository<UrnMapping, Long> {
    @Query(value = "select * from internal_uddi where urn like %?1% or contact like %?1%", 
           nativeQuery = true)
    List<UrnMapping> fullTextSearch(String text);
}

But if I add the second param Pageable, the @Querywill NOT work, and Spring will parse the method's name, then throw the exceptionNo property full found. Is this a bug?

但是如果我添加第二个参数Pageable，@Query它将不起作用，Spring 将解析方法的名称，然后抛出异常No property full found。这是一个错误吗？

public interface UrnMappingRepository extends JpaRepository<UrnMapping, Long> {
    @Query(value = "select * from internal_uddi where urn like %?1% or contact like %?1%",
           nativeQuery = true)
    Page<UrnMapping> fullTextSearch(String text, Pageable pageable);
}

采纳答案by Steve

A similar question was asked on the Spring forums, where it was pointed out that to apply pagination, a second subquery must be derived. Because the subquery is referring to the same fields, you need to ensure that your query uses aliases for the entities/tables it refers to. This means that where you wrote:

在 Spring 论坛上提出了一个类似的问题，其中指出要应用分页，必须导出第二个子查询。因为子查询引用相同的字段，所以您需要确保您的查询对其引用的实体/表使用别名。这意味着你写的地方：

select * from internal_uddi where urn like

You should instead have:

你应该有：

select * from internal_uddi iu where iu.urn like ...

回答by Martin Baumgartner

Rewrite your query to:

将您的查询重写为：

select iu from internal_uddi iu where iu.urn.... 

description: http://forum.spring.io/forum/spring-projects/data/126415-is-it-possible-to-use-query-and-pageable?p=611398#post611398

描述：http: //forum.spring.io/forum/spring-projects/data/126415-is-it-possible-to-use-query-and-pageable?p=611398#post611398

回答by Dimitri Hautot

Considering that the UrnMappingclass is mapped to the internal_udditable, I would suggest this:

考虑到UrnMapping类映射到internal_uddi表，我建议这样做：

@Repository
public interface UrnMappingRepository extends JpaRepository<UrnMapping, Long> {

    @Query(value = "select iu from UrnMapping iu where iu.urn like %:text% or iu.contact like %:text%")
    Page<UrnMapping> fullTextSearch(@Param("text") String text, Pageable pageable);
}

Please note that you might have to turn off native queries with dynamic requests.

请注意，您可能需要关闭带有动态请求的本机查询。

回答by RemusS

You can use pagination with a native query. It is documented here: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#_native_queries

您可以在本机查询中使用分页。它记录在这里：https: //docs.spring.io/spring-data/jpa/docs/current/reference/html/#_native_queries

"You can however use native queries for pagination by specifying the count query yourself: Example 59. Declare native count queries for pagination at the query method using @Query"

"但是，您可以通过自己指定计数查询来使用本机查询进行分页：示例 59. 使用 @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);
}

回答by shenyu1997

Please reference :Spring Data JPA @Query, if you are using Spring Data JPA version 2.0.4 and later. Sample like below:

如果您使用的是 Spring Data JPA 2.0.4 及更高版本，请参考：Spring Data JPA @Query。示例如下：

@Query(value = "SELECT u FROM User u ORDER BY id")
Page<User> findAllUsersWithPagination(Pageable pageable);

回答by kite

I found it works different among different jpa versions, for debug, you'd better add this configurations to show generated sql, it will save your time a lot !

我发现它在不同的 jpa 版本之间工作不同，为了调试，你最好添加这个配置来显示生成的 sql，它会节省你很多时间！

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

for spring boot 2.1.6.RELEASE, it works good!

对于 spring boot 2.1.6.RELEASE，效果很好！

Sort sort = new Sort(Sort.Direction.DESC, "column_name");
int pageNumber = 3, pageSize = 5;
Pageable pageable = PageRequest.of(pageNumber - 1, pageSize, sort);

@Query(value = "select * from integrity_score_view " +
        "where (?1 is null or data_hour >= ?1 ) " +
        "and (?2 is null or data_hour <= ?2 ) " +
        "and (?3 is null or ?3 = '' or park_no = ?3 ) " +
        "group by park_name, data_hour ",
        countQuery = "select count(*) from integrity_score_view " +
                "where (?1 is null or data_hour >= ?1 ) " +
                "and (?2 is null or data_hour <= ?2 ) " +
                "and (?3 is null or ?3 = '' or park_no = ?3 ) " +
                "group by park_name, data_hour",
        nativeQuery = true
)
Page<IntegrityScoreView> queryParkView(Date from, Date to, String parkNo, Pageable pageable);

you DO NOT write order byand limit, it generates the right sql

你不写order byand limit，它会生成正确的 sql

回答by amoljdv06

With @Query , we can use pagination as well where you need to pass object of Pageableclass at end of JPA method

使用 @Query ，我们也可以在需要在 JPA 方法结束时传递Pageable类的对象的地方使用分页

For example:

例如：

Pageable pageableRequest = new PageRequest(page, size, Sort.Direction.DESC, rollNo);

Where, page = index of page (index start from zero)
size = No. of records
Sort.Direction = Sorting as per rollNo
rollNo = Field in User class

其中，页面 = 页面索引（索引从零开始）
大小 = 记录数
Sort.Direction = 按 rollNo 排序
rollNo = 用户类中的字段

UserRepository repo
repo.findByFirstname("John", pageableRequest);

public interface UserRepository extends JpaRepository<User, Long> {

  @Query(value = "SELECT * FROM USER WHERE FIRSTNAME = :firstname)
  Page<User> findByLastname(@Param("firstname") String firstname, Pageable pageable);
}

回答by Harsha Jayamanna

Declare native count queries for pagination at the query method by using @Query

使用@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);

}

Hope this helps

希望这可以帮助

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

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

回答by Peji Da

I tried all above solution and non worked , finally I removed the Sorting from Pagination and it worked

我尝试了上述所有解决方案但没有奏效，最后我从分页中删除了排序并且它起作用了