CODEWITHSUNDEEP
javamysqlspringhibernatejpa
F43nd1r
F43nd1r

Reputation: 7749

Spring JPA optimize transitive @ManyToOne fetching

In my project I have three Entities like this:

@Entity
public class A {
    @Id
    private int id;
}

@Entity
public class B {
    @Id
    private int id;
    @ManyToOne(cascade = {CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REFRESH}, optional = false)
    @OnDelete(action = OnDeleteAction.CASCADE)
    private A a;
}

@Entity
public class C {
    @Id
    private int id;
    @ManyToOne(cascade = {CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REFRESH}, optional = false)
    @OnDelete(action = OnDeleteAction.CASCADE)
    private B b;
}

As you can see, C has a tansitive ManyToOne-Relationship with A. Now, I want to fetch all Cs with a specific A using my JpaRepository:

public interface CRepository extends JpaRepository<C, Integer> {
    List<C> findAllByBA(A a);
}

Which results in the following Hibernate sql query (which is fine):

Hibernate: select ... from c c0_ cross join b b1_ where c0_.b_id=b1_.id and b1_.a_id=?

But afterwards I get another query for each distinct B (which can be several thousand, so is not fine):

Hibernate: select ... from b b0_ inner join a a1_ on b0_.a_id=a1_.id where b0_.id=?

Obviously, this could be solved with a single join using all three tables. But how do I tell Hibernate to do that without losing the convenience of abstraction with JpaRepository?

Upvotes: 0

Views: 1255

Answers (1)

JB Nizet
JB Nizet

Reputation: 691635

The additional, unwanted, queries are caused by the fact that the association is eager. To avoid loading the Bs, you should make the association lazy:

@ManyToOne(fetch: FetchType.LAZY, cascade = {CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REFRESH}, optional = false)

I generally make every association lazy. When you're not interested in Bs, or do not know in advance if loading Bs is necessary, making the association lazy avoids loaded them. And if you know you want the Bs to be loaded along with the Cs, then you just load them explicitly in your query:

@Query("select c from C c left join fetch c.b where c.b.a = ?")

Upvotes: 2

Related Questions