18

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?

mp911de
  • 17,546
  • 2
  • 55
  • 95
voipp
  • 1,243
  • 3
  • 18
  • 31

1 Answers1

20

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.

mp911de
  • 17,546
  • 2
  • 55
  • 95
  • 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()) – voipp Feb 11 '20 at 06:29
  • 6
    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()`. – mp911de Feb 11 '20 at 07:54
  • 1
    this `execute(…).as(PersonalInfo.class)` is still valid? I could not find – RamPrakash Jul 16 '22 at 23:21
  • @RamPrakash take a read at this: https://docs.spring.io/spring-data/r2dbc/docs/3.0.x/reference/html/#upgrading.1.1-1.2. Basically the `execute` method has been replaced by `sql` method – fabiohbarbosa Dec 22 '22 at 19:27
  • @mp911de Could you please have a look at my question on r2dbc here: https://stackoverflow.com/q/76755286/2886891 ? Thanks a lot. – Honza Zidek Jul 26 '23 at 09:03