Not able to set from clause with subquery in Hibernate 6-CodePudding

I have the below piece of the code to get count query form the original query.

But this is the line causing the issue at compile time. countQuery.from(sqmSubQuery);

CriteriaBuilder builder = em.getCriteriaBuilder();
CriteriaQuery<Long> countQuery = builder.createQuery(Long.class);
SqmSubQuery sqmSubQuery = (SqmSubQuery<Tuple>) countQuery.subquery(Tuple.class);
SqmSelectStatement sqmOriginalQuery = (SqmSelectStatement) query;
SqmQuerySpec sqmOriginalQuerySpec = sqmOriginalQuery.getQuerySpec();
sqmSubQuery.setQueryPart(sqmOriginalQuerySpec.copy(SqmCopyContext.simpleContext()));

Root<T> subQuerySelectRoot = (Root<T>) sqmSubQuery.getRoots().iterator().next();
sqmSubQuery.multiselect(subQuerySelectRoot.get("id").alias("id"));

countQuery.select(builder.count(builder.literal(1)));
countQuery.from(sqmSubQuery);

CodePudding user response：

Based on you comment you want to select the distinct count of all employee types. The query you provided should be equivalent to SELECT COUNT(DISTINCT employee_type) FROM Employee.

This can be written in JPA as shown below:

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Long> countQuery = builder.createQuery(Long.class);
Root<Employee> employeeRoot = countQuery.from(Employee.class);
countQuery.select(builder.countDistinct(employeeRoot.get("type")));
Long count = entityManager.createQuery(countQuery).getSingleResult();

where type is the name of the property that maps to the column employee_type

CodePudding user response：

The type org.hibernate.query.criteria.JpaSelectCriteria declares this method:

<X> JpaDerivedRoot<X> from(jakarta.persistence.criteria.Subquery<X> subquery);

which is the one you need to call if you're trying to use a subquery in the from clause.

And SqmSelectStatement implements JpaSelectCriteria. (It is also the object which implements jakarta.persistence.criteria.CriteriaQuery.)

So you can cast any CriteriaQuery to JpaSelectCriteria and call from():

CriteriaQuery<Thing> query = ... ;
Subquery<OtherThing> subquery = ... ;
((JpaSelectCriteria<Thing>) query).from(subquery);

or whatever (I did not test this code).