blocks|key|845116|text|下面是使用Criteria+API使用子查询的伪代码。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|845117|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();|code-block|syntax|javascript|845118|此外，它肯定需要一些修改，因为我已经尝试根据您的查询映射它。这里有一个链接http://www.ibm.com/developerworks/java/library/j-typesafejpa/，它很好地解释了概念。|offset|length|845119|entityMap|0|LINK|mutability|MUTABLE|url|http://www.ibm.com/developerworks/java/library/j-typesafejpa/^0|0|0|11|1P|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

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

<pre><code>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();
</code></pre>

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

blocks|key|1059624|text|晚间复活。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1059625|您的查询看起来与Pro+JPA+2:+Mastering+the+Java+Persistence+API一书第259页的查询非常相似，在JPQL语言中是这样写的：|offset|length|1059626|SELECT+e+
FROM+Employee+e+
WHERE+e+IN+(SELECT+emp
++++++++++++++FROM+Project+p+JOIN+p.employees+emp+
+++++++++++++WHERE+p.name+=+:project)|code-block|syntax|javascript|1059627|使用EclipseLink+%2B+H2数据库，我既无法获得该书的JPQL，也无法获得相应的标准。对于这个特定的问题，我发现如果你直接引用id，而不是让持久化提供者来解决，那么一切都会按预期进行：|1059628|SELECT+e+
FROM+Employee+e+
WHERE+e.id+IN+(SELECT+emp.id
+++++++++++++++++FROM+Project+p+JOIN+p.employees+emp+
++++++++++++++++WHERE+p.name+=+:project)|1059629|最后，为了解决您的问题，下面是一个等效的强类型条件查询：|1059630|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();|1059631|entityMap|0|LINK|mutability|MUTABLE|url|http://books.google.com.br/books/about/Pro_JPA_2.html?id=HyLoCKuKsjcC&redir_esc=y^0|0|8|19|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|10|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|11|8|@]|9|@$D|12|E|13|1|14]]|A|$]]|$1|F|3|G|5|H|7|15|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|16|8|@]|9|@]|A|$]]|$1|M|3|N|5|H|7|17|8|@]|9|@]|A|$I|J]]|$1|O|3|P|5|6|7|18|8|@]|9|@]|A|$]]|$1|Q|3|R|5|H|7|19|8|@]|9|@]|A|$I|J]]|$1|S|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]]|T|$U|$5|V|W|X|A|$Y|Z]]]]

Late resurrection.

Your query seems very similar to the one at page 259 of the book <a href="http://books.google.com.br/books/about/Pro_JPA_2.html?id=HyLoCKuKsjcC&amp;redir_esc=y" rel="noreferrer">Pro JPA 2:
Mastering the Java Persistence API</a>, which in JPQL reads: 

<pre><code>SELECT e 
FROM Employee e 
WHERE e IN (SELECT emp
 FROM Project p JOIN p.employees emp 
 WHERE p.name = :project)
</code></pre>

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:

<pre><code>SELECT e 
FROM Employee e 
WHERE e.id IN (SELECT emp.id
 FROM Project p JOIN p.employees emp 
 WHERE p.name = :project)
</code></pre>

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

<pre><code>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();
</code></pre>

blocks|key|845176|text|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);|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|845177|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>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);
</code></pre>

blocks|key|845218|text|如果表A+B仅通过表AB连接，则可以使用双联接。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|845219|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);
++++};
}|code-block|syntax|javascript|845220|这只是另一种选择|845221|很抱歉，我遇到了这个问题，我也想做SELECT+IN，但我甚至没有想过双重连接。我希望它能帮助一些人。|845222|entityMap^0|3|1|5|1|A|2|0|0|0|H|9|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]|$9|V|A|W|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|X|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|Y|8|@]|D|@]|E|$]]|$1|M|3|N|5|6|7|Z|8|@$9|10|A|11|B|C]]|D|@]|E|$]]|$1|O|3|-4|5|6|7|12|8|@]|D|@]|E|$]]]|P|$]]

You can use double join, if table <code>A</code> <code>B</code> are connected only by table <code>AB</code>.

<pre><code>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);
 };
}
</code></pre>

That's just an another option 
Sorry for that timing but I have came across this question and I also wanted to make <code>SELECT IN</code> but I didn't even thought about double join.
I hope it will help someone.

I have tried to write a query statement with a subquery and an <code>IN</code> 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,

<pre><code>SELECT t0.ID, t0.NAME
FROM EMPLOYEE t0
WHERE IN (SELECT ?
 FROM PROJECT t2, EMPLOYEE t1
 WHERE ((t2.NAME = ?) AND (t1.ID = t2.project)))
</code></pre>

I know the word before 'IN' lose.

Have you ever written such a query? Any suggestion?

JPA 2.0, Criteria API, Subqueries, In Expressions

数据库

我曾多次尝试编写带有子查询和IN表达式的查询语句。但我从来没有成功过。我总是得到一个异常，“Syntax error near keyword 'IN‘"，查询语句是这样构建的，SELECT t0.ID, t0.NAMEFROM EMPLOYEE t0WHERE IN (SELECT ?          FROM P...

问JPA 2.0，标准API，子查询，在表达式中
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问JPA 2.0，标准API，子查询，在表达式中EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问JPA 2.0，标准API，子查询，在表达式中
EN