Modification for sqlResultSetMapping in Chapter 11

[Himai Minh]

In chapter 11, sqlResultSetMappings example,
In OrgStructure.java:

public List findDepartmentSummary() {
 
// Derby can't handle this query, use JP QL for this example.
          Query query = em.createQuery("SELECT d , m, COUNT(e), AVG(e.salary) " + 
              "FROM Department d LEFT JOIN d.employees e " + 
                                 "LEFT JOIN d.employees m " + 
              "WHERE m IN (SELECT de.manager " + 
                        "FROM Employee de " + 
                           "WHERE de.department = d) " + 
            "GROUP BY d, m");
        return query.getResultList();
    }

 @SqlResultSetMapping(
        name="DepartmentSummary",
        entities={@EntityResult(entityClass=Department.class,
                                fields=@FieldResult(name="name", column="DEPT_NAME")),
                  @EntityResult(entityClass=Employee.class)},
        columns={@ColumnResult(name="TOT_EMP"),
                 @ColumnResult(name="AVG_SAL")}
    )
})
public class Employee {
   ....

I think the findDepartmentSummary should be modified into:

 public List findDepartmentSummary() {
 
// Derby can't handle this query, use JP QL for this example.

// Instead of Select d , m...
// do this : select d.name as DEPT_NAME as the sqlResultSetMapping specifies name and DEPT_NAME for the entity result.
          Query query = em.createQuery("SELECT d.name as DEPT_NAME, m, COUNT(e), AVG(e.salary) " + 
              "FROM Department d LEFT JOIN d.employees e " + 
                                 "LEFT JOIN d.employees m " + 
              "WHERE m IN (SELECT de.manager " + 
                        "FROM Employee de " + 
                           "WHERE de.department = d) " + 
            "GROUP BY d, m");
        return query.getResultList();
    }

[Frits Walraven]

Thanks for sharing all your mysql findings!

Have a Cow!