org.hibernate.sql.ast.SqlTreeCreationException: Could not locate TableGroup - model.dao.User(1055362627602899)

Question

I have a spring boot 2.7.5 with hibernate 5.6.12 project and I wanted to upgrade to spring boot 3.1.0 with hibernate 6.6.2 I'm trying a query count in database for getting total number of records in database an I get the error

org.hibernate.sql.ast.SqlTreeCreationException: Could not locate TableGroup - model.dao.User(1057834329991800) at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.prepareReusablePath(BaseSqmToSqlAstConverter.java:3349) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final]

Base class is:

public class JPADaoImpl {
    protected final Logger logger = LoggerFactory.getLogger(getClass());
    
    @Autowired
    @Qualifier("dataSource")
    protected DataSource dataSource;
    
    @PersistenceContext
    protected EntityManager entityManager;
    
    protected <T> PageData<T> getPageData(CriteriaQuery<T> criteria,Root<T> root,Integer pStart, Integer pSize,LockModeType lockModeType) throws Exception{
        PageData<T> pageData =null;
        try {
            pageData = new PageData<T>();
            
            criteria.distinct(true);
            
            criteria.select(root);
            
            TypedQuery<T> query = getPersistenceQuery(criteria, pStart, pSize, lockModeType);
            
            if (pStart == null || pSize == null) {
                pageData.setData(query.getResultList());
                return pageData;
            }
            
            pageData.setpStart(pStart);
            pageData.setpSize(pSize);
            
            Long rowCount = getRowCount(criteria,root,true);
            pageData.setTotalNo(rowCount);

            pageData.setpCount(rowCount / pSize + (rowCount % pSize == 0 ? 0 : 1));
    
            pageData.setData(query.getResultList());
            
            return pageData;
        } catch (Exception e) {
            throw e;
        }
    }
    
    
    /**
     * 
     * @param criteria
     * @param root
     * @param distinct
     * @return
     * @throws Exception
     */
    protected <T> Long getRowCount(CriteriaQuery<T> criteria,Root<T> root,boolean distinct) throws Exception {
        Long rowcount=null;
        CriteriaBuilder builder = entityManager.getCriteriaBuilder(); 
        CriteriaQuery<Long> countCriteria=builder.createQuery(Long.class);
        
        Root<?> entityRoot = countCriteria.from(root.getJavaType());
        entityRoot.alias(root.getAlias());
        
        doJoins(root.getJoins(),entityRoot);
        if( criteria.isDistinct()){
             countCriteria.select(builder.countDistinct(entityRoot));
        }else{
            countCriteria.select(builder.count(entityRoot));
        }
        
        Predicate fromRestriction = criteria.getRestriction();
    
        if (fromRestriction != null) {
            countCriteria.where(fromRestriction);
        }
        
        countCriteria.distinct(criteria.isDistinct());
    
        rowcount=entityManager.createQuery(countCriteria).getSingleResult();

        return rowcount;
    }
    
    /**
     * 
     * @param joins
     * @param root_
     */
    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);
        }
    }
    /**
     * 
     * @param joins
     * @param root_
     */
    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);
        }
    }

    
        /**
     * @param criteria
     * @param pStart
     * @param pSize
     * @param lockModeType
     * @return
     */
    private <T> TypedQuery<T> getPersistenceQuery(CriteriaQuery<T> criteria, Integer pStart, Integer pSize, LockModeType lockModeType) {
        
        TypedQuery<T> query = entityManager.createQuery(criteria);
        
        if(lockModeType!=null){
            query.setLockMode(lockModeType);
        }
        
        if (pStart != null && pSize != null) {
            query.setFirstResult((pStart - 1) * pSize);
            query.setMaxResults(pSize);
        }
        return query;
    }

}

and the class that extends it

public class UserDaoImpl extends JPADaoImpl implements UserDao{
    
    @Override
    public PageData<User> list(User filter, Integer pStart, Integer pSize, List<Order> order, User userAuth) throws Exception {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<User> criteria = criteriaBuilder.createQuery(User.class);
        Root<User> root = criteria.from(User.class);
        root.alias("root");
        criteria.select(root);
    
        List<Predicate> predicateList = null;
                    
        if (filter!=null){
            
            predicateList = new ArrayList<Predicate>();
            
            if (!Utils.isEmpty(filter.getStatus())) {
                predicateList.add(criteriaBuilder.and(criteriaBuilder.equal(root.<Byte>get("status"),filter.getStatus())));
            }
        }
        
        if(!Utils.isEmpty(predicateList)){
            criteria.where(criteriaBuilder.and(predicateList.toArray(new Predicate[predicateList.size()])));
        }
        criteria.orderBy(criteriaBuilder.asc(root.get("last_name")),criteriaBuilder.asc(root.get("first_name")));
        
        PageData<User> pageData;
    
        pageData = getPageData(criteria,root, pStart, pSize);
        
    
        return pageData;
    }

}

in UserDaoImpl.list method, JPADaoImpl.getRowCount at line rowcount=entityManager.createQuery(countCriteria).getSingleResult();

I get the error

org.hibernate.sql.ast.SqlTreeCreationException: Could not locate TableGroup - model.dao.User(1057834329991800) at org.hibernate.query.sqm.sql.BaseSqmToSqlAstConverter.prepareReusablePath(BaseSqmToSqlAstConverter.java:3349) ~[hibernate-core-6.2.2.Final.jar:6.2.2.Final]

If I comment line countCriteria.where(fromRestriction); it works ok

It works also in spring boot 2.7.5 with hibernate 5.6.12

Is it a bug in the new version of hibernate? Is it a workaround to fix this?

Thank you,
Adrian

Answer 1

I managed to solve it like this:

`@Repository public class CustomizedArquivoRepositoryImpl implements CustomizedArquivoRepository {

private final EntityManager entityManager;

public CustomizedArquivoRepositoryImpl(EntityManager entityManager) {
    this.entityManager = entityManager;
}

@Override
public Page<Arquivo> buscarPorPalavras(String palavras, Pageable pageable) {
    String[] keywords = palavras.split(" ");

    // Configuring a Criteria API
    HibernateCriteriaBuilder cb = entityManager.unwrap(Session.class).getCriteriaBuilder();

   JpaCriteriaQuery<Arquivo> query = cb.createQuery(Arquivo.class);

    Root<Arquivo> arquivo = query.from(Arquivo.class);

    List<Predicate> predicates = new ArrayList<>();

    // For each word, add a LIKE condition
    for (String keyword : keywords) {
        System.out.println(keyword);
        predicates.add(cb.like(cb.lower(arquivo.get("titulo")), "%" + keyword.toLowerCase() + "%"));
    }

    // Joining the conditions with OR
    query.where(cb.or(predicates.toArray(new Predicate[0])));

    // Running the query with pagination
    List<Arquivo> documentos = entityManager.createQuery(query)
            .setFirstResult((int) pageable.getOffset())
            .setMaxResults(pageable.getPageSize())
            .getResultList();


    // Total count of results
   Long total = entityManager.createQuery(query.createCountQuery()).getSingleResult();

    return new PageImpl<>(documentos, pageable, total);

}

Answer 2

Predicate fromRestriction = criteria.getRestriction();
// hibernate 5.x
if (fromRestriction != null) {
  countCriteria.where(fromRestriction);
}

---

 Predicate predicateList= new ArrayList();
 // hibernate 6.x
 if (predicateList!= null) {
   countCriteria.where(predicateList);
 }

Answer 3

To resolve this issue, you should create separate CriteriaQuery objects for the counting query and the query to retrieve records. Each of these queries should have its own associated Root object. This ensures that the filters in the where statement refer to the correct Root for both counting and retrieving records.

Using the root from a different CriteriaQuery in the where statement of another CriteriaQuery might cause a "Could not locate TableGroup" error. This error indicates that Hibernate is having difficulty determining the correct table group for the query, likely due to the mismatched roots.

Answer 4

In my case I changed the way to fill the predicate and it worked:

    Predicate[] predicates = {
        builder.like(builder.lower(root.get("colunm1")),"%" + numeroProcesso.toLowerCase() + "%"),
        root.get("colunm2").in(orgaoJulgadores)
    };

    criteria.where(predicates);

for

List<Predicate> predicates = new ArrayList<>();

predicates.add(builder.like(builder.lower(root.get("colunm1")),
            "%" + numeroProcesso.toLowerCase() + "%"));
predicates.add(root.get("colunm2").in(orgaoJulgadores));

criteria.where(predicates.toArray(new Predicate[predicates.size()]));