英文:
JPA criteria filter by related entity without join
问题
我想知道如何使用JPA Criteria API根据相关实体的外键筛选实体。
假设我有两个如下所示的实体:
public class Employee {
@Id
private Long id;
...
@ManyToOne
private Department department;
...
}
public class Department {
@Id
private Long id;
}
我想查询属于部门ID(1、2、3)的员工。
我之前能够使用Hibernate过时的Criteria来做到这一点,并想知道如何使用JPA Criteria谓词**不使用连接(root.join)**来实现它。从逻辑上讲,我不需要任何连接或子查询,因为所需结果可以从一个表中获取:
select e.* from employee e where e.department_id in (1,2,3)
** 更新 **
我的问题是,对于JPA Criteria还很陌生,我之前使用过时的Hibernate Criteria,我已经使用了CriteriaBuilder的所有API,比如(equal、notEqual、isNull、like等等);因此,我使用了CriteriaBuilder.In(expression).in(values)
。
但是,正如@frank的答案中所示,我发现对于IN的用法,我会使用Root.get(<attr>).in(<values>)
。
CriteriaBuilder.in
也可以不同地使用:
In<Object> inClause = criteriaBuilder.in(root.get(<attr>));
for (Long id : ids) {
inClause.value(id);
}
当然,第一种解决方案更简单。
英文:
I want to know how to use JPA Criteria API to filter entity by related entity's foreign key.
Let's say i have two entities as the following:
public class Employee {
@Id
private Long id;
...
@ManyToOne
private Department department;
...
}
public class Department {
@Id
private Long id;
}
I want to query the employees under departments of ids (1,2,3).
I was able to do that using Hibernate's depricated criteria, And want to know how to do it using the JPA Criteria predicate without join (root.join). It is logical that i don't need any join or subquery, as the desired result can be fetched from one table:
select e.* from employee e where e.department_id in (1,2,3)
** Update **
My problem was - as new for JPA Criteria and coming from the deprecated Hibernate Criteria - that I've used all APIs from the CriteriaBuilder such as (equal, notEqual, isNull, like, .....); And ,thus , used the CriteriaBuilder.In(experssion).in(values)
.
But, as shown in the answer of @frank, I figured out that for the IN usage, I'll use Root.get(<attr>).in(<values>)
The CriteriaBuilder.in
also can be used but differently:
In<Object> inClause = criteriaBuilder.in(root.get(<attr>);
for (Long id : ids) {
inClause.value(id);
}
But, Of-course, the first solution is easier.
答案1
得分: 2
Set<Integer> departments = new HashSet<Integer>();
departments.add(1);
departments.add(2);
departments.add(3);
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Employee> cq = cb.createQuery(Employee.class);
Root<Employee> r = cq.from(Employee.class);
cq.select(r).where(r.get(Employee_.department).get(Department_.id).in(departments));
TypedQuery<Employee> query = em.createQuery(cq);
List<Employee> result = query.getResultList();
英文:
Set<Integer> departments = new HashSet<Integer>();
departments.add(1);
departments.add(2);
departments.add(3);
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Employee> cq = cb.createQuery(Employee.class);
Root<Employee> r = cq.from(Employee.class);
cq.select(r).where(r.get(Employee_.department).get(Department_.id).in(departments));
TypedQuery<Employee> query = em.createQuery(cq);
List<Employee> result = query.getResultList();
答案2
得分: 0
在SQL中是合乎逻辑的,但在JPA中,你应该进行连接(Join)操作,以防止查询被启动以初始化部门(Department)类型的对象。
虽然可以这样做,但我认为最好还是使用连接操作。
最终,即使表中有这些信息,你的JPA实体(也就是Criteria API理解的内容)并没有该ID,它引用了一个具有该ID的对象。
虽然我不建议这样做,但实现方式可能类似于以下内容:
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Employee> cq = cb.createQuery(Employee.class);
Root<Employee> root = cq.from(Employee.class);
cq.where(root.get("department").in(listIds));
cq.select(root);
激活日志跟踪以显示已启动的查询,并思考是否值得执行带有连接的查询,还是执行不带连接的N个查询。
为了不过多地加载内存,你应该将关系设置为延迟加载(lazy)。
英文:
It is logical in SQL, but in JPA you should do the Join to prevent queries from being launched to initialize the Department type objects.
It can be done, but I think it would be better to do it with a Join.
In the end, even if the table has the information, your JPA entities, which is what Criteria-api understands, does not have that ID, it has a reference to an object with that ID.
Although I do not recommend it, the implementation would be something like this:
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Employee> cq = cb.createQuery(Employee.class);
Root<Employee> root = cq.from(Employee.class);
cq.where(root.get("department").in(listIds));
cq.select(root);
Activate the log traces to show the launched queries and think if it is worth throwing a query with a join or N queries without a join.
In order not to overload the memory you should establish the relations as lazy.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论