Spring Data JPA with Spring Boot: Repositories and the N+1 Problem

A bike-share operator asks for a simple screen: all available bikes with the name of the station each one is docked at. You write a repository call and a one-line loop, it works on your laptop with six bikes, and in staging with a few thousand it is mysteriously slow. The loop did nothing wrong; it quietly ran a database query for every station it touched. That trap is called N+1, and Spring Data JPA makes it very easy to fall into.

Entities and a repository, with almost no code

Add spring-boot-starter-data-jpa plus a database driver (H2 is convenient for development) and Boot configures Hibernate and a connection pool for you. An entity is a plain class with @Entity and an @Id; JPA needs a no-argument constructor, which can be protected. The relationship below is @ManyToOne(fetch = FetchType.LAZY): loading a bike does not load its station until you touch it.

package com.example.bikes;

import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.EnumType;
import jakarta.persistence.Enumerated;
import jakarta.persistence.FetchType;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.ManyToOne;

enum BikeStatus { AVAILABLE, IN_USE, MAINTENANCE }

@Entity
class Station {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, unique = true)
    private String name;

    protected Station() {}                       // required by JPA
    Station(String name) { this.name = name; }
    Long getId() { return id; }
    String getName() { return name; }
}

@Entity
class Bike {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false, unique = true, length = 12)
    private String code;

    @Enumerated(EnumType.STRING)                  // store names, not ordinals
    @Column(nullable = false)
    private BikeStatus status;

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "station_id")
    private Station station;

    protected Bike() {}
    Bike(String code, BikeStatus status, Station station) {
        this.code = code; this.status = status; this.station = station;
    }
    String getCode() { return code; }
    BikeStatus getStatus() { return status; }
    Station getStation() { return station; }
}

// Each top-level class normally lives in its own file; they are shown together for brevity.

Derived queries: the method name is the query

A repository interface extending JpaRepository<Bike, Long> gives you save, findById, findAll, deleteById and paging for free. Beyond that, Spring Data parses method names: findByStatus becomes where status = ?. Property paths are followed with the property names, so StationName means station.name and triggers a join.

package com.example.bikes;

import java.util.List;
import java.util.Optional;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

record BikeRow(String code, String stationName) {}

interface BikeRepository extends JpaRepository<Bike, Long> {

    Optional<Bike> findByCode(String code);

    boolean existsByCode(String code);

    long countByStationNameAndStatus(String stationName, BikeStatus status);

    List<Bike> findByStatusOrderByCodeAsc(BikeStatus status);

    Page<Bike> findByStatus(BikeStatus status, Pageable pageable);

    // Hand-written JPQL when a method name would become unreadable.
    // The constructor expression needs the fully qualified record name.
    @Query("select new com.example.bikes.BikeRow(b.code, s.name) "
         + "from Bike b join b.station s where b.status = :status order by b.code")
    List<BikeRow> findRows(@Param("status") BikeStatus status);
}

Watching the N+1 happen

Turn on SQL logging in development so you can count statements instead of guessing. logging.level.org.hibernate.SQL=debug prints each statement Hibernate sends (the older spring.jpa.show-sql=true also works). While you are in the file, set spring.jpa.open-in-view=false; Boot enables that setting by default and logs a warning about it, because it keeps a database session open during view rendering and lets lazy loading hide in controllers.

spring:
  datasource:
    url: jdbc:h2:mem:bikes
  jpa:
    open-in-view: false
    hibernate:
      ddl-auto: create-drop   # fine for a demo; use migrations (Flyway/Liquibase) for real data
logging:
  level:
    org.hibernate.SQL: debug

package com.example.bikes;

import java.util.List;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class AvailabilityService {

    private final BikeRepository bikes;

    public AvailabilityService(BikeRepository bikes) {
        this.bikes = bikes;
    }

    // Looks harmless. It is the N+1 trap.
    @Transactional(readOnly = true)
    public List<String> availableLabels() {
        return bikes.findByStatusOrderByCodeAsc(BikeStatus.AVAILABLE).stream()
                .map(b -> b.getCode() + " @ " + b.getStation().getName())
                .toList();
    }
}

findByStatusOrderByCodeAsc runs one query for the bikes. Each station is a lazy proxy, so the first call to getStation().getName() for a new station triggers another select ... from station where id = ?. Hibernate caches loaded entities within the transaction, so you pay one extra query per distinct station, not per bike. With 3,000 bikes spread over 40 stations that is 41 queries; with 3,000 bikes each at a different station it is 3,001. The pain scales with how scattered your data is, which is why it hides in small test data.

Three fixes, and when to choose each

// 1) Entity graph: keep the derived query, ask for the station in the same select.
@EntityGraph(attributePaths = "station")
List<Bike> findByStatusOrderByCodeAsc(BikeStatus status);

// 2) Fetch join in JPQL: explicit and visible in the query text.
@Query("select b from Bike b join fetch b.station where b.status = :status order by b.code")
List<Bike> findWithStation(@Param("status") BikeStatus status);

// 3) Projection: select only the two columns you need, no entities at all.
//    This is the findRows(...) method from earlier, returning BikeRow.

(@EntityGraph is org.springframework.data.jpa.repository.EntityGraph.) Use the entity graph for read paths that always need the relation; a fetch join when the query is already hand-written; and a projection for list screens, where loading whole entities (and their change-tracking overhead) is wasted work. For collection relationships (a station's list of bikes), a fetch join combined with pagination makes Hibernate log a warning that it applied paging in memory, which defeats the point; use a two-step query or batch fetching (spring.jpa.properties.hibernate.default_batch_fetch_size) there.

How do you know the fix worked? Do not trust intuition; measure. Hibernate can count statements for you when spring.jpa.properties.hibernate.generate_statistics is true, and a repository test under @DataJpaTest can assert on that count so a regression fails the build instead of a production dashboard. Even without statistics, a habit of loading your list endpoint once with logging on and counting the select lines takes thirty seconds and catches most mistakes before review.

Common mistakes and what they look like

• Lazy access outside a transaction. With open-in-view off, touching getStation().getName() after the service returns throws LazyInitializationException: could not initialize proxy - no Session. Fetch what you need inside the transactional method or map to a DTO there.
• Misspelled property in a method name. findByStatuz fails at startup with No property 'statuz' found for type 'Bike', which is the good kind of failure: caught on boot, not at runtime.
• Relying on `ddl-auto` in production. create-drop wipes the schema when the app stops. Use validate with Flyway or Liquibase migrations once real data exists.
• Ordinal enums. @Enumerated without EnumType.STRING stores positions, so reordering the enum silently relabels existing rows.
• No unique constraint to back a uniqueness check. existsByCode followed by save races under concurrency; let the database constraint be the final judge and handle DataIntegrityViolationException.

How this shows up in real projects and interviews

N+1 is probably the most asked Spring Data JPA question because everyone has shipped one. A convincing answer describes how you noticed it (SQL log or query counts), why it happens (lazy proxies plus a loop), and the fix you chose and why. Related topics are index design for the columns your derived queries filter on, covered in the database indexing course, and the lazy-versus-eager trade-off, which interviewers use to test whether you understand the persistence context rather than only annotations.