CODEWITHSUNDEEP
jpasubquerycriteriajpa-2.0criteria-api
logan
logan

Reputation: 3365

Tuple result Criteria API subquery

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

Upvotes: 8

Views: 10660

Answers (3)

John Dzikunu
John Dzikunu

Reputation: 11

Subquery<String> duplicateUserSubquery = cQuery.subquery(String.class);
Root<User> subUser = duplicateUserSubquery.from(User.class);


Expression concat = builder.concat(subUser.get("firstname"), subUser.get("surname"));
 duplicateUserSubquery.select(concat)
 .where(resultParams.toArray(new Predicate[0]))
 .groupBy(subUser.get("firstname"), subUser.get("surname"))
 .having(builder.gt(builder.count(subUser), 1));
 Predicate duplicateCondition = builder.and(
 builder.concat(queryRootUser.get("firstname"), queryRootUser.get("surname")).in(duplicateUserSubquery)
);
resultParams.add(duplicateCondition);

Upvotes: 0

Yan Khonski
Yan Khonski

Reputation: 13083

I had similar problem. I had specification, and I wanted to get ids of objects matching this specification.

My solution:

    CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
    CriteriaQuery<Tuple> tupleCriteriaQuery = criteriaBuilder.createTupleQuery();

    Root<Issue> root = tupleCriteriaQuery.from(Issue.class);
    tupleCriteriaQuery = tupleCriteriaQuery.multiselect(root.get(IssueTable.COLUMN_ID));//select did not work.
    tupleCriteriaQuery = tupleCriteriaQuery.where(issueFilter.toPredicate(root, tupleCriteriaQuery, criteriaBuilder));

    List<Tuple> tupleResult = em.createQuery(tupleCriteriaQuery).getResultList();

First I select columns (In my case I need only one column), and then I call where method to merge with my given specification.

Upvotes: 0

rcyza
rcyza

Reputation: 80

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.

Upvotes: 5

Related Questions