CODEWITHSUNDEEP
databasejpajoinentity
Dimitri Dewaele
Dimitri Dewaele

Reputation: 10689

JPA - left join 2 tables without association

I have 2 tables/entities with no association. I can go for cross joins in JPA

FROM A as a, B as b WHERE b.col1=a.col1

How Can I perform a left-join?

I want all values from A and fill them up with B values where possible and leave NULL where there is no B.

Does not work:

FROM A as a LEFT JOIN B as b WHERE b.col1=a.col1

Path expected for join!

Invalid path: 'b.col1'

Upvotes: 13

Views: 21204

Answers (5)

Tom B
Tom B

Reputation: 11

You can do this using JPQL:

FROM A a LEFT JOIN B b ON b.col1=a.col1

Upvotes: 1

nevster
nevster

Reputation: 6465

Apparently you can now do this as of Hibernate 5.1

Upvotes: 0

Mohammad
Mohammad

Reputation: 61

Regarding Dimitri Dewaele's answer, this query is a CARTESIAN JOIN. The CARTESIAN JOIN or CROSS JOIN returns the Cartesian product of the sets of records from two or more joined tables. Thus, it equates to an inner join where the join-condition always evaluates to either True or where the join-condition is absent from the statement. If you want to read more about it, follow the link below: Sql Cartesian Joins

Upvotes: 0

Dimitri Dewaele
Dimitri Dewaele

Reputation: 10689

This is possible with JPA with following query:

FROM A as a, B as b WHERE b.col1=a.col1 OR b.col1 IS NULL

Upvotes: -5

V G
V G

Reputation: 19002

You cannot do that in JPA, as long as there is no relationship between the entities.

Solutions:

  1. Use Native queries.
  2. Add a relationship between them (eventually an indirect lazy one). With indirect I mean something like : A knows PseudoEntity, PseudoEntity knows B (but the relationship owner is entity B), B knows PseudoEntity.

Upvotes: 10

Related Questions