使用Speedment对由多个表中的数据组成的结果表执行聚合函数

Question

我完全不知道如何使用Speedment Framework将此sql查询转换为Java Streams。Java中的“结果表”可以是任何类型(它可以是Map，甚至可以是一个专门定义的用户类)。

下面是我试图转换成Java Streams的sql查询：

SELECT d.dept_name, AVG(s.salary)
FROM employees e
JOIN salaries s
ON e.emp_no=s.emp_no
JOIN dept_emp de ON de.emp_no = e.emp_no
JOIN  departments d ON d.dept_no=de.dept_no
WHERE s.to_date = '9999-01-01' AND de.to_date = '9999-01-01'
GROUP BY d.dept_name;

DB Scheme which I'm using

来源：https://github.com/datacharmer/test_db/tree/master/images

提前谢谢。

Answer 1

多亏了Youtube上的Speedment频道，我找到了解决方案。

1)创建聚合的Result/Wrapper类：

private static class Result {
        private Double salary;
        private String departmentName;

        public Double getSalary() { return salary; }
        public void setSalary(Double salary) { this.salary = salary; }
        public String getDepartmentName() { return departmentName; }
        public void setDepartmentName(String departmentName) {
            this.departmentName = departmentName;
        }

        @Override
        public String toString() {
            return "Result{" +
                    "salary=" + salary +
                    ", departmentName='" + departmentName + '\'' +
                    '}';
        }
    }

2)创建join对象：

Join<Tuple2<Departments, Salaries>> join = app.getOrThrow(JoinComponent.class)
                .from(EmployeesManager.IDENTIFIER)
                .innerJoinOn(DeptEmp.EMP_NO).equal(Employees.EMP_NO)
                    .where(DeptEmp.TO_DATE.equal(Date.valueOf("9999-01-01")))
                .innerJoinOn(Departments.DEPT_NO).equal(DeptEmp.DEPT_NO)
                .innerJoinOn(Salaries.EMP_NO).equal(Employees.EMP_NO)
                    .where(Salaries.TO_DATE.equal(Date.valueOf("9999-01-01")))
                .build((a,b,c,d) -> Tuples.of(c,d));

3)创建聚合规则：

AggregationCollector<Tuple2<Departments, Salaries>, ?, Result> aggregationCollector = Aggregator.builder(Result::new)
                            .firstOn(Tuple2.<Departments, Salaries>getter0())
                                .andThen(Departments.DEPT_NAME)
                                .key(Result::setDepartmentName)
                            .firstOn(Tuple2.getter1())
                                .andThen(Salaries.SALARY)
                                .average(Result::setSalary)
                            .build()
                            .createCollector();

4)创建聚合对象：

Aggregation<Result> aggregation = join.stream().collect(aggregationCollector);

5)做任何你需要做的事情：

aggregation.streamAndClose().forEachOrdered(System.out::println);

输出：

Result{salary=67657.91955820487, departmentName='Development'}
Result{salary=78559.93696229013, departmentName='Finance'}
Result{salary=67843.30198484214, departmentName='Production'}
Result{salary=80058.84880743832, departmentName='Marketing'}
Result{salary=63921.89982943111, departmentName='Human Resources'}
Result{salary=88852.96947030538, departmentName='Sales'}
Result{salary=67285.23017815467, departmentName='Customer Service'}
Result{salary=67913.3749757136, departmentName='Research'}
Result{salary=65441.99340024768, departmentName='Quality Management'}