java - JPQL，获取具有子列表的对象列表，避免n+1请求并仅选择特定字段

Question

我有以下实体项目:

@Data
@NoArgsConstructor
@Entity
@ToString(exclude = "roles")
@JsonInclude(JsonInclude.Include.NON_NULL)
public class Project {

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

  @Column(unique = true)
  private String name;

  private String description;

  private Boolean isArchived;

  private LocalDate archivedDate;

  private LocalDate creationDate;

  @Column(nullable = false, columnDefinition = "BOOLEAN DEFAULT FALSE")
  private Boolean invoicingActivated;

  @ManyToOne
  @NotNull
  private Order order;

  @OneToOne(cascade = CascadeType.ALL)
  private DefaultDailyEntrySettings defaultDailyEntrySettings;

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

我想获取所有项目。每个项目还应该有自己的 projectEmployees 列表。

这就是实体ProjectEmployee:

@Data
@Table(uniqueConstraints = {@UniqueConstraint(columnNames = {"employee_id", "project_id"})})
@NoArgsConstructor
@ToString(exclude = "project")
@JsonInclude(JsonInclude.Include.NON_NULL)
public class ProjectEmployee {

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

  @ManyToOne
  @JsonIgnore
  @JsonProperty(access = JsonProperty.Access.WRITE_ONLY)
  @NotNull
  private Project project;

  @ManyToOne
  @NotNull
  private Employee employee;

  @ManyToOne
  private ProjectEmployeeRole projectEmployeeRole;
}

为了避免 n+1 查询，我编写了以下查询:

@Query("SELECT project FROM Project project JOIN FETCH project.order ord JOIN FETCH ord.customer " +
          "LEFT JOIN FETCH project.projectEmployees projectEmployee LEFT JOIN FETCH project.defaultDailyEntrySettings " +
          "LEFT JOIN FETCH projectEmployee.employee LEFT JOIN FETCH projectEmployee.projectEmployeeRole")
List<Project> findAllProjectsInOneQuery();

这可行，但它返回每个对象的所有属性。例如，我只对 ord.customer 的 id 和名称感兴趣，在这种情况下，我不需要 ord.customer 的所有其他字段。以这种方式获取所有字段的问题是，正在传输大量数据，在这种情况下我不需要这些数据。为了只选择我需要的并减少通过互联网发送的数据量，我可以这样做:

@Query("SELECT new de.project.Project(project.id, project.name, " +
          "project.description, project.isArchived, project.archivedDate, " +
          "project.creationDate, project.invoicingActivated, project.order.id, " +
          "project.order.name, project.order.customer.id, project.order.customer.name) " +
          "FROM  Project project")
List<Project> findAllMinimal();

但是正如您所看到的，我无法以这种方式获取 project.projectEmployees ，因为它是一个列表，而且我认为我无法通过这种方式通过构造函数传递列表。

我尝试过:

@Query("SELECT new de.project.Project(project.id, project.name, " +
              "project.description, project.isArchived, project.archivedDate, " +
              "project.creationDate, project.invoicingActivated, project.order.id, " +
              "project.order.name, project.order.customer.id, project.order.customer.name, " +
              "projectEmployee.id) " +
              "FROM  Project project JOIN project.projectEmployees projectEmployee")
    List<Project> findAllMinimal();

但是 projectEmployee.id 只是第一个 projectEmployee 的 id，我认为我无法通过这种方式传递所有 projectEmployees。

有没有办法获取所有项目及其projectEmployees(以及我在上面的查询中列出的其他属性)并指定我想要获取哪些字段？它不必是一个查询，恒定数量的查询就可以了。显然它应该避免 n+1 次查询。

编辑:

我想出了一个解决方法。我使用以下两个查询:

@Query("SELECT new de.project.Project(project.id, project.name, " +
          "project.description, project.isArchived, project.archivedDate, " +
          "project.creationDate, project.invoicingActivated, project.order.id, " +
          "project.order.name, project.order.customer.id, project.order.customer.name) " +
          "FROM  Project project")
List<Project> findAllMinimal();

@Query("SELECT DISTINCT new de.projectemployee.ProjectEmployee(projectEmployee.id, " +
          "projectEmployee.employee.id, projectEmployee.employee.email, " +
          "projectEmployee.employee.firstName, projectEmployee.employee.lastName, " +
          "projectEmployee.employee.address, projectEmployee.employee.weeklyHoursEnabled, " +
          "projectEmployee.employee.weeklyHours, projectEmployee.employee.isArchived, " +
          "projectEmployee.employee.archivedDate, projectEmployee.project.id, projectEmployeeRole.id, " +
          "projectEmployeeRole.name, projectEmployeeRole.hourlyWage) FROM ProjectEmployee projectEmployee " +
          "LEFT JOIN projectEmployee.projectEmployeeRole projectEmployeeRole " +
          "WHERE projectEmployee.project IN :projects")
  List<ProjectEmployee> findByProjects(@Param("projects") List<Project> projects);

为了给每个项目提供他的projectEmployees，我需要一些额外的java代码:

    List<Project> projects = projectRepository.findAllMinimal();
    List<ProjectEmployee> projectEmployees = projectEmployeeRepository.findByProjects(projects);
    Map<Long, List<ProjectEmployee>> projectIdToProjectEmployeesMap = new HashMap<>();
    for (ProjectEmployee projectEmployee : projectEmployees) {
      List<ProjectEmployee> projectEmployeesToBeSaved = projectIdToProjectEmployeesMap.getOrDefault(projectEmployee.getProject().getId(), new ArrayList<>());
      projectEmployeesToBeSaved.add(projectEmployee);
      projectIdToProjectEmployeesMap.put(projectEmployee.getProject().getId(), projectEmployeesToBeSaved);
    }
    projects.forEach(project -> project.setProjectEmployees(projectIdToProjectEmployeesMap.get(project.getId())));
    return projects;

所以，正如您所见，我能够实现我的目标，即以恒定数量的查询(2)获取所有项目及其项目员工，并且仅选择我需要的字段。缺点是我有一个运行复杂度为 O(n) 的 javacode。但我将传输的数据大小减少了 90% 以上，所以我猜它是值得的。

很难相信需要像我使用的那样的java代码算法来找到我的问题的解决方案，因此如果有人找到能够执行上述操作的更好的解决方案(仅使用sql查询)，请分享。

Answer 1

没有验证您的情况，但乍一看您可能会利用一级缓存。执行完毕后

List<Project> projects = projectRepository.findAllMinimal();
List<ProjectEmployee> projectEmployees = projectEmployeeRepository.findByProjects(projects);

对 projectEmployee.getProject() 的任何访问都应从缓存中返回其项目，以避免 n+1 问题。因此，如果您可以保留 project.projectEmployees 列表不填充，则可以删除“自定义 javacode”。