Spring Boot services · Chapter 23 of 41
Fetching and the N+1 problem
By the end of this chapter you can count the SQL a piece of code sends, find an N+1, and load exactly what a screen needs in one query. 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
Meera's orders dashboard was instant with ten test orders. With the first full day from Bengaluru station it takes seconds, and the database is busy the whole time. The code looks innocent: load the orders, show each one's customer and lines. But every customer and every list of lines is its own SELECT, so the page sends one query for the orders and then N more. Nothing in the Java tells you, and with ten rows you never notice.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
QueryCounter.java: count the SQL statements a piece of code sends
Write QueryCounter.count(work): reset Hibernate's statistics, run the work, and return how many SQL statements it sent, counting every prepared statement (the extra selects that load associations included), not only the queries.
Statements sent before count is called must not be counted: doing nothing gives 0, one findById gives 1, and three repository counts give 3.
- Task 2
Order.java: load an order's customer only when it is used
Make Order.customer load only when someone uses it: loading all 4 orders (of 3 customers) with findAll then sends exactly 1 SQL statement, and an order's customer is not initialized until it is read.
Keep the many-to-one and its join column.
- Task 3
OrderRepository.java: one query for the summaries, with JOIN FETCH
Add a repository query that loads every order with its customer and its lines in one SELECT, ordered by id, and make OrderService.summaries use it instead of findAll.
summaries returns one OrderSummary per order, in id order (id, station, customer name, the total quantity of its lines), each order once, and sends exactly 1 SQL statement for 4 orders. An order with no lines yet must still be in the list.
- Task 4
OrderRepository.java: load a station's orders with their lines
Make findByStationOrderById load each order's lines in the same SELECT, so OrderService.ordersAt(station) sends exactly 1 SQL statement. Keep the derived query's name and the service as they are.
ordersAt("SBC") gives the two SBC orders in id order with their bill totals (quantity times price, summed): 130 and 75. ordersAt("NDLS") gives one order with 60.
- Task 5 · interview question
Interview: fix a LazyInitializationException without EAGER
Make OrderService.view(id) return the order's station and its lines as "quantity x item" strings without a LazyInitializationException: the NDLS order gives station NDLS and "1 x Masala chai" and "2 x Parle-G" (any order).
Order.lines must stay LAZY: switching it to EAGER fails the task. Be ready to explain why the exception happens and why EAGER is not the fix.
What you will learn
- Counting statements with Hibernate's statistics.
- LAZY and EAGER: when an association is loaded.
- JOIN FETCH and @EntityGraph: everything a screen needs in one query.
- LazyInitializationException: why it happens (the interview question).
More in Data
Build "Fetching and the N+1 problem" 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.