Select more aggregate roots in a single query using Spring Data R2DBC

Question

I have an application in Spring Boot 3.4.x using Spring Data R2DBC. Let's say I have two aggregate roots, Book and Author. I understand that from the reactive point of view, it is not possible to work directly with Book#author but Book#authorId, so I fetch them as:

bookRepository.findById(bookId)
                .flatMap(book -> authorRepository.findById(book.getAuthorId())
                        .flatMap(author -> Mono.just(new BookWithAuthor(book, author))));

I have the following wrapper.

record BookWithAuthor(Book book, Author author){}

The issue is that it means two SQL queries. How do I manage it in a single one?

Of course, I may create a DTO object BookAuthorDTO with all properties of Book and Author and modify the repository interface.

@Query("""
       SELECT b.*, a.*
       FROM book b
       JOIN author a ON b.author_id = a.id
       WHERE b.id = :bookId
       """)
Mono<BookAuthorDto> findDtoById(long bookId);

It is acceptable in this trivial example. But when I have more complicated classes, I do not like that the structure is flat and all properties are mixed together. If I join more aggregate roots, the mapping is even more cumbersome.

I am looking for an elegant solution similar to:

@Query("""
       SELECT b.*, a.*
       FROM book b
       JOIN author a ON b.author_id = a.id
       WHERE b.id = :bookId
       """)
Mono<Tuple2<Book, Author>> findTupleById(long bookId);

Is it feasible?

Henrique Martins · Accepted Answer · 2025-03-25 14:11:07Z

In Spring Data R2DBC, you can fetch joined data in a single query instead of two separate ones. This is how I would do it:

Option 1: Use DatabaseClient with Column Aliases

public Mono<Tuple2<Book, Author>> findBookWithAuthorById(Long bookId) {
    String sql = """
            SELECT 
                b.id as b_id, b.title as b_title, b.author_id as b_author_id,
                a.id as a_id, a.name as a_name
            FROM book b
            JOIN author a ON b.author_id = a.id
            WHERE b.id = :bookId
            """;
            
    return databaseClient.sql(sql)
            .bind("bookId", bookId)
            .map((row, metadata) -> {
                Book book = new Book(
                    row.get("b_id", Long.class),
                    row.get("b_title", String.class),
                    row.get("b_author_id", Long.class)
                );
                
                Author author = new Author(
                    row.get("a_id", Long.class),
                    row.get("a_name", String.class)
                );
                
                return Tuples.of(book, author);
            })
            .one();
}

Option 2: Repository with Map Return Type

@Repository
public interface BookRepository extends ReactiveCrudRepository<Book, Long> {
    @Query("""
           SELECT 
               b.id as book_id, b.title as book_title, b.author_id as book_author_id,
               a.id as author_id, a.name as author_name
           FROM book b
           JOIN author a ON b.author_id = a.id
           WHERE b.id = :bookId
           """)
    Mono<Map<String, Object>> findRawDataById(long bookId);
}

public Mono<BookWithAuthor> getBookWithAuthor(long bookId) {
    return bookRepository.findRawDataById(bookId)
        .map(row -> new BookWithAuthor(
            new Book((Long)row.get("book_id"), (String)row.get("book_title"), 
                    (Long)row.get("book_author_id")),
            new Author((Long)row.get("author_id"), (String)row.get("author_name"))
        ));
}

Column aliases are needed to avoid naming conflicts.

Collectives™ on Stack Overflow

Select more aggregate roots in a single query using Spring Data R2DBC

1 Answer 1

Option 1: Use DatabaseClient with Column Aliases

Option 2: Repository with Map Return Type

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Option 1: Use DatabaseClient with Column Aliases

Option 2: Repository with Map Return Type

Comments

Your Answer

Sign up or log in

Post as a guest

Related