如何正确表达JPQL“join fetch”与“哪里”子句作为 JPA 2 CriteriaQuery?
考虑以下 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 技术交流群。
发布评论
评论(3)
我将使用詹姆斯回答中的相同示例并添加替代解决方案来直观地展示问题。
当您执行以下查询时,如果不使用 FETCH
:
Select e from Employee e
join e.phones p
where p.areaCode = '613'
您将从 Employee
获得预期的以下结果:
EmployeeId | EmployeeName | PhoneId | PhoneAreaCode |
---|---|---|---|
1 | James | 5 | 613 |
1 | James | 6 | 416 |
但是当您在 JOIN
(FETCH JOIN
) 上添加 FETCH
子句时,会发生以下情况:
EmployeeId | EmployeeName | PhoneId | PhoneAreaCode |
---|---|---|---|
1 | James | 5 | 613 |
两个查询生成的 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 来避免重复结果。
我可能会晚回答这个问题,但从我的角度来看。
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'
)
我们仅获取在某个区域具有电话号码的员工数据
这应该帮助在每次查询后获得想法。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
在 JPQL 规范中实际上也是如此。 JPA 规范不允许为获取连接指定别名。问题是,通过限制连接获取的上下文,您很容易搬起石头砸自己的脚。加入两次比较安全。
这通常是 ToMany 的问题,而不是 ToOnes 的问题。
例如,
这将错误返回包含“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,
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.