2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / 2ndQuadrant3 / Smart R2DBC Repositories for PostgreSQL Databases
Smart R2DBC Repositories for PostgreSQL Databases
Muhammad Haroon

Smart R2DBC Repositories for PostgreSQL Databases

September 29, 2020/0 Comments/in 2ndQuadrant, Haroon's PlanetPostgreSQL /by Muhammad Haroon

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 1st 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.

Tags: java, PostgreSQL, r2dbc
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
When to deploy or upgrade to a new major PostgreSQL release When to deploy or upgrade to a new major PostgreSQL release PostgreSQL, a community project
Scroll to top
×