Jpa列表查询@OneToOne，@OneToMany出现多条sql（N+1）解决办法

最近用了jpa的@OneToOne，@OneToMany管理对象，但是在查询分页列表，列表的过程中触发了N+1的sql查询，这里我用了@NamedEntityGraphs去解决

实体关系

1.父实体

/**
 * 规则配置
 *
 * @author lyj
 * @date 2023-07-06
 */
@Getter
@Setter
@Entity
@Table(name = "ca_rule_config")
@ToString(callSuper = true)
public class CaRuleConfig extends BaseEntity {
    private static final long serialVersionUID = -1799291883844292900L;

    /**
     * 课程规则配置
     */
    @OneToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL, orphanRemoval = true)
    @JoinColumn(name = "schedule_id")
    private CaCourseScheduleRule caCourseScheduleRule;

}

2.子实体

/**
 * 课程排布规则
 *
 * @author lyj
 * @date 2023-07-06
 */
@Setter
@Getter
@Entity
@Table(name = "ca_course_schedule_rule")
public class CaCourseScheduleRule extends BaseEntity {
    private static final long serialVersionUID = -1523817727837148111L;

    @Fetch(value = FetchMode.SUBSELECT)
    @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL, mappedBy = "caCourseScheduleRule", orphanRemoval = true)
    private Set<CaTimeRuleTable> caTimeRuleTableSet;

    @JsonIgnore
    @OneToOne(fetch = FetchType.LAZY)
    @ToString.Exclude
    private CaRuleConfig caRuleConfig;
}

3.孙实体

/**
 * 规则明细表
 *
 * @author lyj
 * @date 2023-07-06
 */
@Entity
@Getter
@Setter
@Table(name = "ca_time_rule_table")
public class CaTimeRuleTable extends BaseEntity {
    private static final long serialVersionUID = 3301950476260363705L;

    @JsonIgnore
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "schedule_id")
    @ToString.Exclude
    private CaCourseScheduleRule caCourseScheduleRule;

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        CaTimeRuleTable that = (CaTimeRuleTable) o;
        return getId().equals(that.getId());
    }

    @Override
    public int hashCode() {
        return Objects.hash(super.getId());
    }
    
}

查询语句

/**
 * @author lyj
 * @date 2023-07-06
 */
public interface CaRuleConfigDao extends BaseDao<CaRuleConfig, Long> {

    /**
     * 分页获取规则列表
     * @param spec 查询条件
     * @param pageable  分页条件
     */
    @Override
    Page<CaRuleConfig> findAll(Specification<CaRuleConfig> spec, Pageable pageable);
}

查询结果：

从控制台的输出我们可以看见，当前查了多少条数据就执行了多少条sql

在父实体中加入@NamedEntityGraphs后

/**
 * 规则配置
 *
 * @author lyj
 * @date 2023-07-06
 */
@Getter
@Setter
@Entity
@Table(name = "ca_rule_config")
@ToString(callSuper = true)
@NamedEntityGraphs(value = {
        @NamedEntityGraph(
                name = "caRuleConfig",
                attributeNodes = {
                        @NamedAttributeNode(value = "caCourseScheduleRule", subgraph = "caCourseScheduleRule"),
                },
                subgraphs = {
                        @NamedSubgraph(name = "caCourseScheduleRule", attributeNodes = {
                                @NamedAttributeNode(value = "caTimeRuleTableSet", subgraph = "caTimeRuleTable")
                        }),
                        @NamedSubgraph(name = "caTimeRuleTable", attributeNodes = {})
                }
        )
})
public class CaRuleConfig extends BaseEntity {
    private static final long serialVersionUID = -1799291883844292900L;

    /**
     * 课程规则配置
     */
    @OneToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL, orphanRemoval = true)
    @JoinColumn(name = "schedule_id")
    private CaCourseScheduleRule caCourseScheduleRule;

}

查询接口调整

/**
 * @author lyj
 * @date 2023-07-06
 */
public interface CaRuleConfigDao extends BaseDao<CaRuleConfig, Long> {

    /**
     * 分页获取规则列表
     * @param spec 查询条件
     * @param pageable  分页条件
     */
    @Override
    @EntityGraph(value = "caRuleConfig")
    Page<CaRuleConfig> findAll(Specification<CaRuleConfig> spec, Pageable pageable);
}

执行结果

调用查询列表接口后，只执行了一条sql

总结：
利用@NamedEntityGraphs查询，实际就是把三张表的数据构建成一张虚拟表进行数据查询