Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to combine pagination with a criteria query in Spring Data 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?

like image 795
Mariusz Avatar asked Oct 08 '12 12:10

Mariusz


People also ask

How Pagination is used in criteria query?

The simplest way to implement pagination is to use the Java Query Language – create a query and configure it via setMaxResults and setFirstResult: Query query = entityManager. createQuery("From Foo"); int pageNumber = 1; int pageSize = 10; query. setFirstResult((pageNumber-1) * pageSize); query.

Which two methods from the query interface can be used to paginate results of a query?

You can, of course, use pagination with JPA and Hibernate. The easiest way to do that is to add the LIMIT and OFFSET clauses to a native SQL query.

How does JPA Pagination works internally?

You can use the JPA pagination for both entity queries and native SQL. To limit the underlying query ResultSet size, the JPA Query interface provides the setMaxResults method. Navigating the following page requires positioning the result set where the last page ended.


2 Answers

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.

like image 178
Oliver Drotbohm Avatar answered Sep 18 '22 11:09

Oliver Drotbohm


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 ;
}
like image 37
Rahul Agrawal Avatar answered Sep 18 '22 11:09

Rahul Agrawal