Pagination with JPA queries

JPA Queries (6 Part Series)

1 SQL Select with IN clause from list with JPA
2 How to do a Select clause comparison of LocalDateTime in JPA
2 more parts…
3 How to use named queries with JPA and JPQL
4 How to use named parameters in JPA queries
5 Pagination with JPA queries
6 How to execute delete statements with jpa query

Use the firstResult(int startPosition) and setMaxResults(int maxResult) of the JPA Query interface.
The firstResult() method sets the offset value in SQL lexicon, meaning the position of the first result to retrieve.
The setMaxResults() method sets the limit value in SQL lexicon, meaning the maximum number of results to retrieve:

@Stateless
public class PartnerInitialLoadRepository {

  @Inject private EntityManager em;

  public List<PartnerInitialLoad> getNextUnprocessed(Integer pageSize, Integer offset) {
    var jpqlQuery="select p from PartnerInitialLoad p where p.status = 'UNPROCESSED' order by p.partnernumber asc";
    var query =
        em.createNamedQuery(jpqlQuery, PartnerInitialLoad.class);
    query.setFirstResult(offset);
    query.setMaxResults(pageSize);

    return query.getResultList();
  }
}

Enter fullscreen mode Exit fullscreen mode

JPA with Hibernate translates this in the following SQL statement in Oracle dialect which uses offset and limit:

select partnerini0_.PARTNER_NUMBER as PARTNER_1_13_,
       partnerini0_.COMPLETED_AT as COMPLETE2_13_,
       partnerini0_.STATUS as STATUS3_13_
from T_PARTNER_INITIAL_LOAD partnerini0_
where partnerini0_.STATUS is null
   or partnerini0_.STATUS = 'UNPROCESSED'
order by partnerini0_.PARTNER_NUMBER asc
offset ? limit ?;

Enter fullscreen mode Exit fullscreen mode

An alternative would be to use native queries, the major drawback is that you make your code dependent on the underlying RDBMS:

  public List<PartnerInitialLoad> getNextUnprocessed(Integer pageSize, Integer offset) {
    var sqlNativeString =
        String.format(
            "select * from T_PARTNER_INITIAL_LOAD where STATUS = 'UNPROCESSED' order by PARTNER_NUMBER asc OFFSET %s LIMIT %s",
            offset, pageSize);
    var query = em.createNativeQuery(sqlNativeString, PartnerInitialLoad.class);

    return query.getResultList();
  }

Enter fullscreen mode Exit fullscreen mode


Shared with ️ from Codever. Use copy to mine functionality to add it to your personal snippets collection.

JPA Queries (6 Part Series)

1 SQL Select with IN clause from list with JPA
2 How to do a Select clause comparison of LocalDateTime in JPA
2 more parts…
3 How to use named queries with JPA and JPQL
4 How to use named parameters in JPA queries
5 Pagination with JPA queries
6 How to execute delete statements with jpa query

原文链接:Pagination with JPA queries

© 版权声明
THE END
喜欢就支持一下吧
点赞9 分享
评论 抢沙发

请登录后发表评论

    暂无评论内容