java - JPQL，是否可以在选择不同的对象时创建对象的子集合？

Question

我有以下 JPQL 查询:

@Query("SELECT project FROM Project project JOIN FETCH project.employees employee JOIN FETCH project.order ord " +
          "JOIN FETCH ord.customer JOIN FETCH project.defaultDailyEntrySettings LEFT JOIN FETCH employee.projectEmployeeRoles role " +
          "LEFT JOIN FETCH role.project roleProject LEFT JOIN FETCH roleProject.defaultDailyEntrySettings " +
          "WHERE project.id = :id")
Project test(@Param("id") long id);

问题是它在 project.employees 中返回重复的员工。对于员工在 employee.projectEmployeeRoles 中拥有的每个角色，它都会在 project.employees 列表中出现一次。这是由 JOIN FETCH employee.projectEmployeeRoles 角色 引起的。因此，如果员工有 7 个角色，那么它将在 project.employees 列表中出现 7 次。有没有办法使 project.employees 不同，或者是否有其他方法可以确保员工仅在列表中出现一次？我可以在 java 中删除重复项，但如果查询首先不返回任何重复项会更好。

如果我删除LEFT JOIN FETCH，结果是正确的并且不包含任何重复的员工。但这里的问题是，employee.projectEmployeeRoles 是为每个员工延迟获取的，这会导致太多的性能问题。

编辑:

我正在将 JPA 与 hibernate 一起使用。以下是上述实体的连接定义:

项目:

@Data
public class Project {

  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private long id;

  @ManyToMany
  @JsonIgnore
  @JoinTable(
          name = "employee_projects",
          joinColumns = @JoinColumn(name = "project_id"),
          inverseJoinColumns = @JoinColumn(name = "employee_id")
  )
  private List<Employee> employees;

  @OneToMany(mappedBy = "project", cascade = CascadeType.ALL, orphanRemoval = true)
  private List<ProjectEmployeeRole> projectEmployeeRoles;

  // more fields
}

员工:

@Data
public class Employee {

  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private long id;

  @ManyToMany
  @JsonIgnore
  @JoinTable(
          name = "employee_projects",
          joinColumns = @JoinColumn(name = "employee_id"),
          inverseJoinColumns = @JoinColumn(name = "project_id")
  )
  private List<Project> projects;

  @ManyToMany
  @JsonIgnore
  @JoinTable(
          name = "employee_roles",
          joinColumns = @JoinColumn(name = "employee_id"),
          inverseJoinColumns = @JoinColumn(name = "role_id")
  )
  private Set<ProjectEmployeeRole> projectEmployeeRoles;

  // more fields
}

角色:

public class ProjectEmployeeRole {

  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private long id;

  @ManyToOne
  @JsonIgnore
  @NotNull
  private Project project;

  @ManyToMany
  @JsonIgnore
  @JoinTable(
          name = "employee_roles",
          joinColumns = @JoinColumn(name = "role_id"),
          inverseJoinColumns = @JoinColumn(name = "employee_id")
  private List<Employee> employees;

  // more fields

编辑2:

我现在一次加载一个集合，并且能够避免 n+1 请求和笛卡尔积。我也不再有任何重复项:

服务:

Project project = projectRepository.findProjectInOneRequestById(id);
project.setEmployees(employeeRepository.findIneOneRequestByEmployees(project.getEmployees()));

查询:

 @Query("SELECT project FROM Project project LEFT JOIN FETCH project.employees employee JOIN FETCH project.order ord " +
          "JOIN FETCH ord.customer LEFT JOIN FETCH project.defaultDailyEntrySettings " +
          "WHERE project.id = :id")
  Project findProjectInOneRequestById(@Param("id") long id);

@Query("SELECT DISTINCT employee FROM Employee employee LEFT JOIN FETCH employee.projectEmployeeRoles role " +
          "LEFT JOIN FETCH role.project roleProject WHERE employee IN :employees")
  List<Employee> findIneOneRequestByEmployees(@Param("employees") List<Employee> employees);

Answer 1

是的，当您不使用 JOIN FETCH 时，这是可能的，正如您所注意到的。在您的情况下，Hibernate 似乎正在根据您的查询生成笛卡尔积。我注意到您正在私下使用 Set Set<ProjectEmployeeRole> projectEmployeeRoles; 。如果将其更改为列表，可能会收到 MultipleBagFetchException。我猜你以前遇到过这个问题，并尝试使用 Set 来修复它，但结果是现在你有了一个笛卡尔积。

我建议您评估一下您是否真的需要此处的 JOIN FETCH。为了理解 Hibernate 的笛卡尔问题以及如何处理 MultipleBagFetchException 我建议这些文章:

MultipleBagFetchException

Cartesian Product - Hibernate