[FIXED] JPQL Join on one to many database

Issue

I know that it has been mentioned couple of times here before, but I really can't make it working for me,

I have two entities: Recipe, Ingredient:

@Entity
@Table(name = "Recipe")
public class Recipe {

    @Id
    @GeneratedValue
    @Column(name = "Recipe_id")
    private Long id;

    private String name;

    private String description;
    @Lob
    @Basic(fetch = FetchType.LAZY)
    @Column(length = 100000)
    private byte[] image;

    @OneToMany(mappedBy = "recipe", fetch = FetchType.EAGER)
    @Cascade({CascadeType.ALL})
    private List<Ingredient> ingredientsList;
....
}

and Ingredient:

@Entity
@Table
public class Ingredient {

    @Id
    @GeneratedValue
    private Long id;

    private String name;

    private int cpt;

    private String cptyType;

    @ManyToOne
    @JoinColumn(name = "Recipe_id")
    private Recipe recipe;

....
}

I have also set up JPA Reposiotries, I would like to create custom query which would be equivalent to:

SELECT *
FROM `Recipe`
INNER JOIN `Ingredient` ON Recipe.Recipe_id = Ingredient.Recipe_id
WHERE Ingredient.name = "fancyName"
LIMIT 0 , 30

Until now I've tried with this one:

@Query("Select r from Recipe r join r.id i  where i.name = :ingredient")
List<Recipe> findRecipeByIngredient(@Param("ingredient") String ingredient);

End up with expcetion:

Caused by: java.lang.NullPointerException
        at org.hibernate.hql.internal.ast.HqlSqlWalker.createFromJoinElement(HqlSqlWalker.java:395)
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.joinElement(HqlSqlBaseWalker.java:3477)
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElement(HqlSqlBaseWalker.java:3263)
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:3141)
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromClause(HqlSqlBaseWalker.java:694)
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:550)
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:287)
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:235)
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:248)
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:183)
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136)
        at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
        at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
        at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:119)
        at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:214)
        at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:192)

I've tried with thing such as this:

@Query("Select r from Recipe r join fetch r.ingredientsList where r.name = :ingredient")
    List<Recipe> findRecipeByIngredient(@Param("ingredient") String ingredient);

this is not resulting in any errors, but returns empty result.

It is a trival issue, but I don't have any experience with jpql before = /

EDIT:

still getting empty result:

DEBUG (SqlStatementLogger.java:104) - select recipe0_.Recipe_id as Recipe1_1_0_, ingredient1_.id as id0_1_, recipe0_.description as descript2_1_0_, recipe0_.image as image1_0_, recipe0_.name as name1_0_, ingredient1_.cpt as cpt0_1_, ingredient1_.cptyType as cptyType0_1_, ingredient1_.name as name0_1_, ingredient1_.Recipe_id as Recipe5_0_1_, ingredient1_.Recipe_id as Recipe5_1_0__, ingredient1_.id as id0__ from Recipe recipe0_ inner join Ingredient ingredient1_ on recipe0_.Recipe_id=ingredient1_.Recipe_id where ingredient1_.name=?
DEBUG (CollectionLoadContext.java:224) - No collections were found in result set for role: com.bla.model.Recipe.ingredientsList

EDIT 2:

after removing fetch from statement:

DEBUG (SqlStatementLogger.java:104) - select recipe0_.Recipe_id as Recipe1_1_, recipe0_.description as descript2_1_, recipe0_.image as image1_, recipe0_.name as name1_ from Recipe recipe0_ inner join Ingredient ingredient1_ on recipe0_.Recipe_id=ingredient1_.Recipe_id where ingredient1_.name=?
DEBUG (StatefulPersistenceContext.java:899) - Initializing non-lazy collections

Solution

Your last query searches all the recipes whose name is the ingredient name passed as argument. That's not what you want. What you want is all the recipes having an ingredient whose name is the ingredient name passed as argument:

select r from Recipe r
join r.ingredientList i
where i.name = :ingredient

Side note: why can't I have two recipes using the same ingredients? Quite limiting. The association should be a ManyToMany.

Answered By - JB Nizet