解决Hibernate中@Fetch(SUBSELECT)与EntityGraph的多余关联问题

我有一个Employee实体，包含多个关联实体，使用@Fetch(FetchMode.SUBSELECT)加载集合数据。但通过带EntityGraph的查询获取Employee时，原查询的所有JOIN会被加入SUBSELECT，该如何解决？尝试在关联实体上添加@BatchSize注解后，多余JOIN消失但查询次数未变，调整size参数也无效果，恳请指导正确的实体设计方案。

主实体Employee #

@Data
@Entity
@NoArgsConstructor
@Where(clause = "delete_time is null")
@Table(name = "employee")
@NamedEntityGraphs({
    @NamedEntityGraph(name = "withDepartmentManagerAndOffice", attributeNodes = {
            @NamedAttributeNode("department"),
            @NamedAttributeNode("manager"),
            @NamedAttributeNode("office")
    }),
    @NamedEntityGraph(name = "withUserDepartmentManagerAndOffice", attributeNodes = {
            @NamedAttributeNode("user"),
            @NamedAttributeNode("department"),
            @NamedAttributeNode("manager"),
            @NamedAttributeNode("office")
    })
})
public class Employee extends BaseIdentifiableEntity {

@OneToOne(fetch = LAZY)
@JoinColumn(name = "email", foreignKey = @ForeignKey(name = "user_email_pk"), insertable = false, updatable = false)
@ToString.Exclude
private User user;

private String email;
private String lastName;
private String firstName;
private String patronymic;
private String simpleName;//for ldap manager search

private LocalDate birthday;
private String phoneNumber;
private String telegramId;
private byte[] photo;
private String position;
private LocalDate startDate;

@ManyToOne(fetch = LAZY)
@JoinColumn(name = "department_id")
@ToString.Exclude
private Department department;

@Fetch(FetchMode.SUBSELECT)
@OneToMany(mappedBy = "employee")
@ToString.Exclude
private List<Equipment> equipment;

@Fetch(FetchMode.SUBSELECT)
@OneToMany(mappedBy = "employee")
@ToString.Exclude
private List<Bonus> bonuses;

@ManyToOne(fetch = LAZY)
@JoinColumn(name = "manager_id")
@ToString.Exclude
private Employee manager;

@ManyToOne(fetch = LAZY)
@JoinColumn(name = "office_id")
@Cascade(CascadeType.SAVE_UPDATE)
@ToString.Exclude
private Office office;

@Fetch(FetchMode.SUBSELECT)
@OneToMany(mappedBy = "employee")
@ToString.Exclude
private List<Notification> notifications;

@Formula(value = "concat(last_name, ' ', first_name, ' ', patronymic)")
private String fullName;

@Transient
private String managerName;

@Override
public boolean equals(Object o) {
    if (this == o) return true;
    if (o == null || getClass() != o.getClass()) return false;
    if (!super.equals(o)) return false;
    Employee employee = (Employee) o;
    return Objects.equals(email, employee.email) && Objects.equals(lastName, employee.lastName) &&
           Objects.equals(firstName, employee.firstName) && Objects.equals(patronymic, employee.patronymic) &&
           Objects.equals(phoneNumber, employee.phoneNumber) && Objects.equals(telegramId, employee.telegramId) &&
           Objects.equals(position, employee.position) && Arrays.equals(photo, employee.photo) &&
           Objects.equals(startDate, employee.startDate) && Objects.equals(birthday, employee.birthday) &&
           Objects.equals(simpleName, employee.simpleName);
}

@Override
public int hashCode() {
    return Objects.hash(super.hashCode(), email, lastName, firstName, phoneNumber, telegramId,
            Arrays.hashCode(photo), position, department, simpleName, startDate, birthday);
}
}

Equipment实体 #

@Data
@Entity
@Table(name = "equipment")
@Where(clause = "delete_time is null")
public class Equipment extends NamedWithDescriptionIdentifiableEntity {

private LocalDate attachmentDate;
private String status;
private String inventoryNumber;
private String devTypeDescription;

@Where(clause = "delete_date is null")
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "employee_id")
@ToString.Exclude
private Employee employee;

@Override
public boolean equals(Object o) {
    if (this == o) return true;
    if (o == null || getClass() != o.getClass()) return false;
    if (!super.equals(o)) return false;
    Equipment equipment = (Equipment) o;
    return Objects.equals(attachmentDate, equipment.attachmentDate) && Objects.equals(status, equipment.status) &&
           Objects.equals(inventoryNumber, equipment.inventoryNumber) && Objects.equals(devTypeDescription, equipment.devTypeDescription);
}

@Override
public int hashCode() {
    return Objects.hash(super.hashCode(), attachmentDate, status, inventoryNumber, devTypeDescription);
}
}

Bonus实体 #

@Data
@Entity
@NoArgsConstructor
@AllArgsConstructor
@Table(name = "bonus")
@Where(clause = "delete_time is null")
public class Bonus extends NamedWithDescriptionIdentifiableEntity {

private LocalDate expirationDate;

@Enumerated(EnumType.STRING)
private BonusType type;
private Long quantity;

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "employee_id")
@ToString.Exclude
private Employee employee;

@Builder(builderMethodName = "childBuilder")
public Bonus(String name, LocalDate expirationDate, BonusType type, Long quantity, Employee employee) {
    super(name);
    this.expirationDate = expirationDate;
    this.type = type;
    this.quantity = quantity;
    this.employee = employee;
}

@Override
public boolean equals(Object o) {
    if (this == o) return true;
    if (o == null || getClass() != o.getClass()) return false;
    if (!super.equals(o)) return false;
    Bonus bonus = (Bonus) o;
    return Objects.equals(expirationDate, bonus.expirationDate) && type == bonus.type && Objects.equals(quantity, bonus.quantity) && Objects.equals(name, bonus.name);
}

@Override
public int hashCode() {
    return Objects.hash(super.hashCode(), expirationDate, type, quantity, name);
}
}

@EntityGraph("withUserDepartmentManagerAndOffice")
@Query("SELECT e FROM Employee e WHERE e.email= :email")
Optional<Employee> findFullEmployeeByEmail(String email);

问题根源 #

使用EntityGraph时，Hibernate会将EntityGraph中指定的关联查询JOIN条件带入SUBSELECT子查询，这是因为@Fetch(FetchMode.SUBSELECT)会复用主查询的所有条件，而EntityGraph触发的主查询包含额外JOIN，导致子查询被污染。

具体解决步骤 #

1. 移除全局Fetch策略，改为查询级动态指定 #

不要在实体类上全局设置@Fetch(FetchMode.SUBSELECT)，而是在需要批量加载集合的查询方法上，通过@FetchProfile动态指定：

• 先修改Employee实体，移除集合上的@Fetch(FetchMode.SUBSELECT)：

// 移除@Fetch(FetchMode.SUBSELECT)
@OneToMany(mappedBy = "employee")
@ToString.Exclude
private List<Equipment> equipment;

// 其他集合关联（bonuses、notifications）同理修改

• 然后在Repository中定义FetchProfile并绑定查询：

@FetchProfile(name = "employee-with-collections", fetchOverrides = {
    @FetchProfile.FetchOverride(entity = Employee.class, association = "equipment", mode = FetchMode.SUBSELECT),
    @FetchProfile.FetchOverride(entity = Employee.class, association = "bonuses", mode = FetchMode.SUBSELECT),
    @FetchProfile.FetchOverride(entity = Employee.class, association = "notifications", mode = FetchMode.SUBSELECT)
})
@Query("SELECT e FROM Employee e")
List<Employee> findAllWithCollections();

• 使用时激活FetchProfile：

entityManager.enableFetchProfile("employee-with-collections");
List<Employee> employees = employeeRepository.findAllWithCollections();

2. 为EntityGraph显式排除集合属性 #

如果当前EntityGraph查询不需要加载集合，可通过excludeAttributes排除集合，避免Hibernate自动处理其Fetch策略：

@NamedEntityGraph(name = "withUserDepartmentManagerAndOffice", 
    attributeNodes = {
        @NamedAttributeNode("user"),
        @NamedAttributeNode("department"),
        @NamedAttributeNode("manager"),
        @NamedAttributeNode("office")
    },
    excludeAttributes = {"equipment", "bonuses", "notifications"} // 显式排除集合
)

3. 正确配置@BatchSize #

之前@BatchSize未生效，是因为需要配置在集合属性或关联实体类上，同时开启Hibernate批处理配置：

• 在Employee的集合属性上添加：

@BatchSize(size = 20)
@OneToMany(mappedBy = "employee")
@ToString.Exclude
private List<Equipment> equipment;

• 或在Equipment实体类上添加：

@BatchSize(size = 20)
@Entity
@Table(name = "equipment")
@Where(clause = "delete_time is null")
public class Equipment extends NamedWithDescriptionIdentifiableEntity {
    // ...
}

• 同时在Hibernate配置中开启批处理：

hibernate.jdbc.batch_size=20
hibernate.order_inserts=true
hibernate.order_updates=true

4. 拆分查询，避免混合加载策略 #

如果上述方案不适用，可拆分查询：先通过EntityGraph加载Employee及其单值关联，再单独查询集合并手动赋值：

Optional<Employee> employeeOpt = employeeRepository.findFullEmployeeByEmail(email);
if(employeeOpt.isPresent()){
    Employee employee = employeeOpt.get();
    // 单独查询集合
    List<Equipment> equipment = equipmentRepository.findByEmployeeId(employee.getId());
    employee.setEquipment(equipment);
    // 同理查询bonuses和notifications
}

实体设计建议 #

• 单值关联（@ManyToOne、@OneToOne）保持LAZY加载，通过EntityGraph按需加载
• 集合关联（@OneToMany）默认LAZY，根据业务场景选择：
  • 批量查询时用@Fetch(FetchMode.SUBSELECT)或@BatchSize
  • 单个查询时直接单独查询集合，避免N+1问题
• 避免在实体类上全局设置Fetch策略，优先在查询级别动态指定，提升灵活性

内容的提问来源于stack exchange，提问作者KripDo