Why in native query Hibernate lazy load's children entities?

user_7491837 0
Viewed 3305

I don't understand it, when I use JPQL with JOIN fetch hibernate should do one query to join children entities, but when I want to use native query and join all children with one query hibernate still lazy loads children in other queries. I'm using Spring Data 2.

What should I do to avoid Lazy loading or n+1 queries with using native query?

Example:

@Query(value = "SELECT recipe.*, r_ing.*, ing.* FROM recipe recipe join " +
        " on recipe.id = r.recipe_id " +
        " LEFT JOIN recipe_ingredients r_ing on r.recipe_id = r_ing.recipe_id " +
        " LEFT JOIN ingredient ing on r_ing.ingredient_id = ing.id where ing.names in (:ingredientsNames)",
        countQuery = "SELECT count(*) FROM recipe recipe join " +
                " on recipe.id = r.recipe_id " +
                " LEFT JOIN recipe_ingredients r_ing on r.recipe_id = r_ing.recipe_id " +
                " LEFT JOIN ingredient ing on r_ing.ingredient_id = ing.id where ing.names in (:ingredientsNames)",
        nativeQuery = true
)
Page<Recipe> findAllByIngredientsNames(List<String> ingredientsNames, Pageable page);

Entieties:

@Entity
public class Recipe {
    @OneToMany(mappedBy = "recipe", cascade = CascadeType.ALL, orphanRemoval = true)
    private List<RecipeIngredients> ingredients;
}
@Entity
public class RecipeIngredients implements Serializable {

    @EmbeddedId
    private RecipeIngredientsId recipeIngredientsId;

    @ManyToOne(fetch = FetchType.LAZY)
    @MapsId("recipeId")
    private Recipe recipe;

    @ManyToOne(fetch = FetchType.LAZY, cascade = {CascadeType.PERSIST, CascadeType.MERGE})
    @MapsId("ingredientId")
        private Ingredient ingredient;
}

@Entity
public class Ingredient {

    @NaturalId
    @Column(unique = true)
    private String name;
}
spring-data-jpa hibernate
1 Answers

For a native query, Hibernate does not know how to map advanced data. In your case, you have a request to fetch the Recipe entity, where the entity mapper knows how to extract results from SELECT * FROM recipe. But ingredients property is the reverse mapping, which is implemented as a lazy init collection, with query behind. That what JPA and Spring data made for you, but they are not smart enough to automagically understand and map it further to eagerly map the query results to a collection property.

Also, I guess you have seen in the results of your query multiple same Recipe entities.

If for whatever reason you really want to deal with native queries, so just use those properly: results of the native query are in general not a JPA managed entities, but rather projections.

So create a specific projection of the row you are having in your native query:

public class FullRecipeProjection {
    private final Integer recipeId; 
    private final Integer recipeIngredientsId;
    private final Integer ingredientId
    private final Integer ingredientName 

    /* Full-arg-constructor */
    public FullRecipeProjection (Integer recipeId, Integer recipeIngredientsId, Integer ingredientId, String ingredientName) {...}

}

Then you can create your query:

@Query(value = "SELECT new FullRecipeProjection(recipe.recipeId, r_ing.recipeIngredientsId, ing.ingredientId, ing.IngredientName) FROM recipe recipe join " +
        " on recipe.id = r.recipe_id " +
        " LEFT JOIN recipe_ingredients r_ing on r.recipe_id = r_ing.recipe_id " +
        " LEFT JOIN ingredient ing on r_ing.ingredient_id = ing.id where ing.names in (:ingredientsNames)",
        countQuery = "SELECT count(*) FROM recipe recipe join " +
                " on recipe.id = r.recipe_id " +
                " LEFT JOIN recipe_ingredients r_ing on r.recipe_id = r_ing.recipe_id " +
                " LEFT JOIN ingredient ing on r_ing.ingredient_id = ing.id where ing.names in (:ingredientsNames)",
        nativeQuery = true
)
List<FullRecipeProjection> findAllByIngredientsNames(List<String> ingredientsNames);

Then you can transform the collection of FullRecipeProjection to a similar object of your Recipe: 

public class FullRecipe {
    private final Integer recipeId;
    private final Set<IngredientProjection> ingredients;
    public FullRecipe(Integer recipeId, Set<IngredientProjection> ingredients) {...}
}

public class IngredientProjection {
    private final Integer ingredientId;
    private final String ingredientName;
    public IngredientProjection(Integer ingredientId, String ingredientName) {...}
}

And then you can get what you want like this: 

final List<FullRecipeProjection> data = repository.findAllByIngredientsNames(ingredientsNames);

final List<FullRecipe> results = data
    .stream()
    // extracting distinct identities of recipes, you have fetched  
    .map(FullRecipeProjection::recipeId)
    .distinct()
    // now we have unique key for the data and can map it
    .map(it -> 
         new FullRecipe(
             it, 
             // extracting all ingredients, which  were fetched in rows with references to recipe.
             data
                 .stream()
                 .filter(o -> o.recipeId.equals(it))
                 .map(ing -> new IngredientProjection(ing.ingredientId, ing.ingredientName))
                 .collect(Collectors.toSet())
    .collect(Collectors.toList()) ;

Quite a long way. But that is how it works. And when you use JPQL queries this long processing is done by Hibernate.

And note: pagination becomes a cumbersome operation for this kind of data extraction: In a way you have specified, you will paginate not the final result, but the FullRecipeProjection, which may result in a not-complete Recipe fetch, and for sure in badly paged data (it may contain only 1 FullRecipe, which in place could be not fully loaded!).

user_4509510
Related