如何对 JPA 查询进行分页 [英] How to paginate a JPA Query
问题描述
我有一个提交表,其中包含诸如 ID
、Name
、Code
等列以及其他属性.我的要求是根据提到的属性搜索记录并返回一个分页集.
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.
这是我要找的伪代码:
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 the 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屋!