英文:
JPA: Check if Collection has member with property
问题
在我的软件中，我有一个实体（我们称其为Member），其中包含另一个实体的集合（我们称其为State）。我需要编写的查询应该返回所有没有具有特定属性值（例如5）的State的成员（Member）。
以下是实体的相关部分：
public class Member {
@JoinColumn(name = "MEMBER_ID")
@OneToMany
private List<State> states;
@Column
private String name;
}
public class State {
@Column
private int property;
}
请注意，Member和State之间没有双向映射，映射是在关系的非拥有方（Member）上声明的。在SQL中，我会创建如下查询：
SELECT m.name
FROM Member m
WHERE NOT EXISTS (
SELECT *
FROM State s
WHERE
m.id = s.member_id
AND s.property = 5
);
但是我不知道如何在JPQL中实现相同的功能，而不必在关系的拥有方上进行映射。是否有任何方法可以实现这一点，而不必费心去处理双向映射呢？
英文:
In my software, I have an entity (let's call it Member) with a collection of another entity (let's call it State). The query I need to write should return all members who have no State with a specific property value (e. g. 5).
Here are the relevant parts of the entities:
public class Member {
@JoinColumn(name = "MEMBER_ID")
@OneToMany
private List<State> states;
@Column
private String name;
}
public class State {
@Column
private int property;
}
Note that there is no bidirectional mapping between Member and State, the mapping is declared on the non-owning side of the relation (Member). In SQL I would create a query like this:
<!-- language: lang-sql -->
SELECT m.name
FROM Member m
WHERE NOT EXISTS (
SELECT *
FROM State s
WHERE
m.id = s.member_id
AND s.property = 5
);
But I don't know of any way to achieve the same thing in JPQL without having a mapping on the owning side of the relation. Is there any way to achieve this without having to bother with bidirectional mappings?
答案1
得分: 5
JPA允许在子查询的FROM子句中使用集合引用，因此您可以使用以下内容：
SELECT m.name
FROM Member m
WHERE NOT EXISTS(
SELECT 1
FROM m.states s
WHERE s.property = 5
)
这将生成您所需的准确SQL语句。
英文:
JPA allows to use collection references in subquery from clauses, so you can use this:
SELECT m.name
FROM Member m
WHERE NOT EXISTS(
SELECT 1
FROM m.states s
WHERE s.property = 5
)
This will produce exactly the SQL you want.
答案2
得分: 0
尝试类似这样的内容：
选择 m.name 从 Member m 其中不存在 (
从 Member m1 加入 m1.states s 其中 s.property = 5
)
或者
选择 m.name 从 Member m 其中 m.id 不在 (
选择 m1.id 从 Member m1 加入 m1.states s 其中 s.property = 5
)
英文:
Try something like this
select m.name from Member m where not exists(
from Member m1 join m1.states s where s.property = 5
)
or
select m.name from Member m where m.id not in(
select m1.id from Member m1 join m1.states s where s.property = 5
)
答案3
得分: 0
你可以像这样编写本地查询：
选择名称从成员中，其中成员ID不在（选择ID从状态中，其中属性=5）
英文:
you can write native query like this
> select name from Member where member_id not in ( select id from states
> where property = 5)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论