ManyToManyToMany - Joining three tables with Hibernate annotations

Question

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             |

Answer 1

Since I recently ran into this and still struggled, I wanted to share a complete code example. This example uses a separate @EmbeddedId class which will still create a table with 3 PK/FK columns. My example makes use of Lombok to fill in a bunch of boiler-plate code such as getters/setters, constructors, etc. It was also necessary to override the equals and hashcode methods. This was written using Spring framework, which wires up the repos & tests. Hopefully someone finds this a useful guide.

/* ENTITY CLASSES */
@Entity
@Data
@Table(name = "_Who")
public class Who {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    @OneToMany(mappedBy = "who", fetch = FetchType.EAGER)
    @JsonManagedReference
    List<WhoWhatWhere> storage;
}

@Entity
@Data
@Table(name = "_What")
public class What {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String thing;
}

@Entity
@Data
@Table(name = "_Where")
public class Where {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String place;
}

@Data
@EqualsAndHashCode(onlyExplicitlyIncluded = true)
@Entity
@NoArgsConstructor
@Table(name = "_WhoWhatWhere")
public class WhoWhatWhere {
    public WhoWhatWhere(Who who, What what, Where where) {
        this.who = who;
        this.what = what;
        this.where = where;
        this.setId(new WhoWhatWhereId(who.getId(), what.getId(), where.getId()));
    }

    @EmbeddedId
    WhoWhatWhereId id;

    @ManyToOne(fetch = FetchType.EAGER)
    @JsonBackReference
    @JoinColumn(name = "who_id", insertable = false, updatable = false)
    private Who who;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "what_id", insertable = false, updatable = false)
    private What what;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "where_id", insertable = false, updatable = false)
    private Where where;
}

@Embeddable
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode(onlyExplicitlyIncluded = true)
public class WhoWhatWhereId implements Serializable {
    @Column(name = "who_id")
    Long whoId;
    @Column(name = "what_id")
    Long whatId;
    @Column(name = "where_id")
    Long whereId;
}

/* REPOSITORIES */
@Repository
public interface WhoRepository extends PagingAndSortingRepository<Who, Long> {
    Iterable<Who> findWhoByName (String name);
}
@Repository
public interface WhatRepository extends PagingAndSortingRepository<What, Long> {
}
@Repository
public interface WhereRepository extends PagingAndSortingRepository<Where, Long> {
}
@Repository
public interface WhoWhatWhereRepository extends PagingAndSortingRepository<WhoWhatWhere, WhoWhatWhereId> {
}

/* TEST CLASS */
@SpringBootTest
@Slf4j
public class ThreeWayAssocTest {

    private final WhoRepository whoRepository;
    private final WhatRepository whatRepository;
    private final WhereRepository whereRepository;
    private final WhoWhatWhereRepository whoWhatWhereRepository;

    @Autowired
    public ThreeWayAssocTest(WhoRepository whoRepository, WhatRepository whatRepository, WhereRepository whereRepository, WhoWhatWhereRepository whoWhatWhereRepository) {
        this.whoRepository = whoRepository;
        this.whatRepository = whatRepository;
        this.whereRepository = whereRepository;
        this.whoWhatWhereRepository = whoWhatWhereRepository;
    }

    @Test
    public void attemptPersistence() {
        /*
        * the commented pieces can be used to do the initial inserts.  Later, fetch existing values so as not to fill
        * up the database
        */
        Who who =
        /*        new Who();
        who.setName("Carl");
        whoRepository.save(who);*/
                whoRepository.findById(1L).get();
        What what =
        /*        new What();
        what.setThing("strawberry");
        whatRepository.save(what);
        what.setThing("salad");
        whatRepository.save(what);*/
                whatRepository.findById(2L).get();
        Where where =
        /*        new Where();
        where.setPlace("plate");
        whereRepository.save(where);*/
                whereRepository.findById(1L).get();
        WhoWhatWhere whoWhatWhere = new WhoWhatWhere(who, what, where);
        whoWhatWhereRepository.save(whoWhatWhere);
        LOGGER.debug("finished");
    }

    @Test
    public void testSerializing() throws JsonProcessingException {
        Iterable<Who> examples = whoRepository.findWhoByName("Carl");
        Who carl = examples.iterator().next();
        LOGGER.debug("Carl: {}", carl);
        LOGGER.debug("found some: \n {}", new ObjectMapper().writeValueAsString(examples));
    }
}

Answer 2

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;

 }