R2DBC — Pagination Example

2 min readApr 27, 2022

In this tutorial, will see how we could achieve pagination for improved navigation of our application when we use Spring Reactive Data (R2DBC Pagination) with Spring WebFlux.

Linked article: https://prateek-ashtikar512.medium.com/r2dbc-crud-example-567ee4865d22

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
   <modelVersion>4.0.0</modelVersion>
   <parent>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-parent</artifactId>
      <version>2.6.3</version>
      <relativePath/> <!-- lookup parent from repository -->
   </parent>
   <groupId>com.example</groupId>
   <artifactId>r2dbc-pagination</artifactId>
   <version>0.0.1-SNAPSHOT</version>
   <name>r2dbc-pagination</name>
   <description>Demo project for Spring Boot</description>
   <properties>
      <java.version>11</java.version>
   </properties>
   <dependencies>
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-data-r2dbc</artifactId>
      </dependency>
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-webflux</artifactId>
      </dependency>

      <dependency>
         <groupId>io.r2dbc</groupId>
         <artifactId>r2dbc-postgresql</artifactId>
         <scope>runtime</scope>
      </dependency>
      <dependency>
         <groupId>org.postgresql</groupId>
         <artifactId>postgresql</artifactId>
         <scope>runtime</scope>
      </dependency>
      <dependency>
         <groupId>org.projectlombok</groupId>
         <artifactId>lombok</artifactId>
         <optional>true</optional>
      </dependency>
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-test</artifactId>
         <scope>test</scope>
      </dependency>
      <dependency>
         <groupId>io.projectreactor</groupId>
         <artifactId>reactor-test</artifactId>
         <scope>test</scope>
      </dependency>
   </dependencies>

   <build>
      <plugins>
         <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
            <configuration>
               <excludes>
                  <exclude>
                     <groupId>org.projectlombok</groupId>
                     <artifactId>lombok</artifactId>
                  </exclude>
               </excludes>
            </configuration>
         </plugin>
      </plugins>
   </build>

</project>

Product.java

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.springframework.data.annotation.Id;
import org.springframework.data.relational.core.mapping.Table;

@AllArgsConstructor(staticName = "of")
@NoArgsConstructor
@Data
@Builder
@Table
public class Product {
    @Id
    private Integer id;
    private String description;
    private Integer price;
}

ProductRepository.java

import com.example.entity.Product;
import org.springframework.data.domain.Pageable;
import org.springframework.data.repository.reactive.ReactiveSortingRepository;
import org.springframework.stereotype.Repository;
import reactor.core.publisher.Flux;

@Repository
public interface ProductRepository extends ReactiveSortingRepository<Product, Integer> {
    Flux<Product> findAllBy(Pageable pageable);
}

DataSetupService.java

import com.example.entity.Product;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.CommandLineRunner;
import org.springframework.core.io.Resource;
import org.springframework.data.r2dbc.core.R2dbcEntityTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.StreamUtils;
import reactor.core.publisher.Flux;
import reactor.core.publisher.Mono;

import java.nio.charset.StandardCharsets;
import java.util.concurrent.ThreadLocalRandom;

@Service
public class DataSetupService implements CommandLineRunner {
    @Value("classpath:init.sql")
    private Resource initSql;

    @Autowired
    private R2dbcEntityTemplate entityTemplate;

    @Override
    public void run(String... args) throws Exception {
        String query = StreamUtils.copyToString(initSql.getInputStream(), StandardCharsets.UTF_8);
        this.entityTemplate
                .getDatabaseClient()
                .sql(query)
                .then()
                .then(insertProducts())
                .subscribe();
    }

    private Mono<Void> insertProducts(){
        return Flux.range(1,100)
                .map(i -> Product.of(null, "Product-"+i, ThreadLocalRandom.current().nextInt(1,1000)))
                .flatMap(this.entityTemplate::insert)
                .doOnComplete(() -> System.out.println("All records inserted"))
                .then();
    }
}

ProductService.java

import com.example.entity.Product;
import com.example.repository.ProductRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import reactor.core.publisher.Mono;

@Service
public class ProductService {
    @Autowired
    private ProductRepository productRepository;

    public Mono<Page<Product>> getProducts(PageRequest pageRequest){
        return this.productRepository.findAllBy(pageRequest.withSort(Sort.by("price").descending()))
                .collectList()
                .zipWith(this.productRepository.count())
                .map(t -> new PageImpl<>(t.getT1(), pageRequest, t.getT2()));
    }
}

ProductController.java

import com.example.entity.Product;
import com.example.service.ProductService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.web.bind.annotation.GetMapping;
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.Mono;

@RestController
@RequestMapping(value = "product")
public class ProductController {

    @Autowired
    private ProductService productService;

    @GetMapping("/all")
    public Mono<Page<Product>> getAllProducts(@RequestParam("page") int page,
                                              @RequestParam("size") int size) {
        return this.productService.getProducts(PageRequest.of(page, size));
    }
}

MainApp.java

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class R2dbcPaginationApplication {

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

}

application.properties

spring.r2dbc.url=r2dbc:postgresql://localhost:5432/postgres
spring.r2dbc.username=postgres
spring.r2dbc.password=postgres

init.sql

CREATE TABLE public.product (
   id serial4 NOT NULL,
   description varchar NOT NULL,
   price int4 NOT NULL,
   CONSTRAINT product_pk PRIMARY KEY (id)
);

CURL:

http://localhost:8080/product/all?page=0&size=3

{
    "content": [
        {
            "id": 25,
            "description": "Product-25",
            "price": 994
        },
        {
            "id": 94,
            "description": "Product-94",
            "price": 986
        },
        {
            "id": 23,
            "description": "Product-23",
            "price": 977
        }
    ],
    "pageable": {
        "sort": {
            "sorted": false,
            "unsorted": true,
            "empty": true
        },
        "pageNumber": 0,
        "pageSize": 3,
        "offset": 0,
        "paged": true,
        "unpaged": false
    },
    "totalElements": 100,
    "totalPages": 34,
    "last": false,
    "numberOfElements": 3,
    "sort": {
        "sorted": false,
        "unsorted": true,
        "empty": true
    },
    "size": 3,
    "number": 0,
    "first": true,
    "empty": false
}

R2DBC — Pagination Example

Written by Prateek