Thread: JPA many to many

I have a pretty standard scenario whereby I have a table of Users with user_id as the PK and a table of Roles with role_id as the PK. The two tables are related via a many to many relationship (ie. Users can have many roles and a role can be applied to many users) and subsequently I have a joining table called users_has_roles. The only two columns in users_has_roles are users_user_id and roles_role_id.

I have generated the entity classes (see below) and I have no problem creating a new user and roles but I have failed miserably persist anything to the users_has_roles joining table so currently none of my users are being assigned a role. Before I go crazy could somebody put me out of my misery and show me how I should go about adding a users_user_id with a corresponding roles_role_id to the users_has_roles table so my users can have roles?

My Users.java entity class:

@Entity
@Table(name = "users")
@XmlRootElement
@NamedQueries({
    @NamedQuery(name = "Users.findAll", query = "SELECT u FROM Users u"),
    @NamedQuery(name = "Users.findByUserId", query = "SELECT u FROM Users u WHERE u.userId = :userId"),
    @NamedQuery(name = "Users.findByUsername", query = "SELECT u FROM Users u WHERE u.username = :username"),
    @NamedQuery(name = "Users.findByPassword", query = "SELECT u FROM Users u WHERE u.password = :password")})
public class Users implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @NotNull
    @Size(min = 1, max = 60)
    @Column(name = "user_id")
    private String userId;
    @Basic(optional = false)
    @NotNull
    @Pattern(regexp="[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\\.[a-z0-9!#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?", message="Invalid email")
    @Size(min = 1, max = 45)
    @Column(name = "username")
    private String username;
    @Basic(optional = false)
    @NotNull
    @Size(min = 1, max = 120)
    @Column(name = "password")
    private String password;
    @JoinTable(name = "users_has_roles", joinColumns = {
        @JoinColumn(name = "users_user_id", referencedColumnName = "user_id")}, inverseJoinColumns = {
        @JoinColumn(name = "roles_role_id", referencedColumnName = "role_id")})
    @ManyToMany
    private Collection<Roles> rolesCollection;
    @OneToMany(cascade = CascadeType.ALL, mappedBy = "usersUserId")
    private Collection<UserAccount> userAccountCollection;
    @OneToMany(cascade = CascadeType.ALL, mappedBy = "usersUserId")
    private Collection<UserDetails> userDetailsCollection;
 
...
 
All the getter and setter methods etc.

My Roles.java entity class:

@Entity
@Table(name = "roles")
@XmlRootElement
@NamedQueries({
    @NamedQuery(name = "Roles.findAll", query = "SELECT r FROM Roles r"),
    @NamedQuery(name = "Roles.findByRoleId", query = "SELECT r FROM Roles r WHERE r.roleId = :roleId"),
    @NamedQuery(name = "Roles.findByRoleName", query = "SELECT r FROM Roles r WHERE r.roleName = :roleName"),
    @NamedQuery(name = "Roles.findByRolePermission", query = "SELECT r FROM Roles r WHERE r.rolePermission = :rolePermission"),
    @NamedQuery(name = "Roles.findByRoleDescription", query = "SELECT r FROM Roles r WHERE r.roleDescription = :roleDescription")})
public class Roles implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @NotNull
    @Size(min = 1, max = 60)
    @Column(name = "role_id")
    private String roleId;
    @Basic(optional = false)
    @NotNull
    @Size(min = 1, max = 45)
    @Column(name = "role_name")
    private String roleName;
    @Basic(optional = false)
    @NotNull
    @Size(min = 1, max = 45)
    @Column(name = "role_permission")
    private String rolePermission;
    @Size(max = 45)
    @Column(name = "role_description")
    private String roleDescription;
    @ManyToMany(mappedBy = "rolesCollection")
    private Collection<Users> usersCollection;
 
...
 
All the getter and setter methods etc.

Just in case I have confused you regarding the database tables here is the EER:

Screen Shot 2012-04-22 at 10.08.54.jpg

Ok first off thanks to Mikko for leading me to the answer. I just wanted to post an answer that might be directly helpful to anybody else that might be in the position I was in. Also this is based on a Eureka moment so it might not be technically correct but this is how I see it.

The big issue that I faces was that in MySQL I could see the bridging table as an individual table! (sorry I can't post an image of my EER diagram but I don't seem to have enough privileges at the moment) So I assumed that Java would also see the bridging table as a table! Well it doesn't. That bridging table doesn't really exist in Java as a conventional table it is in fact represented by the opposing tables collection type that you associate with it.

The easiest way to see it for me was to completely forget the bridging table and concentrate on the two 'real' tables and associating the data in those. The following code is NOT best practice as I'm simply setting the role_id but it's fine just to show my point.

List<Roles> userRoleList = new ArrayList<Roles>();

Users currentUser = new Users();
currentUser.setUserId(userId);
currentUser.setUsername(email);
currentUser.setPassword(password);

Roles userRole = new Roles();
userRole.setRoleId("2");

userRoleList.add(userRole);
currentUser.setRolesCollection(userRoleList);

getUsersFacade().create(currentUser);


Hope that helps anybody else that is struggling with many to many relationships.

(NB. I've edited the original question code to use a List instead of a Collection for ease but you can just as well use any other type that fits your needs.)