how to select values by joining same table using criteria builder in JPA or Hibernate

Question

We have below employee data in our table and we need to find all employees where flag is false and if the reporting person flag is true we should exclude manger even if this flag is false

Sample Data

 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

Output should be

 id  name  manager_name flag

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

How to achieve the above requirement using Hibernate Criteria builder?

In SQL this is achieved using below query

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

Add to the Employee class --->

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

The criteria Query --->

 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 Query generated --->

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