MEMBER OF in EJB-QL 3 doesn't work

Question

I would like to retrieve many 'Access' which have one 'Role' in common.

It's the named query:

SELECT access 
FROM Access AS access 
WHERE :role MEMBER OF access.listRole

The Access entity

public class Access implements Serializable {
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Long id;
    private String name;
    private String libelle;

    @ManyToOne
    private Module oneModule;
    @ManyToMany
    private List<Role> listRole;
    /* Setter & Getter */
}

The Role entity

public class Role implements Serializable {
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private Long id;
    private String name;
    private String description;
    @Enumerated(EnumType.STRING)
    private Flag oneFlag;
    @Transient
    private int lengthAccess;

    @OneToMany(mappedBy="oneRole")
    private List<UserAccount> listUserAccount;
    @ManyToMany
    private List<Access> listAccess;

    /* Geter & Setter */
}

But I don't achieve to do the right EJB-QL !

Configuration:

• EJB 3
• MySQL (InnoDB)
• jBoss
• Plop

Thanks.

Answer 1

My link @ManyToMany between my two classes isn't write in the right way, during the project's building, 2 Tables has created in MySQL ("access_role" for my link @ManyToMany in the 'access' class, and role_access for my link @ManyToMany in the 'role' class)

So, to correct this, I modified like this

public class Access implements Serializable {
    // ...
    @ManyToMany(mappedBy="listAccess")
    private List<Role> listRole;
   // ...
}

public class Role implements Serializable {
    // ...
    @ManyToMany
        @JoinTable(name = "access_role",
            joinColumns = @JoinColumn(name = "role_id", referencedColumnName = "id"),
            inverseJoinColumns = @JoinColumn(name = "access_id", referencedColumnName = "id"))
    private List<Access> listAccess;
    // ...
}