How to paginate a JPA Query

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

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.