java - Hibernate @ManyToMany 不使用 joinTable 并产生笛卡尔而不是内部连接
问题描述
同事们,当关系@ManyToMany 时,您能否就 Hibernate 问题向我提出建议。项目实体:
@Entity
@Table(name = "PROJECT")
@NamedQueries( {
@NamedQuery(name = "Projects.employeesByExternal", query = "SELECT P FROM Project P INNER JOIN Employee E ON E.external=:external" )
} )
public class Project extends AbstractIdentified
{
@Column(name = "NAME") private String name;
@Column(name = "EXT") private Boolean external;
@ManyToMany
@JoinTable( name = "EMPLOYEE_PROJECTS",
joinColumns = { @JoinColumn(name = "PROJECT_ID") },
inverseJoinColumns = { @JoinColumn(name = "EMPLOYEE_ID") } )
private Set<Employee> employees;
...
// getters/setters here
}
员工实体:
@Entity
@Table(name = "EMPLOYEE")
public class Employee extends AbstractIdentified
{
@ManyToMany(mappedBy = "employees")
private Set<Project> projects;
....
// other fields and getters/setters here
}
当应用程序启动时,它运行 init.sql:
insert into employee (id,status,ext) values (1,'CORP',true);
insert into employee (id,status,ext) values (2,'CORP',false);
insert into employee (id,status,ext) values (3, 'EXT',true);
insert into project (id,name) values (1,'project1');
insert into project (id,name) values (2,'project2');
insert into project (id,name) values (3,'project3');
insert into employee_projects (employee_id, project_id) values (1,1);
insert into employee_projects (employee_id, project_id) values (2,2);
insert into employee_projects (employee_id, project_id) values (3,3);
在 DAO 中,我准备了 DAO 方法来选择员工(至少一个)具有 external=false 的项目:
public List<Project> getProjectsByExternal( boolean isExternal )
{
TypedQuery<Project> typedQuery = entityManager.createNamedQuery( "Projects.employeesByExternal", Project.class );
typedQuery.setParameter( "external", isExternal );
return typedQuery.getResultList();
}
现在我调用如下方法:
List<Project> list = projectDao.getProjectsByExternal(false);
我希望只得到一个 Project#2{name='project2'},但结果是 3 个项目:
01:03:49 INFO vez.Starter - Project#1{name='project1'}
01:03:49 INFO vez.Starter - Project#2{name='project2'}
01:03:49 INFO vez.Starter - Project#3{name='project3'}
当我检查 Hibernate 生成的 SQL(属性 hibernate.show_sql = true)时,我注意到 Hibernate 创建连接表“PROJECT”和“EMPLOYEE”而不使用中间表“EMPLOYEE_PROJECTS”(参见项目类中的注释@JoinTable)。有生成的SQL:
01:03:49 DEBUG org.hibernate.SQL -
select
project0_.ID as ID1_3_,
project0_.NAME as NAME2_3_
from
PROJECT project0_
inner join
EMPLOYEE employee1_
on ( employee1_.EXT=?)
实际上,这意味着已为所有项目选择并乘以一名员工。我很困惑,因为这是我所期望的笛卡尔而不是内部连接。请指教我做错了什么?如何使用 NativeQuery 仅选择一个/多个员工具有外部属性 value=false 的项目?pom.xml:
<spring.version>5.0.5.RELEASE</spring.version>
<hibername.version>5.3.0.CR2</hibername.version>
解决方案
问题来自您的 JPQL 查询:
SELECT P FROM Project P INNER JOIN Employee E ON E.external=:external
您应该将其更改为:
SELECT P
FROM Project P
INNER JOIN P.employees E
Where E.external=:external
推荐阅读
- c# - Xamarin HttpClient GetStringAsync 似乎不一致
- c++ - 为什么不是 while ((i <= 9) && square == pow(i, 2)) { cout << i << square; 我++;按我的意愿打印出来?
- android-studio - 在 AS 3.6 中哪里可以找到 r.java
- javascript - Vuex 状态不会随着突变而更新
- javascript - 如何将参数传递给 API 请求 2,我在反应中从 API 响应 1 收到该参数
- java - 区间列表交叉点
- javascript - 日期未出现 JavaScript
- xamarin.forms - Xamarin 形成多语言不选择其他语言键
- python - 在python opencv中获取强度为255的二值图像的像素位置
- android - 生成签名的 APK 会从工作项目中构建损坏的 APK