Using the same predicates for two criteria queries

user_1637422 0
Viewed 1300

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) Re-using the Root:

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

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) Creating a new 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();

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

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

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.

criteria-api jpa hibernate java
2 Answers

You should use the same alias for the both Root elements, example:

Root<FooEntity> entityRoot = entityQuery.from(FooEntity.class);
entityRoot.alias("alias1") // Property alias

Predicate[] predicates = createPredicates(criteriaBuilder, entityRoot, entityType );

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


CriteriaQuery<Long> countQuery = builder.createQuery(Long.class);
Root<FooEntity> newRoot = countQuery.from(FooEntity.class);
newRoot.alias("alias1"); // Same property alias

countQuery.select(builder.count(newRoot));
countQuery.where(predicates);
Long count = em.createQuery(countQuery).getSingleResult();

This way JPA understands and query using the same restrictions

user_151538890

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

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

Usage:

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

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

user_102055140
Related