CODEWITHSUNDEEP
springhibernatemany-to-one
senorga
senorga

Reputation: 1

hibernate cant lazy fetch @manyToOne with composite foreign key

this one is a real toughy. I have read a ton of other questions/answers but still cant find an answer. The basic problem is this: When I map a Customer entity in a @ManyToOne association through its primary key, specifying lazy fetch, hibernate works as intended, if I dont access the field, no additional query is generated. However if I map that same entity through its two business keys (a composite unique key) hibernate chooses another fetch strategy and always fetches the entities.

An excerpt of my classes are here:

@Entity
@Table(name = "CUSTOMER", uniqueConstraints = {@UniqueConstraint(columnNames = {"BRANCH_ID", "CUSTOMER_CODE"}, name = "UC_CUSTOMER")})
public class Customer {

    @Id
    private Long id;

    @NaturalId
    @Column(name = "BRANCH_ID", nullable = false, length = 3)
    private Long branchId;

    @NaturalId(mutable = true)
    @Column(name = "CUSTOMER_CODE", nullable = false, length = 38)
    private Integer customerCode;
}

@Entity
@Table(name = "invoice")
public class InvoiceHeaderX  {

    @Id
    private Long id;

    @ManyToOne(fetch =FetchType.LAZY, optional = false)
    @JoinColumns(value = {
            @JoinColumn(name = "BRANCH_ID", referencedColumnName = "BRANCH_ID", nullable=false),
            @JoinColumn(name = "CUSTOMER_CODE", referencedColumnName = "CUSTOMER_CODE", nullable=false)
    }, foreignKey = @ForeignKey(name = "FK_INVOICE_ON_CUSTOMER",value = ConstraintMode.CONSTRAINT))
    
    Customer customer; // -> this is fetched eagerly, but why ??

    @ManyToOne(fetch =FetchType.LAZY, optional = false)
    @JoinColumn(name = "OTHER_CUSTOMER_ID", referencedColumnName = "ID")
    
    Customer otherCustomer;// -> this is fetched lazily as intended

}

service:

findAllInvoices(){
  // no mapper, no fields are accessed at all:
  invoiceRepository.findAll().size();
}

resulting sql for a table that contains 10 invoice lines, each of them referring different customers and otherCustomers (20 different records from customer table alltogether):

select .. from invoice
  10 x select .. from customer

I just cant get rid of the N+1 query hibernate generates. I use spring boot 3.3.4 hibernate 6.6.1.Final Thanks !

ps I know about fetch join to have one join query, but sometimes i dont need the Customer data in my response (have a different dto without Customer data for this) and for performance reasons i would like to totaly omit customer table queries in this cases

Upvotes: 0

Views: 66

Answers (1)

Jos3lu
Jos3lu

Reputation: 308

When you use the lazy load strategy, you are basically telling it to load on demand or when requested (link). By default, the relations that are lazy loaded are @OneToMany and @ManyToMany while for associations @OneToOne and @ManyToOne are used the strategy eager. It makes sense, if you have a huge collection is very inefficient to load all the elements of the collection. In the case of @OneToOne or @ManyToOne you just have one entity/object related.

About your question, it looks that Hibernate although you explicitly use the lazy load strategy, because of the behaviour of the annotation NaturalId in branchId and customerCode may decide to load the entity customer as eager.

We can designate a field as a natural identifier simply by annotating it with @NaturalId. This allows us to seamlessly query the associated column using Hibernate’s API.

You indicate that the otherCustomer field uses the lazy load strategy perfectly. In that case, it seems that the use of the NaturalId with the annotation JoinColumns is the cause of the problem.

Note:

  • I tested your code and this is the query generated in that case:

    select i1_0.id,i1_0.branch_id,i1_0.customer_code from invoice i1_0

    select c1_0.id,c1_0.branch_id,c1_0.customer_code from customer c1_0 where (c1_0.branch_id,c1_0.customer_code) in((?,?))

  • In the case you remove/comment the annotation JoinColumns you have the following query:

    select i1_0.id,i1_0.customer_id from invoice i1_0

Basically, it's adding the where clause (where (c1_0.branch_id,c1_0.customer_code) in((?,?)))

In general, because you mentioned it, for the n + 1 query problem I would use JPA Entity Graph. It allow us to define a network of components and attributes to load into a query.

In my opinion, I Would not use the lazy load strategy for the @OneToMany. If you don't want to return the field (customer) use the DTO pattern and only return the fields you need. Otherwise, you have the option mentioned above as well.

Upvotes: 0

Related Questions