Search code examples
javaspring-bootjpajql

JPA Query based on child Entitiy properties

I have 4 entities MultiPlex, Screen, Screening and Movie. I want to write 2 queries.

  1. Find if a screen exists give name of screen given MultiPlexId. (Suppose a Multiples With id 1 has 4 screens, Audi-1, Audi-2, Audi-3 and Audi- 4, Query should be able to find if Audi-2 exists in Multiplex with id 1)

  2. Find All Multiplex running a given movie on a given date(Screening has a reference of screen, movie and date)

My Entities

Movie:-

@Entity
public class Movie {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    private String title;

    private double rating;
}

Multiplex:-

@Entity
public class Multiplex {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    private String name;

    @OneToMany(mappedBy = "multiplex", cascade = CascadeType.ALL)
    private List<Screen> screens;
}

Screen:-

@Entity
public class Screen {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    private String name;

    @ManyToOne
    private Multiplex multiplex;
}

Screening:-

@Entity
public class Screening {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @OneToOne
    private Screen screen;

    @OneToOne
    private Movie movie;

    private LocalDate date;

    private LocalTime time;

    private float pricePerSeat;
}

For the first Query I tried 

@Query("SELECT m FROM Multiplex m Where m.id = :mId and m.screens.name = :screenName")
    public Optional<Multiplex> findScreenByName(@Param("mId") long id, @Param("screenName") String name);

but gives me error :-

Caused by: org.hibernate.QueryException: illegal attempt to dereference collection [multiplex0_.id.screens] with element property reference [id]
    at org.hibernate.hql.internal.ast.tree.DotNode$1.buildIllegalCollectionDereferenceException(DotNode.java:58)
    at org.hibernate.hql.internal.ast.tree.DotNode.checkLhsIsNotCollection(DotNode.java:629)
    at org.hibernate.hql.internal.ast.tree.DotNode.resolve(DotNode.java:242)
    at org.hibernate.hql.internal.ast.HqlSqlWalker.resolve(HqlSqlWalker.java:1045)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.expr(HqlSqlBaseWalker.java:1290)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.exprOrSubquery(HqlSqlBaseWalker.java:4706)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.comparisonExpr(HqlSqlBaseWalker.java:4174)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:2138)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:2066)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.whereClause(HqlSqlBaseWalker.java:815)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:609)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:313)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:261)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:271)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:191)
    ... 77 common frames omitted

I am quite sure I need to use joins but I searched around ad couldn't figure it out. Any help is much appreciated.

Solution

  • m.screens is not a Screen field, but List<Screen>. So expression m.screens.name:=screenName is wrong.

    Try this

    1)

    @Query("select m from Multiplex m, Screen s where m.id=s.multiplex.id and m.id =:mId and s.name =:screenName")
    public Optional<Multiplex> findByMultiplexIdAndScreenName(@Param("mId") long id, @Param("screenName") String name);

    2)

    @Query("select distinct m from Multiplex m, Screening s where m.id=s.screen.multiplex.id and s.movie=:movie and s.date =:date")
        public List<Multiplex> findAllByMovieAndDate(@Param("movie") Movie movie, @Param("date") LocalDate date);

    or

    @Query("select distinct s.screen.multiplex from Screening s where s.movie=:movie and s.date =:date")
            public List<Multiplex> findAllByMovieAndDate(@Param("movie") Movie movie, @Param("date") LocalDate date);

    By using last one you can get exception if make decision to change FetchTypes to Lazy