Spring Data JpaRepository “JOIN FETCH” 返回重复项

huangapple go评论71阅读模式
英文:

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 = &quot;department&quot;)
    List&lt;Employee&gt; employees;
}

@Entity
public class Employee {
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Id
    private Long id;

    @ManyToOne
    @JoinColumn
    private Department department;
}

@Repository
public interface DepartmentRepository extends JpaRepository&lt;Department, Long&gt; {
	@Query(&quot;FROM Department dep JOIN FETCH dep.employees emp WHERE dep = emp.department&quot;)
    List&lt;Department&gt; 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&lt;Department&gt; getAll(); 2) use &quot;SELECT DISTINCT dep&quot; in @Query annotation)

答案1

得分: 4

FROM Department dep JOIN FETCH dep.employees emp 表达式会生成一个本地查询,该查询返回纯粹的 Department-Employee 结果。每个 Department 将会被返回 dep.employees.size() 次。这是 JPA 提供者(在你的情况下是 Hibernate)预期的行为。

使用 distinct 来消除重复项似乎是一个不错的选择。将 Set&lt;Department&gt; 作为查询结果会导致无法获取有序的结果。

英文:

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&lt;Department&gt; 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

根据您提供的内容,以下是翻译好的部分:

根据我所了解的情况,您提到的用例中,您不需要定义自己的方法,而是可以使用继承自 PagingAndSortingRepositoryrepository.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&lt;Department, Long&gt; {

}

Then inject and use it wherever you need, e.g. lets say with DepartmentService

public interface DepartmentService {
    
    List&lt;Link&gt; 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&lt;Department&gt; getAll() {	
        return repository.findAll();
    }
}

huangapple
  • 本文由 发表于 2020年9月3日 18:22:54
  • 转载请务必保留本文链接:https://go.coder-hub.com/63721646.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定