How to combine pagination with a criteria query in Spring Data JPA?

Question

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?

Solution 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.

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.

Solution 2

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

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

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 ;
}