我有一个疑问
SELECT d.name, count(e.id) FROM department d LEFT OUTER JOIN employee e on e.department_id = d.id and e.salary > 5000
以及我如何将其转换为 jpa
现在我有:
CriteriaQuery<Object[]> criteria = builder.createQuery(Object[].class);
Root<Department> root = criteria.from(Department.class);
Path<String> name = root.get("name");
Expression<Long> empCount = builder.count(root.get("employees").get("id"));
criteria.multiselect(name,empCount);
TypedQuery<Object[]> query = em.createQuery(criteria);
我通过删除排序和分组来简化这两个示例
谁能告诉我如何修改 jpa 代码以获得与 sql 查询相同的结果
提前致谢
你离结果不远了。问题是,据我所知,你不能添加任何限制on
子句,使用 JPA。所以查询必须重写为
SELECT d.name, count(e.id) FROM department d
LEFT OUTER JOIN employee e on e.department_id = d.id
where (e.id is null or e.salary > 5000)
这是未测试的查询的等效项):
CriteriaQuery<Object[]> criteria = builder.createQuery(Object[].class);
Root<Department> root = criteria.from(Department.class);
Path<String> name = root.get("name");
Join<Department, Employee> employee = root.join("employees", JoinType.LEFT);
Expression<Long> empCount = builder.count(employee.get("id"));
criteria.multiselect(name,empCount);
criteria.where(builder.or(builder.isNull(employee.get("id")),
builder.gt(employee.get("salary"), 5000)));
TypedQuery<Object[]> query = em.createQuery(criteria);
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)