Spring Boot R2DBC + PostgreSQL example

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

  • How to configure Spring Data Reactive, R2DBC to work with PostgreSQL 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 PostgreSQL Database

More Practice:
Spring Boot + GraphQL + PostgreSQL example
Spring WebFlux File upload example
Spring Boot R2DBC + H2 example
Spring Boot R2DBC + MySQL 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 Multipart File upload example
Spring Boot Pagination and Sorting example
– Dockerize: Docker Compose: Spring Boot and Postgres 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 + PostgreSQL example
Angular 8 + Spring Boot + PostgreSQL example
Angular 10 + Spring Boot + PostgreSQL example
Angular 11 + Spring Boot + PostgreSQL example
Angular 12 + Spring Boot + PostgreSQL example
Angular 13 + Spring Boot + PostgreSQL example
Angular 14 + Spring Boot + PostgreSQL example
Angular 15 + Spring Boot + PostgreSQL example
Angular 16 + Spring Boot + PostgreSQL example
React + Spring Boot + PostgreSQL example


Overview of Spring Data R2DBC and PostgreSQL example

We will build a Spring Boot R2DBC example that makes CRUD Operations with PostgreSQL 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)
  • PostgreSQL Database
  • Maven 3.6.1

Project Structure

spring-boot-r2dbc-postgresql-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 PostgreSQL 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>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <scope>runtime</scope>
  </dependency>

  <dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>r2dbc-postgresql</artifactId>
    <scope>runtime</scope>
  </dependency>
</dependencies>

Configure the R2DBC and PostgreSQL database

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

#spring.data.r2dbc.repositories.enabled=true
spring.r2dbc.url=r2dbc:postgresql://localhost:5432/testdb
spring.r2dbc.username=postgres
spring.r2dbc.password=123

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:postgresql://localhost:5432/[database-name], which indicates that you are using a PostgreSQL database located at localhost on port 5432. 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 SpringBootR2dbcPostgresqlExampleApplication {

  public static void main(String[] args) {
    SpringApplication.run(SpringBootR2dbcPostgresqlExampleApplication.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.postgresql.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 PostgreSQL database.
In repository package, create TutorialRepository interface that extends R2dbcRepository (which extends Spring Data Reactive ReactiveCrudRepository).

repository/TutorialRepository.java

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

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

import com.bezkoder.spring.r2dbc.postgresql.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.postgresql.service;

import java.util.Optional;

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

import com.bezkoder.spring.r2dbc.postgresql.model.Tutorial;
import com.bezkoder.spring.r2dbc.postgresql.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.postgresql.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.postgresql.model.Tutorial;
import com.bezkoder.spring.r2dbc.postgresql.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.

SpringBootR2dbcPostgresqlExampleApplication.java

package com.bezkoder.spring.r2dbc.postgresql;

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

@EnableWebFlux
@SpringBootApplication
public class SpringBootR2dbcPostgresqlExampleApplication {

  public static void main(String[] args) {
    SpringApplication.run(SpringBootR2dbcPostgresqlExampleApplication.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.

SpringBootR2dbcPostgresqlExampleApplication.java

package com.bezkoder.spring.r2dbc.postgresql;

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 SpringBootR2dbcPostgresqlExampleApplication {

  @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(SpringBootR2dbcPostgresqlExampleApplication.class, args);
  }
}

Run & Check

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

Create some Tutorials:

spring-boot-r2dbc-postgresql-example-tutorial-create

PostgreSQL Database:

spring-boot-r2dbc-postgresql-database

Retrieve all Tutorials:

spring-boot-r2dbc-postgresql-example-tutorial-retrieve

Update some Tutorials:

spring-boot-r2dbc-postgresql-example-tutorial-update

PostgreSQL Database:

spring-data-r2dbc-postgresql-example

Retrieve a Tutorial by Id:

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

Filter published Tutorials:

spring-data-r2dbc-postgresql-example-filter

Find all Tutorials which title contains a certain string:

spring-data-r2dbc-postgresql-example-search

Delete a Tutorial with id=4:

spring-data-r2dbc-postgresql-example-delete

PostgreSQL Database:

spring-data-r2dbc-postgresql-database-delete

Delete all Tutorials and check:

spring-boot-r2dbc-postgresql-example-tutorial-delete

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

Conclusion

Today we’ve built a Spring Data R2DBC with PostgreSQL in Spring Boot 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.

Further Reading

Fullstack CRUD App:
Spring Boot Thymeleaf example
Vue + Spring Boot + PostgreSQL example
Angular 8 + Spring Boot + PostgreSQL example
Angular 10 + Spring Boot + PostgreSQL example
Angular 11 + Spring Boot + PostgreSQL example
Angular 12 + Spring Boot + PostgreSQL example
Angular 13 + Spring Boot + PostgreSQL example
Angular 14 + Spring Boot + PostgreSQL example
Angular 15 + Spring Boot + PostgreSQL example
Angular 16 + Spring Boot + PostgreSQL example
React + Spring Boot + PostgreSQL example

More Practice:
Spring Boot + GraphQL + PostgreSQL example
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
– Dockerize: Docker Compose: Spring Boot and Postgres example