Hibernate NOT IN 子查询

2022-01-23 00:00:00 subquery java Hibernate

我的会议室与 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)

扩展:

因为我们确实面临 many-to-many 关系，我们的 HQL 必须更聪明一点，而生成的 SQL 语句将是一个有点矫枉过正.这是 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.

相关文章