使用JPA Criteria API进行分页的总行数 [英] Total row count for pagination using JPA Criteria API

问题描述

我正在为我的系统中的实体实现高级搜索类功能，以便用户可以使用多个条件（eq，ne，gt，lt，像etc等）在此实体的属性上搜索该实体。我使用JPA的Criteria API动态生成Criteria查询，然后使用 setFirstResult（）& setMaxResults（）来支持分页。所有这一切都很好，直到这一点，但现在我想显示结果网格上的结果总数，但我没有看到一个简单的方法来获得总数的条件查询。

这是我的代码看起来像：

I am implementing "Advanced Search" kind of functionality for an Entity in my system such that user can search that entity using multiple conditions(eq,ne,gt,lt,like etc) on attributes of this entity. I am using JPA's Criteria API to dynamically generate the Criteria query and then using setFirstResult() & setMaxResults() to support pagination. All was fine till this point but now I want to show total number of results on results grid but I did not see a straight forward way to get total count of Criteria query.
This is how my code looks like:

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Brand> cQuery = builder.createQuery(Brand.class);
Root<Brand> from = cQuery.from(Brand.class);
CriteriaQuery<Brand> select = cQuery.select(from);
.
.
//Created many predicates and added to **Predicate[] pArray**
.
.
select.where(pArray);
// Added orderBy clause
TypedQuery typedQuery = em.createQuery(select);
typedQuery.setFirstResult(startIndex);
typedQuery.setMaxResults(pageSize);
List resultList = typedQuery.getResultList();

我的结果集可能很大，所以我不想加载我的实体进行计数查询，所以告诉我有效的方法来获得像Criteria（我认为它存在于Hibernate的Criteria中）的 rowCount（）方法的总数。

My result set could be big so I don't want to load my entities for count query, so tell me efficient way to get total count like rowCount() method on Criteria (I think its there in Hibernate's Criteria).

推荐答案

谢谢弗拉基米尔！
我采纳了你的想法，并使用单独的计数查询来使用我现有的谓词数组。最终实现如下所示：

Thanks Vladimir! I took your idea and used separate count query to use my existing array of predicates in it. Final implementation looks like this:

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Brand> cQuery = builder.createQuery(Brand.class);
Root<Brand> from = cQuery.from(Brand.class);
CriteriaQuery<Brand> select = cQuery.select(from);
.
.
//Created many predicates and added to **Predicate[] pArray**
.
.
CriteriaQuery<Long> cq = builder.createQuery(Long.class);
cq.select(builder.count(cq.from(Brand.class)));
// Following line if commented causes [org.hibernate.hql.ast.QuerySyntaxException: Invalid path: 'generatedAlias1.enabled' [select count(generatedAlias0) from xxx.yyy.zzz.Brand as generatedAlias0 where ( generatedAlias1.enabled=:param0 ) and ( lower(generatedAlias1.description) like :param1 )]]
em.createQuery(cq);
cq.where(pArray);
Long count = em.createQuery(cq).getSingleResult();
.
.
select.where(pArray);
.
.
// Added orderBy clause
TypedQuery typedQuery = em.createQuery(select);
typedQuery.setFirstResult(startIndex);
typedQuery.setMaxResults(pageSize);
List resultList = typedQuery.getResultList()

虽然这工作正常但我仍然不知道为什么我必须写

Though this is working fine but still I am not sure why I have to write

em.createQuery(cq);

让它工作。任何想法？

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