JPQL in mini-shop: product card and order

1. Read scenarios: product card and order summary

It is very easy to learn JPQL with examples like “let’s find all users whose name starts with A”. But as soon as you get into a real project, it turns out that queries are almost always tied to a specific screen or service scenario. So we will take two cases that appear in any store-like domain: a product card and a short order summary.

Let us imagine that we have two use cases.

A product card (product card) is reading a product by sku (the unique business identifier of the product). Usually we need the Product itself and some basic conditions, for example “show the product only if the category is active”. This is exactly the case where a derived query starts peeking from around the corner and says: “I can do it, but you will not like it.”

An order summary (order summary) is reading a small set of numbers by orderNumber: how many items are in the order, what the total for those items is, and sometimes also the “order total” as a field in CustomerOrder. And here is the beginner’s main temptation: load the entire order with items, iterate over the collection, and count everything in Java. It works... until there are 2 items in the order, not 2000, and until you are doing it for a list of 100 orders. So we will do the “grown-up” version: calculate what needs to be calculated directly in the query.

To keep things from drifting, let us fix this in a small table:

BigDecimal

And here you can already see a useful boundary. For a product card we still have enough of the entity as a whole. For an order summary, entity is no longer a convenient reading form: we do not need all fields and not the whole graph, but several aggregate values. As soon as a read needs not the whole object but a narrow read-model, returning entity becomes inconvenient.

And now — to the code.

2. Product card: JPQL instead of a monster method

A product card is a great scenario for feeling the “right taste” of JPQL. In essence, we want to say: “give me a Product whose sku is this, and the category must be active”. In derived queries this quickly turns into something like findBySkuAndCategoryActiveTrue(...). As long as it is short, it is still tolerable. But once you add a couple of conditions, the name starts looking like a 200-character URL.

Earlier we broke JPQL down into local repository fragments. Now let us add code to the specific mini-shop files to see one connected read scenario.

Let us start with the repository. Suppose you already have a ProductRepository interface in the catalog package. We will simply add a new method with @Query.

import java.util.Optional;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

// IMPORTANT: this method is added inside the ProductRepository interface.
@Query("""
       select p
       from Product p
       where p.sku = :sku
         and p.category.active = true
       """)
Optional<Product> findProductCardBySku(@Param("sku") String sku);

Pay attention to a few details that matter more than they seem.

First, from Product p is JPQL, so Product here must match the entity name, not the table name. If your table is called product, and the entity is Product, then this is exactly right. If you ever rename the table, JPQL does not “break” because of that alone — it is tied to the entity model.

Second, p.category.active looks like “magic”, but it is just a path across a ManyToOne relation: Product has a category field, and Category has an active field. We do not refer to category_id because that is already an SQL detail, while JPQL lives on top of the object model.

Third, notice the style of the query string. We use a Java text block (""" ... """). That really improves readability, because the query looks like a query, not like a random long string with + and \n. In Java 25, using this is not only allowed, but pleasant too (almost like in normal languages... joke).

If you want to compare this with a derived query, here is what would be “roughly the same” without JPQL:

import java.util.Optional;

// Derived query: works until the conditions start growing.
Optional<Product> findBySkuAndCategoryActiveTrue(String sku);

This version is not bad. It just starts losing once you add another condition, then one more, then sorting, then some “or”, and then you are no longer sure that the method is named in a human way, and you feel awkward showing it to your colleagues.

3. Product card in the service

When we add JPQL to a repository, there is a temptation to “stuff everything in there”. For example, start writing a query that both returns the “product card”, checks business rules, accepts ten parameters, and can also “just in case” return a list. That is the path to a monster repository. We need exactly one scenario — so there will be one method, and a clear one at that.

Suppose you already have a CatalogService (or a service with a similar meaning that lives in catalog.service). We will add a simple method to it that expresses the card scenario. It should not know JPQL — it should know that a product card is read by sku.

import java.util.Optional;

import org.springframework.stereotype.Service;

@Service
public class CatalogService {
    private final ProductRepository productRepository;

    public CatalogService(ProductRepository productRepository) {
        // Constructor injection: we set service dependencies explicitly.
        this.productRepository = productRepository;
    }

    public Optional<Product> findProductCardBySku(String sku) {
        // The service expresses the use case, not the query details.
        return productRepository.findProductCardBySku(sku);
    }
}

Yes, this is “just a proxy method”. And yes, that happens often in educational code. But it matters because it reinforces an architectural habit: repository — for data and queries, service — for the use case. Today it is one call. Tomorrow, the card will get a rule like “do not show products with status ARCHIVED”, or “if the product is not found — return a domain error”, and you will be glad you have a service layer where this can go without polluting the repository.

And the service is also the place where, in the future, you can add scenario logging (“the user requested a product card”) and avoid putting SQL theater inside the repository.

4. Order summary: count and sum in the database

Now let us move on to orders. An order summary is a very telling case for beginners: the brain immediately suggests “well, we can load the order, take order.getItems(), and count it”. And you really can. But at that point you turn a “short read scenario” into “loading a list of entities + business logic in memory”.

We will do it more neatly: let the database count the quantity and the total. The database can do it and does it well, and, importantly, without loading the entire list of objects into the JVM.

Since we will count order items, it makes sense to make OrderItem the root entity of the query. That is, the query starts with from OrderItem oi, and then we filter by order number through the path oi.order.orderNumber. This is absolutely normal JPQL pathing.

Let us add two methods to OrderItemRepository.

import java.math.BigDecimal;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

// IMPORTANT: these methods are added inside the OrderItemRepository interface.

@Query("""
       select count(oi)
       from OrderItem oi
       where oi.order.orderNumber = :orderNumber
       """)
long countItemsForSummary(@Param("orderNumber") String orderNumber);

@Query("""
       select coalesce(sum(oi.lineTotal), 0)
       from OrderItem oi
       where oi.order.orderNumber = :orderNumber
       """)
BigDecimal sumItemsForSummary(@Param("orderNumber") String orderNumber);

There are two nuances here that usually trip up beginners, so let us say them out loud now, before it hurts.

First nuance: count(oi) returns the number of rows (items). That is exactly what we need for “how many items are in the order”. We are not reading the items themselves, we are simply asking “how many are there?”

Second nuance: sum(oi.lineTotal) in the SQL world (and in JPQL too) may return null if there are no rows. In our domain, by invariant, an order should contain at least one item, but in a training project you can easily create an “empty” order while experimenting. So that the order summary does not turn into a NullPointerException, we use coalesce(..., 0). This means: “if the sum is null, return 0”. For BigDecimal this is also fine, because 0 without a fractional part is safely converted to a monetary type.

If the word coalesce sounds like a spell from Harry Potter, do not worry: it simply means “take the first non-null value”.

5. Order by orderNumber: Optional<CustomerOrder>

An order summary is usually still tied to the order itself: we need the orderNumber, status, customer email, and sometimes totalAmount as well (if we store the amount directly in CustomerOrder). For this, we need a method in CustomerOrderRepository that finds an order by its unique number.

From a technical point of view, a derived query would also be fine here: findByOrderNumber(String orderNumber). But here it is more useful to take the JPQL version: it better shows that we choose the query root based on what we actually want to return.

import java.util.Optional;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

// IMPORTANT: this method is added inside the CustomerOrderRepository interface.
@Query("""
       select o
       from CustomerOrder o
       where o.orderNumber = :orderNumber
       """)
Optional<CustomerOrder> findByOrderNumberJpql(@Param("orderNumber") String orderNumber);

Here it is useful to stop and notice: the root entity of the query is now CustomerOrder, because we want to return the order. In the previous section the root entity was OrderItem, because we wanted to count items. That seems like a small thing, but this is exactly “query thinking”: first decide what to return, and only then choose from.

6. Order summary in the service: OrderSummary

When you have three small methods in repositories — “find the order”, “count items”, “count the total” — the next logical step is to gather the result into a single object that matches the use case “order summary”.

And here it is important not to confuse two things.

We do not make a “DTO projection by query” here. We simply assemble the response in Java code, because that is easier and clearer for us at this stage. That is, we write a small data carrier object and populate it with values from several repository calls.

Let us make a record OrderSummary for this. It is convenient because it is literally a “container for data” without unnecessary noise.

import java.math.BigDecimal;

/**
 * The result of the read scenario "order summary":
 * the minimum data needed by the screen/client.
 */
public record OrderSummary(
        String orderNumber,
        long itemsCount,
        BigDecimal itemsTotal) {
}

Now let us make a service that assembles this summary.

import java.util.Optional;

import org.springframework.stereotype.Service;

@Service
public class OrderReadService {

    public Optional<OrderSummary> getSummary(String orderNumber) {
        // First, we make sure the order exists (0 or 1 row).
        return orderRepository.findByOrderNumberJpql(orderNumber)
                // If the order is found, assemble the summary from item aggregates.
                .map(o -> new OrderSummary(
                        o.getOrderNumber(),
                        itemRepository.countItemsForSummary(orderNumber),
                        itemRepository.sumItemsForSummary(orderNumber)
                ));
    }
}

I am deliberately showing only the method, because the rest is a standard service construction with two dependencies: CustomerOrderRepository orderRepository and OrderItemRepository itemRepository, which you will add through the constructor (just like we did in CatalogService).

Now look at the readability. The method is called getSummary(orderNumber), and it returns Optional<OrderSummary>. Inside it says, “if the order is found, build the summary”. No monstrous names, no loops, no manual math over collections. And the result shape is very clear: if there is no order, Optional.empty().

In such places it is nice to see that Optional really helps. It is not “everywhere”, it is where, by the meaning of the scenario, the result may be absent.

7. Verification: demo-runner and generated SQL

Very often students write a query, everything compiles, and then silence falls. It is unclear: does the query really work, or did it just “not crash”? If SQL logs are already enabled, this is the perfect time to quickly check what exactly went to the database.

The simplest option is to add CommandLineRunner as an @Bean in the main application class.

import org.springframework.boot.CommandLineRunner;
import org.springframework.context.annotation.Bean;

@Bean
CommandLineRunner day11Demo(CatalogService catalogService, OrderReadService orderReadService) {
    return args -> {
        // Quick sanity check: "was the product found by SKU".
        System.out.println("Product found = " + catalogService.findProductCardBySku("SKU-001").isPresent()); // Product found = true

        // If the order exists, print the item count (an aggregate without loading the items collection).
        orderReadService.getSummary("ORD-0001")
                .ifPresent(s -> System.out.println("Items = " + s.itemsCount())); // Items = 2
    };
}

Even this tiny piece already helps. If you have data with such SKU-001 and ORD-0001, you will see two printlns. If there is no data, you will see false and the absence of the second line. That is already a signal that JPQL is not “magic”, but ordinary code that either returns a result or does not.

Now about generated SQL. When you run the application, you will see SQL in the logs, something like this (it may differ in table/column names, but the meaning will remain):

-- Example SQL obtained from the JPQL query over Product/Category entities.
select p1_0.id, p1_0.sku, p1_0.name, p1_0.price, p1_0.category_id
from product p1_0
join category c1_0 on c1_0.id = p1_0.category_id
where p1_0.sku = ? and c1_0.active = true

At this moment it is useful to take a small pause and feel the zen: you wrote JPQL over entities, and the database executed ordinary SQL. No mysticism. Just a convenient abstraction layer that translates an “object query” into a “table query”.

You will see the same for count(...) / sum(...): it will be SQL select count(...) and select coalesce(sum(...), 0). And that is exactly why we love aggregates: there is no need to load the order item list just to find out the total.

8. Typical mistakes when working with JPQL

Before you go and add dozens of queries to your project, it is useful to see the rakes that almost everyone steps on beforehand. And people step on them not because they “do not know”, but because the brain is used to thinking either in SQL, or in derived queries, or “well, it compiles”.

Mistake #1: writing table and column names in JPQL.
The most common “transition bug” looks like this: you write from product p or where p.category_id = .... And that is logical if you just refreshed your SQL. But JPQL reads the world through entities. So the query should contain Product and p.category.id or p.category.code. A good habit is to keep the entity code open nearby and literally check with your eyes: “am I referring to a field or a column?”

Mistake #2: making the method name a repetition of the query text.
When a person switches from derived queries to JPQL, they sometimes keep thinking “the method name is the query”, and end up with something like findBySkuAndCategoryActiveTrueAndWhateverAndWhateverJpql. In the end you get the worst of both worlds: a long name plus a long query. In an application project, it is better for the method name to express the scenario: findProductCardBySku, countItemsForSummary, sumItemsForSummary. Let the selection formula live in JPQL.

Mistake #3: mixing parameter styles and “accidentally” breaking the query.
JPQL supports both :named and ?1. But if you started using named parameters, stick with them to the end. And be sure to keep @Param("orderNumber") in sync with :orderNumber in the query. Nothing breaks a beginner developer’s confidence faster than a typo in a parameter name that looks “minor” but causes an error at runtime.

Mistake #4: returning Optional<T> where there may be many rows.
For a product card, sku is usually unique, so Optional<Product> makes sense. But if, out of habit, you make Optional<OrderItem> for a query by orderNumber, you are setting a trap for yourself: there are many items in an order. And at some point you will get an exception because of a “non-unique result”. The rule is simple: if the condition does not guarantee uniqueness, do not pretend that it does.

Mistake #5: sum(...) returns null, and you did not expect that.
Even if, in the domain, “an order is never empty”, in a training project you can easily create an empty order or drop an item because of an error in cascade. At that point sum(...) may return null, and you will get a NullPointerException in the service, in the most unexpected place. There are two options: either use coalesce(sum(...), 0) directly in JPQL (as we did), or return Optional<BigDecimal> and handle empty. The first option is usually simpler, because it honestly says: “the sum of an empty set = 0”.

1

Task

Spring Data JPA, level 11, lesson 4

Locked

Product Card in mini-shop

Product Card in mini-shop

Open

1

Task

Spring Data JPA, level 11, lesson 4

Locked

Order Summary in mini-shop

Order Summary in mini-shop

Open

1

Survey/quiz

Repository queries, level 11, lesson 4

Unavailable

Repository queries

Derived queries, JPQL, and parameters

Open