java - JPA 2 + 标准 API

Question

Employee (table)
id - int
ctd_id - int
message - char

SELECT a.*
FROM Employee a left outer join 
( select * from Employee where message = 23 ) b
on a.ctd_id = b.ctd_id
where a.message = 22 and b.id is null;

这是我尝试过的

CriteriaBuilder cb = entityManager.getCriteriaBuilder();

CriteriaQuery<Employee> criteria = cb.createQuery(Employee.class);
Root<Employee> emp = criteria.from(Employee.class); 
CriteriaQuery<Employee> sq = c.select(emp);  

Subquery<Employee> sq2 = criteria.subquery(Employee.class); 
Root<Employee> emp2 = sq2.from(Employee.class); 
Join<Employee,Employee> sqEmp = emp2.join("ctd_id", JoinType.LEFT); 
sq.select(sqemp).where(cb.equal(emp2.get("message"), cb.parameter(String.class, "23"))); 

sq.where(cb.in(path).value(sq2));
TypedQuery<Employee> q = em.createQuery(criteria);
List<Employee> employeess = q.getResultList()

但是，我无法理解应该如何在带有 where 子句的子查询上应用联接。

请帮忙。

Answer 1

JPA 不支持 FROM 子句中的子查询。

要么使用 SQL 进行查询，要么重写它以不在 from 子句中包含子查询，看起来您不需要它。