Association Table Mapping Pattern

This pattern belongs to Object-Relational Structural Patterns Catalog and this Catalog belongs to Patterns of Enterprise Application Architecture.

Intent

Saves an association as a table with foreign keys to the tables that are linked by the association (This is a many-to-many relationship).

Explanation

A many-to-many relationship occurs when multiple records in a table are associated with multiple records in another table.
For example, a many-to-many relationship exists between employees and skills: an employee can have multiple skills, and skill can be associated with many employees.
One more example is, a many-to-many relationship exists between customers and products: customers can purchase various products, and products can be purchased by many customers. 

The primary key Skill ID uniquely identifies each skill in the skills table. The primary key Employee ID uniquely identifies each employee in the Employees table. The skill-employees table contains the foreign keys skill ID and employee ID.
Let's take one more example here:
The following example includes a Students table, which contains a record for each student, and a Classes table, which contains a record for each class. A join table, Enrollments, creates two one-to-many relationships—one between each of the two tables.

The primary key Student ID uniquely identifies each student in the Students table. The primary key Class ID uniquely identifies each class in the Classes table. The Enrollments table contains the foreign keys Student ID and Class ID.

How It Works

The basic idea behind Association Table Mapping is using a link table to store the association. This table has only the foreign key IDs for the two tables that are linked together, it has one row for each pair of associated objects.
The link table has no corresponding in-memory object. As a result, it has no ID. Its primary key is the compound of the two primary keys of the tables that are associated.
In simple terms, to load data from the link table you perform two queries. Consider loading the skills for an employee. In this case, at least conceptually, you do queries in two stages. The first stage queries the skills employees table to find all the rows that link to the employee you want. The second stage finds the skill object for the related ID for each row in the link table.

Implementation

Let's use Hibernate ORM Framework to implement many-to-many relationship example 
In this example, we will map many-to-many relationship using Hibernate Annotations. Consider the following relationship between Student and Course entity.
According to the relationship a student can enroll in any number of courses and the course can have any number of students.
To create this relationship we need to have a STUDENT, COURSE and STUDENT_COURSEtable. The relational model is shown below.
Let's create Hibernate Model classes.
import java.util.HashSet;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.Table;

@Entity
@Table(name = "STUDENT")
public class Student {

 private long studentId;
 private String studentName;
 private Set<Course> courses = new HashSet<Course>(0);

 public Student() {
 }

 public Student(String studentName) {
  this.studentName = studentName;
 }

 public Student(String studentName, Set<Course> courses) {
  this.studentName = studentName;
  this.courses = courses;
 }

 @Id
 @GeneratedValue
 @Column(name = "STUDENT_ID")
 public long getStudentId() {
  return this.studentId;
 }

 public void setStudentId(long studentId) {
  this.studentId = studentId;
 }

 @Column(name = "STUDENT_NAME", nullable = false, length = 100)
 public String getStudentName() {
  return this.studentName;
 }

 public void setStudentName(String studentName) {
  this.studentName = studentName;
 }

 @ManyToMany(cascade = CascadeType.ALL)
 @JoinTable(name = "STUDENT_COURSE", joinColumns = { @JoinColumn(name = "STUDENT_ID") }, inverseJoinColumns = { @JoinColumn(name = "COURSE_ID") })
 public Set<Course> getCourses() {
  return this.courses;
 }

 public void setCourses(Set<Course> courses) {
  this.courses = courses;
 }

}
The @ManyToMany annotation is used to create the many-to-many relationship between the Student and Course entities. The @JoinTable annotation is used to create the STUDENT_COURSE link table and @JoinColumn annotation is used to refer the linking columns in both the tables.
Course class is used to create the COURSE table.
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="COURSE")
public class Course {

 private long courseId;
 private String courseName;

 public Course() {
 }

 public Course(String courseName) {
  this.courseName = courseName;
 }

 @Id
 @GeneratedValue
 @Column(name="COURSE_ID")
 public long getCourseId() {
  return this.courseId;
 }

 public void setCourseId(long courseId) {
  this.courseId = courseId;
 }

 @Column(name="COURSE_NAME", nullable=false)
 public String getCourseName() {
  return this.courseName;
 }

 public void setCourseName(String courseName) {
  this.courseName = courseName;
 }

}
Now create the hibernate configuration file with the Student and Course class mapping.
<?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="hibernate.connection.driver_class"> org.hsqldb.jdbcDriver</property>
        <property name="hibernate.connection.url"> jdbc:hsqldb:hsql://localhost</property>
        <property name="hibernate.connection.username">sa</property>
        <property name="connection.password"></property>
        <property name="connection.pool_size">1</property>
        <property name="hibernate.dialect"> org.hibernate.dialect.HSQLDialect</property>
        <property name="show_sql">true</property>
        <property name="hbm2ddl.auto">create-drop</property>
        <mapping class="com.vaannila.student.Student" />
        <mapping class="com.vaannila.student.Course" />
    </session-factory>
</hibernate-configuration>
Create the Main class to run the example.
import java.util.HashSet;
import java.util.Set;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.Transaction;

import com.vaannila.util.HibernateUtil;

public class Main {

 public static void main(String[] args) {

  Session session = HibernateUtil.getSessionFactory().openSession();
  Transaction transaction = null;
  try {
   transaction = session.beginTransaction();

   Set<Course> courses = new HashSet<Course>();
   courses.add(new Course("Maths"));
   courses.add(new Course("Computer Science"));

   Student student1 = new Student("Eswar", courses);
   Student student2 = new Student("Joe", courses);
   session.save(student1);
   session.save(student2);

   transaction.commit();
  } catch (HibernateException e) {
   transaction.rollback();
   e.printStackTrace();
  } finally {
   session.close();
  }

 }
}

References


Comments

Post a Comment