Fix JPA N+1 Queries and Open-in-View in Spring Boot

Two JPA defaults cause most of the database trouble in Spring Boot apps. Lazy associations turn one innocent-looking loop into hundreds of queries: the N+1 problem. And open-in-view, which Spring Boot switches on by default, hides those queries by letting them run anywhere in a request, even while Jackson is writing the JSON response. With ten rows on a laptop neither shows up. With real data, both do.

This guide shows how to see N+1 queries, the four ways to fix them, and why spring.jpa.open-in-view=false belongs in every new project. The examples use Spring Boot 3 and Hibernate 6.

The N+1 problem in one loop

Take an order with its lines, mapped the usual way:

@Entity
public class PurchaseOrder {

    @Id @GeneratedValue
    private Long id;
    private String orderNumber;

    @Enumerated(EnumType.STRING)
    private OrderStatus status;

    @OneToMany(mappedBy = "order", cascade = CascadeType.ALL)
    private List<OrderLine> lines = new ArrayList<>();
    // getters ...
}

@Entity
public class OrderLine {

    @Id @GeneratedValue
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)   // @ManyToOne is EAGER unless you say otherwise
    private PurchaseOrder order;

    private String productCode;
    private int quantity;
    // getters ...
}

Now list the placed orders with their line counts:

@Transactional(readOnly = true)
public List<OrderView> placedOrders() {
    return orderRepository.findByStatus(OrderStatus.PLACED).stream()
            .map(o -> new OrderView(o.getOrderNumber(), o.getLines().size()))
            .toList();
}

findByStatus runs one query. Each getLines() then initializes a lazy collection with a query of its own. One hundred orders means 101 queries, and the method looks completely harmless.

Make the queries visible

You cannot fix what you cannot see. In a test profile, turn on Hibernate’s statistics and SQL log:

spring.jpa.properties.hibernate.generate_statistics=true
logging.level.org.hibernate.SQL=debug

Better still, turn the query count into an assertion. A test that expects one statement fails the day somebody adds a lazy access to the loop:

@SpringBootTest
@ActiveProfiles("test")
class OrderQueryCountTest {

    @Autowired EntityManagerFactory entityManagerFactory;
    @Autowired OrderService orderService;

    @Test
    void loadsPlacedOrdersWithOneStatement() {
        // given: a few orders with lines saved in @BeforeEach
        Statistics stats = entityManagerFactory.unwrap(SessionFactory.class).getStatistics();
        stats.clear();

        orderService.placedOrders();

        assertThat(stats.getPrepareStatementCount()).isEqualTo(1);
    }
}

Four ways to fix it

1. Join fetch in the query

Tell Hibernate to load the lines in the same SQL statement as the orders:

@Query("select o from PurchaseOrder o join fetch o.lines where o.status = :status")
List<PurchaseOrder> findWithLinesByStatus(@Param("status") OrderStatus status);

Hibernate 6 removes the duplicate parent rows a join produces by itself, so the distinct you see in older examples is no longer needed.

2. An entity graph on a derived query

If you would rather keep Spring Data’s derived queries, @EntityGraph adds the fetch without writing JPQL:

@EntityGraph(attributePaths = "lines")
List<PurchaseOrder> findByStatus(OrderStatus status);

3. Batch fetching as a safety net

Some lazy access is legitimate and hard to predict. Batch fetching makes Hibernate initialize up to N collections at once with an IN (…) query, which turns 1 + 100 queries into 1 + 2:

spring.jpa.properties.hibernate.default_batch_fetch_size=50

It does not remove the extra queries, but it caps them. That makes it a good default for the whole application, alongside explicit fetches on the queries you know about.

4. Don’t load entities you are only going to read

A list screen that shows an order number and a count does not need managed entities at all. A constructor expression returns exactly the columns it needs, in one query, with nothing for Hibernate to track:

public record OrderSummary(String orderNumber, Long lineCount) {}

@Query("""
       select new com.example.orders.OrderSummary(o.orderNumber, count(l))
       from PurchaseOrder o left join o.lines l
       where o.status = :status
       group by o.orderNumber
       """)
List<OrderSummary> summarize(@Param("status") OrderStatus status);

Spring Data’s interface and class-based projections do the same for derived queries. Use entities when you are going to change them, and projections when you are only going to show them.

One more limit: Hibernate refuses to join fetch two List collections of the same entity in one query (MultipleBagFetchException), because the result would be a cartesian product. Fetch the second collection in a separate query, or rely on batch fetching for it.

What open-in-view actually does

On startup, a default Spring Boot app with JPA logs this warning:

spring.jpa.open-in-view is enabled by default. Therefore, database queries
may be performed during view rendering. Explicitly configure
spring.jpa.open-in-view to disable this warning

With open-in-view on, Spring registers an interceptor that opens a persistence context when a web request starts and closes it only when the response is finished. Lazy associations therefore keep working after your @Transactional service method has returned, including while Jackson serializes an entity in the controller.

That convenience has two costs:

• Hidden queries. Every lazy association that Jackson touches while writing the response is another SELECT, often an N+1, running outside any transaction and invisible in the service code.
• Connections held longer. Once the request has used the database, its connection stays checked out until the response is written. Under load, slow clients and large responses hold connections that other requests are waiting for, and the pool runs dry long before the database is busy.

Turn it off

spring.jpa.open-in-view=false

Now the persistence context lives exactly as long as the transaction. Code that relied on open-in-view fails with a LazyInitializationException, and that failure is useful: it points at exactly the data the service forgot to fetch. Fix each one with a join fetch, an entity graph or a projection in the service, and return DTOs rather than entities from your controllers, so serialization never touches the persistence context at all.

On an existing application, switch it off in a branch and run the tests. Every exception that appears is a query that was previously running unnoticed during serialization.

Checklist

• @ManyToOne and @OneToOne marked LAZY explicitly.
• Statement-count assertions on the queries that matter.
• Join fetch or @EntityGraph where you know what you need; projections for read-only lists.
• hibernate.default_batch_fetch_size as a safety net.
• fail_on_pagination_over_collection_fetch=true, and two-step paging for collections.
• spring.jpa.open-in-view=false, with DTOs returned from controllers.