java - 基于子实体属性的 JPA 查询

Question

我有 4 个实体 MultiPlex、Screen、Screening 和 Movie。我想写 2 个查询。

1. 根据给定 MultiPlexId 的屏幕名称查找屏幕是否存在。(假设id为1的Multiples有4个屏幕，Audi-1，Audi-2，Audi-3和Audi-4，查询应该能够找到id为1的Multiples中是否存在Audi-2)

   <

2. 查找在给定日期运行给定电影的所有多厅影院(放映有屏幕、电影和日期的引用)

我的实体

电影:-

@Entity
public class Movie {

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

    private String title;

    private double rating;
}

多重:-

@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;
}

屏幕:-

@Entity
public class Screen {

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

    private String name;

    @ManyToOne
    private Multiplex multiplex;
}

筛选:-

@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;
}

对于我尝试的第一个查询

@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);

但给了我错误:-

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

我很确定我需要使用联接，但我搜索了广告无法弄清楚。非常感谢任何帮助。

Answer 1

m.screens不是Screen场，但是List<Screen> 。所以表达m.screens.name:=screenName是错误的。

试试这个

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);

或

@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);

通过使用最后一个，如果决定将 FetchTypes 更改为 Lazy，您可能会遇到异常