How to join tables in r2dbc?

Question

In java reactor, r2dbc. I have two tables A, B. I also have repositories for them defined. How can i get data made up of A join B?

I only come up with the following approach: call databaseClient.select from A and consequently in a loop call select from B.

But i want more efficient and reactive way. How to do it?

Ema

Answer 1

TL;DR: Using SQL.

Spring Data's DatabaseClient is an improved and reactive variant for R2DBC of what JdbcTemplate is for JDBC. It encapsulates various execution modes, resource management, and exception translation. Its fluent API select/insert/update/delete methods are suitable for simple and flat queries. Everything that goes beyond the provided API is subject to SQL usage.

That being said, the method you're looking for is DatabaseClient.execute(…):

DatabaseClient client = …;
client.execute("SELECT person.age, address.street FROM person INNER JOIN address ON person.address = address.id");

The exact same goes for repository @Query methods.

Calling the database during result processing is a good way to lock up the entire result processing as results are fetched stream-wise. Issuing a query while not all results are fetched yet can exhaust the prefetch buffer of 128 or 256 items and cause your result stream to stuck. Additionally, you're creating an N+1 problem.

Toffey

problem with your approach lies in impossibility of mapping result on an entity. So, lets take your example of person and address query, i have no idea how to map the result of select on arbitrary entity PersonalInfo(except of map()) – Ema

Spring Data R2DBC is not an object-relational mapper in the first place. You can map single rows to single objects. You can use convention-based mapping for all results via execute(…).as(PersonalInfo.class).fetch().all(). – Toffey

this execute(…).as(PersonalInfo.class) is still valid? I could not find – Hebdomadary

@Hebdomadary take a read at this: docs.spring.io/spring-data/r2dbc/docs/3.0.x/reference/html/…. Basically the execute method has been replaced by sql method – Nazarius

@Toffey Could you please have a look at my question on r2dbc here: https://mcmap.net/q/663974/-support-for-exception-handling-in-r2dbcrepository-or-how-to-decorate-each-method-call-in-r2dbcrepository-with-my-own-error-handler/2886891 ? Thanks a lot. – Acquiesce