Self join in criteria query

Question

so I have a table called 'User' having fields as

• userId
• userName
• supervisorId

I want to fetch the userName of the supervisor of a particular user.

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<User> criteriaQuery = criteriaBuilder.createQuery(User.class);
Root<User> rootUser = criteriaQuery.from(User.class);
List<Predicate> predList = new ArrayList<Predicate>();
Join<User, User> selfJoin = rootUser.join("userId", JoinType.LEFT); //not sure about this line
predList.add(criteriaBuilder.equal(selfJoin.<String>get("userId"), supervisorId)); //supervisorId is the id of the supervisor that I want to find
TypedQuery<User> typedQuery = em.createQuery(criteriaQuery);
List<User> resultList = typedQuery.getResultList();

Now I have many other conditions too beside this supervisorId. So I have to use the same criteria query.

Thanks.

Answer 1

Criteria API is for when you need to build queries dynamically, and you should always remember that it's based on traversing entities.

What you need there is a plain SQL query like this:

select sp.userName
from users u
join users sp on sp.userId = u.supervisorId
where userId = ?

Just because you use JPA and Hibernate, it does not mean that you should not use SQL queries.

The Criteria API query can be written fairly easy if you map the supervisor as a @ManyToOne association:

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name="supervisorId", referencedColumnName="userId")
private User supervisor;

Then, the join becomes:

Join<User, User> selfJoin = rootUser.join("supervisor", JoinType.LEFT);