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?

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

Solution 2

You can use Pageable in the repository method in Spring

@Repository
public interface StateRepository extends JpaRepository<State, Serializable> {

@Query("select state from State state where state.stateId.stateCode = ?1")
public State findStateByCode(String code, Pageable pageable);

}

And in the service layer, you can create the Pageable object:

@Autowire
StateRepository stateRepository;

public State findStateServiceByCode(String code, int page, int size) {
    Pageable pageable = new PageRequest(page, size);
    Page<Order> statePage = stateRepository.findStateByCode(code, pageable);
    return statePage.getContent();
}

Solution 3

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. For this purpose, the JPA Query interface provides the setFirstResult method.

Using pagination with a JPQL query looks as follows:

List<Post> posts = entityManager.createQuery("""
    select p
    from Post p
    order by p.createdOn
    """)
    .setFirstResult(10)
    .setMaxResults(10)
    .getResultList();

Criteria API is just the same since you need to create a Query from the CriteriaQuery:

CriteriaBuilder qb = em.getCriteriaBuilder();
CriteriaQuery<Post> cq = qb.createQuery(Post.class);

Root<Post> root = cq.from(Post.class);
cq.orderBy(qb.asc(root.get("createdOn")));

List<Post> posts = em.createQuery(cq)
    .setFirstResult(10)
    .setMaxResults(10)
    .getResultList();