@OneToOne relationship with spring data JPA, how to avoid N+1 problem when fetching a list of items?

The problem:

Imagine You have the following entities:

The Person entity:

package com.chmits.demo.entities;

import lombok.*;

import javax.persistence.*;

import java.util.UUID;

import static javax.persistence.GenerationType.AUTO;

/**
 * @author Chamseddine Benhamed <chamseddine.bhd at gmail.com>
 */

@Entity(name = "person")
@Table(name = "person")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class Person {
    @Id
    @GeneratedValue(strategy = AUTO)
    @Column(name = "id")
    private UUID id;

    @Column(name = "something")
    private String something;

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "passport")
    private Passport passport;
}

The Passport entity:

package com.chmits.demo.entities;

import lombok.AllArgsConstructor;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

import javax.persistence.*;

import java.util.UUID;

import static javax.persistence.GenerationType.AUTO;

/**
 * @author Chamseddine Benhamed <chamseddine.bhd at gmail.com>
 */

@Entity(name = "passport")
@Table(name = "passport")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class Passport {
    @Id
    @GeneratedValue(strategy = AUTO)
    @Column(name = "id")
    private UUID id;

    @Column(name = "something")
    private String something;
}And a JpaRepository:
package com.chmits.demo.repositories;

import com.chmits.demo.entities.Student;
import org.springframework.data.jpa.repository.JpaRepository;

import java.util.List;

public interface StudentRepository extends JpaRepository<Student, Long>{
}

So there’s one to one relation between Person and Passport

One to One relation

The Person JpaRepository :

package com.chmits.demo.repositories;

import com.chmits.demo.entities.Person;
import org.springframework.data.jpa.repository.JpaRepository;

/**
 * @author Chamseddine Benhamed <chamseddine.bhd at gmail.com>
 */

public interface PersonRepository extends JpaRepository<Person, Long>{

}

The test:

@Test
public void OneToOneFindAll() {
    Person person1 = new Person();
    person1.setSomething("person1");

    Person person2 = new Person();
    person2.setSomething("person2");

    Person person3 = new Person();
    person3.setSomething("person3");

    Passport passport1 = new Passport();
    passport1.setSomething("passport1");

    Passport passport2 = new Passport();
    passport2.setSomething("passport2");

    person1.setPassport(passport1);
    person2.setPassport(passport2);

    System.out.println("SAVE PERSON 1");
    personRepository.save(person1);

    System.out.println("SAVE PERSON 2");
    personRepository.save(person2);

    System.out.println("SAVE PERSON 3");
    personRepository.save(person3);

    System.out.println("FIND ALL PERSONS");
    List<Person> personList = personRepository.findAll();
}

Logs after test execution:

The PersonRepository findAll() method executed 3 queries, one to get all persons (3 in our test) and two queries to get the two passports referenced by person1 and person2 !

Imagine that we have a REST endpoint [GET /v1/persons] in our app to get all persons in a list. That endpoint uses PersonRepository’s findAll() method to return the result.

findAll() in that case will execute N + 1 queries (N is the length of the persons list), one query to get all persons and one query per person to get its passport, so to get a list with 10 000 entities, we should execute 10 001 queries ! That’s huge right ?

The solution:

To make findAll() method executes only one query instead of N + 1 :

Update the Passport definition :

@Entity(name = "person")
@Table(name = "person")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@NamedEntityGraph(name = "Person.withPassport", attributeNodes = @NamedAttributeNode("passport"))
public class Person {
...
}

Then override the findAll() method in PersonRepository or create a new JpaRepository for the Person entity (so you can use two versions of findAll()):

package com.chmits.demo.repositories;

import com.chmits.demo.entities.Person;
import org.springframework.data.jpa.repository.EntityGraph;
import org.springframework.data.jpa.repository.JpaRepository;

import java.util.List;

/**
 * @author Chamseddine Benhamed <chamseddine.bhd at gmail.com>
 */

public interface PersonRepository extends JpaRepository<Person, Long>{

    @Override
    @EntityGraph("Person.withPassport")
    List<Person> findAll();
}

Now the findAll() method will execute one query instead of N +1 queries as show the new test log :