如何正确表达JPQL“join fetch”和“where”子句为JPA 2 CriteriaQuery？

Consider the following JPQL query:

考虑以下JPQL查询：

SELECT foo FROM Foo foo
INNER JOIN FETCH foo.bar bar
WHERE bar.baz = :baz

I'm trying to translate this into a Critieria query. This is as far as I have gotten:

我正在尝试将其转换为Critieria查询。这是我得到的：

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Foo> cq = cb.createQuery(Foo.class);
Root<Foo> r = cq.from(Foo.class);
Fetch<Foo, Bar> fetch = r.fetch(Foo_.bar, JoinType.INNER);
Join<Foo, Bar> join = r.join(Foo_.bar, JoinType.INNER);
cq.where(cb.equal(join.get(Bar_.baz), value);

The obvious problem here is that I am doing the same join twice, because Fetch<Foo, Bar> doesn't seem to have a method to get a Path. Is there any way to avoid having to join twice? Or do I have to stick with good old JPQL with a query as simple as that?

这里显而易见的问题是我正在进行两次相同的连接，因为Fetch 似乎没有获取Path的方法。有没有办法避免必须加入两次？或者我必须坚持使用简单的查询这么好的旧JPQL？，bar>

2 个解决方案

#1

In JPQL the same is actually true in the spec. The JPA spec does not allow an alias to be given to a fetch join. The issue is that you can easily shoot yourself in the foot with this by restricting the context of the join fetch. It is safer to join twice.

在JPQL中，规范中的情况也是如此。 JPA规范不允许为获取连接提供别名。问题在于，通过限制连接提取的上下文，您可以轻松地用脚来射击自己。加入两次会更安全。

This is normally more an issue with ToMany than ToOnes. For example,

这通常是ToMany比ToOnes更多的问题。例如，

Select e from Employee e 
join fetch e.phones p 
where p.areaCode = '613'

This will incorrectly return all Employees that contain numbers in the '613' area code but will left out phone numbers of other areas in the returned list. This means that an employee that had a phone in the 613 and 416 area codes will loose the 416 phone number, so the object will be corrupted.

这将错误地返回包含“613”区号中的数字的所有员工，但会遗漏返回列表中其他区域的电话号码。这意味着拥有613和416区号的电话的员工将丢失416电话号码，因此该对象将被破坏。

Granted, if you know what you are doing, the extra join is not desirable, some JPA providers may allow aliasing the join fetch, and may allow casting the Criteria Fetch to a Join.

当然，如果你知道你在做什么，那么额外的连接是不可取的，一些JPA提供者可能允许对连接提取别名，并且可能允许将Criteria Fetch转换为Join。

#2

I will show visually the problem, using the great example from James answer and adding the alternative solution.

我将使用James回答的优秀示例并添加替代解决方案来直观地展示问题。

When you do the follow query, without the FETCH:

当您执行以下查询时，不使用FETCH：

Select e from Employee e 
join e.phones p 
where p.areaCode = '613'

You will have the follow results from Employee as you expected:

您将按照预期获得Employee的以下结果：

EmployeeId | EmployeeName | PhoneId | PhoneAreaCode
1          | James        | 5       | 613
1          | James        | 6       | 416

But when you add the FETCH word on JOIN, this is what happens*:

但是当你在JOIN上添加FETCH字时，这就是*：

EmployeeId | EmployeeName | PhoneId | PhoneAreaCode
1          | James        | 5       | 613

So, to bring all phones and apply the WHERE, you need to have two JOIN: one for the WHERE and another for the FETCH. Like:

因此，要带上所有电话并应用WHERE，您需要有两个JOIN：一个用于WHERE，另一个用于FETCH。喜欢：

Select e from Employee e 
join e.phones p 
join fetch e.phones
where p.areaCode = '613'

^{*. The original SQL brings the same results from the query without FETCH, but the Hibernate remove on memory the 416 register when you use FETCH with WHERE.}

*。原始SQL从没有FETCH的查询中得到相同的结果，但是当您将FETCH与WHERE一起使用时，Hibernate会在内存中删除416寄存器。

#1