JPA 2.0，标准 API，子查询，表达式 [英] JPA 2.0, Criteria API, Subqueries, In Expressions

问题描述

我多次尝试编写带有子查询和 IN 表达式的查询语句.但我从来没有成功过.

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

我总是得到异常，关键字'IN'附近的语法错误"，查询语句是这样构建的，

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)))

我知道IN"输之前的那个词.

I know the word before 'IN' lose.

你写过这样的查询吗?有什么建议吗?

Have you ever written such a query? Any suggestion?

推荐答案

下面是使用 Criteria API 使用子查询的伪代码.

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();

此外，它肯定需要一些修改，因为我已尝试根据您的查询对其进行映射.这是一个链接 http://www.ibm.com/developerworks/java/library/j-typesafejpa/ 很好地解释了概念.

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.

这篇关于JPA 2.0，标准 API，子查询，表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！