在Spring数据JPA中如何将分页与标准查询相结合?

I use PagingAndSortingRepository and findAll(Pageable pageable) method to paging my data. I think that there is no way to provide any condition. For example sometime I want select and paging addresses where city=NY. Is there any way to provide condition and paging simultaneously?

我使用PagingAndSortingRepository和findAll(Pageable Pageable Pageable)方法对数据进行分页。我认为没有办法提供任何条件。例如，有时我需要选择和分页地址，在这里城市=NY。是否有办法同时提供条件和分页?

2 个解决方案

#1

The PagingAndSortingRepository just adds the very basic CRUD methods in pagination mode. As the reference documentation describes, you can simply add a Pageable parameter to any query method you define to achieve pagination of this query.

PagingAndSortingRepository只是在分页模式中添加了非常基本的CRUD方法。正如参考文档所描述的，您可以向您定义的任何查询方法添加可分页参数，以实现查询的分页。

interface CustomerRepository implements Repository<Customer, Long> {

  Page<Customer> findByLastname(String lastname, Pageable pageable);

  List<Customer> findByFirstname(String firstname, Pageable pageable);
}

The first method will return a Page containing the page metadata like total elements available etc. To calculate this data it will trigger an additional count query for the query derived. The second query method returns the plain sliced result set without the count query being executed.

第一个方法将返回一个包含页面元数据的页面，比如可用的元素总数等等。第二个查询方法返回纯分割的结果集，而不执行计数查询。

#2

For Pagination you need 2 methods like getCount(Pageable pageable) and findAll(Pageable pageable)

对于分页，您需要两个方法，如getCount(Pageable Pageable)和findAll(Pageable Pageable Pageable)

Utilizing this functionality in Hibernate, however, is trivial. If you have any HQL query you can simply do this:

然而，在Hibernate中使用这个功能并不重要。如果您有任何HQL查询，您可以这样做:

public Long getCount(Pageable pageable) {
    Query q = session.createQuery("Select count(t) From TableClass  t where t.city = 'NY'");
    Long cnt = (Long) q.uniqueResult();
    return cnt;
}

public List<TableClass> findAll(Pageable pageable) {
     Query q = session.createQuery("From TableClass  t where t.city = 'NY'");
     q.setFirstResult(start);
     q.setMaxResults(length);
     List<TableClass> tableClasslist = q.list();
     return tableClasslist;
}

Likewise, if you have a Criteria query, it's effectively the same thing:

同样，如果你有一个条件查询，它实际上是一样的:

public Long getCount(Pageable pageable) {
    Criteria c = session.createCriteria(TableClass.class);
    c.setProjection(Projections.rowCount()); 
    Long cnt = (Long) c.uniqueResult();
    return cnt;
}

public List<TableClass> findAll(Pageable pageable) {
    Criteria c = session.createCriteria(TableClass.class);
    c.setParameter("city","NY");
    c.setFirstResult(start);
    c.setMaxResults(length);
    List<TableClass> tableClasslist = c.list();
    return tableClasslist ;
}

#1

interface CustomerRepository implements Repository<Customer, Long> {

  Page<Customer> findByLastname(String lastname, Pageable pageable);

  List<Customer> findByFirstname(String firstname, Pageable pageable);
}