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
Count Number of Unique Characters in a String
How to Select All Records from One Table That Do Not Exist in Another Table
Eliminate Rows Based on Created Timestamp
Select Rows Where Every Child Row Meets a Condition
Sql Server: Check If Variable Is Null and Then Assign Statement for Where Clause
Mysql - Operand Should Contain 1 Column(S)
Database Table With Million of Rows
Mysql Error 1264: Out of Range Value for Column
How to Import a Large (14 Gb) MySQL Dump File into a New MySQL Database
Postgresql Convert Columns to Rows Transpose
How to Select Three Table With Same Column Name But Different Values
Retrieve String Between HTML Tags Using Regex
Postgresql Delete Multiple Rows from Multiple Tables
How to Select an Empty Result Set