Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 3 of 3

Thread: JPA many to many

  1. #1
    Member
    Join Date
    Sep 2011
    Posts
    63
    My Mood
    Confused
    Thanks
    7
    Thanked 0 Times in 0 Posts

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


  2. #2
    Member
    Join Date
    Sep 2011
    Posts
    63
    My Mood
    Confused
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Default Re: JPA many to many

    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

  3. #3
    Member
    Join Date
    Sep 2011
    Posts
    63
    My Mood
    Confused
    Thanks
    7
    Thanked 0 Times in 0 Posts

    Talking Re: JPA many to many

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