I am trying to use subqueries in an application I am writing using JPA 2.0 type-safe criteria API, with Hibernate 3.6.1.Final as my provider. I have no problem selecting primitive types (Long, MyEntity, etc.), but I want to select multiple columns.
Here's an example of something completely reasonable. Ignore the needless use of subquery -- it is simply meant as illustrative.
EntityManager em = getEntityManager();
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Tuple> cq = cb.createTupleQuery();
Subquery<Tuple> subQ = cq.subquery(Tuple.class);
Expression<Long> subqCount;
{
Root<MyEntity> root = subQ.from(MyEntity.class);
Path<MyEntity> filter = root.get(MyEntity.challenge);
subqCount = cb.count(root);
// How to select tuple?
Selection<Tuple> tuple = cb.tuple(filter, subqCount);
// !! Run-time exception here
Expression<Tuple> tupleExpr = (Expression<Tuple>) tuple;
// Not sure why I can't use multiSelect on a subQuery
// #select only accepts Expression<Tuple>
createSubQ.select(tupleExpr);
createSubQ.groupBy(filter);
}
cq.multiselect(subqCount);
Although the compiler doesn't complain, I still get a run-time exception.
java.lang.ClassCastException: org.hibernate.ejb.criteria.expression.CompoundSelectionImpl cannot be cast to javax.persistence.criteria.Expression
multiselect
on a subquery, then how can you perform a groupBy
?groupBy
on a subquery, why is it in the API?I have the same problem.
I can only attempt to answer your last question by saying you can only really use sub queries to perform very simple queries like:
SELECT name FROM Pets WHERE Pets.ownerID in (
SELECT ID FROM owners WHERE owners.Country = "SOUTH AFRICA"
)
The other thing I wanted to say was how much this incident reminds me of xkcd #979.