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.

Answer 2

Late resurrection.

Your query seems very similar to the one at page 259 of the book Pro JPA 2: Mastering the Java Persistence API, which in JPQL reads:

SELECT e  FROM Employee e  WHERE e IN (SELECT emp               FROM Project p JOIN p.employees emp               WHERE p.name = :project) 

Using EclipseLink + H2 database, I couldn't get neither the book's JPQL nor the respective criteria working. For this particular problem I have found that if you reference the id directly instead of letting the persistence provider figure it out everything works as expected:

SELECT e  FROM Employee e  WHERE e.id IN (SELECT emp.id                  FROM Project p JOIN p.employees emp                  WHERE p.name = :project) 

Finally, in order to address your question, here is an equivalent strongly typed criteria query that works:

CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Employee> c = cb.createQuery(Employee.class); Root<Employee> emp = c.from(Employee.class);  Subquery<Integer> sq = c.subquery(Integer.class); Root<Project> project = sq.from(Project.class); Join<Project, Employee> sqEmp = project.join(Project_.employees);  sq.select(sqEmp.get(Employee_.id)).where(         cb.equal(project.get(Project_.name),          cb.parameter(String.class, "project")));  c.select(emp).where(         cb.in(emp.get(Employee_.id)).value(sq));  TypedQuery<Employee> q = em.createQuery(c); q.setParameter("project", projectName); // projectName is a String List<Employee> employees = q.getResultList();