Use Join Table in Separate Entity

Question

I have a ManyToMany relationship established with a join table that is exactly like the one described on the Java Persistence wiki Example of a ManyToMany relationship annotations. Using that wiki example of Employees & Projects as a reference, the code listed in the example works fine to create three tables: EMP, PROJ and the EMP_PROJ join table. What I would like to do is use that EMP_PROJ join table in a separate entity. I don't want to add additional columns to the EMP_PROJ join table. For example, suppose an administrator associates projects with an employee. That list is stored in EMP_PROJ. What I would like to do is create a separate entity called ManagerReport that will return, for an employee, the list of projects associated with that employee. The manager can then enter specific info regarding each project for the employee, like start date, end date, performance, etc.

Below are the tables and sample code pulled from the wiki page.

EMPLOYEE (table)
ID  FIRSTNAME   LASTNAME
1   Bob     Way
2   Sarah   Smith

EMP_PROJ (table)
EMP_ID  PROJ_ID
1   1
1   2
2   1

PROJECT (table)
ID  NAME
1   GIS
2   SIG

@Entity
public class Employee {
  @Id
  @Column(name="ID")
  private long id;
  ...
  @ManyToMany
  @JoinTable(
      name="EMP_PROJ",
      joinColumns={@JoinColumn(name="EMP_ID", referencedColumnName="ID")},
      inverseJoinColumns={@JoinColumn(name="PROJ_ID", referencedColumnName="ID")})
  private List<Project> projects;
  ...
}

Answer 1

You will have to create a ManagerReportProject entity which maps to EMP_PROJ table with @Table annotation.

In Employee entity, instead of @ManyToMany mapping for a collection of Projects use @OneToMany mapping for collection mapping to ManagerReportProject entities.

You will still be able to get a list of employee's projects because each ManagerReportProject further points to Project. You can even create a helper getProjects() method inside Employee to get a list of projects. Method must be annotated with @Transient to mark getProjects() method as not JPA persitent (by default all properties or fields inside Entity mappings are persitent)

     @Entity
     public class Employee 
     {
          @Id
          @Column(name="ID")
          private long id;
          ...
          @OneToMany...
          private List<ManagerReportProject> managerReportProjects;
          ...

          /*
          YOU DON'T NEED THIS ANYMORE:
          @ManyToMany
          @JoinTable(
              name="EMP_PROJ",
              joinColumns={@JoinColumn(name="EMP_ID", referencedColumnName="ID")},
              inverseJoinColumns={@JoinColumn(name="PROJ_ID", referencedColumnName="ID")})
          private List<Project> projects;

           */

       @Transient
       public List<Project> getProjects()
       {
          List<Project> projects = new ArrayList<Project>();
          for(ManagerReportProject managerReportProject: managerReportProjects)
          {
             projects.add(managerReportProject.getProject());
          }
          return projects;
       }
     ...
    }

   

ManagerReportProject should point to Employee, Project and Manager entity with @ManyToOne association.
Put manager report specific columns into ManagerReportProject (start date, end date, performance, etc.).
ManagerReportProject maps to EMP_PROJ table with @Table annotation.

@Entity
@Table(name= "EMP_PROJ")
public class ManagerReportProject{
  @Id
  @Column(name="ID")
  private long id;

//manager report columns
  private Date startDate;
  private Date endDate;
  performance, etc. 

  @ManyToOne...
  private Manager manager;

  ...
  @ManyToOne...
  private Employee employee;

  @ManyToOne...
  private Project project;

  ...
}

   

Create a Manager entity, use @OneToMany for collection mapping to ManagerReportProject entities:

@Entity
public class Manager {
  @Id
  @Column(name="ID")
  private long id;
  ...
  @OneToMany...
  private List<ManagerReportProject> managerReportProjects;
  ...
}

   

Now you can enter specific info regarding each project for the employee, like start date, end date, performance, etc. This is a sketch just to demonstrate an idea of how to edit existing manager report for a specific empolyee working for a specific manager:

Emyployee employee = ...
Manager manager = ...
List<ManagerReportProject> managerReportProjects= employee.getManagerReportProjects()
for(ManagerReportProject managerReportProject: managerReportProjects )
{
    if(manager.equals(managerReportProject.getManager()))
    {
       Project project = managerReportProject.getProject();

       managerReportProject.setStartDate(...);
       managerReportProject.setEndDate(...);
       managerReportProject.setperformance(...);
       ...

    }

}