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 is missing.
Have you ever written such a query? Any suggestion?
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.
subquery.where(…) statements didn’t work for me. I had to use subquery.where(criteriaBuilder.and(…, …)) instead. It seems that the last where statement overwrites the previous.
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(); 
CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder(); CriteriaQuery<Employee> criteriaQuery = criteriaBuilder.createQuery(Employee.class); Root<Employee> empleoyeeRoot = criteriaQuery.from(Employee.class); Subquery<Project> projectSubquery = criteriaQuery.subquery(Project.class); Root<Project> projectRoot = projectSubquery.from(Project.class); projectSubquery.select(projectRoot); Expression<String> stringExpression = empleoyeeRoot.get(Employee_.ID); Predicate predicateIn = stringExpression.in(projectSubquery); criteriaQuery.select(criteriaBuilder.count(empleoyeeRoot)).where(predicateIn); You can use double join, if table A B are connected only by table AB.
public static Specification<A> findB(String input) { return (Specification<A>) (root, cq, cb) -> { Join<A,AB> AjoinAB = root.joinList(A_.AB_LIST,JoinType.LEFT); Join<AB,B> ABjoinB = AjoinAB.join(AB_.B,JoinType.LEFT); return cb.equal(ABjoinB.get(B_.NAME),input); }; } That's just an another option
Sorry for that timing but I have came across this question and I also wanted to make SELECT IN but I didn't even thought about double join. I hope it will help someone.
