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

Hibernate ORM
1

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

}

2

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.

1 Like
3

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.

4

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

1 Like
8

Hello Sir,
This works perfrect. But if we also making fetch statement in our predicate list, using above code for count query resulting into exception:
org.hibernate.query.SemanticException: query specified join fetching, but the owner of the fetched association was not present in the select list

9

Since I was getting tired of answering the same questions over and over, I decided to implement count query creation in 6.4: [HHH-17410] - Hibernate JIRA

If you can’t get it to work by rebuilding the query without fetch joins, then you’ll have to wait for 6.4.0.CR2 or 6.4.0.Final.