JPA“包含"以下内容之一:

问题描述

我正在编写一个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“包含"以下内容之一:的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！