I am trying to perform an SQL query with a join, where the join has an additional filter condition. Using plain SQL my data is modeled like this:
create table food_storage (
name varchar primary key
);
create table food (
name varchar primary key,
expired bool,
stored_in varchar references food_storage(name)
);
insert into food_storage (name) values ('drawer'), ('fridge'), ('cabinet');
insert into food
(name, expired, stored_in)
values
('teabags', false, 'drawer'),
('beans', true, 'drawer'),
('leftovers', true, 'fridge'),
('rice', false, 'cabinet'),
('flour', false, 'cabinet');
And my query looks like this (only selecting a few fields for brevity):
select food_storage.name, food.name from food_storage
left outer join food on food.stored_in = food_storage.name and not food.expired;
| name | name |
|---|---|
| drawer | teabags |
| cabinet | rice |
| cabinet | flour |
| fridge | (null) |
However, I am trying to perform this query using JPA, and have the result successfully map back to JPA entities. I modeled my JPA entities like this:
@Entity
@Table(name = "food_storage")
@Data
public class FoodStorage {
@Id
private String name;
@OneToMany(mappedBy = "stored_in")
private List<Food> foodContents;
}
@Entity
@Table(name = "food")
@Data
public class Food {
@Id
private String name;
@Column
private boolean expired;
@Column(name = "stored_in")
private String storedIn;
}
For the raw SQL query result above, I need the result to be mapped back into entities like this:
[
FoodStorage(name=drawer, foodContents=[
Food(name=teabags, expired=false, storedIn=drawer)
]),
FoodStorage(name=fridge, foodContents=[]),
FoodStorage(name=cabinet, foodContents=[
Food(name=rice, expired=false, storedIn=cabinet),
Food(name=flour, expired=false, storedIn=cabinet)
])
]
I tried using this code:
String sql = """
select food_storage.*, food.* from food_storage
left outer join food on food.stored_in = food_storage.name and not food.expired;
""";
Query query = entityManager.createNativeQuery(sql, FoodStorage.class);
System.out.println(query.getResultList());
But this is the result I get:
[
FoodStorage(name=drawer, foodContents=[
Food(name=teabags, expired=false, storedIn=drawer),
Food(name=beans, expired=true, storedIn=drawer)
]),
FoodStorage(name=fridge, foodContents=[
Food(name=leftovers, expired=true, storedIn=fridge)
]),
FoodStorage(name=cabinet, foodContents=[
Food(name=rice, expired=false, storedIn=cabinet),
Food(name=flour, expired=false, storedIn=cabinet)
]),
FoodStorage(name=cabinet, foodContents=[
Food(name=rice, expired=false, storedIn=cabinet),
Food(name=flour, expired=false, storedIn=cabinet)
])
]
Hibernate appears to ignore my manual join, and instead just constructs one FoodStorage-instance per result row and subsequently fetches all Food per FoodStorage. How can I overcome this problem?
I have found Hibernate's @Where annotation. Unfortunately for my real-world case the join filter is dynamic, so I cannot use it.
One possible solution is to use a "fetch join" in JQPL instead of native SQL:
var query = entityManager.createQuery(
"""
select distinct fs from FoodStorage fs
join fetch fs.foodContents f
where f.expired = false
""", FoodStorage.class);
List<FoodStorage> result = query.getResultList();
System.out.println(result);
which results in:
[
FoodStorage(name=cabinet, foodContents=[
Food(name=rice, expired=false, storedIn=cabinet),
Food(name=flour, expired=false, storedIn=cabinet)
]),
FoodStorage(name=drawer, foodContents=[
Food(name=teabags, expired=false, storedIn=drawer)
])
]
which I believe is because then hibernate is aware of the join's purpose and correctly understands how to map the result back to entities.
But this has a few drawbacks:
left outer join it fails to fetch FoodStorage with zero Food. Using left outer join fetch instead of just join fetch makes no difference.
As far as I understand that is because the filter condition has to be a regular filter where f.expired = false, since JQPL does not support specifying it as a join-condition like left outer join fetch fs.foodContents f on f.expired = false.select distinct, because otherwise hibernate duplicates the FoodStorage entity for each Food it contains.Especially the last point can be a dealbreaker, since I frequently find myself in a situation where JPQL cannot express what I need to express in SQL.
String sql = "select distinct food_storage.* from food_storage left outer join food on food.stored_in = food_storage.name and not food.expired; ";
The food.* is useless.You can use spring.jpa.properties.hibernate.show_sql=true to print sql. Returning food.* does not reduce the number of queries.