Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 36 ยท Data with Panache

Relationships, N+1 and DTO Projections

Map @ManyToOne and @OneToMany in Quarkus, avoid the N+1 query problem with join fetch, and return DTO records or projections instead of entities.

Real data has relationships: an author writes many books, a book has many reviews. Mapping them is easy. Mapping them efficiently is where most database performance problems start. This lesson covers the two relationship annotations you need every day, the famous N+1 query problem, and how to hand data to clients as DTOs (data transfer objects) instead of entities.

The Hibernate and Panache code is read-only. Runnable stand-ins count queries in a fake database so you can see N+1 happen and disappear.

Mapping a relationship

A book belongs to one author; an author has many books:

Java
@Entity
public class Author extends PanacheEntity {

    @Column(nullable = false)
    public String name;

    @OneToMany(mappedBy = "author", cascade = CascadeType.ALL, orphanRemoval = true)
    public List<Book> books = new ArrayList<>();

    public void addBook(Book book) {
        books.add(book);
        book.author = this;       // keep both sides in sync
    }
}

@Entity
public class Book extends PanacheEntity {

    @Column(nullable = false)
    public String title;

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "author_id")
    public Author author;
}

Key points:

  • The owning side is the one with the foreign key: Book.author. The other side uses mappedBy and does not create a column.
  • @ManyToOne is eager by default; set fetch = FetchType.LAZY so the author loads only when you touch it. @OneToMany is lazy by default and should stay lazy.
  • cascade passes operations down: persisting an author persists the books in its list. orphanRemoval deletes books removed from the list.
  • Always update both sides of a bidirectional relationship, as addBook does.
  • @ManyToMany uses a join table (@JoinTable); @OneToOne shares a key or a foreign key. Prefer modelling many-to-many as an entity with its own fields when the link carries data (for example Enrollment).

The N+1 problem

Suppose you list every author with the count of their books:

Java
List<Author> authors = Author.listAll();               // 1 query
for (Author a : authors) {
    System.out.println(a.name + ": " + a.books.size()); // 1 query PER author
}

One query loads the authors. The lazy books collection of each author triggers another query when it is touched: N more. For 500 authors that is 501 queries. Nothing is wrong with the code's logic, only with how often it talks to the database. The stand-in simulates this:

Java Stand-in: counting queries (N+1)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Three authors cost four queries. Scale that up and a page that looked fast with test data becomes slow in production.

Fixing it with join fetch

Tell Hibernate to load the relationship in the same query:

Java
List<Author> authors = Author.find("select distinct a from Author a left join fetch a.books").list();

The database returns authors joined with their books in one statement. Other tools:

  • @BatchSize(size = 50) on a collection loads many collections per query (WHERE author_id IN (...)), turning N queries into N / 50.
  • @EntityGraph or fetch profiles for reusable plans.
  • A projection that selects only the columns you need (below).

Do not make everything EAGER to dodge the problem: that loads data you may not need on every query. Decide per use case.

Java Stand-in: the same listing with one joined query

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Do not expose entities: use DTOs

Returning an entity from an endpoint has problems: lazy collections may fail to load outside a transaction (LazyInitializationException), bidirectional links cause infinite JSON loops, and any field you add to the table appears in your public API. A DTO is a small record with exactly the fields the client needs:

Java
public record AuthorSummary(Long id, String name, int bookCount) {}

public record BookView(Long id, String title, String authorName) {
    public static BookView from(Book b) {
        return new BookView(b.id, b.title, b.author.name);
    }
}

Mapping by hand (a static from method) is simple, explicit and fast. Libraries such as MapStruct (which has a Quarkus integration) generate the mapping code at build time when you have many types.

Projections: select only what you need

Panache can build DTO records directly from the query with project(...), so Hibernate does not even load the full entity:

Java
@RegisterForReflection
public record BookRow(String title, String authorName) {}

List<BookRow> rows = Book
        .find("select b.title, b.author.name from Book b order by b.title")
        .project(BookRow.class)
        .list();

The record's constructor parameters must match the selected columns in order. @RegisterForReflection helps in native images. Projections also work with Page and Sort, and they never become managed entities, so they are cheaper for read-only lists.

Which approach when?

Loading related data

Lazy Load on first use. The default.
N+1 One extra query per row.
Join fetch One query with the relation.
Projection Only the columns you show.

Count the queries your endpoint runs.

1 / 2

A good habit: enable SQL logging while developing (quarkus.hibernate-orm.log.sql=true in dev) and watch the console as you call your endpoints. If one request prints dozens of similar select lines, you have an N+1.

In tests, you can assert the number of statements with Hibernate's statistics (quarkus.hibernate-orm.statistics=true). A test that says "listing authors runs 1 query" will protect you from regressions.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In a bidirectional @OneToMany(mappedBy = "author") relationship, which side owns the foreign key?

    mappedBy marks the inverse side. The @ManyToOne side holds the author_id column.
  2. 2. What is the N+1 problem?

    Lazy relations are loaded one by one as you touch them, which multiplies queries.
  3. 3. Which is a way to avoid N+1 for one use case?

    join fetch loads the relation in the same query. Global EAGER loads too much everywhere.
  4. 4. Why return DTO records instead of entities from REST endpoints?

    A DTO is a stable public shape that decouples your API from the table structure.

Summary

  • Map relationships with @ManyToOne (owning side, lazy) and @OneToMany(mappedBy = ...); keep both sides in sync.
  • N+1 means one query for the list plus one per row for a lazy relation; fix it with join fetch, batch size or projections.
  • Return DTO records, hand-mapped or built with project(...), instead of entities.
  • Watch the SQL log to find hidden queries.

Next: Transactions and Flyway Migrations