1

Suppose we have the following five DB tables: Employee, Client, Month, Onsite and Payment.

Given the following (Oracle) SQL query:

select * from onsite o
join client c on o.client_id = c.client_id
left join payment p on (o.client_id = p.client_id and o.employee_id = p.employee_id and o.month_cd = p.month_cd)
where p.payout_date is null and c.name = 'example';

It joins three tables. The first, inner join is simple, client_id is primary key in the Client table and foreign key in Onsite. Supposing we have the EmployeeEntity_, ClientEntity_, MonthEntity_, OnsiteEntity_ and PaymentEntity_ metamodel classes, it looks like this with Criteria API:

EntityManager em = ...;
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<OnsiteEntity> cq = cb.createQuery(OnsiteEntity.class);
Root<OnsiteEntity> root = cq.from(OnsiteEntity.class);
Join<OnsiteEntity, ClientEntity> client = root.join(OnsiteEntity_.client);

Both the Onsite and Payment tables contain three foreign keys to the primary keys of the Employee, Client and Month tables. The left join joins the Onsite and Payment tables on these three foreign keys.

The question is how to express the left join of the two unconnected tables with the JPA Criteria API using the metamodel classes?

codebliss
  • 31
  • 6
  • I had a very similar problem, and this answer (https://stackoverflow.com/a/46814689/10393763) provided the answer. Define multiple Root objects and join them in the where clause. – Nathan Karasch Jan 12 '21 at 21:51

1 Answers1

0

You can use JoinType.LEFT as join parameter (https://en.wikibooks.org/wiki/Java_Persistence/Criteria#JoinType) 

root.join(OnsiteEntity_.client, JoinType.LEFT);
tvazac
  • 536
  • 2
  • 7
  • 21
  • The question is how to express the left join of the two unrelated tables (Onsite, Payment) in Criteria API. Can you write a concrete example? – codebliss Jan 25 '15 at 19:36
  • Oh sorry, I didn't see it. Can you post code of your entities? – tvazac Jan 25 '15 at 20:51