Hibernate 6.1.6: Using jakarta criteria and count query, getting exception in count query execution if count query contains where predicate

org.hibernate.sql.ast.SqlTreeCreationException: Could not locate TableGroup - com.sfnt.ems.domain.common.User(144949067788000)

SqmBasicValuedSimplePath(com.sfnt.ems.domain.common.User(146863903776100).userId)
{com.sfnt.ems.domain.common.User=StandardTableGroup(com.sfnt.ems.domain.common.User(146971078538600))}
why the bracket number difference here?

CriteriaBuilder cb =restrictions.getCriteriaBuilder();
CriteriaQuery countQuery = restrictions.getCriteriaCountQuery();
Root root = restrictions.getRootCountQuery();
countQuery.select(cb.count(root));
Predicate finalPredicate=restrictions.getCriteriaQuery().getRestriction();
if(finalPredicate!=null)
countQuery.where(cb.and(finalPredicate));
Query queryCount = getSession().createQuery(countQuery);
Long count = (Long) queryCount.getSingleResult();
return count.intValue();

My restriction class has methods as:
public class Restrictions {

private CriteriaBuilder builder;
private Class<T> persistentClass;
private CriteriaQuery<T> criteriaQuery = null;
private CriteriaQuery<Long> criteriaCountQuery = null;
private Root<T> root = null;
private Root<T> rootCountQuery = null;

public Restrictions(CriteriaBuilder criteriaBuilder, Class<T> persistentClass) {
	this.persistentClass = persistentClass;
	builder = criteriaBuilder;
}

public CriteriaBuilder getCriteriaBuilder() {
	return builder;
}

public CriteriaQuery<T> getCriteriaQuery() {
	if (criteriaQuery == null)
		criteriaQuery = builder.createQuery(this.persistentClass);
	return criteriaQuery;
}

public CriteriaQuery<Long> getCriteriaCountQuery() {
	if (criteriaCountQuery == null)
		criteriaCountQuery = builder.createQuery(Long.class);
	return criteriaCountQuery;
}

public Root<T> getRootCountQuery() {
	if (rootCountQuery == null)
		rootCountQuery = getCriteriaCountQuery().from(this.persistentClass);
	rootCountQuery.alias(this.persistentClass.getName());
	return rootCountQuery;
}

public Root<T> getRoot() {
	if (root == null)
		root = getCriteriaQuery().from(this.persistentClass);
	root.alias(this.persistentClass.getName());
	return root;
}

}

You can’t use the same restriction Predicate that you built for one query in the other. You have to rebuild the restriction again, based on the Root/From of the query you want to apply this on.

okay Sir. thanks a lots for replying.
Can i create a new predicate list from existing predicate list of criteria queries, and change root in this new predicate list for my count query in hibernate 6.1.
Here criteriaList is my list of predicates of criteria query. I am trying to create here criteriaCountList with same conditions mentioned in criteriaList, but changing the root for my countQuery:

            CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
	Root<?> root = countQuery.from(getPersistentClass());
	countQuery.select(cb.count(root));
	List<Predicate> criteriaCountList = new ArrayList<Predicate>();
	for(Predicate predicate:criteriaList) {
        Predicate newPredicate = cb.and(predicate.getExpressions().toArray(new Predicate[0]));
        criteriaCountList.add(newPredicate);
	}
	Predicate finalPredicate=restrictions.getFinalPredicate(criteriaCountList);
	if(finalPredicate!=null)
		countQuery.where(cb.and(finalPredicate));
	Query queryCount = getSession().createQuery(countQuery);
	Long count = (Long) queryCount.getSingleResult();
	return count.intValue();

Here i want to use root defined for countQuery in predicateList coming from root of entity class.

This was discussed here already: [HHH-15951] - Hibernate JIRA

1 Like