title: Mapping Multiple Tables into a Single Entity in JPA
body: <p>I'm trying to learn JPA/Hibernate and I'm real green in this field. I tend to usually veer off and try things without knowing much about the API. So I decided to create a simple entity that retrieves information from multiple tables see this is easily implementable with JPA. The reason behind this is, if, hypothetically, the involving tables each has a few hundred columns and we only have a business need to retrieve a very few data, and we only need to focus on retrieval rather than inserts/updates/deletions, I would assume it is best to only retrieve the entire entity (specially if multiple rows need to be returned) then join them across other entities to derive a few columns.</p>
  
  <p>I started up with two simple tables:</p>
  
  <pre><code>EMPLOYEES
  EMPLOYEE_ID, EMAIL, DEPARTMENT_ID, MANAGER_ID, FIRST_NAME, etc...
  
  DEPARTMENTS
  DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, etc...
  </code></pre>
  
  <p>I want my entity to retrieve only the following columns solely based on <code>EMPLOYEES.EMPLOYEE_ID</code>:</p>
  
  <pre><code>EMPLOYEES.EMPLOYEE_ID
  EMPLOYEES.MANAGER_ID
  EMPLOYEES.DEPARTMENT_ID
  DEPARTMENT.DEPARTMENT_NAME
  </code></pre>
  
  <p>One thing to notice here is that <code>EMPLOYEES.MANAGER_ID</code> is a self-referencing foreign key to <code>EMPLOYEES.EMPLOYEE_ID</code>.</p>
  
  <p>I might create the following...</p>
  
  <pre><code>@SecondaryTable(name="DEPARTMENTS",
      pkJoinColumns=@PrimaryKeyJoinColumn(name="managerId",referencedColumnName="employeeId")
  )
  @Table(name="EMPLOYEES")
  @Entity
  public class EmployeesDepartment {
  
      @Id
      private String employeeId;
  
      private String managerId;
  
      private String email;
  
      private int departmentId;
  
      @Column(name="DEPARTMENT_NAME",table="DEPARTMENTS")
      private String departmentDesc;
  
  // Setters and getters
  }
  </code></pre>
  
  <p>Obviously this doesn't give us the correct answer due to the fact that the join between the secondary table (<code>DEPARTMENTS</code>) occurs between its <code>MANAGER_ID</code> and <code>EMPLOYEES.EMPLOYEE_ID</code>, rather than <code>DEPARTMENTS.MANAGER_ID = EMPLOYEES.MANAGER_ID</code>.</p>
  
  <p>I cannot replace <code>referencedColumnName="employeeId"</code> with <code>referencedColumnName="managerId"</code> as managerId of <code>@Entity EmployeesDepartment</code> is not a primary key of <code>EMPLOYEES</code>.</p>
  
  <p>And I can't do the following:</p>
  
  <pre><code>@OneToOne
  @JoinColumn(name="managerId",table="DEPARTMENTS",referencedColumnName="employeeId")
  private String managerId;
  </code></pre>
  
  <p>My question is, how can I make my join to be on <code>DEPARTMENTS.MANAGER_ID = EMPLOYEES.MANAGER_ID</code> while the <code>WHERE</code> clause of the query is based on <code>EMPLOYEES.EMPLOYEE.ID</code>? In other word, how can I have the entity that is mapped to the following query:</p>
  
  <pre><code>SELECT
    E.EMPLOYEE_ID,
    E.MANAGER_ID,
    E.DEPARTMENT_ID,
    D.DEPARTMENT_NAME
  FROM EMPLOYEES E LEFT OUTER JOIN DEPARTMENTS D ON E.MANAGER_ID = D.MANAGER_ID
  WHERE E.EMPLOYEE_ID = ?
  </code></pre>
  
  <p>Or are there better solution with less side effects, e.g. order of updates of tables, loading, etc.?</p>