如何计算通用JPA DAO中JPA 2 CriteriaQuery的行数？ [英] How to count the number of rows of a JPA 2 CriteriaQuery in a generic JPA DAO?

问题描述

我是JPA的新用户，希望实现一个通用的JPA DAO，并且需要查找查询结果集的行数以实现分页。搜索网络后，我找不到一个实用的方法来做到这一点。以下是许多文章中建议的代码：

I'm new in JPA and want to implement a generic JPA DAO and need to find the number of rows of a query result set to implement pagination. After searching the web, I can't find a practical way to do that. Here is the code suggested in many articles:

public <T> Long findCountByCriteria(CriteriaQuery<?> criteria) {
    CriteriaBuilder builder = em.getCriteriaBuilder();

    CriteriaQuery<Long> countCriteria = builder.createQuery(Long.class);
    Root<?> entityRoot = countCriteria.from(criteria.getResultType());
    countCriteria.select(builder.count(entityRoot));
    countCriteria.where(criteria.getRestriction());

    return em.createQuery(countCriteria).getSingleResult();
}

但是，使用加入。是否有任何方法使用JPA Criteria API计算查询结果集的行？

However, that code doesn't work when using join. Is there any way to count the rows of a query result set using the JPA Criteria API?

更新：
这里是创建CriteriaQuery的代码： p>

UPDATE : here is the code that create CriteriaQuery :

    CriteriaQuery<T> queryDefinition = criteriaBuilder.createQuery(this.entityClass);
    Root<T> root = queryDefinition.from(this.entityClass);

并且可以将一些连接添加到根，直到执行查询：

and some joins may be added to the root until the query have been executed:

public Predicate addPredicate(Root<T> root) {
                Predicate predicate = getEntityManager().getCriteriaBuilder().ge(root.join(Entity_.someList).get("id"), 13);
                return predicate;
}

，生成的异常如下：

org.hibernate.hql.ast.QuerySyntaxException：无效的路径：
'generatedAlias1.id'[select count（generatedAlias0）from entity.Entity
as generatedAlias0 where（generatedAlias0.id> = 13L）and（
（generatedAlias1.id <= 34L））]

org.hibernate.hql.ast.QuerySyntaxException: Invalid path: 'generatedAlias1.id' [select count(generatedAlias0) from entity.Entity as generatedAlias0 where ( generatedAlias0.id>=13L ) and ( (generatedAlias1.id<=34L ) )]

generatedAlias1应该在实体和generatedAlias0应该在我加入的关联上。
注意，我实现Join正确，因为当我执行查询无计数查询它执行没有错误，并且Join工作正常，但是当我尝试执行计数查询它抛出异常。

which generatedAlias1 should be on Entity and generatedAlias0 should be on the association that I joined on that. Note that I implement Join properly because when I execute query without count query it executes without error and the Join works properly but when I try to execute count query it throws exception.

推荐答案

我已经这样做了：

public Long getRowCount(CriteriaQuery criteriaQuery,CriteriaBuilder criteriaBuilder,Root<?> root){
    CriteriaQuery<Long> countCriteria = criteriaBuilder.createQuery(Long.class);
    Root<?> entityRoot = countCriteria.from(root.getJavaType());
    entityRoot.alias(root.getAlias());
    doJoins(root.getJoins(),entityRoot);
    countCriteria.select(criteriaBuilder.count(entityRoot));
    countCriteria.where(criteriaQuery.getRestriction());
    return this.entityManager.createQuery(countCriteria).getSingleResult();
}

private void doJoins(Set<? extends Join<?, ?>> joins,Root<?> root_){
    for(Join<?,?> join: joins){
        Join<?,?> joined = root_.join(join.getAttribute().getName(),join.getJoinType());
        doJoins(join.getJoins(), joined);
    }
}

private void doJoins(Set<? extends Join<?, ?>> joins,Join<?,?> root_){
    for(Join<?,?> join: joins){
        Join<?,?> joined = root_.join(join.getAttribute().getName(),join.getJoinType());
        doJoins(join.getJoins(),joined);
    }
}

当然你不需要Root作为输入参数可以从条件查询中获得，

of course you do not need Root as input parameter you could get it from criteria query,

这篇关于如何计算通用JPA DAO中JPA 2 CriteriaQuery的行数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！