Spring Data JPA : Many To Many relationship mapping with extra columns

 

In a previous article (click here to visit it),  we have discussed about how to map many to many relationship with @ManyToMany annotation.  The @ManyToMany annotation is useful only in situations where we need to map only the many to many relationship between entities. It helps to generate a joined table with only the foreign keys that refer the participant tables.

But there are some situations where we need to add some extra columns to the joined table (newly generated intermediate table). In such case, we cannot use the @ManyToMany annotation for mapping many to many relationship between entities.

In this article, i am going to show you how to map many to many relationship and add extra column(s) to the joined table.

 

Please refer the following ER diagram.

 

You can see that the relationship has two attributes known as from_date and to_date. When we convert the above ER-Diagram into the relational table structure, we should get the following tables.

you can see that the attributes in the relationship has been added to the joined(intermediate) table which represents the relationship .

 

Lets implement this relationship with Spring Data JPA Project. The full source code of this article can be found at GitHub.

 

With @ManyToMany annotation, we just maintain only two entities. The intermediate(joined) table is generated as a result of mapping the many to many relationship between two entities.

But in this approach (adding extra columns) we need to maintain the relationship as a separate entity.  So here we will be maintaining three separate entities.

Lets look at each class in detailed.

 

StudentCourseEnrollment

This is used to represent the many to many relationship between Student and Course entities. All the extra columns that need to be added for the joined table will be declared here.

	import javax.persistence.*;
	import java.io.Serializable;
	@Entity
	@Table(name = "student_courses")
	@IdClass(StudentCourseId.class)
	public class StudentCourseEnrollment implements Serializable
	{
	@Id
	@ManyToOne
	@JoinColumn(name = "course_id", referencedColumnName = "id")
	private Course course;
	@Id
	@ManyToOne
	@JoinColumn(name = "student_id", referencedColumnName = "id")
	private Student student;
	@JoinColumn(name = "from_date")
	private String fromDate;
	@JoinColumn(name = "to_date")
	private String toDate;
	//TODO add getters and setters for all properties
	}

view raw

StudentCourseEnrollment.java

hosted with ❤ by GitHub

 

As you can see that all the extra columns known as from_date and to_date has been declared here. In addition the relationship from StudentCourseEnrollment to Student and Course has been declared.

• Many StudentCourseEnrollment entries can refer a single entry in the Course. Therefore the relationship from StudentCourseEnrollment to Course is many-to-one
• Many StudentCourseEnrollment entries can refer a single entry in the Student. Therefore the relationship from StudentCourseEnrollment to Student is many-to-one

 

The joined table (intermediate table) will have a composite primary key which is composed with student_id and course_id.  Since it is a composite key, it will be declared in a separate class and added with @IdClass annotation.

	import java.io.Serializable;
	public class StudentCourseId implements Serializable
	{
	private Long course;
	private Long student;
	//TODO add getters and setters for all above properties
	}

view raw

StudentCourseId.java

hosted with ❤ by GitHub

 

If you observe the StudentCourseId class properly, you may have noticed following points.

• It contains only the properties that are required to form the primary key (composite primary key) of the StudentCourseEnrollment entity.
• The property names of StudentCourseId class are exactly the same as the names in the related properties of the StudentCourseEnrollment class.
• It must implements Serializable interface.

 

Student

	import javax.persistence.*;
	import java.util.List;
	@Entity
	@Table(name = "students")
	public class Student
	{
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Long id;
	private String name;
	private String country;
	private String email;
	@Column(name = "created_at")
	private String createdAt;
	@OneToMany(mappedBy = "student")
	private List<StudentCourseEnrollment> studentCourseEnrollments;
	//TODO add getters and setters for all properties
	}

view raw

Student.java

hosted with ❤ by GitHub

 

Here we are not going to map the direct relationship from Student to Course. We have used an intermediate entity called StudentCourseEnrollment. The relationship from Student to StudentCourseEnrollment is one-to-many. One student can enroll for many number of courses. The same theory is applied to the relationship from Course to StudentCourseEnrollment too.  mappedBy attributes is used to denote the property who owns the relationship.

 

	import javax.persistence.*;
	import java.util.List;
	@Entity
	@Table(name = "courses")
	public class Course
	{
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Long id;
	private String title;
	private String description;
	private String major;
	@Column(name = "created_at")
	private String createdAt;
	@OneToMany(mappedBy = "course")
	private List<StudentCourseEnrollment> courseEnrollments;
	//TODO add getters and setters for all properties
	}

view raw

Course.java

hosted with ❤ by GitHub

 

Run and Test the source code.

You can get the source code related to this project from GitHub (Click here).  Please change the database configuration in application.properties based on your development environment. The run the following command to build the project.

mvn spring-boot:run

 

After executing the above command successfully, the table structure will be created with all declared properties.

 

Here i have shown you how to map many to many relationship with extra columns for the joined table. In here we have not used @ManyToMany annotation and we have used some alternative different approach.

 

Spring Data JPA : Many To Many relationship mapping using @ManyToMany annotation

 

In this article, i am going to discuss how to map many to many relationship using @ManyToMany annotation available in JPA (Java Persistence Api).

 

Lets look at the following ER-Diagram.

This ER-Diagram describes the relationship between Students and Courses. According to the diagram, the relationship can be described as below.

• A student can enroll for many courses.
• A course can be enrolled by many students.

Therefore the relationship between Students and Courses is many to many.

 

If we convert this relationship into relational database model, the table structure should looks like below.

 

Lets implement this relationship with Spring Data JPA Project. The full source code of this article can be found at GitHub.

 

Here is the Student class.

	import javax.persistence.*;
	import java.io.Serializable;
	import java.util.List;
	@Entity
	@Table(name = "students")
	public class Student implements Serializable {
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Long id;
	private String name;
	private String country;
	private String email;
	@Column(name = "created_at")
	private String createdAt;
	@ManyToMany
	@JoinTable(
	name = "student_courses",
	joinColumns = {@JoinColumn(name = "student_id", referencedColumnName = "id")},
	inverseJoinColumns = {@JoinColumn(name = "course_id", referencedColumnName = "id")}
	)
	private List<Course> courses;
	//TODO add getters and setters for all properties
	}

view raw

Student.java

hosted with ❤ by GitHub

 

In Student class we are going the map the relationship from Student to Course. As you are already aware, it is a many to many relationship and there should be an intermediate table (joined table)  to map the relationship.  Therefore we have used the @JoinTable annotation to define the joined/intermediate table with its properties.

The name of the joined table (newly created intermediate table) will be “student_courses” as declared. In addition, there will be two join columns.

• student_id – This will refer the “id” column of the  “students” table.
• course_id – This will refer the “id” column of the “courses” table.

 

Here is the Course class.

	import javax.persistence.*;
	import java.io.Serializable;
	import java.util.List;
	@Entity
	@Table(name = "courses")
	public class Course implements Serializable {
	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private Long id;
	private String title;
	private String description;
	private String major;
	@Column(name = "created_at")
	private String createdAt;
	@ManyToMany(mappedBy = "courses")
	private List<Student> students;
	//TODO getters and setters for all properties
	}

view raw

Course.java

hosted with ❤ by GitHub

 

In Course class, we just declare only the relationship from Course to Student. Since the relationship is many to many, it is just declared with @ManyToMany annotation.  We are not going to map the relationship as it is already mapped by Student class and it is denoted with mappedBy attribute.

 

What is “mappedBy” attribute here?

mappedBy attributes defines the owner of the relationship. As you can see that relationship has been defined and owned by the courses property of the Student class. Therefore in the Course class it is declared with mappedBy=”courses”.

This will tells that the relationship has been mapped and owned by the courses property of the Student class.

 

Run and Test the source code.

You can get the source code related to this project from GitHub (Click here).  Please change the database configuration in application.properties based on your development environment. The run the following command to build the project.

mvn spring-boot:run

 

After executing the above command successfully, the table structure will be created with all defined properties.

 

Here you can see that the joined table contains only the foreign key for reference entities/tables. we have not added any extra column for the joined table.

In next article, we will look at how to map  many to many relationship with extra columns for the joined table.