java get average salary for each company and pass to JSP

Question

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;
}

What JPA implementation are you using? For Hibernate there is the @Formula annotation, see: http://stackoverflow.com/questions/2986318/calculated-property-with-jpa-hibernate — Philipp Merkle, Feb 05 '17 at 19:56
@P.Merkle jdbc eclipselink, using sqlite, i hope this helps. `org.eclipse.persistence.jpa.PersistenceProvider` — passion, Feb 05 '17 at 20:00

score 3 · Accepted Answer · edited May 23 '17 at 12:17

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.

thanks a lot for this great answer. it is working, just i had to change List getAll()to List getAll() in the beginning. — passion, Feb 06 '17 at 23:54
i have a similar question to this one that you answered. it is here http://stackoverflow.com/questions/42057186/java-get-average-for-all-items-in-a-selected-category is it possible to provide an answer to this one as well. There is already an answer to this, but it is doing java inside JSP, how can i create method for that ? thanks — passion, Feb 06 '17 at 23:57
glad I could help, and thank for pointing out the wrong return type, which is fixed now. — Philipp Merkle, Feb 07 '17 at 10:15

score 1 · Answer 2 · answered Feb 05 '17 at 20:01

You can use this method to get the average salary of all employees in a company -

public static int getAverageSalary() {
    EntityManager em = GestionFactory.factory.createEntityManager();
    Query q = em.createQuery("SELECT e FROM Employee e");
    @SuppressWarnings("unchecked")
    List<Employee> listEmployee = q.getResultList();
    // now salary specific calculations
    int sumOfSalaries = 0;
    int totalEmployee = listEmployee.size();
    for(Employee eachEmployee : listEmployee) {
        sumOfSalaries = sumOfSalaries + eachEmployee.getSalary();
    }
    return sumOfSalaries/totalEmployee;
}

where should this go? in CompanyDAO.java ? how do i call this in JSP file? thanks for further explanations — passion, Feb 05 '17 at 20:06

java get average salary for each company and pass to JSP

my question:

2 Answers2

Linked