Spring Boot R2DBC + MySQL example

In this tutorial, we’re gonna build a Spring Boot R2DBC example – CRUD application with MySQL Database using Spring Data Reactive (R2DBC) and WebFlux for REST API. You’ll know:

How to configure Spring Data Reactive, R2DBC to work with MySQL Database
How to define Data Models and Repository interfaces
Way to create Spring Rest Controller to process HTTP requests
Way to use Spring Data R2DBC to interact with MySQL Database

More Practice:
– Spring WebFlux File upload example
– Spring Boot R2DBC + H2 example
– Spring Boot Thymeleaf CRUD example
– Secure Spring Boot App with Spring Security & JWT Authentication
– Spring Boot Rest XML example – Web service with XML Response
– Spring Boot + GraphQL + MySQL example
– Spring Boot Multipart File upload example
– Spring Boot Pagination and Sorting example

Associations:
– Spring Boot One To One example with JPA, Hibernate
– Spring Boot One To Many example with JPA, Hibernate
– Spring Boot Many to Many example with JPA, Hibernate

Fullstack:
– Vue + Spring Boot example
– Angular 8 + Spring Boot example
– Angular 10 + Spring Boot example
– Angular 11 + Spring Boot example
– Angular 12 + Spring Boot example
– Angular 13 + Spring Boot example
– Angular 14 + Spring Boot example
– Angular 15 + Spring Boot example
– Angular 16 + Spring Boot example
– React + Spring Boot example

Contents

Overview of Spring R2DBC and MySQL example
Technology
Project Structure
Create & Setup Spring Boot project
Configure the R2DBC and MySQL database
@EnableR2dbcRepositories
Define Data Model
Create Repository Interface
Create Data Service
Create Spring Reactive Rest API Controller
Enable WebFlux
Create the Database Schema
Run & Check
Conclusion
Further Reading
Source Code

Overview of Spring R2DBC and MySQL example

We will build a Spring Boot R2DBC example that makes CRUD Operations with MySQL database – a Tutorial application in that:

Each Tutorial has id, title, description, published status.
Apis help to create, retrieve, update, delete Tutorials.
Apis also support custom finder methods such as find by published status or by title.

These are APIs that we need to provide:

Methods	Urls	Actions
POST	/api/tutorials	create new Tutorial
GET	/api/tutorials	retrieve all Tutorials
GET	/api/tutorials/:id	retrieve a Tutorial by `:id`
PUT	/api/tutorials/:id	update a Tutorial by `:id`
DELETE	/api/tutorials/:id	delete a Tutorial by `:id`
DELETE	/api/tutorials	delete all Tutorials
GET	/api/tutorials/published	find all published Tutorials
GET	/api/tutorials?title=[keyword]	find all Tutorials which title contains `keyword`

Technology

Java 11/17
Spring Boot 3 (with Spring WebFlux, Spring Data R2DBC)
MySQL Database
Maven 3.6.1

Project Structure

Let me explain it briefly.

– Tutorial data model class.
– TutorialRepository is an interface that extends R2dbcRepository to interact with the database. It is autowired in TutorialService.
– TutorialService is a service component that uses TutorialRepository and provides CRUD methods and custom finder methods for TutorialController.
– TutorialController is a RestController which has request mapping methods for RESTful requests such as: getAllTutorials, createTutorial, updateTutorial, deleteTutorial, findByPublished…
– Configuration for Spring Data R2DBC is in application.properties.
– schema.sql has SQL statement for initializing database table.
– pom.xml contains dependencies for Spring Boot, WebFlux, R2DBC and MySQL database.

Create & Setup Spring Boot project

Use Spring web tool or your development tool (Spring Tool Suite, Eclipse, Intellij) to create a Spring Boot project.

Then open pom.xml and add these dependencies:

<dependencies>
  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-webflux</artifactId>
  </dependency>

  <dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-r2dbc</artifactId>
  </dependency>

  <dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
  </dependency>
  
  <dependency>
    <groupId>com.github.jasync-sql</groupId>
    <artifactId>jasync-r2dbc-mysql</artifactId>
    <version>2.1.16</version>
  </dependency>
</dependencies>

Configure the R2DBC and MySQL database

Under src/main/resources folder, open application.properties and write these lines.

#spring.data.r2dbc.repositories.enabled=true
spring.r2dbc.url=r2dbc:mysql://localhost:3306/testdb_spring
spring.r2dbc.username=root
spring.r2dbc.password=123456

spring.data.r2dbc.repositories.enabled determines the activation of R2DBC repositories in a Spring Boot application.
By default, R2DBC repository support is enabled in a Spring Boot application. If you want to disable R2DBC repository support, you can set the spring.data.r2dbc.repositories.enabled property to false.

The connection URL is set to r2dbc:mysql://localhost:3306/[database-name], which indicates that you are using a MySQL database located at localhost on port 3306. The spring.r2dbc.username and spring.r2dbc.password properties provide the credentials to connect to the database.

@EnableR2dbcRepositories

@EnableR2dbcRepositories is a Spring annotation that is used to enable R2DBC repositories in a Spring Boot application. It provides a convenient way to create a repository layer in a Spring Boot application that uses R2DBC to interact with a database.

Because R2DBC repository support is enabled in our Spring Boot application by default (spring.data.r2dbc.repositories.enabled=true), so that the @EnableR2dbcRepositories is not necessary.

The @EnableR2dbcRepositories annotation could be added to a configuration class in your application, typically the main class that is annotated with @SpringBootApplication:

import org.springframework.data.r2dbc.repository.config.EnableR2dbcRepositories;

@EnableR2dbcRepositories
@SpringBootApplication
public class SpringBootR2dbcMysqlExampleApplication {

  public static void main(String[] args) {
    SpringApplication.run(SpringBootR2dbcMysqlExampleApplication.class, args);
  }
}

Define Data Model

Our Data model is Tutorial with four fields: id, title, description, published.
In model package, we define Tutorial class.

model/Tutorial.java

package com.bezkoder.spring.r2dbc.mysql.model;

import org.springframework.data.annotation.Id;

public class Tutorial {
  
  @Id
  private int id;

  private String title;

  private String description;

  private boolean published;

  public Tutorial() {

  }

  public Tutorial(String title, String description, boolean published) {
    this.title = title;
    this.description = description;
    this.published = published;
  }

  // getters and setters

  @Override
  public String toString() {
    return "Tutorial [id=" + id + ", title=" + title + ", desc=" + description + ", published=" + published + "]";
  }
}

Create Repository Interface

Let’s create a repository to interact with Tutorials from the MySQL database.
In repository package, create TutorialRepository interface that extends R2dbcRepository (which extends Spring Data Reactive ReactiveCrudRepository).

repository/TutorialRepository.java

package com.bezkoder.spring.r2dbc.mysql.repository;

import org.springframework.data.r2dbc.repository.R2dbcRepository;
import org.springframework.stereotype.Repository;

import com.bezkoder.spring.r2dbc.mysql.model.Tutorial;

import reactor.core.publisher.Flux;

@Repository
public interface TutorialRepository extends R2dbcRepository<Tutorial, Integer>{
  Flux<Tutorial> findByTitleContaining(String title);
  
  Flux<Tutorial> findByPublished(boolean isPublished);
}

Now we can use ReactiveCrudRepository‘s methods: save(), findById(), findAll(), count(), delete(), deleteById(), deleteAll()… without implementing these methods.

We also define custom finder methods:
– findByPublished(): returns all Tutorials with published having value as input published.
– findByTitleContaining(): returns all Tutorials which title contains input title.

The implementation is plugged in by Spring Data R2DBC automatically.

Create Data Service

Let’s create a service that uses TutorialRepository to implement CRUD Operations and custom finder methods.
In service package, create TutorialService.

service/TutorialService.java

package com.bezkoder.spring.r2dbc.mysql.service;

import java.util.Optional;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.bezkoder.spring.r2dbc.mysql.model.Tutorial;
import com.bezkoder.spring.r2dbc.mysql.repository.TutorialRepository;

import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

@Service
public class TutorialService {

  @Autowired
  TutorialRepository tutorialRepository;

  public Flux<Tutorial> findAll() {
    return tutorialRepository.findAll();
  }

  public Flux<Tutorial> findByTitleContaining(String title) {
    return tutorialRepository.findByTitleContaining(title);
  }

  public Mono<Tutorial> findById(int id) {
    return tutorialRepository.findById(id);
  }

  public Mono<Tutorial> save(Tutorial tutorial) {
    return tutorialRepository.save(tutorial);
  }

  public Mono<Tutorial> update(int id, Tutorial tutorial) {
    return tutorialRepository.findById(id).map(Optional::of).defaultIfEmpty(Optional.empty())
        .flatMap(optionalTutorial -> {
          if (optionalTutorial.isPresent()) {
            tutorial.setId(id);
            return tutorialRepository.save(tutorial);
          }

          return Mono.empty();
        });
  }

  public Mono<Void> deleteById(int id) {
    return tutorialRepository.deleteById(id);
  }

  public Mono<Void> deleteAll() {
    return tutorialRepository.deleteAll();
  }

  public Flux<Tutorial> findByPublished(boolean isPublished) {
    return tutorialRepository.findByPublished(isPublished);
  }
}

– TutorialService class is annotated with @Service to indicate that it is a service component. Spring will create a singleton bean for this class and manage its lifecycle.
– We use @Autowired to inject TutorialRepository bean to local variable.

Create Spring Reactive Rest API Controller

Finally, we create a controller that provides APIs for creating, retrieving, updating, deleting and finding Tutorials.

controller/TutorialController.java

package com.bezkoder.spring.r2dbc.mysql.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseStatus;
import org.springframework.web.bind.annotation.RestController;

import com.bezkoder.spring.r2dbc.mysql.model.Tutorial;
import com.bezkoder.spring.r2dbc.mysql.service.TutorialService;

import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

@CrossOrigin(origins = "http://localhost:8081")
@RestController
@RequestMapping("/api")
public class TutorialController {
  @Autowired
  TutorialService tutorialService;
  
  @GetMapping("/tutorials")
  @ResponseStatus(HttpStatus.OK)
  public Flux<Tutorial> getAllTutorials(@RequestParam(required = false) String title) {
    if (title == null)
      return tutorialService.findAll();
    else
      return tutorialService.findByTitleContaining(title);
  }

  @GetMapping("/tutorials/{id}")
  @ResponseStatus(HttpStatus.OK)
  public Mono<Tutorial> getTutorialById(@PathVariable("id") int id) {
    return tutorialService.findById(id);
  }

  @PostMapping("/tutorials")
  @ResponseStatus(HttpStatus.CREATED)
  public Mono<Tutorial> createTutorial(@RequestBody Tutorial tutorial) {
    return tutorialService.save(new Tutorial(tutorial.getTitle(), tutorial.getDescription(), false));
  }

  @PutMapping("/tutorials/{id}")
  @ResponseStatus(HttpStatus.OK)
  public Mono<Tutorial> updateTutorial(@PathVariable("id") int id, @RequestBody Tutorial tutorial) {
    return tutorialService.update(id, tutorial);
  }

  @DeleteMapping("/tutorials/{id}")
  @ResponseStatus(HttpStatus.NO_CONTENT)
  public Mono<Void> deleteTutorial(@PathVariable("id") int id) {
    return tutorialService.deleteById(id);
  }

  @DeleteMapping("/tutorials")
  @ResponseStatus(HttpStatus.NO_CONTENT)
  public Mono<Void> deleteAllTutorials() {
    return tutorialService.deleteAll();
  }

  @GetMapping("/tutorials/published")
  @ResponseStatus(HttpStatus.OK)
  public Flux<Tutorial> findByPublished() {
    return tutorialService.findByPublished(true);
  }
}

– @CrossOrigin is for configuring allowed origins.
– @RestController annotation is used to define a controller and to indicate that the return value of the methods should be be bound to the web response body.
– @RequestMapping("/api") declares that all Apis’ url in the controller will start with /api.
– We use @Autowired to inject TutorialService bean to local variable.

Enable WebFlux

We use @EnableWebFlux to enable support for reactive web application using the Spring WebFlux framework.

SpringBootR2dbcMysqlExampleApplication.java

package com.bezkoder.spring.r2dbc.mysql;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;

@EnableWebFlux
@SpringBootApplication
public class SpringBootR2dbcMysqlExampleApplication {

  public static void main(String[] args) {
    SpringApplication.run(SpringBootR2dbcMysqlExampleApplication.class, args);
  }

}

Create the Database Schema

In src/main/resources folder, create schema.sql file with following content:

CREATE TABLE IF NOT EXISTS tutorial (id INT NOT NULL AUTO_INCREMENT, title VARCHAR(255), description VARCHAR(255), published BOOLEAN, PRIMARY KEY (id));

Spring Data R2DBC ConnectionFactoryInitializer provides a convenient way to configure and initialize a connection factory for a reactive database connection in a Spring application. It will scan schema.sql in the classpath, execute SQL script to initialize the database when the database is connected.

SpringBootR2dbcMysqlExampleApplication.java

package com.bezkoder.spring.r2dbc.mysql;

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 org.springframework.web.reactive.config.EnableWebFlux;

import io.r2dbc.spi.ConnectionFactory;

@EnableWebFlux
@SpringBootApplication
public class SpringBootR2dbcMysqlExampleApplication {

  @Bean
  ConnectionFactoryInitializer initializer(ConnectionFactory connectionFactory) {

    ConnectionFactoryInitializer initializer = new ConnectionFactoryInitializer();
    initializer.setConnectionFactory(connectionFactory);
    initializer.setDatabasePopulator(new ResourceDatabasePopulator(new ClassPathResource("schema.sql")));

    return initializer;
  }

  public static void main(String[] args) {
    SpringApplication.run(SpringBootR2dbcMysqlExampleApplication.class, args);
  }
}

Run & Check

Run Spring Boot application with Maven command: mvn spring-boot:run.

Create some Tutorials:

Retrieve all Tutorials:

Update some Tutorials:

Retrieve a Tutorial by Id:

Filter published Tutorials:

Find all Tutorials which title contains a certain string:

Delete a Tutorial with id=4:

Check all current Tutorials:

MySQL Database:

Delete all Tutorials and check:

You can also check this Spring Boot App with Client in one of these posts:

Conclusion

Today we’ve built a Spring Boot R2DBC with MySQL and Maven example successfully using WebFlux Framework for Rest API, Spring Data Reactive, R2DBC for CRUD and custom finder methods.

Happy learning! See you again.

Source Code

You can find the complete source code for this tutorial on Github.