我正在编写一个Dao以获取与Person有关的所有Messages.但是我找不到正确的JPQL语法.
I'm writing a Dao to fetch all Messages relevant to a Person. But I can't find the correct JPQL syntax.
在我的模型中:
- 一个Person具有多个Roles(我将它们作为参数传递给查询:一组枚举值).
- 一个Message与多个Roles相关.
- a Person has multiple Roles (I pass these into the query as a parameter: Set of enum values).
- a Message is relevant to multiple Roles.
因此,我想查找与某人有关的所有消息:
So I want to find all messages relevant to a person:
SELECT m FROM Message m WHERE m.roles [contains one of] :userRoles将其作为Set<Role>参数提供给:userRoles.
该缺失的[包含]部分之一的正确语法是什么?
我看过IN和MEMBER OF 例如
SELECT m FROM MESSAGE m WHERE m.roles IN :userRoles但是这两个关键字的一侧都需要一个单个项.我在两侧上都有多个项.
But both those keywords require a single item on one side. I have multiple items on both sides.
更多细节:
@Entity @Table(name = "message") public class Message { @Id private Long id; @ElementCollection @Enumerated(javax.persistence.EnumType.STRING) @JoinTable(name="message_roles", joinColumns={@javax.persistence.JoinColumn(name="message_id")}) @Column(name="role_code") private Set<Role> roles; ... } public enum Role { DEVELOPER, ADMIN, TESTER, MANAGER }这将产生一个外观正确的表结构:
This produces a correct-looking table structure of:
message message_id message_roles message_id role_code但是我不知道如何查询它是否包含给定角色的特定列表之一.
But I can't work out how to query it to see if it contains one of a specific list of given roles.
我必须重写它才能单独传递每个用户的角色吗?
Must I rewrite it to pass each of the user's roles in individually?
出于特定于应用程序的原因,我无法在任何地方加入以获得他们的角色:角色必须是查询的参数.
For application-specific reasons I can't join anywhere to get their roles: the roles must be a parameter of the query.
推荐答案尝试一下:
SELECT DISTINCT m FROM MESSAGE m JOIN m.roles r WHERE r IN :userRoles更多推荐
JPA“包含"以下内容之一:
发布评论