Spring Boot services · Chapter 19 of 41

Projections and aggregations

By the end of this chapter you can read only the columns a screen needs, into an interface or a record, and let the database count, add up and group. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Data
  • 5 tasks
  • Spring Boot
  • In the all labs plan

The first 2 chapters of Spring Boot services are free. This one comes with the all labs plan.

Why you need this

Head office wants a report every evening: orders and revenue per station, the best sellers, what each counter took in. The station screens want only the item and the amount of each order.

The first version loaded every Order and added them up in Java loops. Each order carries staff notes of up to 500 characters, so the report dragged megabytes of text it never showed, and Hibernate tracked every order it built. findAll and a loop cannot ask for less, and cannot let the database do the adding.

What you build: 5 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1 · interview question

    Interview: read only the columns the station screen needs

    Make findByStationOrderByAmountDesc(station) a derived query that returns Summary projections (item and amount) of every order at that station, largest amount first: SBC gives Cutting chai 100, Masala chai 80, Bun maska 50, Masala chai 40. A station with no orders (HWH) gives an empty list.

    No Order entity may be loaded (the check expects 0), and a summary must not be an Order. Keep the name and the return type. Be ready to explain why a projection beats loading the whole entity.

  2. Task 2

    OrderRepository.java: fill OrderLine records from a derived query

    Make findByStatusOrderById(status) a derived query that returns the item and quantity of every order with that status, in id order, as OrderLine records: PLACED gives OrderLine("Masala chai", 1), then OrderLine("Cutting chai", 2).

    No Order entity may be loaded (the check expects 0). Keep the name and the return type.

  3. Task 3

    OrderRepository.java: orders and revenue per station

    Make salesByStation() a JPQL query that returns one StationSales per station: the station, how many orders it has (every status counts) and the sum of their amounts, sorted by station code: CSMT (1, 50), NDLS (2, 180), SBC (4, 270).

    The database groups and adds, so no Order entity is loaded (the check expects 0).

  4. Task 4

    OrderRepository.java: one station's revenue, 0 when there is none

    Make revenue(station, status) a JPQL query that returns the sum of the amounts of that station's orders with that status: SBC SERVED gives 230 and NDLS SERVED gives 120.

    When no order matches, it returns 0, not an exception: CSMT SERVED (CSMT has only a PLACED order) and HWH SERVED (no orders at all) both give 0. No Order entity is loaded.

  5. Task 5

    OrderRepository.java: the best sellers with group by and having

    Make bestSellers(min) a JPQL query that returns, as ItemCups, every item whose total quantity over all its orders (every status) is at least min, most cups first and items with the same cups by name: bestSellers(3) gives Cutting chai 6, then Masala chai 6; bestSellers(1) gives Cutting chai, Masala chai, Samosa, Bun maska; bestSellers(10) gives an empty list.

    The getters of ItemCups read the columns by their aliases, so the columns must be called item and cups.

What you will learn

  • Interface projections: a getter per column you want, and nothing else is read (the interview question).
  • Record projections: the same, filled through a constructor.
  • Aggregates in JPQL: count, sum, group by and having, into a record or an interface.
  • The empty sum: why adding up nothing can crash a query.

More in Data

Build "Projections and aggregations" yourself

Tapri Cloud: the counters’ Java grows into a Spring Boot service with a database. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of Spring Boot services are free. This one comes with the all labs plan.