JPA @OneToOne select lists with N+1 queries

Question

I'm actually trying to use JPA @OneToOne annotation to link a Child entity to its Parent.

It's working well, except the fact that when getting a list of Childs, the JPA engine (Hibernate in this case) make 1+n queries.

Here is the log of the Hibernate queries :

select child0_.id as id1_0_, child0_.parent as parent3_0_, child0_.value as value2_0_ from child child0_
select parent0_.id as id1_1_0_, parent0_.something as somethin2_1_0_ from parent parent0_ where parent0_.id=?
select parent0_.id as id1_1_0_, parent0_.something as somethin2_1_0_ from parent parent0_ where parent0_.id=?
select parent0_.id as id1_1_0_, parent0_.something as somethin2_1_0_ from parent parent0_ where parent0_.id=?

Using exactly the same entities definition, when I get a child in particular, JPA executes the query with expected JOIN :

select child0_.id as id1_0_0_, child0_.parent as parent3_0_0_, child0_.value as value2_0_0_, parent1_.id as id1_1_1_, parent1_.something as somethin2_1_1_ from child child0_ left outer join parent parent1_ on child0_.parent=parent1_.id where child0_.id=?

Here is the Child entity definition :

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Entity
@Table(name = "child")
public class Child {

    @Id
    private Long   id;
    @Column
    private String value;
    @OneToOne(optional = false)
    @JoinColumn(name = "parent")
    private Parent parent;

}

And the Parent entity :

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Entity
@Table(name = "parent")
public class Parent {

    @Id
    private Long   id;
    @Column
    private String something;

}

You can find a complete example of running code here : https://github.com/Alexandre-Carbenay/demo-jpa-onetoone

Is there a way to avoid the 1+n queries when getting the list of Child entities with Parent ?

Is the parent mandatory? If yes set optional=false in the OneToOne annotation. — Simon Martinelli, Dec 11 '17 at 16:15
I've tried adding this optional=false, but it does not change anything — Alexandre Carbenay, Dec 12 '17 at 07:50

score 8 · Accepted Answer · answered Dec 13 '17 at 10:46

I finally found a better solution than JOIN FETCH that also works with QueryDsl, using @EntityGraph annotation on repository methods.

Here is the updated Child definition :

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Entity
@NamedEntityGraph(name = "Child.withParent", attributeNodes = @NamedAttributeNode("parent"))
@Table(name = "child")
public class Child {
    @Id
    private Long   id;
    @Column
    private String value;
    @OneToOne(optional = false)
    @JoinColumn(name = "parent")
    private Parent parent;
}

And the ChildJpaRepository definition :

public interface ChildJpaRepository extends JpaRepository<Child, Long>, QueryDslPredicateExecutor<Child> {

    @Override
    @EntityGraph("Child.withParent")
    List<Child> findAll();

    @Override
    @EntityGraph("Child.withParent")
    List<Child> findAll(Predicate predicate);

}

Thanks to Simon Martinelli and Vlad Mihalcea for your help

score 2 · Answer 2 · answered Dec 12 '17 at 08:17

2

I can reproduce your observations but I see no reason why Hibernate is doing this.

A solution to avoid the queries is to use JOIN FETCH like

select c from Child c join fetch c.parent

answered Dec 12 '17 at 08:17

Simon Martinelli

34,053
5
48
82

The point is that I would like not to write the query myself, as I will use pagination and filters in a more complex situation than in the example But thanks for the help, it may be the solution I'll have to use in the end – Alexandre Carbenay Dec 12 '17 at 12:32

Vlad Mihalcea · Answer 3 · 2021-01-08T15:12:03.777

1

By default, the @OneToOne and @ManyToOne associations use FetchType.EAGER, and that's why you see the N+1 query issue.

So, the solution is fairly simple, just set the fetch strategy to LAZY:

@OneToOne(optional = false, fetch = FetchType.LAZY)
@JoinColumn(name = "parent")
private Parent parent;

If you have a bidirectional @OneToOne association, the parent-side cannot be made lazy unless you use bytecode enhancement.

edited Jan 08 '21 at 15:12

answered Dec 12 '17 at 08:27

Vlad Mihalcea

142,745
71
566
911

Actually, I do not want my association to be loaded lazily, but eagerly. What I really want is JPA to use one single database query to get the children and their parent association – Alexandre Carbenay Dec 12 '17 at 12:30
The just use JOIN FETCH. However, for EAGER associations, all your queries will require JOIN FETCH, and direct fetching via entityManager.find will always trigger secondary queries. That's why EAGER is bad. – Vlad Mihalcea Dec 12 '17 at 14:11
I want to use EAGER because in that case, I'm sure I will need the associated type information (special query for list rendering purposes), so it makes sense to use EAGER in that situation. But I don't understand what you mean : are you saying that, when using Hibernate EntityManager, if I define JOIN FETCH in combination with EAGER, hibernate will use both the JOIN in initial query, then secondary queries for each associated type ? But if I use only JPA and don't interact with EntityManager directly, everything seems to be OK: `select ... from child child0_ inner join parent parent1_ on ...` – Alexandre Carbenay Dec 12 '17 at 15:57
EAGER means always, not just in one case, and you already have a query, so it will work just fine with LAZY at map time + JOIN FETCH at query time. Hibernate EntityManager is just JPA, like EclipseLink or OpenJPA. if you use JPA and load the `Child` entity by its id (`entityManager.find(Child.class, childId)`), Hibernate will issue a JOIN for this association even if you don't need it. If you execute a JPQL query and forget to JOIN FETCH, a secondary query for every associated entity will be generated, – Vlad Mihalcea Dec 12 '17 at 16:01
OK now I understand why you recommand using FETCH JOIN instead of EAGER association : to ensure that no mistake / forgetting could imply secondary queries. Thanks for the advice – Alexandre Carbenay Dec 13 '17 at 10:34

JPA @OneToOne select lists with N+1 queries

3 Answers3