WL
Software Engineer
Wassim Lagnaoui

Tutorial 03: Repositories, Associations & DTOs

Custom JPQL queries, the DTO pattern, and mappers that keep your layers clean.

← Back to Tutorials

With entities and relationships in place, the next step is controlling how data flows in and out of the database. This tutorial covers three things that work together: repositories (how you query), DTOs (what shape data takes at the API boundary), and mappers (how you convert between the two).

1. Repositories: more than findAll()

Spring Data JPA generates basic CRUD for free. You only need to add methods when the defaults don't fit. In this project most repositories stay small, but a few need custom queries.

MenuItemRepository: filtering with JPQL

public interface MenuItemRepository extends JpaRepository<MenuItem, Long> {

    @Query("SELECT m FROM MenuItem m WHERE m.category = :category")
    List<MenuItem> findByCategory(@Param("category") String category);

    @Query("SELECT m FROM MenuItem m WHERE m.available = true")
    List<MenuItem> findAvailableMenuItems();

    @Query("SELECT m FROM MenuItem m WHERE m.name LIKE %:name%")
    List<MenuItem> findByNameContaining(@Param("name") String name);
}
  • @Query: replaces the auto-generated method name. Useful when the method name would become too long or ambiguous.
  • :category / @Param: named parameters bind cleanly and are immune to position errors.
  • LIKE %:name%: substring search: equivalent to SQL LIKE '%value%'.

OrderRepository: querying across relationships

public interface OrderRepository extends JpaRepository<Order, Long> {

    List<Order> findByTableSession(TableSession tableSession);

    @Query("select o from Order o where o.tableSession.id = :sessionId and o.status <> 'SERVED'")
    List<Order> findUnservedOrderByTableSession(@Param("sessionId") Long sessionId);

    @Query("select o from Order o where o.status <> 'SERVED'")
    List<Order> findAllUnservedOrder();

    @Query("select o.items from Order o where o.tableSession.id = :sessionId")
    List<OrderItem> findItemsByTableSession(@Param("sessionId") Long sessionId);
}
  • findByTableSession: Spring Data derives this query automatically from the method name: no @Query needed.
  • o.status <> 'SERVED': JPQL inequality operator. The string must match the value stored in the DB (the enum name).
  • select o.items: selects the collection field directly, returning a flat list of OrderItem without loading the parent Order.

OrderItemRepository: JPQL constructor expression (DTO projection)

public interface OrderItemRepository extends JpaRepository<OrderItem, Long> {

    @Query("select o from OrderItem o where o.served = false order by o.order.orderDate")
    List<OrderItem> findUnservedOrderItem();

    @Query("SELECT new com.wassimlagnaoui.RestaurantOrder.DTO.MostOrderedItemDTO(" +
           "oi.menuItem.name, SUM(oi.quantity), COUNT(DISTINCT oi.order.id)) " +
           "FROM OrderItem oi WHERE oi.served = true " +
           "GROUP BY oi.menuItem.name ORDER BY SUM(oi.quantity) DESC")
    List<MostOrderedItemDTO> findMostOrderedItems();
}
  • Constructor expression: new DTO(field1, field2) tells Hibernate to build a DTO directly in the query: no full entity load, no manual mapping loop.
  • SUM / COUNT DISTINCT: standard JPQL aggregate functions. DISTINCT oi.order.id counts unique orders, not order item rows.
  • GROUP BY: required whenever you use aggregates. Groups by item name so each name gets one summary row.
  • The DTO's constructor must match the selected fields in exact order and type.

TableSessionRepository: native SQL for complex aggregations

@Query(nativeQuery = true, value =
    "SELECT DATE(session_totals.session_start) AS session_date, " +
    "AVG(session_totals.session_total) AS avg_total " +
    "FROM ( " +
    "  SELECT ts.id, ts.session_start, COALESCE(SUM(o.total), 0) AS session_total " +
    "  FROM table_session ts " +
    "  LEFT JOIN orders o ON ts.id = o.table_session_id " +
    "  GROUP BY ts.id, ts.session_start " +
    ") AS session_totals " +
    "GROUP BY session_date ORDER BY session_date;")
List<Object[]> findAverageTotalByTableSessionGroupedByDate();
  • nativeQuery = true: switches from JPQL to raw SQL. Use it when the query involves subqueries, window functions, or DB-specific functions that JPQL can't express.
  • COALESCE: returns 0 if a session has no orders, preventing null totals in the average.
  • Returns Object[]: native queries without a DTO projection return raw rows. The service maps them manually (record[0], record[1]).

2. The DTO pattern: separate API shape from entity shape

Exposing JPA entities directly from REST endpoints is a common mistake. Entities carry Hibernate proxies, lazy collections, and internal fields that don't belong in an API response. DTOs solve this by defining exactly what goes in and what comes out.

Request DTO: what the client sends

@Data
@AllArgsConstructor
@NoArgsConstructor
public class MenuItemRequest {
    @NotNull private String name;
    private String description;
    @NotNull private Double price;
    @NotNull private String imageUrl;
    @NotNull private String category;
    @NotNull private boolean available;
}

Response DTO: what the client receives

@Data
@AllArgsConstructor
@NoArgsConstructor
public class MenuItemResponse {
    private Long id;
    private String name;
    private Double price;
    private String imageUrl;
    private String category;
    private boolean available;
    // description is intentionally excluded from the response
}
  • Validation on the request: @NotNull and friends belong on the request DTO, not the entity. The entity layer should never trust input.
  • Selective fields on the response: the response includes the generated id (not present in the request) and omits description: a deliberate design choice to keep the list response compact.
  • Lombok's @Data generates getters, setters, equals, hashCode, and toString. @AllArgsConstructor and @NoArgsConstructor cover both Jackson deserialization and test construction.

3. Mappers: converting between layers

A mapper is a plain Spring component (or static utility) that knows how to convert an entity to a DTO and back. Keeping this logic in one place means changing a field name only requires one edit.

@Component
public class MenuItemMapper {

    public static MenuItemResponse fromMenuItem(MenuItem item) {
        MenuItemResponse resp = new MenuItemResponse();
        resp.setId(item.getId());
        resp.setName(item.getName());
        resp.setPrice(item.getPrice());
        resp.setCategory(item.getCategory());
        resp.setAvailable(item.isAvailable());
        resp.setImageUrl(item.getImageUrl());
        return resp;
    }

    public static MenuItem toMenuItem(MenuItemRequest req) {
        MenuItem item = new MenuItem();
        item.setName(req.getName());
        item.setPrice(req.getPrice());
        item.setCategory(req.getCategory());
        item.setAvailable(req.isAvailable());
        item.setDescription(req.getDescription());
        item.setImageUrl(req.getImageUrl());
        return item;
    }
}
  • fromMenuItem: entity to response: notice id is included (it exists on the entity after save), while description is excluded.
  • toMenuItem: request to entity: notice id is NOT set (the DB generates it), but description IS included since the request provides it.
  • Static methods work here because mappers have no state. The @Component annotation lets Spring inject the class when needed by services, but the methods are called statically: MenuItemMapper.fromMenuItem(item).

OrderItemMapper: mapper with relationships

@Component
public class OrderItemMapper {

    public static OrderItemResponse fromOrderItem(OrderItem orderItem) {
        OrderItemResponse resp = new OrderItemResponse();
        double totalPrice = orderItem.getQuantity() * orderItem.getMenuItem().getPrice();
        resp.setMenuItemId(orderItem.getMenuItem().getId());
        resp.setUnitPrice(orderItem.getMenuItem().getPrice());
        resp.setTotalPrice(totalPrice);
        return resp;
    }

    public static OrderItem toOrderItem(OrderItemRequest req, MenuItem menuItem, Order order) {
        OrderItem item = new OrderItem();
        item.setMenuItem(menuItem);
        item.setOrder(order);
        item.setQuantity(req.getQuantity());
        return item;
    }
}
  • The mapper computes totalPrice at mapping time: the entity stores unit price on the MenuItem, not the line total.
  • toOrderItem receives the already-loaded MenuItem and Order as parameters. The service is responsible for loading them; the mapper only assembles the object.

Key Takeaways

  • Use @Query when method-name derivation becomes unreadable or when you need JPQL features like ORDER BY or aggregates.
  • Switch to nativeQuery = true only for queries that JPQL cannot express: subqueries, window functions, or DB-specific functions.
  • Constructor expressions (new DTO(...) in JPQL) avoid loading full entities when you only need aggregated data.
  • Always separate request DTOs (with validation) from response DTOs (with selective fields). Never expose entities directly.
  • Mappers belong in their own class. One method per direction: entity-to-DTO and DTO-to-entity.
  • The mapper computes derived fields (like totalPrice) rather than storing them on the entity: keep the entity as a faithful representation of the database row.