如何用最少SQL查询加载关联的Client与CreditDetails实体？

实体类定义 #

Bank #

public class Bank {

    @Id
    @Column(name = "bank_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private UUID id;

    @Column(name = "bank_name")
    private String bankName;

    @OneToMany(orphanRemoval = true, cascade = CascadeType.ALL,
            mappedBy = "bank", fetch = FetchType.LAZY)
    private List<CreditDetails> creditDetails = new ArrayList<>();

    @OneToMany(orphanRemoval = true, cascade = CascadeType.ALL,
            mappedBy = "bank", fetch = FetchType.LAZY)
    private List<Client> clients = new ArrayList<>();
}

Client #

public class Client {

    @Id
    @Column(name = "client_id")
    @GeneratedValue(strategy = GenerationType.AUTO)
    private UUID id;

    @Column(name = "client_name")
    private String clientName;

    @Column(name = "client_surname")
    private String clientSurName;

    @Column(name = "client_full_name")
    private String clientFullName;

    @Column(name = "telephone_number")
    private Long telephoneNumber;

    @Column(name = "email")
    private String email;

    @Column(name = "passport_number")
    private Long passportNumber;

    @ManyToOne(
            cascade = {CascadeType.DETACH, CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REFRESH})
    @JoinColumn(name = "bank_id")
    private Bank bank;

    @OneToMany(cascade = CascadeType.ALL,
            mappedBy = "client")
    private List<CreditOffer> creditOffers = new ArrayList<>();

}

CreditDetails #

public class CreditDetails {

    @Id
    @Column(name = "credit_details_id")
    @GeneratedValue(strategy = GenerationType.AUTO)
    private UUID id;

    @Column(name = "credit_limit")
    private BigDecimal creditLimit;

    @Column(name = "credit_percent")
    private BigDecimal creditPercent;

    @ManyToOne(targetEntity = Bank.class, cascade = {CascadeType.DETACH, CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REFRESH}, fetch = FetchType.EAGER)
    @JoinColumn(name = "bank_id")
    private Bank bank;

    @OneToMany(cascade = CascadeType.ALL,
            mappedBy = "creditDetails")
    List<CreditOffer> creditOffers = new ArrayList<>();
}

遇到的问题 #

• 使用@NamedEntityGraph同时加载Bank的clients和creditDetails集合时，触发MultiBagFetchException，根源是多集合关联查询产生笛卡尔积，JPA无法正确处理重复的实体数据
• 手动编写原生SQL JOIN查询后，仍出现大量额外查询，因为Client和CreditDetails的creditOffers关联默认懒加载，访问时会触发N+1查询

解决方案 #

1. 拆分EntityGraph，分两次查询加载集合 #

同时Fetch两个List类型的关联集合必然会产生笛卡尔积，因此可以拆分两个独立的EntityGraph，分别加载clients和creditDetails，利用JPA的一级缓存自动合并实体数据，避免重复查询Bank本身：

首先在Bank实体上定义两个命名实体图：

@NamedEntityGraph(name = "BankWithClients", attributeNodes = @NamedAttributeNode("clients"))
@NamedEntityGraph(name = "BankWithCreditDetails", attributeNodes = @NamedAttributeNode("creditDetails"))
@Entity
public class Bank {
    // 原有代码保持不变
}

然后在Repository中定义对应查询方法：

public interface BankRepository extends JpaRepository<Bank, UUID> {
    @EntityGraph(value = "BankWithClients")
    Optional<Bank> findWithClientsById(UUID id);

    @EntityGraph(value = "BankWithCreditDetails")
    Optional<Bank> findWithCreditDetailsById(UUID id);
}

业务层调用逻辑：

public Bank getBankWithAllAssociations(UUID bankId) {
    // 第一次查询：加载Bank+关联的clients
    Bank bank = bankRepository.findWithClientsById(bankId).orElseThrow(() -> new RuntimeException("Bank not found"));
    // 第二次查询：利用一级缓存，仅加载Bank关联的creditDetails，不会重复查询Bank
    bankRepository.findWithCreditDetailsById(bankId);
    return bank;
}

这种方式总共只会发送3次SQL查询：查Bank+clients、查Bank+creditDetails，完全避免笛卡尔积和N+1问题。

2. 控制深层关联的加载行为 #

如果不需要访问Client和CreditDetails的creditOffers集合，确保其fetch类型为LAZY（你的代码中已默认配置，无需修改），这样只有主动访问该集合时才会触发查询；如果需要加载creditOffers，可以给EntityGraph添加子图，但同样要遵循不同时Fetch多个List集合的原则：

比如定义加载Bank+clients+creditOffers的实体图：

@NamedEntityGraph(name = "BankWithClientsAndOffers", 
    attributeNodes = @NamedAttributeNode(value = "clients", subgraph = "ClientWithOffers"),
    subgraphs = @NamedSubgraph(name = "ClientWithOffers", attributeNodes = @NamedAttributeNode("creditOffers")))

若同时需要加载creditDetails，仍需拆分两次查询。

3. 使用subselect优化懒加载集合 #

在Bank的@OneToMany注解中添加subselect = true，当首次加载Bank后，访问懒加载集合时，JPA会用子查询一次性加载所有关联数据，将N+1查询变为2次查询：

@OneToMany(orphanRemoval = true, cascade = CascadeType.ALL,
        mappedBy = "bank", fetch = FetchType.LAZY, subselect = true)
private List<CreditDetails> creditDetails = new ArrayList<>();

@OneToMany(orphanRemoval = true, cascade = CascadeType.ALL,
        mappedBy = "bank", fetch = FetchType.LAZY, subselect = true)
private List<Client> clients = new ArrayList<>();

此方式下，访问clients会触发一次子查询加载所有该Bank的Client，访问creditDetails会触发另一次子查询，总查询次数为3次（查Bank、查clients、查creditDetails），远优于N+1。

4. 用DTO投影减少不必要的查询 #

如果仅需要展示数据，无需操作实体对象，可以通过JPQL构造DTO，一次性获取所有需要的数据，避免关联实体的懒加载问题：

先定义DTO类：

public class BankDTO {
    private UUID bankId;
    private String bankName;
    private String clientName;
    private BigDecimal creditLimit;

    // 构造方法，需与JPQL中的字段顺序匹配
    public BankDTO(UUID bankId, String bankName, String clientName, BigDecimal creditLimit) {
        this.bankId = bankId;
        this.bankName = bankName;
        this.clientName = clientName;
        this.creditLimit = creditLimit;
    }

    // Getters
}

然后在Repository中定义查询：

@Query("SELECT new com.example.dto.BankDTO(b.id, b.bankName, c.clientName, cd.creditLimit) " +
       "FROM Bank b LEFT JOIN b.clients c LEFT JOIN b.creditDetails cd " +
       "WHERE b.id = :id")
List<BankDTO> getBankDataById(UUID id);

此方式仅发送1次SQL查询，返回所需数据，无需处理实体关联的懒加载问题。

总结 #

完全不需要接受N+1查询问题，通过以下手段可以高效优化：

• 拆分EntityGraph分两次查询加载多个集合，规避笛卡尔积
• 利用subselect优化懒加载集合的查询次数
• 按需加载深层关联，避免不必要的查询触发
• 使用DTO投影减少数据量和查询次数

内容的提问来源于stack exchange，提问作者Smetana Po Aktsii