How to Make a Join of 3 Tables in Jpa

How to join 3 tables into one table with JPA?

here is an example of how to implement your solution already working:

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.Table;

@Entity
@Table(name = "user_book_status")
public class UserBookStatus {

  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private long id;

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

  @ManyToOne
  @JoinColumn(name = "book_id")
  private Book book;

  @ManyToOne
  @JoinColumn(name = "status_id")
  private Status status;
}

@Table to specify the name of the Table, so you can change the name of the entity freely.

@ManyToOne means that you can have many records of UserBookStatus for a single user, or book or status.

@JoinColumn use to specify the name of the column in your entity that maps to the @Id from the entity (User,Book,Status) you are referencing.

Join 3 tables Hibernate & JPA

Should be implemented like below:

Project class

 @Entity
public class Project extends ABaseEntity{

private Set<User> users = new HashSet<>();

@ManyToMany
@JoinTable(name = "Collaborators",
        joinColumns = @JoinColumn(name = "Project_ID", referencedColumnName = "id", updatable=false, nullable=false),
        inverseJoinColumns = @JoinColumn(name = "User_ID", referencedColumnName = "id", updatable=false, nullable=false))
public Set<User> getUsers() {
    return users;
}

public void setUsers(Set<User> users) {
    this.users = users;
}
}

User class

@Entity
@Table(name = "EMA_USER")
public class User extends ABaseEntity {

private Set<Project> projects = new HashSet<>();


@ManyToMany(fetch = FetchType.EAGER,
        cascade = CascadeType.ALL,
        targetEntity = Project.class)
@JoinTable(name = "Collaborators",
        joinColumns = @JoinColumn(name = "User_ID", referencedColumnName = "id", updatable = false, nullable = false),
        inverseJoinColumns = @JoinColumn(name = "Project_ID", referencedColumnName = "id", updatable = false, nullable = false))
public Set<Project> getProjects() {
    return projects;
}

public void setProjects(Set<Project> projects) {
    this.projects = projects;
}
}

Task class

@Entity
public class Task extends ABaseEntity{

private Map<Project, User> projectUserMap = new HashMap<>();

@ManyToMany(
        fetch = FetchType.EAGER,
        cascade = CascadeType.ALL
)
@JoinTable(
        name = "Collaborators",
        joinColumns = @JoinColumn(name = "Task_ID", referencedColumnName = "id", updatable = false, nullable = false),
        inverseJoinColumns = @JoinColumn(name = "User_ID", referencedColumnName = "id", updatable = false, nullable = false)
)
@MapKeyJoinColumn(name = "Project_Id")
public Map<Project, User> getProjectUserMap() {
    return projectUserMap;
}

public void setProjectUserMap(Map<Project, User> projectUserMap) {
    this.projectUserMap = projectUserMap;
}
}

What is wrong in your task class:

@MapKeyJoinColumn(name = "User_id")

The @MapKeyJoinColumn is used to map the column for the key, here is Project entity.

@ElementCollection

is used if the value of the map is @Embeddable

@JoinTable

defines the relation between the entity class with entity defined in value of the Map (here is relation between Task and User).

 targetEntity = User.class

All targetEntity are redundant because you used collection with defined generic type.

Moreover, you don't have to configure 3 classes like this. As far as i understand, you just need a table containing relations between those 3 classes.
So just define an entity called Collaboration like this:

@Entity
public class Collaboration{
  @ManyToOne
  private User user;
  @ManyToOne
  private Task task;
  @ManyToOne
  private Project project;
}

With the separated Collaboration entity, you can find all collaborations which have given User.

Without this entity, you can write a query to find all the tasks, which related to the given user and then of course, collect the Projects and Tasks yourself in java code.
The query will be like

 'Select t from Task t join projectUserMap m where m = :user '

How I can Join this 3 Tables with JPA

Your @JoinTable has incorrect @JoinColumn specifications and corresponds to the following ddl.

create table person_contact (person_id bigint not null, contacts_id bigint not null, primary key (person_id, contacts_id))

To map your db structure, use following (note removed @Column annotation)

@OneToMany
@JoinTable(name = "person_contact", joinColumns =
        {
                @JoinColumn(name = "person_id", referencedColumnName = "id"),
        },
        inverseJoinColumns = {
                @JoinColumn(name = "contact_id", referencedColumnName = "id")
        })
private Set<ContactEntity> contacts;

I also encourage you to read https://vladmihalcea.com/the-best-way-to-map-a-onetomany-association-with-jpa-and-hibernate/ and reconsider a db structure without a join table (depending on your load and the effort to make this db change)

how to make a join of 3 tables in JPA?

SELECT tu.taskId, t.start, t.end 
FROM task as t, user as u, task_user as tu 
WHERE tu.taskId = t.taskId AND tu.userId = u.userId 
      AND t.userId = u.userId 
      AND tu.userId = '2';

---

Related Topics