java获得每个公司的平均薪水并传递给JSP [英] 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?

这是我的Company.java

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

在我的controller.java内部

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

在我的CompanyDAO.java文件中

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

在我的EmployeeDAO.java内部

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

并在Employee.java内部

and inside Employee.java

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

推荐答案

出于性能原因，应将平均值计算委托给数据库.避免调用CompanyDAO.getAll()自己计算平均值.这样做可能会导致 n + 1选择问题. >已选择加载.

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.

使用此JPQL查询在数据库中进行平均值计算:

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

更准确地说，如下更改CompanyDAO.java:

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

要执行此操作，请扩展您的Company.java实体类型，如下所示:

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

在您的JSP中，只需使用<%=company.getAverageSalary()%>.

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

请认为这是一个需要进一步完善的解决方案草案.该解决方案适用于EclipseLink.对于Hibernate，您宁愿使用@Formula批注.

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

这篇关于java获得每个公司的平均薪水并传递给JSP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！