ManyToMany on itself with AssociationTable with EmbeddedId - Spring-Data-Jpa

Question

I have some trouble with Hibernate during a ManyToMany association :/ I want to have a user with his contacts. The association Table is used to have a creation_date of the association and her status (ex. Active, inactive, etc...)

• Hibernate version : 5.2.17
• Spring boot : 2.0.5

My class USER :

@Entity(name = "user")
public class User implements Serializable {

@ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@JoinTable(name = "assoc_user_user", joinColumns = {@JoinColumn(name = 
"id.myself.id")}, inverseJoinColumns = {@JoinColumn(name = 
"id.contact.id")})
private List<AssocUserUser> contacts;

@ManyToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY)
@JoinTable(name = "assoc_user_user", joinColumns = {@JoinColumn(name = 
"id.contact.id")}, inverseJoinColumns = {@JoinColumn(name = 
"id.myself.id")})
private List<AssocUserUser> contactOf;

}

My Association class AssocUserUser :

@Entity(name = "assoc_user_user")
public class AssocUserUser implements Serializable {

private static final long serialVersionUID = 1L;

@EmbeddedId
private AssocUserUserId id;

@Column(name = "creation_date", nullable = false)
private LocalDateTime creationDate;

@Column(name = "status")
@Enumerated(EnumType.STRING)
private ContactStatusEnum status;

}

My EmbeddedId class AssocUserUserId :

@Embeddable
public class AssocUserUserId implements Serializable {

private static final long serialVersionUID = 1L;

@ManyToOne
@JoinColumn(name = "user_id_myself", nullable = false)
private User myself;

@ManyToOne
@JoinColumn(name = "user_id_contact", nullable = false)
private User contact;

}

My Error :

Caused by: org.hibernate.AnnotationException: A Foreign key refering com.....AssocUserUser from com.....User has the wrong number of column. should be 2

Answer 1

Well, this question has been asked and answered at Spring-Data-JPA ManyToMany relationship with extra column but I guess it's a little different with the self reference. I'm not a big fan of all the JoinTable and JoinColumn annotations but only because they are generally redundant and, IMHO, meant for changing the defaults. My answer doesn't include those annotations so add them as needed. Note that with Spring-Data-Jpa you need repositories for each entity and so you need a repository for the Association Entity. This is different than a single entity manager in JPA. This affects a little bit how you deal with the Association Entity. Here we persist new relationships by creating and persisting new AssocUserUser entities.

The way I've done the classes is:

@Entity
public class User {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    @OneToMany(mappedBy="myself")
    private Set<AssocUserUser> contacts;

@Entity
public class AssocUserUser {
    @EmbeddedId
    private AssocUserUserId id = new AssocUserUserId();
    @ManyToOne @MapsId("myselfId")
    private User myself;
    @ManyToOne @MapsId("contactId")
    private User contact;

@SuppressWarnings("serial")
@Embeddable
public class AssocUserUserId implements Serializable {
    private Long myselfId;
    private Long contactId;

and Repositories for both entities

public interface UserRepository extends JpaRepository<User, Long> {
    @Query("select u from User u left outer join fetch u.contacts where u.id = :userId")
    User getContactsForUser(@Param("userId") Long userId);

public interface AssocUserUserRepository extends JpaRepository<AssocUserUser, AssocUserUserId> {

How to create the association

private void update() {
    User user1 = new User();
    User contact1 = new User();
    userRepo.save(user1);
    userRepo.save(contact1);
    AssocUserUser assoc = new AssocUserUser();
    assoc.setMyself(user1);
    assoc.setContact(contact1);
    assocUserUserRepo.save(assoc);
}   

How to read the association in a Unidirectional fashion

private void read() {
    User me = new User();
    me.setId(1L);
    AssocUserUser assoc = new AssocUserUser();
    assoc.setMyself(me);
    List<AssocUserUser> contacts = assocUserUserRepo.findAll(Example.of(assoc));
    System.out.println(contacts.size());
}

And to read the association in a Bidirectional fashion:

private void readBi() {
    User me = userRepo.getContactsForUser(1L);
    System.out.println(me.getContacts().size());
}

As always, check the logs:

create table assoc_user_user (contact_id bigint not null, myself_id bigint not null, primary key (contact_id, myself_id))
create table user (id bigint generated by default as identity, primary key (id))
alter table assoc_user_user add constraint FKaccetv956cu63fwiejjfrm0mi foreign key (contact_id) references user
alter table assoc_user_user add constraint FK1absxfuktrjnom8vwtjfqx5l0 foreign key (myself_id) references user

insert into user (id) values (null)
insert into user (id) values (null)
select assocuseru0_.contact_id as contact_1_0_0_, assocuseru0_.myself_id as myself_i2_0_0_ from assoc_user_user assocuseru0_ where assocuseru0_.contact_id=? and assocuseru0_.myself_id=?
select user0_.id as id1_1_0_ from user user0_ where user0_.id=?
select user0_.id as id1_1_0_ from user user0_ where user0_.id=?
insert into assoc_user_user (contact_id, myself_id) values (?, ?)

select assocuseru0_.contact_id as contact_1_0_, assocuseru0_.myself_id as myself_i2_0_ from assoc_user_user assocuseru0_ inner join user user1_ on assocuseru0_.myself_id=user1_.id where user1_.id=1
select user0_.id as id1_1_0_ from user user0_ where user0_.id=?
select user0_.id as id1_1_0_ from user user0_ where user0_.id=?

select user0_.id as id1_1_0_, contacts1_.contact_id as contact_1_0_1_, contacts1_.myself_id as myself_i2_0_1_, contacts1_.myself_id as myself_i2_0_0__, contacts1_.contact_id as contact_1_0_0__ from user user0_ left outer join assoc_user_user contacts1_ on user0_.id=contacts1_.myself_id where user0_.id=?
select user0_.id as id1_1_0_ from user user0_ where user0_.id=?