如何计算通用 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();
}

但是，当使用 join 时，该代码不起作用.有没有办法使用 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 的代码:

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作为 generateAlias0 其中(generatedAlias0.id>=13L)和((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 应该在Entity 上，generatedAlias0 应该在我加入的关联上.请注意，我正确实现了 Join，因为当我在没有 count 查询的情况下执行查询时，它执行时不会出错并且 Join 工作正常，但是当我尝试执行 count 查询时，它会引发异常.

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屋！