N+1 problem. Couldn't EAGER fetch entities by Specification

Hibernate ORM
1

Hi there!

I’m experiencing N+1 problem with Hibernate ORM in Spring Boot application written in Kotlin.
The entities in the problem definition are: CreditPayment, CreditContract, Client.
So, client may have many credits. Each credit has many payments.
I’m trying to get all the payments by specification using repository method:
List findAll(@Nullable Specification spec)

My entities are:

@Entity
@Table(name = "credit_payment")
data class CreditPayment(
        @Id
        @Column(name = "id")
        var id: Long? = null,

        @ManyToOne
        @Fetch(FetchMode.JOIN)
        @JoinColumn(name = "credit_id", nullable = false)
        val creditContract: CreditContract,

        //omitted
)

@Entity
@Table(name = "credit_contract")
data class CreditContract(
        @Id
        @Column(name = "id")
        var id: Long? = null,

        @ManyToOne
        @JoinColumn(name = "client_id", nullable = false)
        val client: Client,

        @OneToMany(mappedBy = "creditContract", fetch = FetchType.LAZY)
        val creditPaymentList: List<CreditPayment>

        //omitted
)

@Entity
@Table(name = "client")
data class Client(
        @Id
        @Column(name = "id")
        var id: Long? = null,

        @Column(name = "fio")
        val name: String

        //omitted
)

Here is my repository:

interface CreditPaymentRepo: JpaRepository<CreditPayment, Long>, JpaSpecificationExecutor<CreditPayment>

What I see in the logs that here is one CreditPayment query without any joins, then following N CreditContract queries with Client join.

Any suggestions to avoid N+1?
I want all the entities queried by one query.

2

@Fetch(FetchMode.JOIN) doesn’t work with queries. It will work if you load entity by primary key (e.g., EntityManager.find(id, type), findById(), findOne()). For query methods you explicitly have to specify fetch joins in the query definition. Just use @EntityGraph (requires JPA 2.1). Define the entity graph and override findAll(Specification spec):

@Override
@EntityGraph(value = "{entity_graph_name}")
public List<CreditPayment> findAll(Specification spec);

You can find more details here.