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

User · Answer

CriteriaBuilder criteriaBuilder   em getCriteriaBuilder    CriteriaQuery lt Employee gt  criteriaQuery   criteriaBuilder createQuery Employee class   Root lt Employee gt  empleoyeeRoot   criteriaQuery from Employee class    Subquery lt Project gt  projectSubquery   criteriaQuery subquery Project class   Root lt Project gt  projectRoot   projectSubquery from Project class   projectSubquery select projectRoot    Expression lt String gt  stringExpression   empleoyeeRoot get Employee  ID   Predicate predicateIn   stringExpression in projectSubquery    criteriaQuery select criteriaBuilder count empleoyeeRoot   where predicateIn

User · Answer

Below is the pseudo-code for using sub-query using Criteria API       CriteriaBuilder criteriaBuilder   entityManager getCriteriaBuilder    CriteriaQuery lt Object gt  criteriaQuery   criteriaBuilder createQuery    Root lt EMPLOYEE gt  from   criteriaQuery from EMPLOYEE class   Path lt Object gt  path   from get  compare field       field to map with sub-query from fetch  name    from fetch  id    CriteriaQuery lt Object gt  select   criteriaQuery select from    Subquery lt PROJECT gt  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 lt Object gt  typedQuery   entityManager createQuery select   List lt Object gt  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

User · Answer

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 lt Employee gt  c   cb createQuery Employee class   Root lt Employee gt  emp   c from Employee class    Subquery lt Integer gt  sq   c subquery Integer class   Root lt Project gt  project   sq from Project class   Join lt Project  Employee gt  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 lt Employee gt  q   em createQuery c   q setParameter  project   projectName      projectName is a String List lt Employee gt  employees   q getResultList

User · Answer

You can use double join  if table A B are connected only by table AB   public static Specification lt A gt  findB String input        return  Specification lt A gt    root  cq  cb  - gt            Join lt A AB gt  AjoinAB   root joinList A  AB LIST JoinType LEFT           Join lt AB B gt  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

[jpa] JPA 2.0, Criteria API, Subqueries, In Expressions

Examples related to jpa

Examples related to subquery

Examples related to jpa-2.0

Examples related to criteria-api

Examples related to in-subquery