如何在JPA或Hibernate中使用criteria builder连接同一个表来选择值

Question

我们的表中有以下员工数据，我们需要找到标志为false的所有员工，如果报告人标志为true，即使该标志为false，我们也应该排除经理

样本数据

 id  name  manager_name flag

 1   a     null         false
 2   b     a            true
 3   c     d            false
 4   e     null         false
 5   f     e            false

输出应为

 id  name  manager_name flag

 3   c     d            false
 4   e     null         false
 5   f     e            false

如何使用Hibernate Criteria builder实现上述要求？

在SQL中，这是使用下面的查询实现的

select * from employee where flag = false and id not in (
        select e1.id from employee e1, employee e2 where e1.name = e2.manager_name 
     and e2.flag= true) 

Answer 1

添加到Employee类---

 @ManyToOne(fetch = FetchType.LAZY)
 @JoinColumn(name="managerName", referencedColumnName="name",insertable=false, 
 updatable=false)
 private Employee manager;

条件查询---

 CriteriaBuilder cb  =  em.getCriteriaBuilder() ;
 CriteriaQuery<Employee> cq = cb.createQuery(Employee.class);
 Root<Employee> root =  cq.from(Employee.class);

 Subquery<Integer> sub = cq.subquery(Integer.class);
 Root<Employee> subRoot = sub.from(Employee.class);
 Join <Employee,Employee> empJoin = subRoot.join("manager",JoinType.INNER);
 Predicate subP2 = cb.equal(subRoot.get("flag"),true);
 sub.where(subP2);
 sub.select(empJoin.get("id"));

 Predicate flagP =  cb.equal(root.get("flag"),false);
 Predicate idNot  = cb.not(root.get("id").in(sub)) ;
 Predicate finalP  = cb.and(flagP,idNot);
 cq.where(finalP);

 TypedQuery<Employee>query = em.createQuery(cq.select(root));
 List<Employee> result = query.getResultList();

Hibernate查询生成---

select
    employee0_.id as id1_0_,
    employee0_.flag as flag2_0_,
    employee0_.manager_name as manager_3_0_,
    employee0_.name as name4_0_ 
from
    employee employee0_ 
where
    employee0_.flag=? 
    and (
        employee0_.id not in  (
            select
                employee2_.id 
            from
                employee employee1_ 
            inner join
                employee employee2_ 
                    on employee1_.manager_name=employee2_.name 
            where
                employee1_.flag=?
        )
    )