将sql查询转换为jpa [英] conversion sql query to jpa

问题描述

我有一个查询

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 现在我有:

and how i can convert this to jpa right now i have:

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查询中获得相同的结果

I simplified both examples by removing ordering and grouping can anyone tell me how i can modifie my jpa code to get same reslults like from my sql query

预先感谢

推荐答案

您离结果不远.问题是，对于AFAIK，您不能使用JPA在on子句上添加任何限制.因此查询必须重写为

You're not far from the result. The problem is that, AFAIK, you can't add any restriction on the on clause, using JPA. So the query wil have to be rewritten as

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)

以下是此查询的等效项，未经测试):

Here is the equivalent of this query not tested):

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

这篇关于将sql查询转换为jpa的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！