Spring Data JPA: repositories, query methods, projections, N+1

← Back to the section

Spring Data JPA removes almost all the boilerplate around working with a database. That's convenient as long as the queries are simple. Once they get more complex, you need to understand what happens under the hood, otherwise strange slowdowns appear. Let's start from scratch.

Why you need a repository

Before Spring Data, every table got the same tedious code: open a connection, compose the SQL, walk the result, assemble objects, close the connection. Ten tables meant ten nearly identical classes, each easy to get wrong.

A repository is an object responsible for reading and writing a single entity — an order, for example. The point of Spring Data: you declare only an interface, and Spring generates the implementation.

public interface OrderRepository extends JpaRepository<Order, UUID> {
}

Here Order is the entity class that maps to a table row, and UUID is the type of its primary key. From this single line alone you get ready-made methods: save, findById, findAll, deleteById, count, and others. Spring creates the implementation itself when the application starts.

JpaRepository is the richest of the ready-made interfaces. There are simpler ones (CrudRepository, PagingAndSortingRepository), but in practice JpaRepository is the usual choice — it includes the others.

Queries from a method name

Ready-made methods like findById aren't always enough — you often need to search by other fields. Spring Data has a cleverer trick: it reads the method name and composes the query itself.

public interface OrderRepository extends JpaRepository<Order, UUID> {

    List<Order> findByCustomerIdAndStatus(UUID customerId, OrderStatus status);

    Optional<Order> findFirstByCustomerIdOrderByCreatedAtDesc(UUID customerId);

    long countByCustomerId(UUID customerId);

    boolean existsByOrderNumber(String orderNumber);
}

Spring breaks the name into parts: findBy (we're searching), CustomerId and Status (by which fields), And (both conditions). The method parameters are substituted in the same order.

The names understand keywords: And, Or, Between, LessThan, GreaterThan, Like, In, IsNull, OrderBy<Field>Asc/Desc, Top<N> (the first N), Distinct. A fairly complex search can be assembled from these.

There is no magic here: it is string parsing. The same trick in plain Java, for the single keyword And:

live example

public class QueryMethodDemo {

    static String toJpql(String method) {
        String conditions = method.substring("findBy".length());
        StringBuilder where = new StringBuilder();
        for (String part : conditions.split("And")) {
            String field = Character.toLowerCase(part.charAt(0)) + part.substring(1);
            if (!where.isEmpty()) {
                where.append(" AND ");
            }
            where.append("o.").append(field).append(" = :").append(field);
        }
        return "SELECT o FROM Order o WHERE " + where;
    }

    public static void main(String[] args) {
        System.out.println(toJpql("findByStatus"));
        System.out.println(toJpql("findByCustomerIdAndStatus"));
    }
}

The technique has a limit: once a method name grows to six or seven words, it becomes impossible to read — time to switch to a hand-written query.

A hand-written query with @Query

When a query can't be expressed through a method name, you write it explicitly in a @Query annotation. Inside is not pure SQL but JPQL: a similar language, except it operates on entity classes and their fields rather than tables and columns.

@Query("""
    SELECT o FROM Order o
    WHERE o.customerId = :customerId
      AND o.status IN :statuses
    ORDER BY o.createdAt DESC
""")
List<Order> findRecent(UUID customerId, Collection<OrderStatus> statuses);

Here Order is the class name, not the table name; o.status is a field of the object. Parameters are passed by name: :customerId is taken from the customerId argument.

If JPQL isn't enough (for example, you need functions specific to a particular database), you can write real SQL — for that you add nativeQuery = true:

@Query(value = "SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '24 hours'",
       nativeQuery = true)
List<Order> findRecentNative();

The price is that the query becomes tied to a specific database and isn't checked at compile time: you'll only spot a typo in a column name at runtime.

Projections — when you don't need every field

An ordinary repository method returns a whole entity — every field. A list on the screen often needs two or three of them, and pulling the rest out of the database is wasted work and memory.

A projection returns only the fields you need. The simplest option is an interface with the required getters:

public interface OrderSummary {
    UUID getId();
    String getOrderNumber();
    BigDecimal getTotalAmount();
}

public interface OrderRepository extends JpaRepository<Order, UUID> {
    List<OrderSummary> findByCustomerId(UUID customerId);
}

Spring will see that the method returns OrderSummary and compose SQL with just three columns instead of the whole row.

The second option is your own class (a record, say), assembled right in the query — that way you can pull in a field of a related entity:

public record OrderCard(UUID id, String customerName, BigDecimal totalAmount) {}

@Query("""
    SELECT new com.example.OrderCard(o.id, c.name, o.totalAmount)
    FROM Order o JOIN o.customer c
    WHERE c.id = :customerId
""")
List<OrderCard> findCards(UUID customerId);

The benefit is the same either way: only what you actually need arrives from the database.

Paged output: Page and Slice

An order list may hold millions of rows — you can't hand it over whole, so the data is cut into pages. To get one page, you pass the method a Pageable object:

Page<Order> page = repo.findByStatus(OrderStatus.PENDING,
    PageRequest.of(0, 20, Sort.by("createdAt").descending()));

page.getContent();        // 20 orders on this page
page.getTotalElements();  // how many orders in total
page.getTotalPages();     // how many pages in total

PageRequest.of(0, 20, ...) means: page number 0 (the first), 20 elements each, sorted by date descending.

An important detail: Page runs two queries — one fetches the 20 rows, the second counts the total. On a large table that count can be expensive.

If you don't need the total — for infinite scrolling, where all that matters is "is there more" — take Slice. It skips the count and runs a single query:

Slice<Order> slice = repo.findByStatus(OrderStatus.PENDING, PageRequest.of(0, 20));
slice.hasNext();  // true if there's another page ahead

The rule is simple: need page numbers and a total count — Page; need only "load more" — Slice.

The N+1 problem

This is the most common and most treacherous problem in JPA. First, how it works.

Entities have relationships: an order has a customer, an order has line items. Here is the trap almost everyone burns on. A reference to a single object — @ManyToOne and @OneToOne, the customer of an order — is loaded eagerly by default: you fetched the order, and Hibernate went to the database for the customer along the way, even if nobody needs it. Collections — @OneToMany, @ManyToMany, those same order lines — are the opposite: they are loaded lazily, and until you touch them the database isn't queried, but on the first access Hibernate quietly runs a separate query.

That's why fetch = FetchType.LAZY is written on references by hand almost always, and the data you do need is fetched explicitly — with JOIN FETCH or @EntityGraph.

That sounds reasonable, but look what happens in a loop. Below the database is replaced by a query counter: the first run reaches for the customer lazily, the second gets it right away.

live example

import java.util.List;

public class NPlusOneDemo {

    record Order(long id, long customerId, String customer) {}

    static int queries;

    static List<Order> findAll(boolean joinFetch) {
        queries++;
        return List.of(new Order(1, 7, joinFetch ? "Smith" : null),
                       new Order(2, 8, joinFetch ? "Jones" : null),
                       new Order(3, 9, joinFetch ? "Brown" : null));
    }

    static String loadCustomer(long customerId) {
        queries++;
        return "customer " + customerId;
    }

    public static void main(String[] args) {
        queries = 0;
        for (Order order : findAll(false)) {
            loadCustomer(order.customerId());
        }
        System.out.println("lazy reference: queries " + queries);

        queries = 0;
        for (Order order : findAll(true)) {
            order.customer();
        }
        System.out.println("JOIN FETCH:     queries " + queries);
    }
}

A hundred orders is one query for the list plus a hundred queries for the customers. Hence the name: N+1. On the page everything works, on test data it's fast, but under real load the database chokes. It's cured with two techniques.

JOIN FETCH — pull it all at once

We ask Hibernate to fetch orders and customers in a single query:

@Query("SELECT o FROM Order o JOIN FETCH o.customer WHERE o.status = :status")
List<Order> findWithCustomer(OrderStatus status);

Now it's one SQL statement instead of a hundred and one.

@EntityGraph — the same thing, but declaratively

If you'd rather not write a query, just list what to load along with it:

@EntityGraph(attributePaths = {"customer", "lines"})
List<Order> findByStatus(OrderStatus status);

The result is the same: the related data arrives in a single query.

Open Session In View

Another trap tied to lazy loading. By default, Spring Boot keeps the Hibernate session open for the entire HTTP request — this setting is called Open Session In View (spring.jpa.open-in-view=true). In practice the connection is held with it: taken on the first database access, returned to the pool only when the request ends.

The convenience is that lazy fields can be touched anywhere: in the controller, in the page template. The problem is exactly that: queries start firing silently while the response is being built, far from where the data is actually needed. That very N+1 problem spreads across the whole application, and it's hard to notice because "everything still works."

A common recommendation is to turn this setting off:

spring.jpa.open-in-view=false

After that, an attempt to access a lazy field outside a transaction immediately produces a LazyInitializationException error. That's not a bug but a useful signal: it forces you to fetch everything you need up front (via JOIN FETCH, @EntityGraph, or a projection) where the database work happens, and hand out a ready result.

In short

• A repository is declared as an interface; Spring writes the implementation. The default choice is JpaRepository.
• From a method name (findByCustomerIdAndStatus) Spring composes the query itself; when the name gets too long — switch to @Query: there you write JPQL (over classes and fields) or real SQL with nativeQuery = true.
• Projections return only the fields you need — through an interface or through your own record in the query.
• Page runs two queries (data + total count), Slice runs one (only "is there more").
• N+1 — accessing lazy relationships in a loop spawns one query per element; cured with JOIN FETCH or @EntityGraph.
• Open Session In View is often turned off (open-in-view=false): it exposes hidden queries and forces you to load data deliberately.

What to read next

• @Transactional in depth — how a transaction manages the database connection.
• Spring Testing — @DataJpaTest and testing repositories.
• The N+1 problem in Hibernate — the same trouble from Hibernate's side: how to spot it in the logs and what else cures it.
• ACID and isolation levels in PostgreSQL — what happens at the database level itself.