Fetching data is not just about reducing the number of SQL queries.
Sometimes, one query is worse than five.
Today, while following a Virtual JUG session with Vlad Mihalcea, we discussed a classic trap:
At first, the temptation is obvious:
“Let’s fetch everything with one big JOIN.”
But this can quickly become a Cartesian product.
And suddenly, your “optimized query” becomes a data duplication machine. 🧨
🔸 TL;DR
▪️ JOIN FETCH is not bad.
▪️ Fetching several to-many associations at once is dangerous.
▪️ DISTINCT may hide duplicate root entities, but not the cost of duplicated SQL rows.
▪️ Splitting the fetch into several small queries is often cleaner.
▪️ jOOQ MULTISET offers another elegant option: nested results without the flat Cartesian explosion.
🔸 THE JDBC TEMPLATE TRAP
With plain JDBC or JdbcTemplate, the Cartesian product is visible.
The SQL result is flat.
But your object graph is nested.
So your Java code becomes responsible for rebuilding the tree and removing duplicates.
That is possible.
But it is not always pleasant. 😅
🔸 THE SPRING DATA APPROACH: MULTIPLE SMALL QUERIES
With Spring Data / JPA, a safer approach is often:
Example:
Then for nested collections:
And the service becomes explicit:
This may look less “clever”.
But it is often more predictable.
The Hibernate Persistence Context will reconnect everything to the same managed entities inside the transaction.
🔸 THE JOOQ MULTISET OPTION
jOOQ offers another very interesting model with MULTISET.
Instead of returning duplicated flat rows, you can ask for nested collections directly.
Then with jOOQ:
This is powerful because the result shape matches the DTO shape:
No giant object hydration trick.
No manual deduplication loop.
No pretending that a flat relational row is already an object tree.
🔸 THE REAL LESSON
The goal is not:
The goal is:
Sometimes that means:
▪️ One JOIN FETCH for a simple detail page.
▪️ Multiple small JPA queries for a controlled entity graph.
▪️ DTO projections for read-only screens.
▪️ jOOQ MULTISET when you want nested DTOs from SQL directly.
🔸 TAKEAWAYS
▪️ Do not blindly optimize for fewer queries.
▪️ A single query can create thousands of duplicated rows.
▪️ JOIN FETCH is good for to-one associations.
▪️ Be careful with one to-many.
▪️ Split multiple to-many fetches.
▪️ For read APIs, DTOs are often better than entities.
▪️ jOOQ MULTISET is a serious option for nested read models.
The best fetching strategy is not the most magical one.
It is the one whose cost you can explain. 🎯
#Java #SpringBoot #Hibernate #JPA #JOOQ #SQL #Database #BackendDevelopment #Performance #SoftwareEngineering #JavaDeveloper
If you want to play with Vlad code: https://github.com/vladmihalcea/high-performance-spring-persistence-demo
Go further with Java certification:
Java👇
Spring👇
SpringBook👇
JavaBook👇