JPA 左连接查找未使用的条目

发布于 2024-09-13 01:43:30 字数 633 浏览 5 评论 0原文

我确信我很愚蠢,但我似乎无法弄清楚这一点......

我有两张表:

department(did,name) 员工(eid,first,last,did)

他们有相应的实体JPA管理的实体部门和员工。员工有一个部门字段,部门不维护员工列表。我想做的是找到所有没有员工的部门。使用普通的旧式 SQL,通过左连接很容易实现:

SELECT d.* 
FROM department as d LEFT OUTER JOIN employee as e
ON d.did = e.did
WHERE e.did IS NULL

不过,我不知道如何将此查询转换为 JPQL。例如,我找到的 JPQL 左连接的所有示例都以另一种方式遍历链接。

SELECT e FROM Employee e LEFT JOIN e.departmert d

虽然我需要更多类似的东西

SELECT d FROM Department d LEFT JOIN d.???? WHERE e.department IS NULL

,但部门不保留对其员工的引用(在我的应用程序中,显然不是部门和员工)。这在 JPQL 中可能吗?

I'm sure I'm being stupid but I can't seem to figure this one out...

I have two tables:

department( did, name )
employee( eid, first, last, did )

they have corresponding entities JPA managed entites Department and Employee. Employee has a Deparment field, Department doesn't maintain an Employee list. What I want to do though is find all the Departments that have no Employees. Using plain old SQL this is easy with a left join:

SELECT d.* 
FROM department as d LEFT OUTER JOIN employee as e
ON d.did = e.did
WHERE e.did IS NULL

I can't see how to translate this query into JPQL though. All the examples I've found for JPQL left joins traverse the link the other way, for example.

SELECT e FROM Employee e LEFT JOIN e.departmert d

Whereas I need something more like

SELECT d FROM Department d LEFT JOIN d.???? WHERE e.department IS NULL

but the department doesn't maintain a reference to it's employees (in my application it's not departments and employees obviously). Is this even possible in JPQL?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

此岸叶落 2024-09-20 01:43:30

要执行您想要执行的操作,您需要设置“部门”->“部门”的映射。员工(使用您的示例实体)。您可以使用 < 的 mappedBy 属性code>@OneToMany,这很可能不会破坏您的架构,例如,

@Entity
class Department {
    ...
    @OneToMany(mappedBy="employee")
    Collection<Employee> getEmployees() {
        ....
    }
    ...
}

这将允许您运行类似的内容:

SELECT d FROM Department d WHERE d.employees IS EMPTY

这应该会给您相同的结果。

在不更改映射的情况下,您应该能够运行类似以下查询的内容来获得您想要的结果:

SELECT d from Department d WHERE NOT EXIST (SELECT e FROM Employee e where e.department = d)

To do what you are trying to do, you would need to setup a mapping from Departments -> Employees (using your example entities). You could used the mappedBy attribute of @OneToMany, which will most likely not disrupt your schema, e.g.,

@Entity
class Department {
    ...
    @OneToMany(mappedBy="employee")
    Collection<Employee> getEmployees() {
        ....
    }
    ...
}

This would allow you to run something like:

SELECT d FROM Department d WHERE d.employees IS EMPTY

Which should give you equivalent results.

Without altering your mapping, you should be able to run something like this query to get the results you want:

SELECT d from Department d WHERE NOT EXIST (SELECT e FROM Employee e where e.department = d)
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文