Click here to Skip to main content
15,867,686 members
Articles / Programming Languages / SQL

A Note on Hibernate - Many to Many

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
3 Jan 2020CPOL3 min read 4.5K   27  
This is a note on Hibernate many to many mappings.

Introduction

This is a note on Hibernate many to many mappings.

Background

In my earlier note, I talked about the one to many mappings in Hibernate. In this note, I will talk about the many to many mappings.

Image 1

The student and course relation is a typical many to many relation. The following situations are very common to all the many to many relations.

  • A student is registered to the school before he is enrolled in any course. So the student exists in the [Student] table before any corresponding entries in the [Student_Course] table.
  • A course is designed by the school before it has any enrolled student. So the course exists in the [Course] table before any corresponding entries in the [Student_Course] table.
  • The most common operations on the many to many relationship are simply inserting and deleting rows in the [Student_Course] to show if a student is enrolled in or dropped from a course.

This note is to show an example on how to manipulate the many to many relations in Hibernate.

The Database

Same as my earlier note, I use a MySQL database. If you are not familiar with MySQL, you can take a look at my earlier note. In this note, I used the version 5.5.62-0ubuntu0.14.04.1 on a Linux Mint machine. You can issue the following script to create the database and the tables.

SQL
DROP DATABASE IF EXISTS experimentB;
CREATE DATABASE experimentB;
    
USE experimentB;
    
CREATE TABLE `Student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(100) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
    
CREATE TABLE `Course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
    
CREATE TABLE `Student_Course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `studentId` int(11) NOT NULL,
  `courseId` int(11) NOT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_constraint` (`studentId`,`courseId`),
  KEY `fk_Student_Course_Student_idx` (`studentId`),
  KEY `fk_Student_Course_Course_idx` (`courseId`),
  CONSTRAINT `fk_Student_Course_Course` FOREIGN KEY (`courseId`)
    REFERENCES `Course` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_Student_Course_Student` FOREIGN KEY (`studentId`)
    REFERENCES `Student` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

Image 2

The Entities & Hibernate Configurations

Attached is a Maven project. It is exactly the same structure as the earlier note.

Image 3

The following classes map the tables to the entities.

Java
package com.song.example.hibernate.entities;
    
import java.util.ArrayList;
import java.util.List;
    
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;
    
@Entity
@Table(name = "Student")
public class Student {
    private Integer id;
    private String name;
    private List<StudentCourse> studentCourses = new ArrayList<StudentCourse>();
    
    public Student() {}
    public Student(String name) { this.name = name; }
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "Id")
    public Integer getId() { return this.id; }
    public void setId(Integer id) { this.id = id; }
    
    @Column(name = "Name", length = 100)
    public String getName() { return this.name; }
    public void setName(String name) { this.name = name; }
    
    @OneToMany(fetch = FetchType.LAZY, mappedBy = "student")
    public List<StudentCourse> getStudentCourses() { return this.studentCourses; }
    public void setStudentCourses(List<StudentCourse> studentCourses) { 
        this.studentCourses = studentCourses; 
    }
}
Java
package com.song.example.hibernate.entities;
    
import java.util.ArrayList;
import java.util.List;
    
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;
    
@Entity
@Table(name = "Course")
public class Course {
    private Integer id;
    private String name;
    private List<StudentCourse> studentCourses = new ArrayList<StudentCourse>();
    
    public Course() {}
    public Course(String name) { this.name = name; }
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "Id")
    public Integer getId() { return this.id; }
    public void setId(Integer id) { this.id = id; }
    
    @Column(name = "Name", length = 100)
    public String getName() { return this.name; }
    public void setName(String name) { this.name = name; }
    
    @OneToMany(fetch = FetchType.LAZY, mappedBy = "course")
    public List<StudentCourse> getStudentCourses() { return this.studentCourses; }
    public void setStudentCourses(List<StudentCourse> studentCourses) {
        this.studentCourses = studentCourses; 
    }
}
Java
package com.song.example.hibernate.entities;
    
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
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 = "Student_Course")
public class StudentCourse {
    private Integer id;
    private Integer score;
    
    private Student student;
    private Course course;
    
    public StudentCourse() {}
    public StudentCourse(Student student, Course course) {
        this.student = student;
        this.course = course;
    }
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "Id")
    public Integer getId() { return this.id; }
    public void setId(Integer id) { this.id = id; }
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "studentId", nullable = false)
    public Student getStudent() { return this.student; }
    public void setStudent(Student student) { this.student = student; }
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "courseId", nullable = false)
    public Course getCourse() { return this.course; }
    public void setCourse(Course course) { this.course = course; }
    
    @Column(name = "score", length = 100)
    public Integer getScore() { return this.score; }
    public void setScore(Integer score) { this.score = score; }
}

The hibernate configurations are added in the hibernate.cfg.xml file.

XML
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
        "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
        
<hibernate-configuration>
    <session-factory>
        <property name="connection.driver_class">
            com.mysql.cj.jdbc.Driver
        </property>
        <property name="connection.url">
            jdbc:mysql://localhost/experimentB
        </property>
        <property name="connection.username">root</property>
        <property name="connection.password">password</property>
    
        <property name="connection.pool_size">100</property>
        <property name="dialect">
            org.hibernate.dialect.MySQLDialect
        </property>
    
        <property name="cache.provider_class">
            org.hibernate.cache.NoCacheProvider
        </property>
        
        <property name="org.hibernate.flushMode">MANUAL</property>
    
        <property name="show_sql">false</property>
        <property name="format_sql">false</property>
        
        <mapping class="com.song.example.hibernate.entities.Student" />
        <mapping class="com.song.example.hibernate.entities.Course" />
        <mapping class="com.song.example.hibernate.entities.StudentCourse" />
        
    </session-factory>
</hibernate-configuration>

In the hibernate.cfg.xml file, I added the user-name and password entries. If you want to run the example, you need to make sure the user-name has the necessary permissions to operate on the tables.

The Unit Tests

In the note, I use TestNG to run the unit tests. All the tests are written in the SC_Test.java file.

Java
package com.song.example.hibernate;
    
import java.sql.Statement;
import java.util.List;
    
import org.hibernate.FlushMode;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.testng.Assert;
import org.testng.Reporter;
import org.testng.annotations.BeforeTest;
import org.testng.annotations.Test;
    
import com.song.example.hibernate.entities.Course;
import com.song.example.hibernate.entities.Student;
import com.song.example.hibernate.entities.StudentCourse;
    
public class SC_Test {
    private final SessionFactory sessionFactory
        = SessionFactoryInstance.Instance;
    
    @BeforeTest
    public void Init() {
        final String test_name = "Init()";
        Reporter.log(test_name, true);
        
        try (Session session = sessionFactory.openSession()) {
            FlushMode flushmode = session.getHibernateFlushMode();
            Assert.assertEquals(flushmode, FlushMode.MANUAL);
            
            session.doWork(connection -> {
    
                try (Statement statement = connection.createStatement()) {
                    statement.execute("SET FOREIGN_KEY_CHECKS = 0;");
                    statement.execute("TRUNCATE TABLE Student_Course;");
                    statement.execute("TRUNCATE TABLE Student;");
                    statement.execute("TRUNCATE TABLE Course;");
                    statement.execute("SET FOREIGN_KEY_CHECKS = 1;");
                }
            });
        }
        
        try (Session session = sessionFactory.openSession()) {
            Transaction tx = session.getTransaction();
            
            try {
                tx.begin();
                session.save(new Student("Student No.1"));
                session.save(new Student("Student No.2"));
                session.save(new Course("Course No.1"));
                session.save(new Course("Course No.2"));
                session.flush();
                tx.commit();
            }
            catch(Exception e) {
                tx.rollback();
                Assert.fail(test_name + " - Add students and courses");
            }
        }
        
        // Validate students and courses are added
        try (Session session = sessionFactory.openSession()) {
            
            List<Student> students = session
                    .createQuery("SELECT s FROM Student s", Student.class)
                    .getResultList();
            List<Course> courses = session
                    .createQuery("SELECT c FROM Course c", Course.class)
                    .getResultList();
            
            Assert.assertEquals(students.size(), 2);
            Assert.assertEquals(courses.size(), 2);
        }
    }
    
    @Test
    public void Add_Student_To_Course_Test() {
        final String test_name = "Assign_Student_To_Course_Test()";
        Reporter.log(test_name, true);
        
        final int studentId = 1, courseId = 1;
        final String courseName = "Course No.1";
        
        try (Session session = sessionFactory.openSession()) {
            Transaction tx = session.getTransaction();
            
            try {
                tx.begin();
                Student student = session.get(Student.class, studentId);
                Course course = session.get(Course.class, courseId);
                
                StudentCourse studentCourse = new StudentCourse(student, course);
                student.getStudentCourses().add(studentCourse);
                session.save(studentCourse);
                
                session.flush();
                tx.commit();
            } catch(Exception e) {
                tx.rollback();
                Assert.fail(test_name + " - Failed to commit the changes");
            }
        }
        
        // Validate the student assigned to the course
        try (Session session = sessionFactory.openSession()) {
            Student student = session.get(Student.class, studentId);
            
            List<StudentCourse> studentCourses = student.getStudentCourses();
            Assert.assertEquals(studentCourses.size(), 1);
            
            StudentCourse studentCourse = studentCourses.get(0);
            Assert.assertNull(studentCourse.getScore(),
                    test_name + " - score should not have been assigned");
            
            Course course = studentCourse.getCourse();
            Assert.assertEquals(course.getName(), courseName);
        }
    }
    
    @Test(dependsOnMethods = {"Add_Student_To_Course_Test"})
    public void Assign_Score_To_Student_Test() {
        final String test_name = "Assign_Score_To_Student_Test()";
        Reporter.log(test_name, true);
        
        final int studentId = 1, score = 95;
        
        try (Session session = sessionFactory.openSession()) {
            Transaction tx = session.getTransaction();
            
            try {
                tx.begin();
                Student student = session.get(Student.class, studentId);
                StudentCourse studentCourse = student.getStudentCourses().get(0);
                
                studentCourse.setScore(score);
                session.flush();
                tx.commit();
            } catch(Exception e) {
                tx.rollback();
                Assert.fail(test_name + " - Failed to commit the changes");
            }
        }
        
        // Validate the score updated
        try (Session session = sessionFactory.openSession()) {
            Student student = session.get(Student.class, studentId);
            
            List<StudentCourse> studentCourses = student.getStudentCourses();
            StudentCourse studentCourse = studentCourses.get(0);
            Assert.assertEquals(studentCourse.getScore().intValue(), score);
        }
    }
    
    @Test(dependsOnMethods = {"Assign_Score_To_Student_Test"})
    public void Drop_And_Add_Student_To_Course_Test() {
        final String test_name = "Drop_And_Add_Student_To_Course_Test()";
        Reporter.log(test_name, true);
        
        final int studentId = 1, courseId = 2, score = 100;
        final String courseName = "Course No.2";
        
        try (Session session = sessionFactory.openSession()) {
            Transaction tx = session.getTransaction();
            
            try {
                tx.begin();
                Student student = session.get(Student.class, studentId);
                List<StudentCourse> studentCourses = student.getStudentCourses();
                
                StudentCourse existingCourse = studentCourses.get(0);
                studentCourses.remove(existingCourse);
                
                Course course = session.get(Course.class, courseId);
                StudentCourse newCourse = new StudentCourse(student, course);
                newCourse.setScore(score);
                studentCourses.add(newCourse);
                
                session.delete(existingCourse);
                session.save(newCourse);
                
                session.flush();
                tx.commit();
            } catch(Exception e) {
                tx.rollback();
                Assert.fail(test_name + " - Failed to commit the changes");
            }
        }
        
        // Validate course dropped and added
        try (Session session = sessionFactory.openSession()) {
            Student student = session.get(Student.class, studentId);
            
            List<StudentCourse> studentCourses = student.getStudentCourses();
            Assert.assertEquals(studentCourses.size(), 1);
            
            StudentCourse studentCourse = studentCourses.get(0);
            Assert.assertEquals(studentCourse.getScore().intValue(), score);
            
            Assert.assertEquals(studentCourse.getCourse().getName(), courseName);
        }
    }
}
  • public void Init() - Before we start the tests, 2 students and 2 courses are added in the database.
  • public void Add_Student_To_Course_Test() - This tests to enroll a student to a course.
  • public void Assign_Score_To_Student_Test() - This tests to update the score for the student in the course;
  • public void Drop_And_Add_Student_To_Course_Test() - This tests to drop the student from a class and enroll to another class at the same time.

This unit test class also shows that it is very easy to query the database to load all the courses enrolled by a student.

Run the Unit Tests

To run the unit test, you need to have the MySQL server running. You can start the MySQL server in a Linux system by the following command:

sudo service mysql start

You can then run the unit tests by the Maven command.

mvn test

If you load the project into Eclipse, you can also run the unit test by the TestNG plug-in.

Image 4

Points of Interest

  • This is a note on Hibernate many to many mappings.
  • I hope you like my posts and I hope this note can help you in one way or the other.

History

  • 3rd January, 2020: First revision

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
United States United States
I have been working in the IT industry for some time. It is still exciting and I am still learning. I am a happy and honest person, and I want to be your friend.

Comments and Discussions

 
-- There are no messages in this forum --