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?

Reine

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.

Shoring

Answer 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();
}

Icecold

Constructor new PageRequest(page, size) is deprecated. Use Pageable pageable = PageRequest.of(page, size) instead – Partridgeberry

This is specific to Spring tho if I'm not mistaken. Can you please specify it in your answer? – Audette

What? I didn't know you could pass Pageable to manual queries. – Slacks

Answer 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();

Jemima

While it is true that setMaxResults and setFirstResult api exist and are useable on Native SQL queries, I would hesitate using them. How they work is up to the provider, but since they just pass through the Native SQL statement, the only filtering that can be done is on the full returned results. Again, implementation dependent, but setFirstResult/setMaxResults lets providers inject DB specific pagination (like rownum filtering) into the generated SQL when it is JPQL or criteria queries. You are better off doing the same with native SQL queries to avoid the network/DB hit. – Shoring

@Shoring HIbernate just modifies the SQL prior to sending it to the DB. There's no extra network/DB hit. – Jemima

Does it modify your native SQL string that it is sending to the DB? For a JPQL/CriteriaQuery/HB query, sure, it can modify the SQL it generates, but I'd be surprised it does so for a Native SQL query for fear of it messing up the SQL. As mentioned though, this is JPA provider specific - others do not touch your Native SQL query and so rely on other means for pagination which are less than what you can and should do in the SQL itself. – Shoring

You might wonder how it works, but as a top Hibernate committer, I can confirm that for you. Here's one example on GitHub. As for JPA portability, that's myth. People don't really change JPA providers and the vast majority of them use Hibernate anyway, so this is not a real-life issue. – Jemima

Thanks for the info, but I was surprised that they do it, not how they do it. I'll avoid getting into a JPA vendor debate as this question/thread wasn't on Hibernate or your views on JPA, and I doubt anyone cares about my own. – Shoring