How to get subquery values when using Hibernate Criteria API

Question

Here below is a subquery to retrieve a list of ids:

protected Long getIdList(
        @NonNull final CriteriaBuilder rootBuilder,
        @NonNull final CriteriaQuery<?> rootQuery,
        @NonNull final Pageable pageable) {
    final HibernateCriteriaBuilder listBuilder = (HibernateCriteriaBuilder) rootBuilder;
    final JpaCriteriaQuery<Long> listQuery = listBuilder.createQuery(Long.class);
    final SqmSubQuery<Tuple> subQuery = (SqmSubQuery<Tuple>) listQuery.subquery(Tuple.class);
    final SqmQuerySpec<Tuple> rootQuerySpec = ((SqmSelectStatement) rootQuery).getQuerySpec();
    final SqmQuerySpec<Tuple> subQuerySpec = rootQuerySpec.copy(SqmCopyContext.simpleContext());
    subQuery.setQueryPart(subQuerySpec);
    final Root<?> subRoot = subQuery.getRoots().iterator().next();
    subQuery.multiselect(subRoot.get("id").alias("id"));

    // listQuery.multiselect(listBuilder.count(listBuilder.literal(1))).from(subQuery);
    // instead of counting the ids, I want to get them...
    listQuery.multiselect(subRoot.get("id")).from(subQuery);

    return entityManager
        .createQuery(listQuery)
        .setFirstResult(PageableUtils.getOffsetAsInteger(pageable))
        .setMaxResults(pageable.getPageSize())
        .getResultList();
}

Unfortunately it doesn't work and I always get this error message:

org.springframework.orm.jpa.JpaSystemException: Could not locate TableGroup

Any hint would be appreciated. Thanks.

Answer 1

I would try constructing the subQuery this and try something like this by mentioning expected Type and expected entity to deal with :

import javax.persistence.criteria.*;
import javax.persistence.EntityManager;
import javax.persistence.Tuple;
import org.hibernate.query.criteria.internal.HibernateCriteriaBuilder;
import org.hibernate.query.criteria.internal.compile.CriteriaQueryTypeQueryAdapter;
import org.springframework.data.domain.Pageable;

public class YourRepository {

    private final EntityManager entityManager;

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

    protected List<Long> getIdList(CriteriaBuilder rootBuilder, CriteriaQuery<?> rootQuery, Pageable pageable) {
        // Create the main query
        CriteriaQuery<Long> listQuery = rootBuilder.createQuery(Long.class);
        Root<?> root = rootQuery.from(YourEntity.class);

        // Create the subquery
        Subquery<Long> subQuery = listQuery.subquery(Long.class);
        Root<YourEntity> subRoot = subQuery.from(YourEntity.class);
        subQuery.select(subRoot.get("id"));

        // Add conditions to the subquery if needed
        // subQuery.where(rootBuilder.equal(subRoot.get("someField"), someValue));

        // Use the subquery in the main query
        listQuery.select(root.get("id")).where(root.get("id").in(subQuery));

        // Execute the query
        return entityManager.createQuery(listQuery)
                .setFirstResult((int) pageable.getOffset())
                .setMaxResults(pageable.getPageSize())
                .getResultList();
    }
}