Master Reactive Database Access with Spring Data R2DBC: 1‑to‑1 & 1‑to‑Many Examples

Introduction

Spring Data R2DBC is an open‑source reactive relational database connectivity framework that simplifies interaction with SQL databases in Spring applications. R2DBC provides a non‑blocking API allowing asynchronous database operations for better performance and scalability.

Key Features

Reactive programming model for asynchronous DB access.

Simplified interaction using familiar Spring abstractions and repositories.

Support for multiple databases such as PostgreSQL, MySQL, MariaDB, Oracle, etc.

Easy integration with other Spring projects and extensibility.

Environment Setup

Dependencies:

<code>&lt;dependency&gt;
  &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;
  &lt;artifactId&gt;spring-boot-starter-data-r2dbc&lt;/artifactId&gt;
&lt;/dependency&gt;
&lt;dependency&gt;
  &lt;groupId&gt;com.github.jasync-sql&lt;/groupId&gt;
  &lt;artifactId&gt;jasync-r2dbc-mysql&lt;/artifactId&gt;
  &lt;version&gt;2.1.24&lt;/version&gt;
&lt;/dependency&gt;</code>

Configuration (application.yml):

<code>spring:
  r2dbc:
    url: r2dbc:mysql://localhost:3306/r2dbc?serverZoneId=GMT%2B8&amp;sslMode=DISABLED
    username: root
    password: xxoo
    pool:
      initialSize: 100
      maxSize: 100
      max-acquire-time: 30s
      max-idle-time: 30m</code>

Database Schema

Three tables are created: t_user , t_order , and t_order_item .

<code>CREATE TABLE `t_user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE `t_order` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `sno` varchar(255) DEFAULT NULL,
  `amount` decimal(9,4) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `uid` bigint DEFAULT NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE `t_order_item` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `product_name` varchar(255) DEFAULT NULL,
  `price` double DEFAULT NULL,
  `quantity` tinyint unsigned DEFAULT NULL,
  `oid` bigint DEFAULT NULL,
  PRIMARY KEY (`id`)
);</code>

Entity Classes

<code>@Table("t_user")
public class User {
  @Id
  private Long id;
  private String name;
  private Integer age;
}
@Table("t_order")
public class Order {
  @Id
  private Long id;
  private String sno;
  private BigDecimal amount;
  private Instant createTime;
  private Long uid;
  @Transient
  private List&lt;OrderItem&gt; items = new ArrayList&lt;&gt;();
  @Transient
  private User user = new User();
}
@Table("t_order_item")
public class OrderItem {
  @Id
  private Long id;
  private String productName;
  private Double price;
  private Integer quantity;
  private Long oid;
}</code>

1‑to‑Many Save Example

<code>@Transactional
public Mono&lt;Order&gt; save(Order order) {
  return this.orderRepository.save(order).map(o -> {
    o.getItems().forEach(item -> item.setOid(order.getId()));
    return o;
  })
  .thenMany(this.orderItemRepository.saveAll(order.getItems()))
  .then(Mono.just(order));
}</code>

1‑to‑Many Query Example

<code>public Flux&lt;Order&gt; queryOrder() {
  return this.databaseClient
    .sql("select x.*, y.id as iid, y.product_name as pname, y.price, y.quantity " +
         "from t_order x left join t_order_item y on x.id = y.oid order by id")
    .fetch()
    .all()
    .bufferUntilChanged(ret -> ret.get("id").toString())
    .map(orderMap -> {
      Order order = new Order();
      Map<String, Object> o = orderMap.get(0);
      order.setId((Long) o.get("id"));
      order.setSno(o.get("sno").toString());
      order.setCreateTime(((LocalDateTime) o.get("create_time")).toInstant(ZoneOffset.UTC));
      order.setAmount((BigDecimal) o.get("amount"));
      orderMap.forEach(item -> {
        OrderItem oi = new OrderItem();
        oi.setId((Long) item.get("iid"));
        oi.setPrice((Double) item.get("price"));
        oi.setProductName(item.get("pname").toString());
        oi.setQuantity(Integer.valueOf(item.get("quantity").toString()));
        order.addItem(oi);
      });
      return order;
    });
}</code>

1‑to‑1 Query with Custom Converter

<code>public interface OrderRepository extends ReactiveCrudRepository&lt;Order, Long&gt;, ReactiveSortingRepository&lt;Order, Long&gt; {
  @Query("select x.*, y.id as uid, y.name, y.age from t_order x " +
         "left join t_user y on x.uid = y.id where x.id = ?")
  Mono&lt;Order&gt; queryOrderAndUser(Long id);
}</code>

Custom converter:

<code>public class OrderConverter implements Converter&lt;Row, Order&gt; {
  @Override
  public Order convert(Row source) {
    Order order = new Order();
    order.setId((Long) source.get("id"));
    order.setSno(source.get("sno").toString());
    order.setCreateTime(((LocalDateTime) source.get("create_time")).toInstant(ZoneOffset.UTC));
    order.setAmount((BigDecimal) source.get("amount"));
    order.setUid(source.get("uid", Long.class));
    order.getUser().id(source.get("uid", Long.class));
    order.getUser().name(source.get("name", String.class));
    order.getUser().age(source.get("age", Integer.class));
    return order;
  }
}</code>

Testing

Unit tests demonstrate saving orders, querying with joins, and using the custom converter. Console output shows inserted rows and correctly assembled Order objects with associated OrderItem and User data.