Question

0

116

Views

Map result of query with filtered join back to entity with relation

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.

over 4 years ago · Santiago Trujillo

2 answers

Answer question

0

Answer question

Find remote jobs

Answer 1 · 2022-03-28T17:58:22.063Z

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:

Despite the 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.
It requires a select distinct, because otherwise hibernate duplicates the FoodStorage entity for each Food it contains.
The limitations of JPQL apply, naturally.

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.

Answer 2 · 2022-03-28T17:58:22.366Z

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.

0

116

Map result of query with filtered join back to entity with relation

2 answers

0

0

Find remote jobs

Andres GPT