JPA 2.0, Criteria API, Subqueries, In Expressions

Question

I have tried to write a query statement with a subquery and an IN expression for many times. But I have never succeeded.

I always get the exception, " Syntax error near keyword 'IN' ", the query statement was build like this,

SELECT t0.ID, t0.NAME
FROM EMPLOYEE t0
WHERE IN (SELECT ?
          FROM PROJECT t2, EMPLOYEE t1
          WHERE ((t2.NAME = ?) AND (t1.ID = t2.project)))

I know the word before 'IN' lose.

Have you ever written such a query? Any suggestion?

Answer 1

Below is the pseudo-code for using sub-query using Criteria API.

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Object> criteriaQuery = criteriaBuilder.createQuery();
Root<EMPLOYEE> from = criteriaQuery.from(EMPLOYEE.class);
Path<Object> path = from.get("compare_field"); // field to map with sub-query
from.fetch("name");
from.fetch("id");
CriteriaQuery<Object> select = criteriaQuery.select(from);

Subquery<PROJECT> subquery = criteriaQuery.subquery(PROJECT.class);
Root fromProject = subquery.from(PROJECT.class);
subquery.select(fromProject.get("requiredColumnName")); // field to map with main-query
subquery.where(criteriaBuilder.and(criteriaBuilder.equal("name",name_value),criteriaBuilder.equal("id",id_value)));

select.where(criteriaBuilder.in(path).value(subquery));

TypedQuery<Object> typedQuery = entityManager.createQuery(select);
List<Object> resultList = typedQuery.getResultList();

Also it definitely needs some modification as I have tried to map it according to your query. Here is a link http://www.ibm.com/developerworks/java/library/j-typesafejpa/ which explains concept nicely.