如何正确表达JPQL“join fetch”与“哪里”子句作为 JPA 2 CriteriaQuery?

发布于 11-04 07:03 字数 567 浏览 3 评论 0原文

考虑以下 JPQL 查询:

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

我正在尝试将其转换为 Criteria 查询。据我所知:

var cb = em.getCriteriaBuilder();
var query = cb.createQuery(Foo.class);
var foo = query.from(Foo.class);
var fetch = foo.fetch(Foo_.bar, JoinType.INNER);
var join = foo.join(Foo_.bar, JoinType.INNER);
query.where(cb.equal(join.get(Bar_.baz), value);

这里明显的问题是我做了两次相同的连接,因为 Fetch 类似乎没有获取 Path< /代码>。 有什么办法可以避免加入两次吗?或者我是否必须坚持使用良好的旧式 JPQL 来进行如此简单的查询?

Consider the following JPQL query:

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

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

var cb = em.getCriteriaBuilder();
var query = cb.createQuery(Foo.class);
var foo = query.from(Foo.class);
var fetch = foo.fetch(Foo_.bar, JoinType.INNER);
var join = foo.join(Foo_.bar, JoinType.INNER);
query.where(cb.equal(join.get(Bar_.baz), value);

The obvious problem here is that I am doing the same join twice, because the Fetch class 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?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

万劫不复2024-11-11 07:03:41

在 JPQL 规范中实际上也是如此。 JPA 规范不允许为获取连接指定别名。问题是,通过限制连接获取的上下文,您很容易搬起石头砸自己的脚。加入两次比较安全。

这通常是 ToMany 的问题,而不是 ToOnes 的问题。
例如,

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

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

当然,如果您知道自己在做什么,那么额外的联接是不可取的,一些 JPA 提供程序可能允许对联接获取别名,并且可能允许将 Criteria Fetch 强制转换为联接。

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.

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

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.

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.

书信已泛黄2024-11-11 07:03:41

我将使用詹姆斯回答中的相同示例并添加替代解决方案来直观地展示问题。

当您执行以下查询时,如果不使用 FETCH

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

您将从 Employee 获得预期的以下结果:

EmployeeIdEmployeeNamePhoneIdPhoneAreaCode
1James5613
1James6416

但是当您在 JOIN (FETCH JOIN) 上添加 FETCH 子句时,会发生以下情况:

EmployeeIdEmployeeNamePhoneIdPhoneAreaCode
1James5613

两个查询生成的 SQL 相同,但当您使用 WHERE 时,Hibernate 会在内存删除 416 寄存器在 FETCH 连接上。

因此,要使所有手机正确应用WHERE,您需要有两个JOIN:一个用于WHERE 和另一个用于 FETCH。例如:

Select e from Employee e 
join e.phones p 
join fetch e.phones      //no alias, to not commit the mistake
where p.areaCode = '613'

也许在最新版本的 Hibernate 中,您需要使用 SELECT DISTINCT 来避免重复结果。

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

When you do the follow query, without the 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:

EmployeeIdEmployeeNamePhoneIdPhoneAreaCode
1James5613
1James6416

But when you add the FETCH clause on JOIN (FETCH JOIN), this is what happens:

EmployeeIdEmployeeNamePhoneIdPhoneAreaCode
1James5613

The generated SQL is the same for the two queries, but the Hibernate removes on memory the 416 register when you use WHERE on the FETCH join.

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

Select e from Employee e 
join e.phones p 
join fetch e.phones      //no alias, to not commit the mistake
where p.areaCode = '613'

Maybe in the newest versions of Hibernate you will need to use SELECT DISTINCT to avoid duplicate results.

埖埖迣鎅2024-11-11 07:03:41

我可能会晚回答这个问题,但从我的角度来看。

Select e from Employee e 
join e.phones p 
join fetch e.phones      //no alias, to not commit the mistake
where p.areaCode = '613'

这可以转换为以下 SQL 查询

Select e.id, e.name, p.id ,p.phone
From Employe e
inner join Phone p on e.id = p.emp_id
where exists(
  select 1 from Phone where Phone.id= p.id and Phone.area ='XXX'  
)

这将获取属于某个区域的员工的所有电话。

Select e from Employee e 
join fetch e.phones p      //no alias, to not commit the mistake
where p.areaCode = '613'

可以转换为以下 SQL 查询

Select  e.id, e.name, p.id ,p.phone
From    Employe e
inner   join Phone p on e.id = p.id
Where   p.area ='XXX'  

,否则

Select e.id, e.name, p.id ,p.phone
From Employe e
inner join Phone p on e.id = p.emp_id and p.area ='XXX'  

这会将行选择限制为仅员工电话位于 XXX 区域的行

最后编写此内容

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

可以被视为

Select e.id, e.name 
from Employe e
where exists (
 select 1 from phone p where p.emp_id = e.id and p.area = 'XXX'
)

我们仅获取在某个区域具有电话号码的员工数据

这应该帮助在每次查询后获得想法。

I may answer late this but from my point of view.

Select e from Employee e 
join e.phones p 
join fetch e.phones      //no alias, to not commit the mistake
where p.areaCode = '613'

This could be translated to the following SQL Query

Select e.id, e.name, p.id ,p.phone
From Employe e
inner join Phone p on e.id = p.emp_id
where exists(
  select 1 from Phone where Phone.id= p.id and Phone.area ='XXX'  
)

This will get all phones of an employee that belongs to an area.

BUT

Select e from Employee e 
join fetch e.phones p      //no alias, to not commit the mistake
where p.areaCode = '613'

could be translated to the following SQL Queries

Select  e.id, e.name, p.id ,p.phone
From    Employe e
inner   join Phone p on e.id = p.id
Where   p.area ='XXX'  

or

Select e.id, e.name, p.id ,p.phone
From Employe e
inner join Phone p on e.id = p.emp_id and p.area ='XXX'  

this will restrict row selection to only rows where employees phone is of area XXX

And finally writing this

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

Could be seen as

Select e.id, e.name 
from Employe e
where exists (
 select 1 from phone p where p.emp_id = e.id and p.area = 'XXX'
)

Where we are only getting employee data that have a phone number in some area

This should help get the idea after each query.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文