休眠NOT IN子查询 [英] Hibernate NOT IN subquery

问题描述

我有一些与SeminarLectures具有多对多关系的房间. 我想获取在给定日期没有演讲的所有会议室.

I have Rooms with a Many-To-Many relationship to SeminarLectures. I want to get all Rooms that have no lecture at a given date.

为什么这个休眠查询不起作用?

Why is this hibernate query not working?

SELECT r FROM Room as r
WHERE r NOT IN
    (SELECT DISTINCT ls.rooms FROM SeminarLecture AS ls
    WHERE ls.date <> :date)

我得到:

Syntax Error in ...; expected "*, NOT, EXISTS, INTERSECTS, SELECT, FROM"

推荐答案

如此处所述:

16.13.子查询

对于支持子选择的数据库，Hibernate支持查询中的子查询.子查询必须用括号括起来(通常用SQL聚合函数调用).甚至允许相关的子查询(在外部查询中引用别名的子查询).

For databases that support subselects, Hibernate supports subqueries within queries. A subquery must be surrounded by parentheses (often by an SQL aggregate function call). Even correlated subqueries (subqueries that refer to an alias in the outer query) are allowed.

摘要:

from DomesticCat as cat
where cat.name not in (
    select name.nickName from Name as name
)

所以我们需要明确说出什么不在

so we would need to explicitly say what is NOT IN

// instead fo this
// WHERE r NOT IN
// we have to say what is not IN
WHERE r.id NOT IN
    (SELECT DISTINCT ls.rooms FROM SeminarLecture AS ls
    WHERE ls.date <> :date)

其他选择是使用NOT EXISTS (但是我们应该使用一些匹配项扩展内部WHERE子句)

Other option would be to use NOT EXISTS (but then we should extend the inner WHERE clause with some match)

EXTEND:

因为我们面对的是many-to-many关系，所以我们的HQL必须更加聪明，而生成的SQL语句将是 bit overkill .这就是many-to-many映射的副作用，我建议避免使用(请参见此处)

Because we do face many-to-many relation, our HQL must be a bit more smarter, while resulting SQL statement will be a bit overkill. That's the side effect of many-to-many mapping, which I would suggest to avoid (see here)

所以，让我们期待这样的实体模型:

So, Let's expect the entity model like this:

public class Room {
    ...
    @ManyToMany(....
    public Set<SeminarLecture> getSeminarLectures() {
        return this.seminarLectures;
    }

还有相反的一端:

public class SeminarLecture {
    ...
    @ManyToMany(fetch = FetchType.LAZY, mappedBy = "seminarLectures")
    public Set<Room> getRooms() {
        return this.rooms;
    }

然后我们需要的HQL将是这样的:

Then the HQL we need would be like this:

SELECT r FROM Room as r
WHERE r.ID NOT IN
    (SELECT DISTINCT r.ID 
       FROM SeminarLecture AS ls
       JOIN ls.rooms r
       WHERE ls.date <> :date)

就是这样.我们从内部子查询中选择房间ID，然后将其与外部查询中的ID进行比较.

That is it. We select the room ID from inner sub-query, and compare it with the same ID from the outer query.

我的建议/方式是避免使用many-to-many.映射为第一级公民的显式配对表将为我们提供更多.我们可以在此联接表上引入更多属性(Order，IsMain ...).而且在大多数情况下，过滤将变得更加简单明了.

My suggestion/way would be to avoid many-to-many. Explicit pairing table mapped as first level citizen will give us much more. We can introduce some more properties on this join table (Order, IsMain...). And mostly, the filtering will become much more simple and straightforward.

这篇关于休眠NOT IN子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！