CODEWITHSUNDEEP
javaspringjpaspring-bootspring-data
Michał Szewczyk
Michał Szewczyk

Reputation: 8168

How to perform nested join fetch in Spring Data JPA

I would like to perform nested join, where my class User will contain list of Users analogus to SQL query:

SELECT user1.id, user2.id FROM ( users user1 LEFT JOIN friends friend ON user1.id=friend.who ) LEFT JOIN users user2 ON friend.with=user2.id GROUP BY user1.id, user2.id

My User entity class:

@Entity
@Table(name = "users")
public class User implements Serializable {
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    @Column(name = "id")
    private Integer id;
    @Column(name = "login")
    private String login;
    @Column(name = "name")
    private String name;
    @Column(name = "lastname")
    private String surname;
    @Column(name = "password")
    private String password;
    @ManyToMany
    ????
    private List<User> users;
}

Relation between users - FriendRelation entity class:

@Entity
@Table(name="friends")
public class FriendRelation implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private Integer id;
    @Column(name = "who")
    private Integer who;
    @Column(name = "with")
    private Integer with;
    @OneToMany
    @JoinColumn(name="with", referencedColumnName = "id")
    private List<User> users;
}

who - refers to id of user
with - refers to id of user in friend relationship with user who

What sould I put in place of "????" to acheive it?

I'm using Spring Data JPA

Upvotes: 0

Views: 2217

Answers (1)

Michał Szewczyk
Michał Szewczyk

Reputation: 8168

The solution was to add annotation @JoinTable.
We can use this annotation to join two tables using mapping table, in this case, it is inverse join.

@JoinTable(name = "friends", joinColumns = @JoinColumn(name = "who"), inverseJoinColumns = @JoinColumn(name = "with"))

User class:

@Entity
@Table(name = "users")
public class User implements Serializable {
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    @Column(name = "id")
    private Integer id;
    @Column(name = "login")
    private String login;
    @Column(name = "name")
    private String name;
    @Column(name = "lastname")
    private String surname;
    @Column(name = "password")
    private String password;
    @ManyToMany
    @JoinTable(name = "friends", joinColumns = @JoinColumn(name = "who"), inverseJoinColumns = @JoinColumn(name = "with"))
    private List<User> friends;
}

Here is perfectly explained how to deal with this situation.
Hope it helps someone.

Upvotes: 1

Related Questions