如何在JPA 2.0的Criteria API中使用In-Expressions编写子查询？

Question

我已经尝试过很多次使用子查询和IN表达式来编写查询语句，但从未成功过。

每次都会出现异常，提示"关键字'IN'附近有语法错误"。查询语句的构建如下：

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之前的单词丢失了。

你曾经写过这样的查询吗？有什么建议吗？

- Keating

---

你使用的JPQL查询是什么？ - James

这里可以找到JPQL和Criteria API的示例：http://stackoverflow.com/questions/10854334/jpa2-criteria-api-select-in-select-from-where/10858479#10858479 - Techky

你缺少了 WHERE 表达式的“左侧”，就在 IN 之前。WHERE <something> IN ... - Matthias B

Answer 1

以下是使用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/，可以很好地解释这个概念。

- Nayan Wadekar

---

总体来说是+1，但使用多个subquery.where(…)语句对我没有起作用。我不得不使用subquery.where(criteriaBuilder.and(…, …))代替。似乎最后一个where语句会覆盖之前的语句。 - Chriki

@Chriki 感谢您的建议，我会根据您的评论进行编辑。这段代码就像手写的伪代码一样，所以出现这样的问题是可以预料的。 - Nayan Wadekar

这对我来说在子查询中创建了一个交叉连接 - 这正常吗？ - Sepultura

Answer 2

迟到的复活。

你的查询似乎与《Pro JPA 2：掌握Java持久化API》第259页上的查询非常相似，该查询在JPQL中读取为：

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

使用 EclipseLink + H2 数据库时，我无法使书籍的 JPQL 或相应的 criteria 正常工作。 对于这个特定的问题，我发现如果直接引用 ID 而不是让持久性提供程序自己解决，一切都按预期工作：

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

最后，为了回答你的问题，这里提供一个等价的强类型标准查询：

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

- Anthony Accioly

---

我现在是一个Rails程序员，感谢您的回答Anthony，我的问题来自书籍<<Pro JPA 2>>。我喜欢JPQL，并会时不时地从答案中学习。 - Keating

我如何在JoinTable上创建子查询？也就是说，我没有根子查询的实体。 - Zlatko

@Zlatko，打开一个问题来描述你的实体和结构。 - Anthony Accioly

谢谢，我已经找到了一些东西。 - Zlatko

@AnthonyAccioly 你愿意看一下吗？http://stackoverflow.com/questions/20557444/ - Zlatko

@Zlatko，你应该为根实体建立一个实体，这是你将子查询加入其中的原因。好处是你不需要为子查询建立实体，它只用于过滤结果。它不会提供新的可检索数据。 - Wallace Howery

Answer 3

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

- maniac787

Answer 4

如果表格A和B只能通过表格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);
    };
}

那只是另一个选择
很抱歉，这个时间节点可能不太合适，但我看到这个问题，我也想使用SELECT IN，但我甚至没有想到双重连接。 希望能对某些人有所帮助。

- Adik