Projection-based reading of lists

1. Two backoffice lists in Commerce Persistence Lab

Projection is easy to underestimate as “well, sure, you can trim fields.” But on a real list, this is no longer cosmetic; it is a proper refactoring with a very clear result. We will take two typical scenarios: a product table and an order table in the backoffice. In both cases, the screen user (and the code) needs only a few columns, not the entire entity graph.

Let us pin down the goal as concretely as possible. We have two “list” read use cases:

The first is the Product list. Usually, the product table needs id, sku, name, and sometimes status. We do not need to drag along ProductDetails, categories, related entities, and certainly not a managed entity that could accidentally be changed during “just reading.”

The second is the PurchaseOrder list. In a typical order table, you need id, orderNumber, status, creation date, and the customer email. Note: the email lives in Customer, which means that the order list in its simplest form almost guarantees that someone will “innocently” reach for order.getCustomer().getEmail() and end up with either N+1, or a series of lazy loads, or the temptation to make everything EAGER (and then cry about it).

The key point is this: we are not “adding another DTO.” We are shaping a read-model for a specific table and locking it in at the repository and query-service level.

To keep the idea from dissolving, you can picture it like this:

flowchart TD
    UI["Backoffice list screen
(roughly, a table)"]
    QS["Query Service
@Transactional(readOnly=true)"]
    R["Repository
@Query + projection"]
    DB[(PostgreSQL)]
    DTO["ProductListRow / OrderListRow
(read-model)"]

    UI --> QS --> R --> DB
    DB --> R --> DTO --> QS --> UI

Here, DTO is not a “portable bag of data.” It is a “table row” contract, not a “domain object for life and growth.”

2. Start: table columns and sorting

It is very tempting to start the refactoring in the IDE: open the repository, change the return type, and proudly hit Build. But if you do that, you will almost certainly end up with either a projection the size of a half-entity, or three different versions of the same DTO, or the eternal “let us add a few more fields, just in case.” So the starting step is not code, but clarity: which columns does the list actually need today?

For the product list, let us agree (for the course example) that we need id, sku, and name. That is enough to see the principle. If your screen in the project already shows status or price as well, no problem — add it later, but only add what is actually displayed.

For the order list, let us agree that we need id, orderNumber, customerEmail, status, and createdAt. This already shows an important thing: one field (customerEmail) lives in another entity, which means we either explicitly do a join or start living in a world of “accidental fetches.”

The next piece of discipline is stable sorting. For products, it is convenient to sort by name, and then by id, so that identical names do not break predictability. For orders, it is often convenient to sort by createdAt desc, and then by id desc, so that page #2 does not “move” when new orders appear.

If you want to remember this in one sentence, it is this: first define the columns and sorting order, then choose the projection, and only then write the query. Otherwise, you are building the API backwards: “here is the entity, and now somehow pull what you need out of it.”

To avoid assembling each list from scratch, it is convenient to keep a short workflow:

1. Fix the columns and the default sort.
2. Choose the read-model shape: interface-based projection when the list is simple and single-table; class-based DTO or an explicit @Query when you need joins, field renaming, and want to control the column set visually.
3. Make the repository return the read-model right away, not the entity.
4. Attach Pageable and stable sorting through a unique tie-breaker.
5. Check the SQL trace and the count query if you return a Page.

Now we will simply walk through this path on Product and PurchaseOrder. For both examples, we will stay on the class-based track: it is easier to see both the result shape and the joins themselves, and with orders this is especially useful.

3. Product: entity → ProductListRow

On Product, this path is almost boring — and that is its advantage. The list is single-table and flat, so interface-based projection would also fit here. But to avoid branching into two different paths later, we will keep class-based select new: it shows the result shape as clearly as possible and then moves over to PurchaseOrder without surprises.

Let us imagine that we had something like this in the repository:

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;

public interface ProductRepository extends JpaRepository<Product, Long> {

    // We return a managed entity: inside a transaction this is a live Hibernate object
    // with all the "benefits" of the persistence context (dirty checking, accidental changes, etc.).
    Page<Product> findByStatus(ProductStatus status, Pageable pageable);
}

At first glance, everything looks nice. But the problem is precisely that the method is “too universal.” It returns a managed entity, and that means that inside @Transactional this product list becomes part of the persistence context with all its habits: snapshots, dirty checking, potential accidental updates, and the temptation to pull lazy relationships directly in list rendering.

Creating a read-model for a list row

The read-model here is intentionally simple: the same ProductListRow, containing only id, sku, and name. This shape is enough for a catalog row, and it honestly separates the list from the Product entity.

Adding a projection method to the repository

Now we replace the “universal” entity method with an honest read method that returns Page<ProductListRow>. We write an explicit JPQL query with a constructor expression so that it is obvious by inspection which fields are selected.

import com.example.commerce.catalog.dto.ProductListRow;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;

public interface ProductRepository extends JpaRepository<Product, Long> {

    @Query("""
           select new com.example.commerce.catalog.dto.ProductListRow(p.id, p.sku, p.name)
           from Product p
           where p.status = :status
           """)
    // Important: the query is responsible for the result shape, and the stable order comes from Pageable.
    Page<ProductListRow> findProductRowsByStatus(ProductStatus status, Pageable pageable);
}

There are several important points here.

First, we selected only the needed columns. Hibernate will not materialize Product as a managed entity, because we are not selecting p; we are selecting new ....

Second, the query stays in the same class-based style already familiar from select new. So this is not a new baseline; it is the same pattern applied to the product list.

Third, we do not hardcode the sorting in @Query. The page order lives in Pageable, so the default sort is visible where we actually assemble the list.

Using it through a query service (thin layer)

A good style in the project is to keep such methods not as “just another repository,” but as a read use case at the query package level. Then the catalog write service can remain entity-oriented, while the read service becomes projection-oriented.

import com.example.commerce.catalog.dto.ProductListRow;
import com.example.commerce.catalog.entity.ProductStatus;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.transaction.annotation.Transactional;

@Transactional(readOnly = true) // Declare the intent: this is reading, without accidental writes.
public Page<ProductListRow> listActiveProducts(int page, int size) {
    Sort sort = Sort.by("name").ascending()
            .and(Sort.by("id").ascending());

    PageRequest pageable = PageRequest.of(page, size, sort);

    return productRepository.findProductRowsByStatus(ProductStatus.ACTIVE, pageable);
}

This keeps the default sort close to the read use case, and the repository does not start dragging a fixed order by around for every situation.

SQL after the transition

If before the refactoring you were doing Page<Product>, Hibernate usually selects all columns from the product table (and sometimes also issues unexpected secondary queries if someone somewhere touched a lazy association).

After the transition to projection, the SQL becomes flat and honest, approximately like this (simplified):

-- Read exactly the columns needed by the list row.
-- Sorting comes from Pageable, not from a hardcoded ORDER BY in the query.
select p.id, p.sku, p.name
from product p
where p.status = ?
order by p.name, p.id
limit ? offset ?;

And you can see it right away: “yes, I am reading the table, yes, only three columns, yes, only what the list needs.” At the same time, Spring Data will produce the expected count. At that point, the list behaves predictably: only the needed columns, one content query plus count, and no extra managed-life around it.

4. PurchaseOrder: a list with customer email

If the product list was a warm-up, the order list is already much closer to reality. Here it is very easy to end up in the situation “I read orders, and then I read customers separately, and then I read more separately...”. This is exactly the list that often gives birth to the classic N+1: we showed 20 orders, and for each one we display the customer email — and now there are +20 queries for no evil reason whatsoever, just because the code “looks normal.”

We will make a read-model that already contains customerEmail, that is, what the table actually needs, and we will get it in one query via an explicit join.

DTO/record for an order list row

The read-model here is a little richer: OrderListRow(id, orderNumber, customerEmail, status, createdAt). What matters is not the length of the result, but the fact that customerEmail comes as part of the list row, not as a reason to then go to o.getCustomer().

Repository: join, Pageable, and countQuery

We write the query method. Now the new delta compared with Product is not the projection itself, but the explicit join to Customer and the explicit countQuery for Page.

import com.example.commerce.orders.dto.OrderListRow;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;

public interface PurchaseOrderRepository extends JpaRepository<PurchaseOrder, Long> {

    @Query(
        value = """
               select new com.example.commerce.orders.dto.OrderListRow(
                   o.id, o.orderNumber, c.email, o.status, o.createdAt
               )
               from PurchaseOrder o
               join o.customer c
               """,
        countQuery = """
               select count(o)
               from PurchaseOrder o
               join o.customer c
               """
    )
    Page<OrderListRow> findOrderRows(Pageable pageable);
}

The countQuery is shown explicitly here not as a ritual. Page has two different questions: which rows to show now, and how many rows exist in total. As soon as joins and conditions grow, an explicit countQuery removes unnecessary magic and makes the contract clearer.

And this is exactly where you can see why class-based DTO remains a good choice for such a list: the join and the set of columns are written directly in the query, rather than hidden behind “first return PurchaseOrder, then somehow get the email.”

Read-only service and Pageable

The service remains very thin: it sets a stable default sort and passes it to the repository.

import com.example.commerce.orders.dto.OrderListRow;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.transaction.annotation.Transactional;

@Transactional(readOnly = true)
public Page<OrderListRow> listOrders(int page, int size) {
    Sort sort = Sort.by("createdAt").descending()
            .and(Sort.by("id").descending());

    PageRequest pageable = PageRequest.of(page, size, sort);

    return purchaseOrderRepository.findOrderRows(pageable);
}

This keeps the page order where it should be for a list use case: in Pageable, while the query is responsible only for the result shape and the joins.

Projection instead of JOIN FETCH in a list

Here it is useful to understand a subtle point that is often confused after a day of fetch tooling. On the order list, you could solve the customer email problem with JOIN FETCH, which would still load managed PurchaseOrder and also Customer. The result is a “live graph” where you actually need a flat read result.

Projection solves the task differently: the join is needed only for the customerEmail column, not for loading an entity graph. So it is not a competitor to fetch tooling; it is often the more correct move at the level of choosing the read-model.

5. Read-model in services and transactions

When you first move a list to projection, a typical internal developer dialogue appears: “But how do I edit the product now if the list does not return Product?” That is exactly the moment when we realize that listing and editing are different use cases. The list returns a table row. Editing returns an entity or loads an entity inside a write scenario.

In our project, this can be kept very simple: write services remain entity-oriented, and query services become projection-oriented. In the catalog.service package, the methods for creating and updating a product live; they work by the find + mutate pattern, manage states, and rely on managed semantics. In the catalog.query package, list reading lives as Page<ProductListRow>. The same applies to orders.

Transactions also become simpler, not harder. A projection result is not managed. It does not live in the persistence context. It does not need to “live” in an open session. This means you depend less on transaction boundaries and are less likely to run into a LazyInitializationException because someone later touched an association. ProductListRow has no associations — at most you will access row.name(), and that is just a string, not an SQL trigger.

Finally, an important rule: “do not shoot yourself in the foot, even if the foot is pretty.” Projection should not be used for writing. Do not try to “save the list” or “update the order status through a DTO.” A write scenario must remain a write scenario: load a managed entity (or get a reference), modify it, commit it. Projection is a read-only contract. Its strength is precisely that it does not pretend to be everything at once.

6. Verification via SQL trace

There is a dangerous illusion: “I replaced List<Product> with List<ProductListRow>, so I made it faster.” In the Hibernate world, that does not work. We already have the course’s main tool: SQL trace and statistics. And a good list refactoring must be verified visually: which queries went to the DB, how many there are, and how wide they are.

Before the refactoring, the order list very easily looked like this: one query for orders, and then another batch of queries for the customer (if the email is read somewhere). In the SQL trace, this is usually visible as repeated identical select ... from customer where id = ? queries. And yes, the code can look innocent, especially if it is “just logging” or “just mapping into a DTO.”

After moving orders to projection, the picture becomes boring — and that is a compliment. You will see one query that already includes a join to the customer, and no more surprises around email. For Page, count will be added, but that is expected and controlled. And the nicest part: the width of the select becomes smaller. The list will include exactly the columns you chose. Not 20 order fields, not the whole customer, but only the needed 4–5 values.

If the sql-trace profile is enabled, it is very useful to get used to a short ritual after such changes: run the list scenario, inspect the SQL, confirm that there is one query (or two for Page), confirm that there are no secondary selects on lazy associations, confirm that the select is not dragging in anything extra. It takes a minute, but saves hours of “why is this slow in production when everything flies on my laptop?”

7. Common mistakes

Mistake #1: a “cosmetic” refactoring without changing the result type.
When moving lists to projection, the most common mistake is doing a “cosmetic” refactoring: renaming the method to findProductRows..., but keeping Page<Product>. Outwardly it looks prettier, but in essence you are still returning a managed entity with all its side effects. So check not the name, but the result type and the shape of the select.

Mistake #2: a “universal” projection for all occasions.
Another common mistake is trying to make a “universal” projection that fits the list, the card view, editing, and “just in case” also includes a couple of fields from related entities. Usually such a projection quickly grows into a half-entity, and you are back to the same problem: too wide a contract and too much data in each read. The discipline here is simple: one use case — one projection contract.

Mistake #3: the argument order is mixed up in select new ....
More technical details also break often. For example, with class-based projection, it is easy to mix up the argument order in select new ... and in the constructor (or the canonical record constructor). The code compiles, the application starts, and then in the list sku suddenly ends up in the name field. This is an unpleasant bug because it does not always throw an exception — it just quietly makes the data wrong. It is fixed by paying attention and by the habit of keeping select and constructor side by side and reading them left to right.

Mistake #4: unstable sorting in pageable lists.
A separate pain is unstable sorting in pageable lists. You sort products only by name, do not add id, and at some point the user pages through the list and sees rows “jumping” around. This is not a Hibernate bug; it is your omission: for equal name values, the order is undefined. That is why pageable lists almost always need a unique tie-breaker, most often id.

Mistake #5: “optimizing count” without understanding that Page means two queries.
Finally, many people are surprised that Page makes two queries and start “optimizing count” without realizing that the problem is not there at all. In a real project, count can indeed become expensive, but first it is more important to understand that a pageable scenario is two queries, and to be able to read them in SQL trace. Once you understand this, you stop mystically fearing pagination and start designing it intentionally.