对两个条件查询使用相同的谓词 [英] Using the same predicates for two criteria queries

问题描述

我想使用相同的Predicate数组运行一对查询:一个要对记录进行计数，一个要获取记录的特定页面.对我来说，这似乎是一个非常正常的用例，因此必须有一种很好的方法来实现，但是我还没有找到它.

I want to run a pair of queries using the same array of Predicate: one to count the records, one to get a certain page of records. This seems like a pretty normal use case, to me, so there must be a good way to do it, but I have yet to find it.

所以这是获取实体的部分:

So this is the part that fetches entities:

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
EntityType<ENTITY> entityType = entityManager.getMetamodel().entity(FooEntity.class);
CriteriaQuery<FooEntity> entityQuery = criteriaBuilder.createQuery(FooEntity.class);
Root<FooEntity> entityRoot = entityQuery.from(FooEntity.class);

// Use the criteria builder, root, and type to create some predicates.
Predicate[] predicates = createPredicates(criteriaBuilder, entityRoot, entityType );

// Fetch the entities.
entityQuery.select(entityRoot);
entityQuery.where(predicates);
List<FooEntity> entities = entityManager.createQuery(entityQuery)
    .setFirstResult(0) // Just get the first page
    .setMaxResults(50)
    .getResultList();

这有效.我们得到了我们想要的东西，谓词是正确的等等.

This works. We get what we desire, predicates are correct etc.

但是，使用相同的谓词创建另一个查询来确定计数失败.我尝试了两种不同的方式:

However, creating another query to determine the count using the same predicates fails. I've tried two different ways:

(1)重用Root:

CriteriaQuery<Long> countQuery = criteriaBuilder.createQuery(Long.class);
countQuery.select(criteriaBuilder.count(entityRoot));
countQuery.where(predicates);
long count = entityManager.createQuery(countQuery).getSingleResult();

这不起作用，给了我java.lang.IllegalStateException: No criteria query roots were specified.奇怪，因为我清楚地指定了Root，但是也许我不能重复使用由其他CriteriaQuery创建的Root?好的，让我们用相同的CriteriaQuery ...

This doesn't work and gives me java.lang.IllegalStateException: No criteria query roots were specified. Odd, since I clearly specified the Root, but perhaps I can't re-use a Root that was created from a different CriteriaQuery? Fine, let's create one from the same CriteriaQuery...

(2)创建一个新的Root:

CriteriaQuery<Long> countQuery = criteriaBuilder.createQuery(Long.class);
countQuery.select(criteriaBuilder.count(countQuery.from(FooEntity.class));
countQuery.where(predicates);
long count = entityManager.createQuery(countQuery).getSingleResult();

现在我们得到了另一个错误:org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'generatedAlias1.fooProperty' [select count(generatedAlias0) from com.foo.FooEntity as generatedAlias0 where ( generatedAlias1.fooProperty = :param0 )]

Now we get a different error: org.hibernate.hql.internal.ast.QuerySyntaxException: Invalid path: 'generatedAlias1.fooProperty' [select count(generatedAlias0) from com.foo.FooEntity as generatedAlias0 where ( generatedAlias1.fooProperty = :param0 )]

看一下创建的HQL，看来"from"子句设置了generatedAlias0，但是"where"子句中的所有内容都引用了generatedAlias1.我的猜测是因为Predicate的数组是使用与CriteriaQuery<Long>中使用的不同的Root构建的.

Looking at the HQL that was created, it appears that the "from" clause sets a generatedAlias0, but all the stuff in the "where" clause references generatedAlias1. My guess is because the array of Predicate was built using a different Root than was used in the CriteriaQuery<Long>.

因此，如果两种方法都不起作用，我将如何重用相同的Predicate数组?我是否真的必须使用第二个Root重新创建所有这些?对我来说，这似乎太过分了，尤其是因为它们都是Root<FooEntity>.我觉得必须有更好的方法.

So, if it doesn't work either way, how would I re-use the same array of Predicate? Do I really have to re-create all of them with the second Root? That seems super excessive to me, especially since they're both Root<FooEntity>. I feel like there must be a better way.

推荐答案

您必须为每个查询创建新的Root，CriteriaQuery和CriteriaBuiler.

You have to create new Root, CriteriaQuery and CriteriaBuiler for every query.

如果您使用 Spring ，则

If you use Spring, Specification can be opted for making Predicate reusable. Otherwise you can create your own Specification functional interface like this

@FunctionalInterface
public interface Specification<T> {
    Predicate toPredicate(
          Root<T> root, 
          CriteriaQuery<?> query, 
          CriteriaBuilder criteriaBuilder);
}

用法:

public Specification<FooEntity> createSpecification(YourParameters parameters) {
    return (root, query, criteriaBuilder) -> {
        Predicate fullPredicate;

        // create predicates using parameters, root, query, criteriaBuilder 
        // and concatenate them into one: fullPredicate = predicate.and(anotherPredicate);

        return fullPredicate;
    };
}

然后您可以通过这种方式为每个查询获取谓词

And then you can get predicate for every query this way

Predicate predicate = createSpecification(parameters)
    .toPredicate(entityRoot, entityQuery, criteriaBuilder);

最好的方法是使用每个规范的单独方法创建实用工具类，并使用Specification.and方法将它们组合起来

The best approach is to create utility class with separated methods for every specification and combine them you need using Specification.and method

这篇关于对两个条件查询使用相同的谓词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！