Estoy tratando de realizar una consulta SQL con una unión, donde la unión tiene una condición de filtro adicional. Usando SQL simple, mis datos se modelan así:
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');Y mi consulta se ve así (solo seleccionando algunos campos por brevedad):
select food_storage.name, food.name from food_storage left outer join food on food.stored_in = food_storage.name and not food.expired;| nombre | nombre |
|---|---|
| cajón | bolsas de té |
| gabinete | arroz |
| gabinete | harina |
| refrigerador | (nulo) |
Sin embargo, estoy tratando de realizar esta consulta usando JPA y hacer que el resultado se asigne con éxito a las entidades JPA. Modelé mis entidades JPA así:
@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; }Para el resultado de la consulta SQL sin procesar anterior, necesito que el resultado se vuelva a asignar a entidades como esta:
[ 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) ]) ]Intenté usar este código:
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());Pero este es el resultado que obtengo:
[ 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 parece ignorar mi unión manual y, en su lugar, solo construye una FoodStorage por fila de resultados y, posteriormente, obtiene todos los Food por FoodStorage . ¿Cómo puedo superar este problema?
Encontré la anotación @Where de Hibernate. Desafortunadamente para mi caso del mundo real, el filtro de unión es dinámico, por lo que no puedo usarlo.
Una posible solución es usar una "unión de búsqueda" en JQPL en lugar de SQL nativo:
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);lo que resulta en:
[ 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) ]) ]lo cual creo que se debe a que hibernate es consciente del propósito de la unión y comprende correctamente cómo asignar el resultado a las entidades.
Pero esto tiene algunos inconvenientes:
left outer join , no puede obtener FoodStorage con cero Food . El uso de left outer join fetch en lugar de solo join fetch no hace ninguna diferencia. Por lo que entiendo, eso se debe a que la condición del filtro tiene que ser un filtro regular where f.expired = false , ya que JQPL no admite especificarlo como una condición de combinación como left outer join fetch fs.foodContents f on f.expired = falseselect distinct porque, de lo contrario, hibernate duplica la entidad FoodStorage para cada Food que contiene.Especialmente el último punto puede ser un factor decisivo, ya que con frecuencia me encuentro en una situación en la que JPQL no puede expresar lo que necesito expresar en 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; "; La food.* es inútil. Puede usar spring.jpa.properties.hibernate.show_sql=true para imprimir sql. La devolución food.* no reduce el número de consultas.