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:
Post -> Comments -> Votes -> Tags
At first, the temptation is obvious:
“Let’s fetch everything with one big JOIN.”
But this can quickly become a Cartesian product.
posts × comments × votes × tags
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.
String sql = """ SELECT p.id AS post_id, p.title, c.id AS comment_id, c.text, v.id AS vote_id, t.id AS tag_id, t.name AS tag_name FROM post p LEFT JOIN comment c ON c.post_id = p.id LEFT JOIN vote v ON v.comment_id = c.id LEFT JOIN comment_tag ct ON ct.comment_id = c.id LEFT JOIN tag t ON t.id = ct.tag_id WHERE p.id = ? """; List<PostDto> posts = jdbcTemplate.query(sql, rs -> { // Now you must manually rebuild: // Post -> Comments -> Votes -> Tags // while deduplicating everything. });
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:
1. Fetch the root entities 2. Fetch one collection 3. Fetch another collection 4. Fetch nested collections separately
Example:
public interface PostRepository extends JpaRepository<Post, Long> { @Query(""" select distinct p from Post p left join fetch p.comments where p.id in :ids """) List<Post> fetchPostsWithComments(List<Long> ids); @Query(""" select distinct p from Post p left join fetch p.tags where p in :posts """) List<Post> fetchPostsWithTags(List<Post> posts); }
Then for nested collections:
public interface CommentRepository extends JpaRepository<Comment, Long> { @Query(""" select distinct c from Comment c left join fetch c.votes where c.post in :posts """) List<Comment> fetchCommentsWithVotes(List<Post> posts); @Query(""" select distinct c from Comment c left join fetch c.tags where c.post in :posts """) List<Comment> fetchCommentsWithTags(List<Post> posts); }
And the service becomes explicit:
@Transactional(readOnly = true) public List<Post> findPostGraph(List<Long> postIds) { List<Post> posts = postRepository.fetchPostsWithComments(postIds); postRepository.fetchPostsWithTags(posts); commentRepository.fetchCommentsWithVotes(posts); commentRepository.fetchCommentsWithTags(posts); return posts; }
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.
record VoteDto(Long id, String value) {} record TagDto(Long id, String name) {} record CommentDto( Long id, String text, List<VoteDto> votes, List<TagDto> tags ) {} record PostDto( Long id, String title, List<CommentDto> comments ) {}
Then with jOOQ:
List<PostDto> result =
ctx.select(
POST.ID,
POST.TITLE,
multiset(
select(
COMMENT.ID,
COMMENT.TEXT,
multiset(
select(VOTE.ID, VOTE.VALUE)
.from(VOTE)
.where(VOTE.COMMENT_ID.eq(COMMENT.ID))
).convertFrom(r -> r.map(mapping(VoteDto::new))),
multiset(
select(TAG.ID, TAG.NAME)
.from(COMMENT_TAG)
.join(TAG).on(TAG.ID.eq(COMMENT_TAG.TAG_ID))
.where(COMMENT_TAG.COMMENT_ID.eq(COMMENT.ID))
).convertFrom(r -> r.map(mapping(TagDto::new)))
)
.from(COMMENT)
.where(COMMENT.POST_ID.eq(POST.ID))
).convertFrom(r -> r.map(mapping(CommentDto::new)))
)
.from(POST)
.where(POST.ID.in(postIds))
.fetch(mapping(PostDto::new));This is powerful because the result shape matches the DTO shape:
PostDto
└── comments
├── votes
└── tagsNo 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:
“Use only one SQL query.”
The goal is:
“Fetch the right shape with predictable cost.”
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👇