JPA 通过避免 JOIN 查找表来优化查询？

发布于 2024-09-17 21:46:59 字数 1152 浏览 3 评论 0原文

想象一下表 emp:

CREATE TABLE emp
( id           NUMBER
, name         VARCHAR
, dept_code    VARCHAR
)

和表 dept:

CREATE TABLE dept
( code         VARCHAR
, name         VARCHAR
)

emp.dept_code 将 dept.code 引用为外键。

这些表映射到 JPA 实体，并且将外键建模为关联：

@ManyToOne
@JoinColumn(name = "dept_code")
private Department department;

给定以下数据：

emp                     dept    
----------------        ------------------
1    John   SALS        SALS     Sales
2    Louis  SALS        SUPT     Support
3    Jack   SUPT 
4    Lucy   SUPT

我想编写一个返回支持部门中所有员工的 JPA 查询。假设我知道支持部门的主键 (SUPT)，

我猜这将是：

SELECT emp
  FROM Employee emp JOIN emp.department dept
 WHERE dept.code = 'SUPT'

问题：

作为部门键 SUPT< /code> 代码在 emp 表中可用，有没有办法重写 JPA 查询通过避免连接到部门实体？

这会带来性能提升吗？或者 JPA 实现（如 Hibernate）是否足够智能，可以避免数据库连接到 dept 表？

原文

Imagine a table emp:

CREATE TABLE emp
( id           NUMBER
, name         VARCHAR
, dept_code    VARCHAR
)

and a table dept:

CREATE TABLE dept
( code         VARCHAR
, name         VARCHAR
)

emp.dept_code references dept.code as a ForeignKey.

These tables are mapped to JPA Entities, and the ForeignKey is modeled as an association:

@ManyToOne
@JoinColumn(name = "dept_code")
private Department department;

Given following data:

emp                     dept    
----------------        ------------------
1    John   SALS        SALS     Sales
2    Louis  SALS        SUPT     Support
3    Jack   SUPT 
4    Lucy   SUPT

I would like to write a JPA query that returns all Emloyees in the Support Department. Assume I know the PrimaryKey of the Support Department (SUPT)

I guess that would be:

SELECT emp
  FROM Employee emp JOIN emp.department dept
 WHERE dept.code = 'SUPT'

Question:

As the Department key SUPT code is available in the emp table, is there a way to rewrite the JPA query by avoiding the JOIN to the Department Entity?

Would this result in a performance improvement? Or is a JPA implementation (like Hibernate) smart enough to avoid the database join to the dept table?

分享到QQ

分享到微博

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

发布评论

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

一直在等你来 2024-09-24 21:46:59

您通常会将查询编写为

select emp
from employee emp
where emp.department.code = 'SUPT'

并让您的提供商找出得出结果的最佳方法。就休眠而言，是的，它足够聪明，意识到它可以只查看连接列。

编辑：值得注意的是，您尚未在注释中设置延迟加载，因此无论如何它都会加入表以创建部门实体:)

You would usually write the query as

select emp
from employee emp
where emp.department.code = 'SUPT'

and let your provider figure out the best way to come up with the result. In the case of hibernate, yes, it is smart enough to realize it can just look at the join column.

edit : It is worth noting, that you haven't set up lazy loading in your annotations, so it's going to join the table in to create the department entity anyway :)

回复收藏 0 原文

~没有更多了~