Querying Postgres with Spring Data and Micronaut Data

Table of contents

Create the project
Define the domain
Create a repository
Implement the controller
Test the application

Repository API of Spring Data is one of the most elegant and compelling features of Spring framework. It generates runtime implementations of the repositories. But what if those implementations can be generated at compile-time with type-checks that fail during compilation, rather than at runtime.

Micronaut Data is one of the projects under Micronaut Framework. It provides database toolkit that

doesn’t use reflection or proxies,
generates the code at compile-time, and
provides compile-time type checks to ensure the repository methods fail early when incorrectly implemented

It works with multiple backends (including JPA and JDBC), provides a traditional synchronous API as well as reactive APIs for RxJava and Project Reactor, and integrates nicely with Spring Data and Spring Data JPA. In this post, we’ll learn how to use its JDBC backend with a Spring Data Repository.

You can spin an instance of Postgres using the following Compose file.

yaml

 1version: "3.1"
 2
 3services:
 4  db:
 5    image: postgres:12.2-alpine
 6    container_name: postgres_12.2
 7    ports:
 8      - 5432:5432
 9    environment:
10      POSTGRES_PASSWORD: example

Execute the following command to launch the container.

docker-compose up -d

Create the project

Generate a Gradle project and add the following in the build.gradle file.

groovy

 1plugins {
 2  id 'java'
 3  id 'org.springframework.boot' version '2.2.6.RELEASE'
 4  id 'io.spring.dependency-management' version '1.0.9.RELEASE'
 5  id 'net.ltgt.apt' version '0.19'
 6}
 7
 8group = 'dev.mflash.guides'
 9version = '0.0.1'
10sourceCompatibility = JavaVersion.VERSION_11
11
12repositories {
13  jcenter()
14}
15
16dependencyManagement {
17  imports {
18    mavenBom 'io.micronaut:micronaut-bom:1.3.4'
19  }
20}
21
22dependencies {
23  annotationProcessor('io.micronaut:micronaut-inject-java')
24  annotationProcessor('io.micronaut:micronaut-validation')
25  annotationProcessor('io.micronaut.spring:micronaut-spring-annotation')
26  annotationProcessor('io.micronaut.spring:micronaut-spring-web-annotation')
27  annotationProcessor('io.micronaut.spring:micronaut-spring-boot-annotation')
28  annotationProcessor('io.micronaut.data:micronaut-data-processor')
29
30  implementation('io.micronaut:micronaut-inject')
31  implementation('io.micronaut:micronaut-validation')
32  implementation('io.micronaut:micronaut-runtime')
33  implementation('io.micronaut:micronaut-http-client')
34  implementation('io.micronaut:micronaut-http-server')
35  implementation('io.micronaut.data:micronaut-data-jdbc')
36  implementation('org.springframework.data:spring-data-commons')
37  implementation('org.springframework.boot:spring-boot-starter-web')
38  implementation('jakarta.persistence:jakarta.persistence-api')
39
40  runtime('io.micronaut.spring:micronaut-spring-web')
41  runtime('io.micronaut.configuration:micronaut-jdbc-tomcat')
42  runtime('io.micronaut:micronaut-http-server-netty')
43  runtimeOnly('org.postgresql:postgresql')
44}

These dependencies allow Micronaut to map Spring annotations to Micronaut annotations at compile time. This allows us to use the familiar Spring annotations and still take advantage of AOT compilation offered by Micronaut. A more comprehensive explanation about the relevance of these dependencies is available here.

Open application.yml file and add the following database configuration.

yaml

 1# src/main/resources/application.yml
 2
 3datasources:
 4  default:
 5    url: jdbc:postgresql://localhost:5432/spring-guides
 6    driverClassName: org.postgresql.Driver
 7    username: postgres
 8    password: example
 9    schema-generate: CREATE_DROP
10    dialect: POSTGRES

Note that this is not a Spring Boot specific configuration; instead this configuration will be used by Micronaut Data to build the datasources.

Define the domain

Create an entity, say Customer, as follows.

java

 1// src/main/java/dev/mflash/guides/customer/Customer.java
 2
 3public @Entity class Customer {
 4
 5  @GeneratedValue(strategy = GenerationType.IDENTITY)
 6  private @Id long customerId;
 7  private String firstName;
 8  private String lastName;
 9
10  // constructors, getters, setters, etc.
11}

The customerId field is of type SERIAL which Postgres autoincrements for every new record. That’s why we’re delegating the ID generation to Postgres with GenerationType.IDENTITY strategy.

Create a repository

Define a CustomerRepository interface and add the following code.

java

 1// src/main/java/dev/mflash/guides/customer/CustomerRepository.java;
 2
 3@JdbcRepository
 4public interface CustomerRepository extends PagingAndSortingRepository<Customer, Long> {
 5
 6  @Override
 7  Set<Customer> findAll();
 8
 9  default Optional<Customer> persist(Customer customer) {
10    Customer saved = findById(customer.getCustomerId()).get();
11    BeanUtils.copyProperties(customer, saved, getNullPropertyNames(customer));
12    update(saved.getCustomerId(), saved.getFirstName(), saved.getLastName());
13    return findById(customer.getCustomerId());
14  }
15
16  @Query("UPDATE Customer SET first_name = :firstName, last_name = :lastName WHERE customer_id = :id")
17  void update(long id, String firstName, String lastName);
18
19  static String[] getNullPropertyNames (Object source) {
20    final BeanWrapper src = new BeanWrapperImpl(source);
21    PropertyDescriptor[] pds = src.getPropertyDescriptors();
22
23    Set<String> emptyNames = new HashSet<>();
24    for(PropertyDescriptor pd : pds) {
25      Object srcValue = src.getPropertyValue(pd.getName());
26      if (srcValue == null) emptyNames.add(pd.getName());
27    }
28
29    String[] result = new String[emptyNames.size()];
30    return emptyNames.toArray(result);
31  }
32
33  Set<Customer> findByLastName(String lastName);
34
35  Set<Customer> findByFirstName(String firstName);
36
37  Set<Customer> findByFirstNameAndLastName(String firstName, String lastName);
38}

This repository is pretty familiar Spring PagingAndSortingRepository with some filter methods to get customers by their firstName, lastName, etc. Also, note that the persist method provides an update facility for Customer objects using an UPDATE operation. The @JdbcRepository annotation configures this interface as a Micronaut-managed @Repository, enabling Micronaut to build necessary datasources and JdbcRepositoryOperations for the associated entity.

Implement the controller

Create a CustomerController to use this repository and provide a bunch of CRUD operations, as follows.

java

 1// src/main/java/dev/mflash/guides/customer/CustomerController.java;
 2
 3@RequestMapping("/customers")
 4public @RestController class CustomerController {
 5
 6  private final CustomerRepository repository;
 7
 8  public CustomerController(CustomerRepository repository) {
 9    this.repository = repository;
10  }
11
12  @GetMapping
13  Set<Customer> get(@RequestParam(required = false) String firstName, @RequestParam(required = false) String lastName) {
14    Set<Customer> result;
15
16    if (Objects.nonNull(firstName) && Objects.nonNull(lastName)) {
17      result = repository.findByFirstNameAndLastName(firstName, lastName);
18    } else if (Objects.nonNull(firstName)) {
19      result = repository.findByFirstName(firstName);
20    } else if (Objects.nonNull(lastName)) {
21      result = repository.findByLastName(lastName);
22    } else {
23      result = repository.findAll();
24    }
25
26    return result;
27  }
28
29  @PostMapping
30  Customer save(Customer customer) {
31    return repository.save(customer);
32  }
33
34  @PatchMapping
35  Optional<Customer> patch(Customer customer) {
36    return repository.persist(customer);
37  }
38
39  @DeleteMapping("/{id}")
40  void delete(@PathVariable("id") @NotBlank Long id) {
41    repository.deleteById(id);
42  }
43}

To launch this application, create a Launcher as follows.

java

1// src/main/java/dev/mflash/guides/customer/Launcher.java
2
3public @SpringBootApplication class Launcher {
4
5  public static void main(String... args) {
6    Micronaut.run(Launcher.class);
7  }
8}

Note that we’re not launching this application as SpringApplication; instead we’re launching it with Micronaut. This is necessary so that Micronaut can manage the repository we created earlier.

Test the application

Try the following curl requests to test-drive the application.

 1# Add some entries for Customer
 2curl --location --request POST 'http://localhost:8080/customers' \
 3--header 'Content-Type: application/json' \
 4--data-raw '{ 
 5  "firstName": "Tamara",
 6  "lastName": "May"
 7}'
 8
 9curl --location --request POST 'http://localhost:8080/customers' \
10--header 'Content-Type: application/json' \
11--data-raw '{ 
12  "firstName": "Lorna",
13  "lastName": "Jones"
14}'
15
16curl --location --request POST 'http://localhost:8080/customers' \
17--header 'Content-Type: application/json' \
18--data-raw '{ 
19  "firstName": "Delores",
20  "lastName": "Jones"
21}'
22
23# Fetch all customers
24curl --location --request GET 'http://localhost:8080/customers'
25
26# Fetch by filtering firstName, lastName
27curl --location --request GET 'http://localhost:8080/customers?firstName=Lorna'
28curl --location --request GET 'http://localhost:8080/customers?lastName=Jones'
29curl --location --request GET 'http://localhost:8080/customers?firstName=Delores&lastName=Jones'
30
31# Patch a Customer record
32curl --location --request PATCH 'http://localhost:8080/customers' \
33--header 'Content-Type: application/json' \
34--data-raw '{
35    "customerId": 1,
36    "firstName": "Robyn"
37}'
38
39# Delete a Customer record
40curl --location --request DELETE 'http://localhost:8080/customers/2'

References

Source code

spring-data-micronaut-data