java - 这种情况下是否可以避免N+1次请求呢？

Question

我有以下类模型:

我是这样实现的:

项目:

@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<Role> roles;

  // 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 List<Role> roles;

  // more fields
}

角色:

public class Role {

  @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
  )
}

因此每个项目都有多个员工和角色。在我的应用程序中，我能够向项目添加具有角色的员工。所以他们永远是一对。项目中的一名员工始终扮演一个角色。我在业务逻辑中控制它。

问题:给定一个项目，是否可以找到其所有员工，将每个员工与其在该项目中的角色相关联(即既与员工又与给定项目相关联的一个角色) ，我确保在一个请求中始终只有一个角色可以满足此条件)？

因此，调用 findEmployeeWithRole(project) 应返回此类的列表，其中包含项目的所有员工及其与该项目相关的角色:

public class EmployeeAndRole {

  private Employee employee;
  private Role role;
}

我目前只能在 n+1 次请求中实现此目的:

查找项目所有员工的一个请求:

@Query("SELECT project.employees FROM Project project WHERE project.id = :id")
List<Employee> findByProjectId(@Param("id") long id)

然后n个请求(针对列表中的每个员工)来查找该员工在该项目中所连接的角色:

@Query("SELECT role FROM Role role WHERE role.project.id = :projectId AND :employeeId IN (SELECT employees.id FROM role.employees employees)
Role findByProjectIdAndEmployeeId(@Param("projectId") long projectId, @Param("employeeId") long employeeId)

我可以通过一个请求实现这一目标吗？如果没有，我还有什么其他选择，我应该尝试 reshape 我的类图吗？

Answer 1

我不知道我是否理解正确，如果您搜索更多 hibernate 解决方案或 SQL 解决方案，但我认为这会起作用:

SELECT
    p.employees

FROM project p

INNER JOIN employee e
    ON e.project.id = p.id      -- this ensure to get all emps from the selected project

LEFT JOIN role r                -- left join ensure to keep all records from employee, even if he hasn't a role
    ON  r.project.id = p.id     -- this ensure to get all role from the selected project
    AND r.employee.id = e.id    -- and linked to a employee too

WHERE
    p.id = :id

这是基于您的“问题”句子。