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