One query that turns into hundreds
A easy Java puzzle in the jpa and hibernate pitfalls family. One line below contains a real bug. Try to spot it first, then read why it fails and how to fix it.
Highlighted: line 9. Play this one as a puzzle without the answer.
What is wrong
findAll runs one query for the orders. Each getItems on a lazy collection runs another query, so with 500 orders the method sends 501 statements. This is the N+1 problem and it grows with your data.
The fix
How to avoid it next time
Fetch associations in the query, or aggregate in the database. Count the SQL statements in tests.
Want the background? Read Lazy loading and the N+1 query problem in the Java concepts library.
About jpa and hibernate pitfalls
The persistence context, lazy loading and entity equality rules create bugs that look fine in a unit test and break once there is a real session, a real transaction or real data volume.
In code review: Ask where the session is open when a lazy field is read, how many queries a loop causes, and whether entity equals and hashCode depend on the generated id.
More puzzles in this family
Frequently asked questions
What is the bug in "One query that turns into hundreds"?
findAll runs one query for the orders. Each getItems on a lazy collection runs another query, so with 500 orders the method sends 501 statements. This is the N+1 problem and it grows with your data. The faulty line is line 9: total += o.getItems().size();
How do I fix it?
Replace it with: @Query("select o from Order o join fetch o.items") List
How do I catch jpa and hibernate pitfalls in code review?
Ask where the session is open when a lazy field is read, how many queries a loop causes, and whether entity equals and hashCode depend on the generated id.
Is there a new puzzle every day?
Yes. The daily Spot the Bug puzzle picks one of these snippets for each UTC day, with the difficulty set by the weekday. Your streak is stored only in your browser.