我正在处理一个Spring-Boot应用程序,JPA在板上,数据库在Postgres中。
当前的目标是从一个表中检索条目,以及从另一个表中检索一些聚合的统计信息。
在传统的SQL中,我会使用LEFT JOIN (子查询)语句,如下所示:
SELECT d.*, stats.avg_salary
FROM departments d
LEFT JOIN
(SELECT e.dep_id, avg(e.salary) AS avg_salary
FROM employees e
GROUP BY e.dep_id) stats
ON (d.id = stats.dep_id)
现在我需要JPQL中类似的东西。
重点是检索所有部门,包括那些具有空工资的员工或根本没有员工的部门。
到目前为止,我创建的查询(见下文)只返回具有匹配的部门(它的工作方式类似于内部联接),但我需要它还包括平均工资为空的条目。
@Query("SELECT d, avg(e.salary) "
+"FROM Department d, Employee e "
+"WHERE (e.department = d) "
+"GROUP BY d")
public List<Tuple> getDepartmentsStats();
用JPQL编写这样的查询最优雅的方式是什么?
对我选择的解决方案执行
当我的查询变得更加复杂时,我意识到最合适的方法是使用原生查询和投影,就像@Dirk Deyne建议的那样。
我想说,这保持了实体类和数据模型的清晰和简单,同时允许自由地构建复杂的查询。
发布于 2019-05-23 03:42:46
您可以使用原生查询,如
@Query(value =
"SELECT d.name, stats.avg_salary " +
"FROM department d " +
"LEFT JOIN " +
" (SELECT e.department_id, avg(e.salary) AS avg_salary " +
" FROM employee e " +
" GROUP BY e.department_id) stats " +
"ON (d.id = stats.department_id)", nativeQuery = true
)
public List<Tuple> getNativeDepartmentsStats();
如果Department
与Employee
有密切的关系,比如
@Entity
class Department {
@Id @GeneratedValue
Long id;
String name;
@OneToMany(fetch = FetchType.EAGER, mappedBy = "department")
Set<Employee> employees;
...
然后,您可以使用纯JPQL,如
@Query("SELECT d , avg(e.salary) FROM Department d left join d.employees e GROUP BY d")
public List<Tuple> getFullDepartmentsStats();
也许更好的解决方案是使用像这样的投影
interface DepAvg {
Department getDepartment();
Long getAverage();
}
然后你可以使用像这样的东西
@Query(value =
"SELECT dep.name Department, stats.Average Average " +
"FROM department dep " +
"LEFT JOIN " +
" (SELECT e.department_id, avg(e.salary) AS Average " +
" FROM employee e " +
" GROUP BY e.department_id) stats " +
"ON (dep.id = stats.department_id)", nativeQuery = true
)
public List<DepAvg> getDepartmentsAvg();
https://stackoverflow.com/questions/56260693
复制相似问题