4

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.

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
archMage
  • 41
  • 1
  • 7

2 Answers2

4

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);
Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
3

An example that I implemented and working for me. This is not an exact code but this one should work. I have not created a reference variable in the same entity.

SQL

select right.* from MyTable left, MyTable right where left.subjectid = 7 and left.studentid = right.studentid
and left.subjectid != right.subjectid;

Criteria Code

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<MyTable]> criteria = builder.createQuery(MyTable.class);
Root<MyTable> MyTableRootLeft = criteria.from(MyTable.class);
Root<MyTable> MyTableRootRight = criteria.from(MyTable.class);
Predicate selfJoinPredicate = 
builder.and(
            builder.equal(MyTableRootLeft.get(subjectid), subjectid),
            builder.equal(MyTableRootLeft.get(studentid),MyTableRootRight.get(studentid)),
              builder.notEqual(MyTableRootLeft.get(subjectid),
MyTableRootRight.get(subjectid)));
user3092515
  • 43
  • 7