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


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

spring-boot-r2dbc-mysql-example-project

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:

spring-boot-r2dbc-mysql-example-tutorial-create

Retrieve all Tutorials:

spring-boot-r2dbc-mysql-example-tutorial-retrieve

Update some Tutorials:

spring-boot-r2dbc-mysql-example-tutorial-update

Retrieve a Tutorial by Id:

spring-boot-r2dbc-mysql-example-tutorial-retrieve-one

Filter published Tutorials:

spring-boot-r2dbc-mysql-example-tutorial-filter

Find all Tutorials which title contains a certain string:

spring-boot-r2dbc-mysql-example-tutorial-search

Delete a Tutorial with id=4:

spring-boot-r2dbc-mysql-example-tutorial-delete

Check all current Tutorials:

spring-boot-r2dbc-mysql-example-tutorial-check

MySQL Database:

spring-boot-r2dbc-mysql-example-tutorial-database

Delete all Tutorials and check:

spring-boot-r2dbc-mysql-example-tutorial-delete-all

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.

Further Reading

Fullstack CRUD App:
Spring Boot Thymeleaf example
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

More Practice:
Spring WebFlux File upload example
Secure Spring Boot with Spring Security & JWT Authentication
Spring Boot Rest XML example – Web service with XML Response
Spring Boot Multipart File upload example
Spring Boot Pagination and Sorting example

Source Code

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