本文介绍了如何对JPA查询进行分页的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个提交表,其中的列包含 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查询进行分页的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-23 14:30