Joins

Select with linked entities

Linked entities can be fetched using a single select query, using the SelectQuery class. Select can also contains conditions on linked entities.

For example for a one to many relationship between Publisher and Book:

@Table
public class Book {
  @Id @GeneratedValue
  private Long id;

  @ForeignKey
  private Publisher publisher;
  
  [...]
}

@Table
public class Publisher {
  @Id @GeneratedValue
  private Long id;
  
  @ForeignTable(joinKey = "publisher")
  private Set<Book> books;
  
  [...]
}

A Publisher can be fetched together with its books in a single SELECT query:

  public Mono<Publisher> getPublisherWithBooks(long publisherId) {
    return SelectQuery.from(Publisher.class, "publisher")          // SELECT publisher FROM Publisher
      .where(Criteria.property("publisher", "id").is(publisherId)) // WHERE publisher.id = :publisherId
      .join("publisher", "books", "book")                          // JOIN publisher.books as book
      .execute(lcClient).next();
  }

Or find publishers by book name:

  public Flux<Publisher> findPublisherByBookName(String bookName) {
    return SelectQuery.from(Publisher.class, "publisher")     // SELECT publisher FROM Publisher
      .join("publisher", "books", "book")                     // JOIN publisher.books as book
      .where(Criteria.property("book", "name").is(bookName))  // WHERE book.name = :bookName
      .execute(lcClient);
  }

Note that you need a lcClient to execute the query, it can simply be injected using @Autowired:

@Autowired
private LcReactiveDataRelationalClient lcClient;

Another way is to put those methods directly into your Spring Data repositories. Instead of extending R2dbcRepository you can extend LcR2dbcRepository and the lcClient will be automatically available through the method getLcClient():

public interface PublisherRepository extends LcR2dbcRepository<Publisher, Long> {

  default Mono<Publisher> getPublisherWithBooks(long publisherId) {
    return SelectQuery.from(Publisher.class, "publisher")          // SELECT publisher FROM Publisher
      .where(Criteria.property("publisher", "id").is(publisherId)) // WHERE publisher.id = :publisherId
      .join("publisher", "books", "book")                          // JOIN publisher.books as book
      .execute(getLcClient()).next();
  }
  
  default Flux<Publisher> findPublisherByBookName(String bookName) {
    return SelectQuery.from(Publisher.class, "publisher")     // SELECT publisher FROM Publisher
      .join("publisher", "books", "book")                     // JOIN publisher.books as book
      .where(Criteria.property("book", "name").is(bookName))  // WHERE book.name = :bookName
      .execute(getLcClient());
  }

}

If you are using Kotlin, default methods are not supported by Spring Data repositories, so this way is not yet possible.