Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

JPA @OneToOne select lists with N+1 queries

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 ?

like image 348
Alexandre Carbenay Avatar asked Dec 11 '17 13:12

Alexandre Carbenay


People also ask

How can we find and fix N 1 Select issue in hibernate?

Hibernate N+1 issue occurs when you use `FetchType. LAZY` for your entity associations. Hibernate will perform n-additional queries to load lazily fetched objects. To escape this issue use join fetch, batching or sub select.

What is the N 1 problem in hibernate JPA?

The N+1 query problem is said to occur when an ORM, like hibernate, executes 1 query to retrieve the parent entity and N queries to retrieve the child entities. As the number of entities in the database increases, the queries being executed separately can easily affect the performance of the application.

What are three methods to execute queries in JPA?

There are three basic types of JPA Queries:Query, written in Java Persistence Query Language (JPQL) syntax. NativeQuery, written in plain SQL syntax. Criteria API Query, constructed programmatically via different methods.

What is the use of @query in JPA?

In order to define SQL to execute for a Spring Data repository method, we can annotate the method with the @Query annotation — its value attribute contains the JPQL or SQL to execute. The @Query annotation takes precedence over named queries, which are annotated with @NamedQuery or defined in an orm. xml file.


1 Answers

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

like image 149
Alexandre Carbenay Avatar answered Sep 27 '22 18:09

Alexandre Carbenay