英文:
Spring Data JpaRepository "JOIN FETCH" returns duplicates
问题
我正在编写一个简单的Spring Data JPA应用程序。我使用MySQL数据库。有两个简单的表:
- Department(部门)
- Employee(员工)
每个员工都在某个部门工作(Employee.department_id)。
@Entity
public class Department {
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Id
private Long id;
@Basic(fetch = FetchType.LAZY)
@OneToMany(mappedBy = "department")
List<Employee> employees;
}
@Entity
public class Employee {
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Id
private Long id;
@ManyToOne
@JoinColumn
private Department department;
}
@Repository
public interface DepartmentRepository extends JpaRepository<Department, Long> {
@Query("FROM Department dep JOIN FETCH dep.employees emp WHERE dep = emp.department")
List<Department> getAll();
}
方法getAll
返回一个带有重复部门的列表(每个部门重复出现的次数与该部门中的员工数量相同)。
问题1:我是否正确,这与Spring Data JPA无关,而与Hibernate有关?
问题2:修复它的最佳方法是什么?(我至少找到两种方法:1)使用Set<Department> getAll();
2)在@Query
注释中使用"SELECT DISTINCT dep"
)
英文:
I'm writing a simple Spring Data JPA application. I use MySQL database. There are two simple tables:
- Department
- Employee
Each employee works in some department (Employee.department_id).
@Entity
public class Department {
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Id
private Long id;
@Basic(fetch = FetchType.LAZY)
@OneToMany(mappedBy = "department")
List<Employee> employees;
}
@Entity
public class Employee {
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Id
private Long id;
@ManyToOne
@JoinColumn
private Department department;
}
@Repository
public interface DepartmentRepository extends JpaRepository<Department, Long> {
@Query("FROM Department dep JOIN FETCH dep.employees emp WHERE dep = emp.department")
List<Department> getAll();
}
The method getAll
returns a list with duplicated departments (each department is repeated as many times as there are employees in this department).
Question 1: Am I rigth that this is a feature related not to Spring Data JPA, but to to Hibernate?
Question 2: What is the best way to fix it? (I found at least two ways: 1) use Set<Department> getAll();
2) use "SELECT DISTINCT dep"
in @Query
annotation)
答案1
得分: 4
FROM Department dep JOIN FETCH dep.employees emp
表达式会生成一个本地查询,该查询返回纯粹的 Department-Employee
结果。每个 Department
将会被返回 dep.employees.size()
次。这是 JPA 提供者(在你的情况下是 Hibernate
)预期的行为。
使用 distinct
来消除重复项似乎是一个不错的选择。将 Set<Department>
作为查询结果会导致无法获取有序的结果。
英文:
FROM Department dep JOIN FETCH dep.employees emp
expression generates native query which returns plain result Department-Employee
. Every Department
will be returned dep.employees.size()
times. This is a JPA-provider expected behavior (Hibernate
in your case).
Using distinct
to get rid of duplicates seems like a good option. Set<Department>
as a query result makes it impossible to get the ordered result.
答案2
得分: 0
你可能也想使用 LEFT JOIN FETCH
以及 DISTINCT
。普通的 JOIN FETCH
使用内连接,因此没有员工的部门将不会被查询返回。而 LEFT JOIN FETCH
则使用外连接。
英文:
You probably want to use LEFT JOIN FETCH
as well as DISTINCT
. Normal JOIN FETCH
uses an inner join so Departments without any employees will not be returned by your query. LEFT JOIN FETCH uses an outer join instead.
答案3
得分: -1
我对在这里使用@Query
并不确定。如果您通过@OneToMany
和@ManyToOne
注解映射了这两个实体,则此连接是多余的。
而且在这里,为什么要使用@Basic(fetch = FetchType.LAZY)
?也许您应该在@OneToMany
中设置延迟初始化?
查看这个资源,希望您会找到它有帮助
英文:
I'm not sure about using @Query
here. If you mapped these 2 entities via annotations @OneToMany
and @ManyToOne
this join is redundant.
And second moment here, why is @Basic(fetch = FetchType.LAZY)
here? Maybe you should set lazy init in @OneToMany
?
Check out this resource, I hope you'll find it helpfull
答案4
得分: -1
根据您提供的内容,以下是翻译好的部分:
根据我所了解的情况,您提到的用例中,您不需要定义自己的方法,而是可以使用继承自 PagingAndSortingRepository
的 repository.findAll
方法,该方法又继承自 CrudRepository
并由 SimpleJpaRepository
实现。请参阅此处。
因此,只需将 repository 接口保留为空白,如下所示:
@Repository
public interface DepartmentRepository extends JpaRepository<Department, Long> {
}
然后在需要的地方注入并使用它,例如在 DepartmentService
中:
public interface DepartmentService {
List<Link> getAll();
}
@Component
public class DepartmentServiceImpl implements DepartmentService {
private final DepartmentRepository repository;
// 通过构造函数进行依赖注入
@Autowired
public DepartmentServiceImpl(DepartmentRepository repository) {
this.repository = repository;
}
@Override
public List<Department> getAll() {
return repository.findAll();
}
}
英文:
With my knowledge, the use case you have mentioned, you don't need to define your own method and instead use repository.findAll
which is inherited from PagingAndSortingRepository
which extends CrudRepository
and implemented by SimpleJpaRepository
. See here
So just leave the repository interface blank as below,
@Repository
public interface DepartmentRepository extends JpaRepository<Department, Long> {
}
Then inject and use it wherever you need, e.g. lets say with DepartmentService
public interface DepartmentService {
List<Link> getAll();
}
@Component
public class DepartmentServiceImpl implements DepartmentService {
private final DepartmentRepository repository;
// dependency injection via constructor
@Autowired
public DepartmentServiceImpl(DepartmentRepository repository) {
this.repository = repository;
}
@Override
public List<Department> getAll() {
return repository.findAll();
}
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论