I have the entities:

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<>();

}

Credit Details:

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<>();
}

My task is to load Clients and Credit Details with the least number of requests using Spring Data.

I tried to solve this problem using graphs, I added the following construction above the bank entity:

@NamedEntityGraph(name = "BankWithClientsAndDetails", attributeNodes = {
        @NamedAttributeNode(value = "creditDetails"),
        @NamedAttributeNode(value = "clients",subgraph = "ClientWithBank")
})

However, when calling the method, I received a MultyBagFetchException, googled that this occurs due to the fact that a Cartesian product is obtained in the request. Next, I tried to solve the problem by writing a sql query manually, I made a test method of the following form:

@Query(value = "SELECT * FROM banks \n"  
            "JOIN clients ON clients.bank_id = banks.bank_id  \n"  
            "JOIN credit_details ON credit_details.bank_id = banks.bank_id \n"  
            "WHERE banks.bank_id = :id",nativeQuery = true)
    Optional<Bank> findBankWithSubEntitiesById(@Param(value = "id") UUID id);

The number of requests has remained huge, since related entities have more related entities, and those have one more. At this stage, my hands dropped and I came to you to ask for help. How would you solve this situation? Do I really have to put up with N 1 in my case. I would be grateful for any help

CodePudding user response：

Using just JOIN in your query merely narrows the results, eg - it won't loads banks without any clients and credit details.

You can tell hibernate to load each of these associations eagerly using JOIN FETCH jpql keyword.

SELECT bank
FROM Bank bank
JOIN FETCH bank.clients
JOIN FETCH bank.creditDetails
WHERE bank.id = :bankId

Keep in mind that this is a jpql query, so you have to remove nativeQuery = true parameter. Also, note that by default, join is inner and not outer - meaning that this query also won't return a bank without clients or credit details. You can change that by using LEFT JOIN FETCH, but that will reduce performance, so choose whatever strategy suits best your use-case.

Further reading