JPA Native Query Postgres Optional Params

Common Problems

• In some Spring Boot projects with JPA we usually have some apis for filtering data from database base on values of some columns.
• For example, In an entity contains columns as below.

package com.springboot.project.entity;

import jakarta.persistence.Entity;
import jakarta.persistence.EnumType;
import jakarta.persistence.Enumerated;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Index;
import jakarta.persistence.Table;
import lombok.Getter;
import lombok.Setter;

import java.util.Date;
import java.util.UUID;

@Getter
@Setter
@Entity
@Table(name = "customers", indexes = {
        @Index(name = "uniqueEmailIndex", columnList = "email", unique = true),
        @Index(name = "uniquePhoneIndex", columnList = "phone", unique = true),
        @Index(name = "uniqueMultiIndex", columnList = "email, phone", unique = true)
})
public class CustomerEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private UUID id;
    private String fullName;
    private String email;
    private String address;
    private String phone;
    @Enumerated(EnumType.STRING)
    private Gender gender;
    private Date dob;

}

• We want to expose an api for getting customers base on fullName, email, address and phone columns and those input values for filtering customers are optional. It means, the query input can contain only fullName and others can be null etc...
• If we use the query like we usually use as below with the input is only full_name.

select * from customers c 
where c.full_name = :full_name
and c.email = :email
and c.address = :address
and c.phone = regexp_replace(:phone , '-', '', 'g');

• Then we will receive the result with the data like this.

Name	Value
id	2b7b468d-4d15-4a62-bbd2-0f7712488471
address
dob	1995-10-10 07:00:00.000
email
full_name	Katrina Kerluke
gender	M
phone

• It will try to get the record with fullName is as the input and other fields have to contain null values.
• Of course, this is not what we are expecting, we are expecting that the query will execute base on input params that contain values, for null values we will skip them. In this post we will handle this case by using native query in JPA.

Native Query With Optional Params

• Jpa supports us to use the native query through using @Query annotation with param nativeQuery=true.
• For example:

@Query(value = "select * from customers c where c.email = :email", 
             nativeQuery = true)
Optional<CustomerEntity> getCustomerByEmail(@Param("email") String fullName);

• Then in SQL we have a way to avoid null input value in query as below.

SELECT * FROM customers c  
WHERE (:full_name is null or c.full_name = :full_name);

• So before the or we have :full_name is null, it means if we don't input any value for :full_name then we have null is null and this statement is true . So the query will become.

SELECT * FROM customers c  
WHERE true;

• The Sql statement above is correct and it is not broken with the WHERE clause.
• Go back to the example that we discussed before, so to to expose an api for getting customers base on fullName, email, address and phone columns. We will have the native query statement below.

SELECT * FROM customers c  
WHERE (:full_name is null or c.full_name = :full_name)  
AND (:email is null or c.email = :email)  
AND (:address is null or c.address = :address)  
AND (:phone is null or c.phone = :phone);

• So if we only input the value for email then we have the sql statement.

SELECT * FROM customers c  
WHERE true  
AND c.email = 'abcxyz@example.com')  
AND true
AND true;

• Then now, the database will execute the query for filtering with only email other null values will be ignore.
• Or if we only input email and full_name then we have.

SELECT * FROM customers c  
WHERE c.full_name = 'Duc Nguyen'
AND c.email = 'abcxyz@example.com'
AND true
AND true;

the database will execute the query for filtering with only full_name and email, other null values will be ignore.

Example Project

• Now, let's take an example for applying Indexing with JPA.

Dependency

• Let's create an sample project and add these dependencies below.

    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>3.1.4</version>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
            <version>3.1.4</version>
        </dependency>

        <!-- postgresql driver -->
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.6.0</version>
        </dependency>


        <!-- lombok -->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.28</version>
            <scope>provided</scope>
        </dependency>

        <!-- Apache commons-lang3 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.13.0</version>
        </dependency>

        <!-- slf4j -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>2.0.9</version>
        </dependency>

        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-simple</artifactId>
            <version>2.0.9</version>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.openapitools</groupId>
            <artifactId>jackson-databind-nullable</artifactId>
            <version>0.2.6</version>
        </dependency>

        <dependency>
            <groupId>jakarta.validation</groupId>
            <artifactId>jakarta.validation-api</artifactId>
            <version>3.0.2</version>
        </dependency>

        <dependency>
            <groupId>io.swagger.core.v3</groupId>
            <artifactId>swagger-annotations</artifactId>
            <version>2.2.16</version>
        </dependency>

        <dependency>
            <groupId>org.hibernate.validator</groupId>
            <artifactId>hibernate-validator</artifactId>
            <version>8.0.1.Final</version>
        </dependency>

        <dependency>
            <groupId>org.mapstruct</groupId>
            <artifactId>mapstruct</artifactId>
            <version>1.5.5.Final</version>
        </dependency>
    </dependencies>

• Then let's apply the plugin below for OpenApi generator and Mapstruct

 <plugins>
    <plugin>
        <groupId>org.openapitools</groupId>
        <artifactId>openapi-generator-maven-plugin</artifactId>
        <version>7.0.1</version>
        <executions>
            <execution>
                <goals>
                    <goal>generate</goal>
                </goals>
                <configuration>
                    <!-- path to the openapi file spec `.yml` -->
                    <inputSpec>
                        ${project.basedir}/src/main/resources/openapi/openapi-server.yml
                    </inputSpec>
                    <generatorName>spring</generatorName>
                    <!-- generated package for api interface -->
                    <apiPackage>com.springboot.project.generated.api</apiPackage>
                    <!-- generated package for models -->
                    <modelPackage>com.springboot.project.generated.model</modelPackage>
                    <!-- using supportingFilesToGenerate -->
                    <supportingFilesToGenerate>
                        ApiUtil.java
                    </supportingFilesToGenerate>
                    <configOptions>
                        <useTags>true</useTags>
                        <delegatePattern>true</delegatePattern>
                        <dateLibrary>java8</dateLibrary>
                        <java8>false</java8>
                        <interfaceOnly>true</interfaceOnly>
                        <useBeanValidation>true</useBeanValidation>
                        <performBeanValidation>true</performBeanValidation>
                        <useOptional>false</useOptional>
                        <useSpringBoot3>true</useSpringBoot3>
                    </configOptions>
                </configuration>
            </execution>
        </executions>
    </plugin>
    <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>3.11.0</version>
        <configuration>
            <source>17</source>
            <target>17</target>
            <annotationProcessorPaths>
                <path>
                    <groupId>org.mapstruct</groupId>
                    <artifactId>mapstruct-processor</artifactId>
                    <version>1.5.5.Final</version>
                </path>
                <path>
                    <groupId>org.projectlombok</groupId>
                    <artifactId>lombok</artifactId>
                    <version>1.18.28</version>
                </path>
                <path>
                    <groupId>org.projectlombok</groupId>
                    <artifactId>lombok-mapstruct-binding</artifactId>
                    <version>0.2.0</version>
                </path>
            </annotationProcessorPaths>
        </configuration>
        </plugin>
</plugins>

OpenApi

• Now, in the folder resource, we will create a folder openapi and a openapi-server.yaml inside it. The content of this file will look like below with some apis.

openapi: 3.0.3
info:
  title: Swagger Customer - OpenAPI 3.0
  description: Everything about sample hibernate second level cache
  termsOfService: http://swagger.io/terms/
  contact:
    email: apiteam@swagger.io
  license:
    name: Apache 2.0
    url: http://www.apache.org/licenses/LICENSE-2.0.html
  version: 1.0.11
externalDocs:
  description: Find out more about Swagger
  url: http://swagger.io
servers:
  - url: https://petstore3.swagger.io/api/v1
tags:
  - name: customer
    description: Everything about your Customer
    externalDocs:
      description: Find out more
      url: http://swagger.io
  - name: order
    description: Everything about your Order
    externalDocs:
      description: Find out more
      url: http://swagger.io
paths:
  /v1/customers:
    get:
      tags:
        - customer
      summary: get an existing customer info
      description: get an existing customer info by Email
      operationId: getCustomerInfoByEmail
      parameters:
        - name: email
          in: query
          required: true
          schema:
            type: string
      responses:
        '200':
          description: Successful operation
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/CustomerResponse'
        '400':
          description: Invalid ID supplied
        '404':
          description: Pet not found
        '405':
          description: Validation exception
    post:
      tags:
        - customer
      summary: Add a Customer to database
      description: Add a Customer to database
      operationId: addCustomer
      requestBody:
        description: Create a Customer to database
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/CustomerRequest'
        required: true
      responses:
        '200':
          description: Successful operation
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/CustomerResponse'
        '405':
          description: Invalid input
  /v1/customers/{customerId}/info:
    get:
      tags:
        - customer
      summary: get an existing customer info
      description: get an existing customer info by Id
      operationId: getCustomerInfo
      parameters:
        - name: customerId
          in: path
          required: true
          schema:
            type: string
            format: uuid
      responses:
        '200':
          description: Successful operation
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/CustomerResponse'
        '400':
          description: Invalid ID supplied
        '404':
          description: Pet not found
        '405':
          description: Validation exception
    put:
      tags:
        - customer
      summary: Update an existing customer
      description: Update an existing customer by Id
      operationId: updateCustomers
      parameters:
        - name: customerId
          in: path
          required: true
          schema:
            type: string
            format: uuid
      requestBody:
        description: Update an existent customer in the database
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/CustomerResponse'
        required: true
      responses:
        '200':
          description: Successful operation
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/CustomerResponse'
        '400':
          description: Invalid ID supplied
        '404':
          description: Pet not found
        '405':
          description: Validation exception
  /v1/customers/action-filter:
    post:
      tags:
        - customer
      summary: filter customers
      description: filter customers
      operationId: filterCustomers
      requestBody:
        description: Update an existent customer in the database
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/CustomerFilterRequest'
        required: true
      responses:
        '200':
          description: Successful operation
          content:
            application/json:
              schema:
                type: array
                items:
                  $ref: '#/components/schemas/CustomerResponse'
        '400':
          description: Invalid ID supplied
        '404':
          description: Pet not found
        '405':
          description: Validation exception

components:
  schemas:
    CustomerResponse:
      allOf:
        - $ref: '#/components/schemas/CustomerRequest'
        - type: object
          properties:
            id:
              type: string
              format: uuid

    CustomerRequest:
      type: object
      required:
        - email
        - phone
      properties:
        fullName:
          type: string
        email:
          type: string
        address:
          type: string
        phone:
          type: string
        gender:
          type: string
          enum:
            - M
            - F
        dob:
          type: string
          format: date

    CustomerFilterRequest:
      type: object
      properties:
        fullName:
          type: string
        email:
          type: string
        address:
          type: string
        phone:
          type: string

• Now, we can use command mvn clean install to build and generate apis and model from openapi-server.yaml.

Entity

• Then let's define a CustomerEnitty as below.

package com.springboot.project.entity;

import jakarta.persistence.Entity;
import jakarta.persistence.EnumType;
import jakarta.persistence.Enumerated;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;
import jakarta.persistence.Index;
import jakarta.persistence.Table;
import lombok.Getter;
import lombok.Setter;

import java.util.Date;
import java.util.UUID;

@Getter
@Setter
@Entity
@Table(name = "customers", indexes = {
        @Index(name = "uniqueEmailIndex", columnList = "email", unique = true),
        @Index(name = "uniquePhoneIndex", columnList = "phone", unique = true),
        @Index(name = "uniqueMultiIndex", columnList = "email, phone", unique = true)
})
public class CustomerEntity {

    @Id
    @GeneratedValue(strategy = GenerationType.UUID)
    private UUID id;
    private String fullName;
    private String email;
    private String address;
    private String phone;
    @Enumerated(EnumType.STRING)
    private Gender gender;
    private Date dob;

}

package com.springboot.project.entity;

public enum Gender {

    M, F

}

Repository

• Now, lets create the Repository for the CustomerEntity above.

package com.springboot.project.repository;


import com.springboot.project.entity.CustomerEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;
import java.util.UUID;

@Repository
public interface CustomerRepository extends JpaRepository<CustomerEntity, UUID> {

    Optional<CustomerEntity> findCustomerByEmail(String email);

    @Query(
            value = "  SELECT * FROM customers c                                                      "  +  /*1*/
                    "  WHERE (:full_name is null or c.full_name = :full_name)                         "  +  /*2*/
                    "  AND   (:email is null or c.email = :email)                                     "  +  /*3*/
                    "  AND   (:address is null or c.address = :address)                               "  +  /*4*/
                    "  AND   (:phone is null or c.phone = :phone);                                    ",    /*5*/
            nativeQuery = true
    )
    List<CustomerEntity> filterCustomers(@Param("full_name") String fullName,
                                         @Param("email") String email,
                                         @Param("address") String address,
                                         @Param("phone") String phone);

}

• As you can see in this Repository we will define a method to filter customers using native query with optional params.

Dto

• Next, let's create DTOs for CustomerEntity and CustomerFilterRequest as below.

package com.springboot.project.model;  

import com.springboot.project.entity.Gender;  
import lombok.Getter;  
import lombok.NoArgsConstructor;  
import lombok.Setter;  

import java.util.Date;  
import java.util.UUID;  

@Getter  
@Setter  
@NoArgsConstructor  
public class Customer {  

    private UUID id;  
    private String fullName;  
    private String email;  
    private String address;  
    private String phone;  
    private Gender gender;  
    private Date dob;  

}

package com.springboot.project.model;

import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
public class CustomerFilter {

    private String fullName;
    private String address;
    private String email;
    private String phone;

}

Mapper

• Next, let's create a mapper interface for mapping Customer DTO, Entity and CustomerFilterRequest models using MapStruct.

package com.springboot.project.mapper;

import com.springboot.project.entity.CustomerEntity;
import com.springboot.project.generated.model.CustomerFilterRequest;
import com.springboot.project.generated.model.CustomerRequest;
import com.springboot.project.generated.model.CustomerResponse;
import com.springboot.project.model.Customer;
import com.springboot.project.model.CustomerFilter;
import org.mapstruct.Mapper;
import org.mapstruct.MappingTarget;
import org.mapstruct.factory.Mappers;

import java.util.List;

@Mapper
public interface AutoCustomerMapper {

    AutoCustomerMapper MAPPER = Mappers.getMapper(AutoCustomerMapper.class);

    Customer mapToCustomerFromRequest(CustomerRequest customerRequest);

    Customer mapToCustomer(CustomerEntity customerEntity);

    CustomerEntity mapToCustomerEntity(Customer customer);

    CustomerResponse mapToCustomerResponse(Customer customer);

    void updateCustomerEntity(@MappingTarget CustomerEntity customerEntityTarget, CustomerEntity updateEntity);

    List<CustomerEntity> mapToCustomerEntities(List<Customer> customers);

    List<Customer> mapToCustomers(List<CustomerEntity> customerEntities);

    List<CustomerResponse> mapToCustomerResponses(List<Customer> customers);

    CustomerFilter mapToCustomerFilter(CustomerFilterRequest customerFilterRequest);

}

Service

• Now, with these classes that we defined above, we can create service class for handling logics for CRUD entities.

package com.springboot.project.service;

import com.springboot.project.entity.CustomerEntity;
import com.springboot.project.mapper.AutoCustomerMapper;
import com.springboot.project.model.Customer;
import com.springboot.project.model.CustomerFilter;
import com.springboot.project.repository.CustomerRepository;
import lombok.AllArgsConstructor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;
import java.util.Optional;
import java.util.UUID;

@Service
@AllArgsConstructor(onConstructor = @__(@Autowired))
public class CustomerService {

    private final CustomerRepository customerRepository;

    public Customer createCustomer(Customer customer) {
        CustomerEntity customerEntity = AutoCustomerMapper.MAPPER.mapToCustomerEntity(customer);
        customerEntity = this.customerRepository.save(customerEntity);
        return AutoCustomerMapper.MAPPER.mapToCustomer(customerEntity);
    }

    public Customer getCustomer(UUID customerId) {
        Optional<CustomerEntity> customerEntity = this.customerRepository.findById(customerId);
        if (customerEntity.isPresent()) {
            return AutoCustomerMapper.MAPPER.mapToCustomer(customerEntity.get());
        }
        throw new RuntimeException("Customer Not Found!");
    }

    public List<Customer> filterCustomers(CustomerFilter customerFilter) {
        List<CustomerEntity> foundCustomers = this.customerRepository
                .filterCustomers(
                        customerFilter.getFullName(),
                        customerFilter.getEmail(),
                        customerFilter.getAddress(),
                        customerFilter.getPhone());
        return AutoCustomerMapper.MAPPER.mapToCustomers(foundCustomers);
    }

    public void updateCustomer(UUID customerId, Customer customer) {
        Optional<CustomerEntity> customerEntity = this.customerRepository.findById(customerId);
        if (customerEntity.isPresent()) {
            CustomerEntity existedCustomerEntity = customerEntity.get();
            CustomerEntity updateCustomerEntity = AutoCustomerMapper.MAPPER.mapToCustomerEntity(customer);
            AutoCustomerMapper.MAPPER.updateCustomerEntity(existedCustomerEntity, updateCustomerEntity);
            this.customerRepository.save(existedCustomerEntity);
            return;
        }
        throw new RuntimeException("Customer Not Found!");
    }

    public void deleteCustomer(UUID customerId) {
        this.customerRepository.deleteById(customerId);
    }

    public Customer findCustomerByEmail(String email) {
        Optional<CustomerEntity> customerEntity = this.customerRepository.findCustomerByEmail(email);
        if (customerEntity.isPresent()) {
            return AutoCustomerMapper.MAPPER.mapToCustomer(customerEntity.get());
        }
        throw new RuntimeException("Customer Not Found! with email: " + email);
    }

}

Controller

• Now, we can create some basic controllers and implement the generated apis.

package com.springboot.project.controller;

import com.springboot.project.generated.api.CustomerApi;
import com.springboot.project.generated.model.CustomerFilterRequest;
import com.springboot.project.generated.model.CustomerRequest;
import com.springboot.project.generated.model.CustomerResponse;
import com.springboot.project.mapper.AutoCustomerMapper;
import com.springboot.project.model.Customer;
import com.springboot.project.model.CustomerFilter;
import com.springboot.project.service.CustomerService;
import lombok.AllArgsConstructor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;
import java.util.UUID;

@RestController
@AllArgsConstructor(onConstructor = @__(@Autowired))
public class CustomerController implements CustomerApi {

    private final CustomerService customerService;

    @Override
    public ResponseEntity<CustomerResponse> addCustomer(CustomerRequest customerRequest) {
        Customer customer = AutoCustomerMapper.MAPPER.mapToCustomerFromRequest(customerRequest);
        customer = this.customerService.createCustomer(customer);
        CustomerResponse customerResponse = AutoCustomerMapper.MAPPER.mapToCustomerResponse(customer);
        return new ResponseEntity<>(customerResponse, HttpStatus.CREATED);
    }

    @Override
    public ResponseEntity<CustomerResponse> getCustomerInfo(UUID customerId) {
        Customer customer = this.customerService.getCustomer(customerId);
        CustomerResponse customerResponse = AutoCustomerMapper.MAPPER.mapToCustomerResponse(customer);
        return new ResponseEntity<>(customerResponse, HttpStatus.OK);

    }

    @Override
    public ResponseEntity<CustomerResponse> getCustomerInfoByEmail(String email) {
        Customer customer = this.customerService.findCustomerByEmail(email);
        CustomerResponse customerResponse = AutoCustomerMapper.MAPPER.mapToCustomerResponse(customer);
        return new ResponseEntity<>(customerResponse, HttpStatus.OK);
    }

    @Override
    public ResponseEntity<List<CustomerResponse>> filterCustomers(CustomerFilterRequest customerFilterRequest) {
        CustomerFilter customerFilter = AutoCustomerMapper.MAPPER.mapToCustomerFilter(customerFilterRequest);
        List<Customer> customers = this.customerService.filterCustomers(customerFilter);
        List<CustomerResponse> customerResponses = AutoCustomerMapper.MAPPER.mapToCustomerResponses(customers);
        return new ResponseEntity<>(customerResponses, HttpStatus.OK);
    }

}

Testing

• Now, let's start the application then use the postman and with the request body as below.

{
    "fullName": "{{$randomFullName}}",
    "email": "{{$randomExampleEmail}}",
    "address": "{{$randomStreetAddress}}",
    "phone": "{{$randomPhoneNumber}}",
    "gender": "M",
    "dob": "1995-10-10"
}

• Postman supports us to random some common fields every time we send the request body.
• Now, let's run the api CreateCustomer for 10000 times. Then we will have 10000 records in the customers table.

• Next, let's continue use postman for filterCustomers api contain the body as below.

{
    "fullName": "Katrina Kerluke",
    "address": null,
    "email": null,
    "phone": null
}

• We will filter customers base on fullName only. Then we will have the result as below.

• Now, let's try with pushing more address into the filter.

See Also

• SB 3x JPA With Indexing
• SB 3x JPA Native Query Postgres Full Text Search

References

• Baeldung.com
• Full Source Code