MEMBER OF in EJB-QL 3 doesn't work

Question

Hi,

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

It's the Named Querie

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

I cannot reproduce the problem. When running the JPQL query you provided, like this:

String qlString = "SELECT access " + 
                    "FROM Access AS access " + 
                   "WHERE :role MEMBER OF access.listRole";

Role role = new Role();
role.setId(1L);

List accesses = session.createQuery(qlString).setParameter("role", role).list();

Hibernate generates the following SQL query for me (I simplified a bit your entities by removing some attributes):

select
    access0_.id as id127_,
    access0_.libelle as libelle127_,
    access0_.name as name127_ 
from
    Access access0_ 
where
    ? in (
        select
            role2_.id 
        from
            Access_ROLES listrole1_,
            ROLES role2_ 
        where
            access0_.id=listrole1_.Access_id 
            and listrole1_.listRole_id=role2_.id
    )

Everything seems correct (tested with Hibernate Core 3.3.0.SP1, Hibernate Annotations 3.4.0.GA, Hibernate EM 3.4.0.GA)

What version of Hibernate (Core, Annotations, EntityManager) are you using exactly? What error do you get exactly? Can you show how you invoke the query?

Answer 2

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;
    // ...
}