JPA SQL Subquery won't work - method undefined

Question

Having a query in MySql like this:

SELECT * FROM runninggame WHERE game = 6 AND spieler IN (SELECT id FROM spieler WHERE tormann = 1);

The Query runs fine in MySQL and I need a similar query in my RunninggameDAO.java. I tried this in the JPA-SQL-Editor of Rapidclipse:

findGoaliesInGame (Game game) {
select * from Runninggame where game = :game and spieler in (select id from Spieler where tormann = 1) order by spieler

}

The JPA-SQL-Editor shows no error, but in the Java-Tab of the DAO-Class there's an error in the line "subquery.multiselect(subqueryRoot.get(Spieler_.id));" => "The method multiselect(subqueryRoot.get(Spieler_.id)) is undefined for the type Subquery"

public List<Runninggame> findGoaliesInGame(final Game game) {
    final EntityManager entityManager = em();
    final CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
    final ParameterExpression<Game> gameParameter = criteriaBuilder.parameter(Game.class, "game");
    final CriteriaQuery<Runninggame> criteriaQuery = criteriaBuilder.createQuery(Runninggame.class);
    final Subquery<Integer> subquery = criteriaQuery.subquery(Integer.class);
    final Root<Spieler> subqueryRoot = subquery.from(Spieler.class);
    subquery.multiselect(subqueryRoot.get(Spieler_.id));
    subquery.where(criteriaBuilder.equal(subqueryRoot.get(Spieler_.tormann), criteriaBuilder.literal(1)));
    final Root<Runninggame> root = criteriaQuery.from(Runninggame.class);
    criteriaQuery.where(criteriaBuilder.and(criteriaBuilder.equal(root.get(Runninggame_.game), gameParameter),  root.get(Runninggame_.spieler).in(subquery)));
    criteriaQuery.orderBy(criteriaBuilder.asc(root.get(Runninggame_.spieler)));
    final TypedQuery<Runninggame> query = entityManager.createQuery(criteriaQuery);
    query.setParameter(gameParameter, game);
    return query.getResultList();
}

This code is generated by Rapidclipse and can't be changed, so how can I manage this query in Rapidclipse? Any help is appreciated.

Answer 1

There seems to be an issue with this kind of subselect, so I have created an issue for it.

But the shorthand version for this query seems to work just fine:

findGoaliesInGame(Game game) {
    from Runninggame
    where game = :game
        and spieler.tormann = 1
    order by spieler
}

I hope this helped!