skip to content

Show the ways to fetch and assemble related data in Spring Data R2DBC given there are no mapped relations. What are the trade-offs?

level: middleimportance: should knowfreq 55%

answer

  1. flatMap/zip vs JOIN @Query vs DatabaseClient
  2. N+1 → batch IN + groupingBy
  3. one-to-many join fans out, de-dup manually
  4. no cascade save — insert children yourself
  5. @MappedCollection is JDBC not R2DBC

basics

~20 s

Either run separate reactive queries and combine them (flatMap/zip), or write a JOIN in a @Query and map the flat rows into a DTO with a custom converter, or use DatabaseClient for raw control. There is no automatic association mapping.

solid answer

~40 s

Because R2DBC maps one entity per table with no relations, you assemble graphs yourself. Three common approaches: (1) **Separate queries + compose in memory** — load the root, load children by foreign key, combine with `flatMap`/`zipWith`/`collectList`; simplest and composable, but multiple round-trips and easy N+1 if done per-row. (2) **Custom `@Query` with SQL JOIN** returning flat columns, mapped to a DTO via a `BiFunction`/custom `Converter` or `DatabaseClient.map(...)`; one round-trip but you manually de-duplicate parent rows when the join fans out. (3) **`R2dbcEntityTemplate`/`DatabaseClient`** for full programmatic control of SQL and row mapping. For many parents, batch children with `findBy...In(ids)` and group in memory to collapse N+1 into two queries. Choose separate queries for simple aggregates, JOINs when you need a single round-trip or filtering across tables.

code

java · 12 lines
java
// Two-query pattern that collapses N+1 into 2 round-trips
public Flux<PostView> allPostsWithComments() {
    return postRepo.findAll().collectList().flatMapMany(posts -> {
        List<Long> ids = posts.stream().map(Post::getId).toList();
        return commentRepo.findByPostIdIn(ids).collectList().flatMapMany(comments -> {
            Map<Long, List<Comment>> byPost = comments.stream()
                .collect(Collectors.groupingBy(Comment::getPostId));
            return Flux.fromIterable(posts)
                .map(p -> new PostView(p, byPost.getOrDefault(p.getId(), List.of())));
        });
    });
}

go deeper

for a junior

Know you must run extra queries and combine them; there is no automatic relation mapping.

for a middle

Show flatMap/zip composition and a JOIN @Query, and recognize the N+1 batching fix.

for a senior

Reason about de-duplicating one-to-many join fan-out, transactional multi-insert with generated ids, and when to drop to DatabaseClient.

for a principal

Set team conventions: aggregate boundaries, projection strategy, batching rules, and where raw SQL is acceptable vs. derived queries.

Spring Data R2DBC has **no relationship mapping**, so building an object graph is an application-level task. The toolbox: **1) Separate queries, combined reactively.** Load the aggregate root, then load its children by foreign key and merge: ```java postRepo.findById(id) .flatMap(post -> commentRepo.findByPostId(post.getId()) .collectList() .map(comments -> new PostView(post, comments))); ``` For two independent lookups use `Mono.zip`. **Pros:** simple, each query reusable, composable, clear backpressure. **Cons:** N round-trips; if you do it per parent in a `Flux`, you get **N+1**. **Batching to kill N+1.** Collect all parent ids first, then one `IN` query for children, group in memory: ```java postRepo.findAll().collectList().flatMap(posts -> { List<Long> ids = posts.stream().map(Post::getId).toList(); return commentRepo.findByPostIdIn(ids).collectList().map(comments -> { Map<Long, List<Comment>> byPost = comments.stream() .collect(Collectors.groupingBy(Comment::getPostId)); return posts.stream() .map(p -> new PostView(p, byPost.getOrDefault(p.getId(), List.of()))) .toList(); }); }); ``` Two queries total. **2) SQL JOIN via `@Query`.** Write the join yourself and map flat rows: ```java public interface PostRepository extends ReactiveCrudRepository<Post, Long> { @Query("SELECT p.id, p.title, c.body FROM post p LEFT JOIN comment c ON c.post_id = p.id WHERE p.id = :id") Flux<PostCommentRow> findWithComments(Long id); } ``` Then fold the flat `PostCommentRow` stream into one `PostView`. **Pro:** single round-trip, DB-side filtering. **Con:** with a one-to-many join the parent columns repeat per child row — you must de-duplicate/aggregate manually; derived queries can't do this for you. **3) `DatabaseClient` / `R2dbcEntityTemplate`.** Lowest-level: build SQL, bind params, and provide a row mapper: ```java databaseClient.sql("SELECT ...") .bind("id", id) .map((row, meta) -> new PostCommentRow(row.get("id", Long.class), ...)) .all(); ``` Use when you need full control (dynamic SQL, projections, complex mapping). **Key gotchas:** - **No cascade save.** Saving a parent does not save children — you insert each explicitly, usually inside a transaction. - **Ordering matters for FKs.** Insert the parent, get its generated id, then insert children referencing it (chain with `flatMap`). - **`@MappedCollection`** exists in Spring Data **JDBC** (aggregate model) but **not** in R2DBC — do not confuse the two. - **Projections** (interface- or DTO-based) help map join results but still won't materialize nested collections automatically. **When to use which:** separate-queries for simple, aggregate-shaped reads and writes; JOIN when a single round-trip or cross-table filtering matters; `DatabaseClient` for anything the derived/`@Query` layer can't express.

  • When you write a LEFT JOIN one-to-many query, why can't a derived/interface projection give you a Post with a nested List<Comment> automatically?
    Because the join returns a flat, denormalized result set where parent columns repeat once per child row. R2DBC has no aggregate/collection materializer, so it cannot group those rows back into one parent with a child collection. You must fold the flat Flux yourself (e.g. reduce/groupBy) into the nested shape.
  • How do you insert a parent and its children so the child rows carry the parent's generated id?
    Save the parent first; R2DBC populates the @Id on the returned Mono. Then in flatMap set the foreign key on each child and saveAll them. Wrap the whole chain in a reactive transaction so a child failure rolls back the parent.

saying these in an interview costs you the question

  • Expecting @Query with a JOIN to auto-populate a nested collection field
  • Doing a per-parent child query inside a Flux (silent N+1)
  • Assuming saving a parent cascades to children
  • Confusing Spring Data JDBC's @MappedCollection with R2DBC

context

resolving links…