@ManyToMany

Service table

Now let's look at another common case - many-to-many. Let's imagine that we have a many-to-many relationship between tasks and employees :

• One employee in the employee table can do many tasks from the task table.
• One task in the task table can be assigned to several employees.

This relationship between entities is called many-to-many. And in order to implement it at the SQL level, we need an additional service table. Let's call it, for example, employee_task.

The employee_task table will contain only two columns:

• employee_id
• task_id

Each time we assign a specific task to a specific user, a new row will be added to this table. Example:

Well, the task table should lose its employee_id column . It makes sense only if the task can only be assigned to one employee. If the task can be assigned to several employees, then this information must be stored in the employee_task service table .

Table-Level Relationship

Here's what our new tables will look like:

Employee table ( not changed ) :

This table has the following columns:

• id INT
• name VARCHAR
• occupation VARCHAR
• salary INT
• age INT
• join_date DATE

And this is how the task table looks like , lost the employee_id column (marked in red):

This table now has only 3 columns:

• id - unique task number (and rows in the table)
• employee_id - (removed)
• name - the name and description of the task
• deadline - the time until which the task must be completed

We also have the employee_task service table , where the employee_id data has migrated from the task table:

I purposely temporarily saved the deleted column in the task table so that you can see that the data from it has moved to the employee_task table.

Another important point is the red line "(NULL) 6" in the employee_task table. I've marked it in red because it won't be in the employee_task table .

If task 7 is assigned to user 4, then there should be a row (4, 7) in the employee_task table.

If task 6 is not assigned to anyone, then there will simply be no record for it in the employee_task table. Here is what the final versions of these tables will look like:

task table :

employee_task table:

Communication at the Java class level

But with communication at the level of Entity-classes, we have a complete order. Let's start with the good news.

First, Hibernate has a special @ManyToMany annotation that allows you to well describe the case of a many-to-many table relationship.

Secondly, two Entity classes are still enough for us. We don't need a class for the service table.

Here's what our classes will look like. The Employee class in its original form:

@Entity
@Table(name="user")
class Employee {
   @Column(name="id")
   public Integer id;

   @Column(name="name")
   public String name;

   @Column(name="occupation")
   public String occupation;

   @Column(name="salary")
   public Integer salary;

   @Column(name="join_date")
   public Date join;
}

And the EmployeeTask class in its original form:

@Entity
@Table(name="task")
class EmployeeTask {
   @Column(name="id")
   public Integer id;

   @Column(name="name")
   public String description;

   @Column(name="deadline")
   public Date deadline;
}

@ManyToMany annotation

I will omit the existing fields in the examples, but I will add new ones. Here's what they will look like. Employee class :

@Entity
@Table(name="employee")
class Employee {
   @Column(name="id")
   public Integer id;

   @ManyToMany(cascade = CascadeType.ALL)
   @JoinTable(name="employee_task",
	       joinColumns=  @JoinColumn(name="employee_id", referencedColumnName="id"),
           inverseJoinColumns= @JoinColumn(name="task_id", referencedColumnName="id") )
   private Set<EmployeeTask> tasks = new HashSet<EmployeeTask>();

}

And the EmployeeTask class :

@Entity
@Table(name="task")
class EmployeeTask {
   @Column(name="id")
   public Integer id;

   @ManyToMany(cascade = CascadeType.ALL)
   @JoinTable(name="employee_task",
       	joinColumns=  @JoinColumn(name="task_id", referencedColumnName="id"),
       	inverseJoinColumns= @JoinColumn(name=" employee_id", referencedColumnName="id") )
   private Set<Employee> employees = new HashSet<Employee>();

}

It seems that everything is complicated, but in fact everything is simple.

First, it uses the @JoinTable annotation (not to be confused with @JoinColumn), which describes the employee_task service table.

Second, it describes that the task_id column of the employee_task table refers to the id column of the task table.

Third, it says that the employee_id column of the employee_task table refers to the id column of the employee table.

In fact, with the help of annotations, we described what data is contained in the employee_task table and how Hibernate should interpret it.

But now we can very easily add (and delete) a task to any employee. And also add any performer to any task.

Request examples

Let's write a couple of interesting queries to better understand how these ManyToMany fields work. And they work exactly as expected.

First, our old code will work without changes, since the director had a tasks field before:

EmployeeTask task1 = new EmployeeTask();
task1.description = "Do Something Important";
session.persist(task1);

EmployeeTask task2 = new EmployeeTask();
task2.description = "Nothing to do";
session.persist(task2);
session.flush();

Employee director = session.find(Employee.class, 4);
director.tasks.add(task1);
director.tasks.add(task2);

session.update(director);
session.flush();

Secondly, if we want to assign another performer to some task, then it is even easier to do this:

Employee director = session.find(Employee.class, 4);
EmployeeTask task = session.find(EmployeeTask.class, 101);
task.employees.add(director);

session.update(task);
session.flush();

Important! As a result of executing this request, not only the task will have an executor-director, but also the director will have task No. 101.

First, the fact about the relationship between the director and the task in the employee_task table will be stored as a string: (4,101).

Secondly, the fields marked with @ManyToMany annotations are proxy objects, and when they are accessed, a database query is always executed.

So if you add a task to an employee and save information about the employee to the database, then after that the task will have a new executor in the list of executors.

1

Task

Module 4. Working with databases, level 13, lesson 3

Locked

Create a ManyToMany relationship table

task1305

Open

1

Task

Module 4. Working with databases, level 13, lesson 3

Locked

Co-authorship

task1306

Open