title: JPA Native Query to map One to Many
body: <p>I have a complex Oracle query which for simplicity's sake looks like this;</p>
  
  <pre><code>SQL&gt; SELECT d.id AS dept_id,
    2    d.name AS dept_name,
    3    e.id AS emp_id,
    4    e.name AS emp_name,
    5    e.dept_id AS emp_dept_id
    6  FROM drs2_dept d, drs2_emp e
    7  WHERE d.id = e.dept_id (+)
    8  /
  
     DEPT_ID DEPT_NAME           EMP_ID      EMP_NAME      EMP_DEPT_ID
  ---------- ------------------- ---------- -------------- -----------
           1 SALES                       101 JOHN                    1
           1 SALES                       102 JANE                    1
           2 ADMIN                                                       
  
  </code></pre>
  
  <p>My <code>Department</code> class is;</p>
  
  <pre><code>@SqlResultSetMapping(
      name = "Department.employeeMapping",
      classes = {
          @ConstructorResult(
              targetClass = Department.class,
              columns = {
                  @ColumnResult(name = "DEPT_ID", type = Integer.class),
                  @ColumnResult(name = "DEPT_NAME")
              }
          ),
          @ConstructorResult(
              targetClass = Employee.class,
              columns = {
                  @ColumnResult(name = "EMP_ID", type = Integer.class),
                  @ColumnResult(name = "EMP_NAME"),
                  @ColumnResult(name = "EMP_DEPT_ID", type = Integer.class)
              }
          )
      }
  )
  
  @NamedNativeQuery(
      name = "Department.findAllEmployees",
      query = "SELECT d.id AS dept_id, " + 
              "  d.name AS dept_name,  " +
              "  e.id AS emp_id,  " +
              "  e.name AS emp_name " +
              "  e.dept_id AS emp_dept_id, " +
              "FROM drs2_dept d, drs2_emp e " +
              "WHERE d.id = e.dept_id (+)",
      resultSetMapping = "Department.employeeMapping"
  )
  
  @Entity
  public class Department {
      @Id // JPA will not start without it.
      Integer id;  
      String name;
  
      @OneToMany // JPA will not start without it.
      List&lt;Employee&gt; employees = new ArrayList&lt;&gt;();
  
      public Department(Integer id, String name) {
          this.id = id;
          this.name = name;
      }
  
      public Department() {}
  
      // getters and setters
  }
  
  </code></pre>
  
  <p>My <code>Employee</code> class is;</p>
  
  <pre><code>@Entity
  public class Employee {
      @Id Integer id;
      Integer departmentId;
      String name;
  
      public Employee(Integer id, String name, Integer departmentId) {
          this.id = id;
          this.name = name;
          this.departmentId = departmentId;
      }
  
      public Employee() {}
  
      // getters and setters
  }
  </code></pre>
  
  <p>Because I am using <code>@ConstructorResult</code> I am able to get the data, but it still in a flat structure, that is to say a <code>List&lt;Object[]&gt;</code> with three entries, each containing <code>[Department, Employee]</code>. So I have to do the following to move the <code>Employee</code> records within their respective <code>Department</code>;</p>
  
  <pre><code>@Component
  public class DepartmentDAO  {
  
      @PersistenceContext EntityManager entityManager;
  
      public Collection&lt;Department&gt; getAllDepartments() {
          Query query = entityManager.createNamedQuery("Department.findAllEmployees");
  
          Map&lt;Integer, Department&gt; map = new HashMap&lt;&gt;();
          List&lt;Object[]&gt; list = query.getResultList();
          for (Object[] tuple: list) {
              Department d = (Department) tuple[0];
              if (! map.containsKey(d.getId())) {
                  map.put(d.getId(), d);
              }
              d = map.get(d.getId());
              Employee e = (Employee) tuple[1];
              if (e.getId() != null) {
                  d.getEmployees().add(e);
              }
          }
          return map.values();
      }
  }
  </code></pre>
  
  <p>Whenever I add any additional properties to the <code>@OneToMany</code> I seem to get spurious SQL generated in the Hibernate logs which is incorrect (i.e. non-existent column or table names), but as I stated at the start of this question, I want the native SQL only - I don't want Hibernate to figure out what I am trying to do.</p>
  
  <p>Is there any way to get JPA/Hibernate to put the <code>Employee</code> objects into the <code>Department</code>'s list for me? </p>
  
  <p>(
  As a sub-note, I have seen this question asked here, but either never answered or answered back in 2011, by which time JPA and Hibernate may have progressed.</p>
  
  <p>I should also add that elsewhere in my project I already have <code>Department</code> and <code>Employee</code> fully mapped for <code>CrudRepository</code> use with <code>@Table</code> and <code>@Column</code>, however their <code>@OneToMany</code> definitions do not depict what I am doing in the above query, hence their absence in my example code.
  )</p>