JPA标准查询过滤相关实体,无需连接

huangapple go评论76阅读模式
英文:

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(&lt;attr&gt;).in(&lt;values&gt;)

The CriteriaBuilder.in also can be used but differently:

In&lt;Object&gt; inClause = criteriaBuilder.in(root.get(&lt;attr&gt;);
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&lt;Integer&gt; departments = new HashSet&lt;Integer&gt;();
departments.add(1);
departments.add(2);
departments.add(3);

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery&lt;Employee&gt; cq = cb.createQuery(Employee.class);
Root&lt;Employee&gt; r = cq.from(Employee.class);
cq.select(r).where(r.get(Employee_.department).get(Department_.id).in(departments));
TypedQuery&lt;Employee&gt; query = em.createQuery(cq);
List&lt;Employee&gt; 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&lt;Employee&gt; cq = cb.createQuery(Employee.class);

Root&lt;Employee&gt; root = cq.from(Employee.class);

cq.where(root.get(&quot;department&quot;).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.

huangapple
  • 本文由 发表于 2020年8月28日 05:52:06
  • 转载请务必保留本文链接:https://go.coder-hub.com/63624603.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定