someangular someangular - 1 year ago 168
Java Question

java get average salary for each company and pass to JSP

I am trying to get the average salary of all employees for each company:

<table>
<tr>
<th>Company</th>
<th>Total Number of Employees</th>
<th>Average Salary</th>
</tr>
<% for (Company company : companys) {%>
<tr>
<td><%=company.getName()%></td>
<td><%=company.getEmployees().size()%></td>
<td><%=company.getEmployees() ???? %></td>
</tr>
<% } %>
</table>


my question:



I don't know how to calculate the average of the salary for each company. Could somebody help me to achieve this?

here is my Company.java

public class Company implements Serializable {
...
@OneToMany(mappedBy="company", fetch=FetchType.LAZY)
private List<Employee> employees;

...
public List<Employee> getEmployees() {
return this.employees;
}
}


inside my controller.java

private void doCompanys(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
List<Company> companys = CompanyDAO.getAll();
request.setAttribute("companys", companys);
loadJSP(urlCompanys, request, response);
}


and inside my CompanyDAO.java file

public static List<Company> getAll() {
EntityManager em = GestionFactory.factory.createEntityManager();
Query q = em.createQuery("SELECT c FROM Company c");
@SuppressWarnings("unchecked")
List<Company> listCompany = q.getResultList();
return listCompany;
}


inside my EmployeeDAO.java

public static List<Employee> getAll() {
EntityManager em = GestionFactory.factory.createEntityManager();
Query q = em.createQuery("SELECT e FROM Employee e");
@SuppressWarnings("unchecked")
List<Employee> listEmployee = q.getResultList();
return listEmployee;
}


and inside Employee.java

public int getSalary() {
return this.empSalary;
}

Answer Source

For performance reasons, the average calculation should be delegated to the database. Avoid calling CompanyDAO.getAll() to calculate the average by yourself. Doing so may lead to the n+1 selects problem when you have LAZY loading selected.

Use this JPQL query to do the average calculation in the database:

SELECT e.company, AVG(e.salary) FROM Employee e GROUP BY e.company

More precisely, change CompanyDAO.java as follows:

public static List<Company> getAll() {
    EntityManager em = GestionFactory.factory.createEntityManager();
    Query q = em.createQuery("SELECT e.company, AVG(e.salary) FROM Employee e GROUP BY e.company");

    // each list entry contains a tuple (company, avgSalary)
    List<Object[]> results = q.getResultList();

    List<Company> companiesList = new LinkedList<>();
    for (Object[] entry : results) {
        Company company = (Company) entry[0]; // contains "e.company"
        Double avgSalary = (Double) entry[1]; // contains "AVG(e.salary)"
        company.setAverageSalary(avgSalary);
        companiesList.add(company);
    }

    return companiesList;
}

For this to work, extend your Company.java entity type as follows:

@Entity
public class Company implements Serializable {

    // ...

    @Transient
    private double averageSalary;

    public void setAverageSalary(double averageSalary) {
        this.averageSalary = averageSalary;
    }

    public double getAverageSalary() {
        return averageSalary;
    }
}

In you JSP, simply use <%=company.getAverageSalary()%>.

Please consider this a draft solution that needs further refinement. This solution works for EclipseLink. With Hibernate, you would rather use the @Formula annotation.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download