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

问题描述

我正在为系统中的实体实现高级搜索"类型的功能，以便用户可以使用该实体的属性上的多个条件(eq、ne、gt、lt、like 等)搜索该实体.我使用 JPA 的 Criteria API 动态生成 Criteria 查询，然后使用 setFirstResult() &setMaxResults() 支持分页.到目前为止一切都很好，但现在我想在结果网格上显示结果总数，但我没有看到获得 Criteria 查询总数的直接方法.
这是我的代码的样子:

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 上的 rowCount() 方法(我认为它在 Hibernate 的标准中).

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

推荐答案

感谢 Vladimir！我接受了你的想法并使用了单独的计数查询来使用我现有的谓词数组.最终实现如下所示:

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

让它工作.有什么想法吗?

to get it working. Any Idea?

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