Mr Spoon Mr Spoon - 1 year ago 52
Java Question

JPA "contains one of"

I'm writing a Dao to fetch all

Messages
relevant to a
Person
. But I can't find the correct JPQL syntax.

In my model:


  • 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


Giving it
:userRoles
as a
Set<Role>
parameter.

What is the correct syntax for that missing [contains one of] section?

I've looked at
IN
and
MEMBER OF

e.g.

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.

More detail:

@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.

Answer Source

Try with this:

SELECT DISTINCT m FROM MESSAGE m JOIN m.roles r WHERE r IN :userRoles
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download