Smart R2DBC Repositories for PostgreSQL Databases

Continuing on from the Reactive Spring Boot application development blogs we authored and provided samples for in the last two blogs (part 1 and part 2), we will now move on and discuss how to design and deploy “intelligent” repositories for R2DBC project for PostgreSQL. The purpose of this post is to show you an amazing feature of R2DBC ORM for PostgreSQL database engines that translates your Java methods (using their signature) to PostgreSQL queries. These queries can perform all sort of actions:

1. Applying the suitable WHERE clause in the query
2. Translating the Java method parameters to SQL data types
3. Adding WHERE clause constraints with aggregation (AND, OR, NOT, etc.)
4. Safe propagation of null checks and finding null or non-null data from database
5. Checking for value to be within bounds (the SQL BETWEEN and IN clauses)

R2DBC provides the developers with access to writing these queries in both manner; you can write them using raw SQL with the @Query attribute, or you can write the method with a specific signature that helps R2DBC translate the query for you. In this post, you will learn how to use the R2DBC feature to translate the query automatically for you in the repository.

Java Web App

We will continue using the same Java app that we developed for the last 2 parts of the Reactive Spring Boot series. The goal is to explain more about the interesting feature that R2DBC offers. I will add the changes for the components and mention any new component that I add in the library for you to add in your project as well.

PostgreSQL Repository

We have developed the PostgreSQL repository (just implemented it) that enables us to perform the basic CRUD operations. Our RESTful controllers use this repository to create new records in PostgreSQL and query or remove the records from the database as well. That works just fine, but in order to have custom queries written, we will need to use the @Query attribute and write our SQL script there. Something like this:

@Query(“SELECT * FROM books WHERE publish_year = :year”)
public Flux<Book> findAllBooksByPublishYear(int year);

This should work just fine, but it requires us to write the query for every single operation that we need to perform. Instead, what R2DBC offers is that we write the Java methods in a special format that R2DBC can understand and translate automatically.

The structure goes as, we start the method name using “findBy” and then add the column name which we need to use as a filter. Such as, if we want to find the books by their rating, we would write findByRating and provide the rating value as a parameter to this method.

As an example, we will showcase two methods:

1. findByPublishYear
2. findByRatingGreaterThan

These two methods showcase the benefits that R2DBC offers. In the first method, we use the publish year column to translate the query with a WHERE clause that uses the publish year for a book. In the second approach, we let R2DBC know that we need results where the rating column has a value that is greater than the provided one. Here are the Java methods for this:

package com.demos.reactivepostgresql.data;

import com.demos.reactivepostgresql.models.Book;

import org.springframework.data.repository.reactive.ReactiveCrudRepository;

import reactor.core.publisher.Flux;

public interface BookRepository extends ReactiveCrudRepository<Book, Long> {
    Flux<Book> findByPublishYear(int publishYear);
    Flux<Book> findByRatingGreaterThan(double rating);
}

This is the complete code that we need to write in our repository to get this feature; remember that ReactiveCrudRepository exposes its own methods for basic CRUD operations as well.

Limiting results

R2DBC allows you to write “First” in the method name before “By” to limit the query results to the top 1^st element that is returned by the overall cursor.

Flux<Book> findFirstByPublishYear(int publishYear);

Text search

Repository enables you to use the SQL LIKE operation in the WHERE clause to query the text-content that follows a structure. For that you can use “Like”, “StartingWith”, and “EndingWith” to apply the specific query when the SQL is executed.

Likewise, you can also limit when the text is “not like” thus reversing the operation. All this is done by the repository for you.

Data types

The repository also enables you to use custom/language-specific data types to query the results. The most common one would be the Date type in Java language. You can use the “Before” or “After” to check if the record was created before a specific date, or after that day.

Java has a huge application of the null-keyword, and R2DBC allows you to verify is something is null in the database or not, and then take specific actions. In the repository you can use “IsNull” or “IsNotNull” in the method names to apply the flags.

You can also use Boolean values such as “IsTrue” or “IsFalse” to query the records that relate to such types and can help make the Java app more verbose; readable.

Before writing your own queries, please learn the basic rules that R2DBC uses to translate the methods and their names to the underlying SQL queries at runtime. It would help you prevent making any runtime/logical errors.

RESTful endpoints

We have added two new methods in the repository that query the database based on a search criterion which is passed by the Java parameters. In our controller, we need to add 2 more endpoints that can help us query PostgreSQL database based on the SQL queries we generated in repository.

Here is the code that we will use:

package com.demos.reactivepostgresql.controllers;

import com.demos.reactivepostgresql.data.BookRepository;
import com.demos.reactivepostgresql.models.Book;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.GetMapping;
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.RestController;

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

@RestController
@RequestMapping(value = "/api/books")
public class HomeController {

    @Autowired BookRepository bookRepository;

    // 1
    @GetMapping("")
    public Flux<Book> getAllBooks() {
        return bookRepository.findAll();
    }

    // 2
    @GetMapping("publishyear")
    public Flux<Book> getBooksByPublishYear(int value) {
        return bookRepository.findByPublishYear(value);
    }

    // 3
    @GetMapping("rating")
    public Flux<Book> getBooksByRating(double value) {
        return bookRepository.findByRatingGreaterThan(value);
    }

    @PostMapping("")
    public Mono<Book> postBook(@RequestBody Book book) {
        return bookRepository.save(book);
    }

    @PutMapping("")
    public Mono<Book> updateBook(@RequestBody Book book) {
        return bookRepository.save(book);
    }

    @DeleteMapping("")
    public boolean deleteBook(@RequestBody Book book) {
        try {
            bookRepository.deleteById(book.getId()).block();
            return true;
        } catch (Exception e) {
            return false;
        }
    }
}

We have three GET handlers in the code, one of them handles the basic read operation, but two are for the specific actions. In next section, you will see the SQL query that should be written by hand if Java developer needs to use the @Query approach to write the methods.

For this code to work, we need to modify our Java model for the Book class as well, here is the updated code:

package com.demos.reactivepostgresql.models;

import org.springframework.data.annotation.Id;

public class Book {

    @Id
    private Long id;
    private String title;
    private String author;
    private int publishYear;
    private double rating;

    public Book() {}

    public Book(String title, String author, int publishYear, double rating) {
        this.title = title;
        this.author = author;
        this.publishYear = publishYear;
        this.rating = rating;
    }

    public Book(Long id, String title, String author, int publishYear, double rating) {
        this.id = id;
        this.title = title;
        this.author = author;
        this.publishYear = publishYear;
        this.rating = rating;
    }

    public Long getId() {
        return this.id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getTitle() {
        return this.title;
    }

    public String getAuthor() {
        return this.author;
    }

    public int getPublishYear() {
        return this.publishYear;
    }

    public double getRating() {
        return this.rating;
    }

    @Override
    public String toString() {
        return "book"; // update to suit your app-specific needs, reader.
    }
}

Getting logs

In order to get the logs for our SQL queries, please add the following line to your application.properties file:

logging.level.org.springframework.data.r2dbc=DEBUG

This would enable SQL logging for the queries that are being sent to PostgreSQL database engine.

Performing Smart Queries

Now that we have laid the foundation for our PostgreSQL engine as well as the Java web app to run queries on database, let us run the queries by hitting our RESTful endpoints. Like our previous blogs, we will be using Postman to send the request and capture/print the response—then we will use OmniDB to verify and cross-check for the results and see if the results match.

Here is the screenshot for the contents in our database now:

Get all results

If we send the request to /api/books endpoint, our findAll() method will be executed on the repository. This method will return all the records from the table.

We receive the data in a sequence as PostgreSQL returns the data to our Java application. Spring Boot Reactive translates the SQL query as:

o.s.d.r2dbc.core.DefaultDatabaseClient   : Executing SQL statement [SELECT book.* FROM book]

In the next part, you will see how R2DBC translates the queries for our smart methods.

Get by publish year

Now we use the publish year property of our entity and get the results that are filtered for this column by PostgreSQL. We send the request to the endpoint /api/books/publishyear and pass the value using the value query string parameter.

PostgreSQL returns only the data that agrees with the condition; publish year being 2019 for the data. You can compare this result with the original data in the table shown above in OmniDB screenshot.

We can get this result using the following SQL:

SELECT * FROM public.books WHERE publish_year = 2019;

This is a very basic SQL query to filter the data; for specialized scenario you might need to add ORDER BY clause or include the column names as well. That makes it difficult to write these queries by hand, especially when you have hundreds of tables and hundreds of columns to filter from.

R2DBC, uses the parameterized SQL query to capture the data from database:

o.s.d.r2dbc.core.DefaultDatabaseClient : Executing SQL statement [SELECT book.id, book.title, book.author, book.publish_year, book.rating FROM book WHERE book.publish_year = $1]

The rating value is passed on as a parameter to prevent SQL injection.

Get by rating

R2DBC allows us to use greater than, equals or less than operators in the SQL query while writing the Java methods. In this approach, we will filter the data where the records have a higher value in the column; the rating column.

We call the endpoint /api/books/rating and pass the value of 4 in the query string, this helps us limit the results to those records that have a rating of 4 or more.

You can compare this result with the original database screenshot of OmniDB as well. PostgreSQL returned a list of the records where the rating value was more than 4. We can get the results using this SQL:

SELECT * FROM public.books WHERE rating > 4;

As shown in the screenshot below:

R2DBC translated the method to the following SQL query to get the results:

o.s.d.r2dbc.core.DefaultDatabaseClient : Executing SQL statement [SELECT book.id, book.title, book.author, book.publish_year, book.rating FROM book WHERE book.rating > $1]

Following this approach, you can write Java methods for each of the operation, filter, sort or limit value and R2DBC will handle the SQL generation itself.

Summary

In this post, we discussed how to write neater Java-based R2DBC repositories for PostgreSQL database connections that can help us build expressive SQL queries in the backend. We also saw various method naming conventions that are supported by R2DBC PostgreSQL repositories that allow you to translate the query in SQL-friendly and Java-friendly manner. We also saw how easy it is to use R2DBC in Java apps to develop a front-end for PostgreSQL database.

Also visit this documentation page for the R2DBC project and learn what other methods are available for the Java to PostgreSQL translation. The translation for PostgreSQL happens with the PostgreSQL dependency.