How to paginate a JPA Query

Question

I have a submission table with columns like ID, Name, Code among other properties. My requirement is to search for records based on the mentioned properties and return a paginated set.

This is the pseudocode for what I am looking for:

searchSubmission(searchFilter sf,pageIndex,noOfRecords) {    query = 'from submisssion where code=sf.code or id=sf.id order by id start_from (pageIndex*noOfRecords) limit noOfRecords'    return result(); } 

There seem to be many options like CriteriaBuilder, NamedQuery, etc. Which is the most efficient one in this situation?

Answer 1

For all JPA query objects (except for native SQL queries), you would use pagination through the setMaxResults(int) and setFirstResult(int) methods. For instance:

  return em.createNamedQuery("yourqueryname", YourEntity.class)       .setMaxResults(noOfRecords)       .setFirstResult(pageIndex * noOfRecords)       .getResultList(); 

JPA will perform the pagination for you.

Named queries are just predefined and can be cached, while other types are dynamically created.
So the choice is to use JPQL like:

Query query = em.createQuery("SELECT s FROM Submission s WHERE s.code = :code or s.id = :id ORDER BY s.id", Submission.class); 

Or CriteriaBuilder api to form a similar query:

    CriteriaBuilder qb = em.getCriteriaBuilder();     CriteriaQuery<Submission> cq = qb.createQuery(Submission.class);      Root<Submission> root = cq.from(Submission.class);     cq.where( qb.or(          qb.equal(root.get("code"), qb.parameter(String.class, "code")),         qb.equal(root.get("id"), qb.parameter(Integer.class, "id"))     ));     Query query = em.createQuery(cq); 

Don't forget to set the parameter values using query.setParameter("id", sf.id) for example.