LEFT Join not Working with JPA, CriteriaQuery
0 Votes
270 Views
I have two tables with one-to-many
association:
Student Subject
------------ ----------------------
ID|name|age ID | Student_FK | name
------------ ----------------------
A Student can have any number of subjects associated with him. (no subjects too). Now I want to get all students with
subject.name = ‘PHYSICS’ OR no subject at all (NULL)
I am using QueryBuilder and predicates to generate the query, following is the code snippet.
CriteriaBuilder cb = mEntityManager.getCriteriaBuilder();
CriteriaQuery cq = cb.createQuery(mEntityClass);
Root root = cq.from(mEntityClass);
cq.select(root);
List<String> subjectList = new ArrayList<>();
subjectList.add("PHYSICS");
Join subjectJoin= root.join("subjects", JoinType.LEFT);
Predicate predicate1 = subjectJoin.get("name").in(subjectList);
Predicate predicate2 = subjectJoin.get("name").isNull();
Predicate predicate = cb.or(predicate1,predicate2);
predicates.add(predicate);
cq.where(predicates.toArray(new Predicate[] {}));
When I do a mEntityManager.createQuery(cq).getResultList()
. I get Error as –
old style outer join (+) cannot be used with ANSI joins
So my question is –
- How to get a LEFT join using CritariaQuery ?
- Is there some other way to get all Students list (both with subjects/ without Subject) ?