如何对JPA查询进行分页 [英] How to paginate a JPA Query

问题描述

我有一个提交表，其中的列包含 ID ，名称，代码以及其他物业。我的要求是根据提到的属性搜索记录并返回一个分页集。

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

似乎有很多选项，比如 CriteriaBuilder ， NamedQuery 等。在这种情况下哪一个最有效？

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

推荐答案

对于所有JPA查询对象（本机SQL查询除外），您将通过setMaxResults（int）和setFirstResult（int）方法使用分页。例如：

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将为您执行分页。

JPA will perform pagination for you.

命名查询只是预定义的，可以缓存，而其他类型是动态创建的。

因此选择使用JPQL：

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

或CriteriaBuilder api形成类似的查询：

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

不要忘记使用query.setParameter（id，sf.id）设置参数值）例如。

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

这篇关于如何对JPA查询进行分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！