@ManyToMany Relationship Between Three Tables

Question

I have three separate entities in my Spring JPA application - User, Department, Role

I have a single join table in my database to relate each of these Entities: USER_DEPARTMENT_ROLE

My question is, how can I define this relation in my entity classes? Do I have to define a @ManyToMany relationship in each of the separate entities? I know how to define this relationship between two tables, but for more than two I'm not sure where to start.

Any help is appreciated!

Answer 1

If you have more than two relations mapped in your join table then i would suggest creating a separate entity which would be used for mapping that particular table.

The question is whether you can have a distinct id column which would serve as an artificial primary key or you have to stick with the composite primary key build from the three foreign keys.

If you can add that artificial id (which is the modern way of designing your database) then your mapping should look something like the following:

Option 1

class User {
    @OneToMany(mappedBy = "user", cascade = {CascadeType.PERSIST, CascadeType.MERGE})
    private Set<UserDepartmentRoleLink> userDepartmentRoleLinks;
}

class Department{
    @OneToMany(mappedBy = "department", cascade = {CascadeType.PERSIST, CascadeType.MERGE})
    private Set<UserDepartmentRoleLink> userDepartmentRoleLinks;
}

class Role{
    @OneToMany(mappedBy = "role", cascade = {CascadeType.PERSIST, CascadeType.MERGE})
    private Set<UserDepartmentRoleLink> userDepartmentRoleLinks;
}

class UserDepartmentRoleLink {

    @Id
    @GeneratedValue
    private Long id;

    @ManyToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE})
    @JoinColumn(name = "user_id")
    private User user;

    @ManyToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE})
    @JoinColumn(name = "department_id")
    private Department department;

    @ManyToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE})
    @JoinColumn(name = "role_id")
    private Role role;

}

Regarding setting the cascade types for the many to many relatioship is tricky and for many to many involving three tables is even trickier as every entity can play a role of parent or child depending on the circumstances.. i would suggest sticking only with the cascade = {CascadeType.PERSIST, CascadeType.MERGE} and handling other operations manually.

If you have to stay with the composite primary key then you should add additional Id class and change the link entity to the following:

Option 2

class User {
    @OneToMany(mappedBy = "linkPk.user", cascade = {CascadeType.PERSIST, CascadeType.MERGE})
    private Set<UserDepartmentRoleLink> userDepartmentRoleLinks;
}

class Department{
    @OneToMany(mappedBy = "linkPk.department", cascade = {CascadeType.PERSIST, CascadeType.MERGE})
    private Set<UserDepartmentRoleLink> userDepartmentRoleLinks;
}

class Role{
    @OneToMany(mappedBy = "linkPk.role", cascade = {CascadeType.PERSIST, CascadeType.MERGE})
    private Set<UserDepartmentRoleLink> userDepartmentRoleLinks;
}

Linkage table

class UserDepartmentRoleLink {

    @EmbeddedId
    private UserDepartmentRoleLinkId linkPk 
          = new UserDepartmentRoleLinkId(); 

    @Transient
    public User getUser() {
        return getLinkPk().getUser();
    }

    @Transient
    public User getDepartment() {
        return getLinkPk().getDepartment();
    }

    @Transient
    public User getRole() {
        return getLinkPk().getRole();
    }    
 }

@Embeddable
public class UserDepartmentRoleLinkId implements java.io.Serializable {

    @ManyToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE})
    @JoinColumn(name = "user_id")
    private User user;

    @ManyToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE})
    @JoinColumn(name = "department_id")
    private Department department;

    @ManyToOne(cascade = {CascadeType.PERSIST, CascadeType.MERGE})
    @JoinColumn(name = "role_id")
    private Role role;

The bottom line is that you can use Many To Many here like outlined in this post -> example. But in my opinion you would save yourself a lot of headache if you map that link table as above. In the end the call is yours..