Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ManyToManyToMany - Joining three tables with Hibernate annotations

At first I thought this solution might solve my problem:

@Entity
public class User {

    @JoinTable(name = "user_permission",
        joinColumns = @JoinColumn(name = "user_id"),
        inverseJoinColumns = @JoinColumn(name = "permission_id"))
    @MapKeyJoinColumn(name = "project_id")
    @ElementCollection
    private Map<Project, Permission> permissions = new HashMap<>();

}

@Entity
public class Project {
    ...
}

@Entity
public class Permission {
    ...
}

But in this implementation there can only be one Permission set per Project. I'd like to accomplish the ability to set multiple permissions for a project such that the following could be true:

| user_id | project_id | permission_id |
|---------|------------|---------------|
| 1       | 1          | 1             |
|---------|------------|---------------|
| 1       | 1          | 2             |
|---------|------------|---------------|
| 1       | 2          | 1             |
|---------|------------|---------------|
| 1       | 2          | 2             |
|---------|------------|---------------|
| 2       | 1          | 1             |
|---------|------------|---------------|
| 2       | 1          | 2             |
|---------|------------|---------------|
| 2       | 2          | 1             |
|---------|------------|---------------|
| 2       | 2          | 2             |
like image 605
Rawr Avatar asked Feb 01 '17 07:02

Rawr


1 Answers

You can use an entity dedicated to your relation table. It's the way we declare relations with their own attributes for instance.
This would result in the following implementation:

@Entity
@IdClass(PermissionAssignation.class)
public class PermissionAssignation {

     @Id
     @ManyToOne
     @JoinColumn(name="user_id")
     private User user;

     @Id
     @ManyToOne
     @JoinColumn(name="project_id")
     private Project project;

     @Id
     @ManyToOne
     @JoinColumn(name="permission_id")
     private Permission permission;
     ...
}

I used the solution found in this post: Hibernate and no PK

It explains how to create the PK with field (I did not test it). If it does not work, you'd better use a EmbeddedId class.

And if you want your relation to be bidirectional, you can then use a Set<PermissionAssignation> (or List, as you prefer/need):

 @Entity
 public class User {

      @OneToMany(mappedBy="user")
      private Set<PermissionAssignation> permissions;

 }
like image 133
Grégory Elhaimer Avatar answered Sep 19 '22 18:09

Grégory Elhaimer