CODEWITHSUNDEEP
hibernatejpapersistencejpql
Gopi Lal
Gopi Lal

Reputation: 427

How to join two collection of an entity in JPA JPQL Query?

I have encountered in a situation where I have a entity which is having two collections lets say

@Entity
Company

@OneToMany
Collection<Cars>

@OneToMany
Collection<Scooter>

Here is my jpql

select e from com.net.company e JOIN e.cars cr where cr.carname IN ('BMW');

works fine returns the company

select e from com.net.company e JOIN e.cars cr JOIN e.scooter where cr.carname IN ('BMW');

Does not returns anything

I do understand my jpql is wrong I think the issue is with the way I am joining it can some help me on this

Upvotes: 1

Views: 2985

Answers (1)

Thorben Janssen
Thorben Janssen

Reputation: 3275

Let me start with a general comment on queries in JPQL and SQL:

Don't add any joins that you don't need. They just slow down your query. You don't use the joined cars or scooter in your query. The only effect of the join clauses is that your query only returns companies which are associated with at least 1 car and 1 scooter. But that doesn't seem to be the intended behavior.

If you want to make the joins to the scooter and cars entities optional, you need to use LEFT JOIN (but then again: why do you join the entities if you don't use them):

select e from com.net.company e LEFT JOIN e.cars cr LEFT JOIN e.scooter where cr.carname IN ('BMW');

And in case you're joining the entities to initialize the association mapping attributes of your company entity, you need to use a JOIN FETCH clause or a LEFT JOIN FETCH clause:

select e from com.net.company e LEFT JOIN FETCH e.cars cr LEFT JOIN FETCH e.scooter where cr.carname IN ('BMW');

I explain the JOIN FETCH clause and other options to initialize associations in more details here: 5 ways to initialize lazy relations and when to use them

Upvotes: 1

Related Questions