Categories
hibernate jpa spring spring-data sql

Join 3 tables Hibernate & JPA

I have three tables and I want to join them and it’s driving me crazy.

Tabels : Task
Project
User

They All have a Many-To-Many relationShip between them : like project has multiple users and vice versa and also Tasks have multiple users and vice versa and the same for project and users.

I’m trying to find a way to implement that in JPA & Hibernate and I have 2 choices :
** 1 ** Join the 3 entities like this :

Project :

   @ManyToMany(fetch = FetchType.EAGER, 
cascade =
{
CascadeType.DETACH,
CascadeType.MERGE,
CascadeType.REFRESH,
CascadeType.PERSIST
},
targetEntity = User.class)
@JoinTable(name = "Collaborators",
joinColumns = @JoinColumn(name = "ProjectFk", referencedColumnName = "id", updatable=false, nullable=false),
inverseJoinColumns = @JoinColumn(name = "UserFk", referencedColumnName = "id", updatable=false, nullable=false))
private Set<User> users = new HashSet<>();

User :

    @ManyToMany(fetch = FetchType.EAGER, 
cascade =
{
CascadeType.DETACH,
CascadeType.MERGE,
CascadeType.REFRESH,
CascadeType.PERSIST
},
targetEntity = Project.class)
@JoinTable(name = "Collaborators",
joinColumns = @JoinColumn(name = "UserFk", referencedColumnName = "id", updatable=false, nullable=false),
inverseJoinColumns = @JoinColumn(name = "ProjectFk", referencedColumnName = "id", updatable=false, nullable=false))
private Set<Project> projects = new HashSet<>();

Task :

    @ManyToMany(fetch = FetchType.EAGER, 
cascade =
{
CascadeType.DETACH,
CascadeType.MERGE,
CascadeType.REFRESH,
CascadeType.PERSIST
},
targetEntity = User.class)
@JoinTable(name = "Collaborators",
joinColumns = @JoinColumn(name = "TaskFk", referencedColumnName = "id", updatable=false, nullable=false),
inverseJoinColumns = @JoinColumn(name = "ProjectFk", referencedColumnName = "id", updatable=false, nullable=false))
@MapKeyJoinColumn(name = "User_id")
@ElementCollection
private Map<Project, User> projectUserMap = new HashMap<>();

But here it dosn’t seem to work

2 : Create a 4th Entity and name it Collaborator and then try to find a way to implement this SQL query :

select * from (
select user.id, Project.id, Task.id
inner join Project on user.join_key = Project.join_key
inner join Task on task.join_key = = Project.join_key
) collaborator

Any help would be MUCH apreciated. Thanks.