R2DBC Reactive Database Example in Spring
Published
Updated
This article will guide you on how to configure and use a relational database reactively using Spring Framework 5.3 and the R2DBC library. For your convenience, I have also included details on how to configure R2DBC for MySQL and PostgreSQL.
Introduction
Reactive design patterns have rapidly increased in popularity due to the increased scalability, elasticity, and responsiveness they provide. Until recently, relational database interaction has largely remained unchanged and reliant on synchronous queries. With the establishment of the R2DBC specification, database drivers can be developed in a unified standard (as seen with JDBC) and built on a non-blocking network layer, providing all of the expected benefits of a reactive data source.
With the release of Spring Framework 5.3, R2DBC core has been migrated out of the Spring Data package and up a level into the r2dbc sub-package. If you try to use any components in the *org.springframework.data.r2dbc.**, you will notice the deprecations.
With this 5.3 milestone comes a large number of features and improved integration with other parts of the Spring ecosystem. Most notably, ConnectionFactory implementations, schema initializations, and integration with transaction management constructs such as the @Transactional annotation.
Driver Support
As of December 2020, the following databases have implemented R2DBC drivers:
- H2
- Microsoft SQL Server
- PostgreSQL
- MySQL Driver (r2dbc-mysql & jasync-sql)
- Google Cloud Spanner
- MariaDB
- SAP Hana
- Oracle (not yet available, development in progress)
- DB2 (not yet available, development in progress)
Maven Configuration
These demos rely on Spring Boot 2.4 and the the R2DBC starter.
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.codetinkering.example</groupId>
<artifactId>r2dbc-example</artifactId>
<version>1.0-SNAPSHOT</version>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.4.2</version>
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-r2dbc</artifactId>
</dependency>
<!-- Comment the following if using a db other than h2 -->
<dependency>
<groupId>io.r2dbc</groupId>
<artifactId>r2dbc-h2</artifactId>
<scope>runtime</scope>
</dependency>
<!-- Uncomment the following to use PostgreSQL: -->
<!--
<dependency>
<groupId>io.r2dbc</groupId>
<artifactId>r2dbc-postgresql</artifactId>
<version>0.8.6.RELEASE</version>
</dependency>
-->
<!-- Uncomment the following to use MySQL: -->
<!--
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>dev.miku</groupId>
<artifactId>r2dbc-mysql</artifactId>
<version>0.8.2.RELEASE</version>
</dependency>
-->
</dependencies>
<properties>
<java.version>15</java.version>
</properties>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
Creating a ReactiveCrudRepository
import org.springframework.data.r2dbc.repository.Query;
import org.springframework.data.repository.reactive.ReactiveCrudRepository;
import reactor.core.publisher.Flux;
public interface VehicleRepository extends ReactiveCrudRepository<Vehicle, Integer> {
@Query("SELECT * FROM vehicle WHERE make = :make")
Flux<Vehicle> findByMake(String make);
@Query("SELECT * FROM vehicle WHERE model = :model")
Flux<Vehicle> findByModel(String model);
}
Reactive Repository Examples
package com.codetinkering.example;
import io.r2dbc.spi.ConnectionFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.core.io.ClassPathResource;
import org.springframework.r2dbc.connection.init.ConnectionFactoryInitializer;
import org.springframework.r2dbc.connection.init.ResourceDatabasePopulator;
import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;
import java.time.Duration;
import java.util.Arrays;
import java.util.List;
@SpringBootApplication
public class R2dbcExampleApplication {
private static final Logger logger = LoggerFactory.getLogger(R2dbcExampleApplication.class);
@Bean()
ConnectionFactoryInitializer initializer(ConnectionFactory connectionFactory) {
ConnectionFactoryInitializer initializer = new ConnectionFactoryInitializer();
initializer.setConnectionFactory(connectionFactory);
// This will create our database table and schema
initializer.setDatabasePopulator(new ResourceDatabasePopulator(new ClassPathResource("dbsetup.sql")));
// This will drop our table after we are done so we can have a fresh start next run
initializer.setDatabaseCleaner(new ResourceDatabasePopulator(new ClassPathResource("cleanup.sql")));
return initializer;
}
@Bean
public CommandLineRunner reactiveDatabaseExample(VehicleRepository vehicleRepository) {
return args -> {
List<Vehicle> vehicles = Arrays.asList(
new Vehicle("Ford", "Mustang", "Red"),
new Vehicle("Ford", "Bronco", "Orange"),
new Vehicle("Chevy", "Silverado", "Blue"),
new Vehicle("Chevy", "Tahoe", "Black"),
new Vehicle("Toyota", "Supra", "Green")
);
// Create some vehicles and insert them into the database, blocking for up to 5 seconds
vehicleRepository.saveAll(vehicles).blockLast(Duration.ofSeconds(5));
// Find one or more vehicles from the repository using a query
vehicleRepository.findByMake("Chevy").doOnNext(vehicle -> {
logger.info(vehicle.toString());
}).blockLast(Duration.ofSeconds(5));
// Find a single vehicle by an ID, returns a Mono
vehicleRepository.findById(1).doOnNext(vehicle -> {
logger.info(vehicle.toString());
}).block(Duration.ofSeconds(5));
// Print all vehicles from the repository
vehicleRepository.findAll().doOnNext(vehicle -> {
logger.info(vehicle.toString());
}).blockLast(Duration.ofSeconds(5));
// Print all vehicles that match the provided make "Ford"
// block the thread until the mono is completed or the request times out (5 seconds)
Mono<List<Vehicle>> vehicleListMono = vehicleRepository.findByMake("Ford").collectList();
List<Vehicle> vehicleList = vehicleListMono.block(Duration.ofSeconds(5));
for (Vehicle v : vehicleList) {
logger.info(v.toString());
}
};
}
public static void main(String[] args) {
SpringApplication.run(R2dbcExampleApplication.class, args);
}
}
Running the Example
Start your project via Maven using the following:
mvn spring-boot:run
Starting R2dbcExampleApplication using Java 15.0.1 on ... with ...
No active profile set, falling back to default profiles: default
Bootstrapping Spring Data R2DBC repositories in DEFAULT mode.
Finished Spring Data repository scanning in 32 ms. Found 1 R2DBC repository interfaces.
Started R2dbcExampleApplication in 0.888 seconds (JVM running for 1.266)
...
R2DBC MySQL Connection
Here’s how to configure your MySQL Database as a datasource for Spring R2DBC (instead of H2):
Step 1: Remove H2 Dependencies
Remove or comment out the h2 dependency:
<dependency>
<groupId>io.r2dbc</groupId>
<artifactId>r2dbc-h2</artifactId>
<scope>runtime</scope>
</dependency>
Step 2: Add MySQL R2DBC Maven dependencies
Include the Spring MySQL database connector and the Miku r2dbc MySQL connector within your pom.xml file:
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>dev.miku</groupId>
<artifactId>r2dbc-mysql</artifactId>
<version>0.8.2.RELEASE</version>
</dependency>
Step 3: Configure your application.properties yml file
The following is configured for a local MySQL instance which we will start in the next step via Docker Compose.
# MySQL
spring.r2dbc.url=r2dbc:mysql://localhost:3306/db
spring.r2dbc.username=user
spring.r2dbc.password=pass
spring.r2dbc.initialization-mode=always
Step 4:
Start the MySQL docker compose image by navigating to the project directory and running the following in your terminal:
cd docker-compose-files/mysql
docker-compose up
R2DBC PostgreSQL Connection
Here’s how to configure your PostgreSQL as a datasource for Spring R2DBC (instead of H2):
Step 1: Remove H2 Dependencies
Remove or comment out the h2 dependency:
<dependency>
<groupId>io.r2dbc</groupId>
<artifactId>r2dbc-h2</artifactId>
<scope>runtime</scope>
</dependency>
Step 2: Add PostgreSQL R2DBC Maven dependencies
Include the r2dbc PostgreSQL database connector within your pom.xml file:
<dependency>
<groupId>io.r2dbc</groupId>
<artifactId>r2dbc-postgresql</artifactId>
<version>0.8.6.RELEASE</version>
</dependency>
Step 3: Configure your application.properties yml file
The following is configured for a local PostgreSQL instance which we will start in the next step via Docker Compose.
# PostgreSQL
spring.r2dbc.url=r2dbc:postgresql://localhost:5432/postgres
spring.r2dbc.username=postgres
spring.r2dbc.password=example
spring.r2dbc.initialization-mode=always
Step 4:
Start the PostgreSQL docker compose image by navigating to the project directory and running the following in your terminal:
cd docker-compose-files/postgresql
docker-compose up
Common Issues
Caused By: io.r2dbc.postgresql.ExceptionFactory$PostgresqlBadGrammarException: [42P07] relation already exists
or
org.springframework.r2dbc.connection.init.ScriptStatementFailedException: Failed to execute SQL script statement
...
io.r2dbc.spi.R2dbcBadGrammarException: [1050] [42S01] Table already exists
Your table already exists. Try deleting the table from your Postgres database or removing the ConnectionFactoryInitializer bean from your application code so that the table isn’t created each time upon startup. I have included a database cleaner in the ResourceDatabasePopulator for your convenience so that the table is automatically dropped after each run.
Checkout this project from Github
git clone https://github.com/code-tinkering/r2dbc-exampleDownload Zip