JPA Criteria Query for Left joins

Question

I am new to JPA and I have a Left Join scenario. I have my native sql query as below and I am using left join to fetch the complete records from V_MONITORING table for st.id = 10001. I have some null values for id_legislature which also needs to be selected and hence using a left join

select distinct 
    mv.id_set, mv.id_travel, mv.id_legislature
from 
   V_MONITORING mv 
     left join v_set st on mv.id_set = st.id
     left join v_travel fg on mv.id_travel = fg.id
     left join v_legislature gg on mv.id_legislature = gg.id;

The same thing I am implementing using the JPA criteria query, I am unable to fetch the null records Below is the code

Predicate predicate = cb.conjunction();
Root<MonitoringBE> mvRoot = criteriaQuery.from(MonitoringBE.class); 
mvRoot.fetch(MonitoringBE.id_set, JoinType.LEFT);
mvRoot.fetch(MonitoringBE.id_travel, JoinType.LEFT);
mvRoot.fetch(MonitoringBE.id_legislature, JoinType.LEFT);

final Path<Object> serieneinsatzterminPath= mvRoot.get(MonitoringBE.id_set);
predicate = cb.and(predicate, cb.EqualTo(serieneinsatzterminPath.get(SetBE.GUELTIG_VON_DATUM), startSetDate));

criteriaQuery.multiselect(
   mvRoot.get(MonitoringBE.id_travel).alias("id_travel"),
   mvRoot.get(MonitoringBE.id_set).alias("id_set"),
   mvRoot.get(MonitoringBE.id_legislature).alias("id_legislature"))
.distinct(true).where(predicate);

Can some one guide me.

Can you explain more clearly what behaviour your observe and how it is different from what you expect? https://stackoverflow.com/help/how-to-ask — joanis, Jan 23 '19 at 13:35

score 0 · Answer 1 · answered Jan 23 '19 at 15:43

0

You have to replace the mvRoot.get(MonitoringBE.id_travel) and others in the multiselect-statement with mvRoot.join(MonitoringBE.id_travel, JoinType.LEFT). Otherwise they will end up in a inner join.

answered Jan 23 '19 at 15:43

M.F

403
2
10

Thank you so much Martin, it work perfectly. Thank you so so much. – Phani.sumalatha Jan 23 '19 at 15:58

JPA Criteria Query for Left joins

1 Answers1