Spring Data R2DBC Query By Example

vIns

3 years ago

Overview:

In this tutorial, I would like to show you the R2DBC Query By Example API with Spring WebFlux and how we can use this to search for records without exposing multiple methods in our Repository object.

Lets first see what it is exactly before diving into coding.

Query By Example:

Query By Example (QBE) is a method for creating queries dynamically based on the given Example object. For example, lets consider this ‘customer‘ table with the fields as shown here.

first_name	last_name	email	address	country

Lets also assume that this is our entity object which represents a record in our customer table.

public class Customer {
  
  private String firstName;
  private String lastName;
  private String email;
  private String address;
  private String country;
  
}

We might be interested in searching for records with any of the fields or combination of fields etc.

For ex:

Search by last_name.
- If I create an object and set the last name as shown here,

public class Customer {
  
  private String firstName;
  private String lastName = "Jackson";
  private String email;
  private String address;
  private String country;
  
}

- then this will create query as shown below and will fetch the records matching the example. It basically skips ‘null’ fields in the object and construct a query accordingly.

select
    *
from
    customer
where
    last_name = 'Jackson';

Search by first_name and email combination
- Create an object like this

public class Customer {
  
  private String firstName = "Michael";
  private String lastName;
  private String email = "mj@thriller.com";
  private String address;
  private String country;
  
}

- then this will create a SQL like this

select
    *
from
    customer
where
    first_name = 'Michael'
and email = 'mj@thriller.com';

Query By Example – Advantages:

The biggest advantage of ‘Query By Example‘ is, we do NOT have to expose multiple methods in our repository like this. It simplifies the table search by accepting an example object.

@Repository
public interface CustomerRepository {
  
  Flux<Customer> findByFirstName(String firstName);
  Flux<Customer> findByLastName(String lastName);
  Flux<Customer> findByEmail(String email);
  ...
  ...
  Flux<Customer> findByFirstNameAndLastName(String firstName, Stirng lastName);
  ...
  ...  
  
}

If you have a Customer search form with various fields, we do not have to check which field is filled and which method has to be invoked. We can easily build an Example object based on the form details and find the matching records.

Query By Example – Limitations:

It has below limitations.

No support for nested or grouped property constraints, such as firstname = 'michael' or (firstname = 'janet' and lastname = 'jackson').
We can not do any range check
Only supports below string matching
- startsWith
- endsWith
- contains
- regex
- exact

Project Setup:

Lets setup a project to play with R2DBC Query By Example API with the following dependencies.

Create a init.sql under src/main/resources to create the table and insert data.

create table customer (
    id INT auto_increment,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(50),
    country VARCHAR(50),
    is_active Boolean
);

insert into customer (first_name, last_name, email, country, is_active) values
     ('Tara','Wheeliker','twheeliker0@storify.com','Brazil','false'),
     ('Daisy','Klulisek','dklulisek1@constantcontact.com','Peru','true'),
     ('Fredericka','Marusik','fmarusik2@google.ca','China','true'),
     (NULL,'Pinckstone','mpinckstone3@prlog.org','Philippines','true'),
     ('Dela','Dacks','ddacks4@hao123.com','China','false'),
     ('Brita','Doughton','bdoughton5@tmall.com','China','false'),
     ('Jamima','Sorsby','jsorsby6@goo.ne.jp','Poland','true'),
     ('Vincenty','Ianinotti','vianinotti7@freewebs.com','Israel','false'),
     ('Karin','Sollas','ksollas8@cdc.gov','Sweden','false'),
     ('Foss','Bifield','fbifield9@statcounter.com','Philippines','false');

id	first_name	last_name	email	country	is_active
1	Tara	Wheeliker	twheeliker0@storify.com	Brazil	false
2	Daisy	Klulisek	dklulisek1@constantcontact.com	Peru	true
3	Fredericka	Marusik	fmarusik2@google.ca	China	true
4		Pinckstone	mpinckstone3@prlog.org	Philippines	true
5	Dela	Dacks	ddacks4@hao123.com	China	false
6	Brita	Doughton	bdoughton5@tmall.com	China	false
7	Jamima	Sorsby	jsorsby6@goo.ne.jp	Poland	true
8	Vincenty	Ianinotti	vianinotti7@freewebs.com	Israel	false
9	Karin	Sollas	ksollas8@cdc.gov	Sweden	false
10	Foss	Bifield	fbifield9@statcounter.com	Philippines	false

Create an entity object

@Data
@ToString
@NoArgsConstructor
public class Customer {

    @Id
    private Integer id;
    private String firstName;
    private String lastName;
    private String email;
    private String country;
    private Boolean isActive;

}

Create a repository – Note that it also extends ReactiveQueryByExampleExecutor.

@Repository
public interface CustomerRepository extends ReactiveCrudRepository<Customer, Integer>, ReactiveQueryByExampleExecutor<Customer> {
}

Create a command line runner which will help us to run the init.sql when the app starts.

@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()
                .subscribe();
    }
    
}

Customer Query Service

@Service
public class CustomerQueryService {

    @Autowired
    private CustomerRepository repository;

    public Flux<Customer> search(Customer customer){
        return this.repository.findAll(Example.of(customer));
    }
    
}

The rest controller

@RestController
@RequestMapping("customer")
public class CustomerController {

    @Autowired
    private CustomerQueryService queryService;

    @GetMapping("search")
    public Flux<Customer> search(Customer customerSearchCriteria){
        return this.queryService.search(customerSearchCriteria);
    }

}

Lets start the application and see how it works.

R2DBC Query By Example – Demo:

Exact Match:

Case 1: Search by first name
- Request:

http://localhost:8080/customer/search?firstName=Tara

- Response

[
   {
      "id":1,
      "firstName":"Tara",
      "lastName":"Wheeliker",
      "email":"twheeliker0@storify.com",
      "country":"Brazil",
      "isActive":false
   }
]

Case 2: Search by first name & last name
- Request:

http://localhost:8080/customer/search?firstName=Tara&lastName=Dacks

- Response (no results matching the given example)

[]

- Request

http://localhost:8080/customer/search?firstName=Dela&lastName=Dacks

- Response

[
   {
      "id":5,
      "firstName":"Dela",
      "lastName":"Dacks",
      "email":"ddacks4@hao123.com",
      "country":"China",
      "isActive":false
   }
]

This is the default behavior of this API. Constructing search query with non-null fields. It does exact match. For ex, the below request will not find any records. Because by default, It is case-sensitive search.

http://localhost:8080/customer/search?firstName=dela&lastName=dacks

Case Insensitive Search:

To do case insensitive search, we need to use ExampleMatcher api as shown below.

public Flux<Customer> search(Customer customer){
    return this.repository.findAll(Example.of(customer, ExampleMatcher.matching().withIgnoreCase()));
}

Request

http://localhost:8080/customer/search?firstName=dela&lastName=dacks

This creates a SQL query as shown below which will bring the record we are looking for.

SELECT *
FROM   customer
WHERE  ( Upper(customer.first_name) = Upper(:firstName) )
       AND ( Upper(customer.last_name) = Upper(:lastName) )

If you want to do case insensitive compare only for a specific field , say last name alone, then we can do this way.

public Flux<Customer> search(Customer customer){
    return this.repository.findAll(Example.of(customer, ExampleMatcher.matching().withIgnoreCase("lastName")));
}

This will create the query as shown helow.

SELECT customer.*
FROM   customer
WHERE  ( customer.first_name = :firstName )
       AND ( Upper(customer.last_name) = Upper(:lastName) )

OR Condition:

The ExampleMatcher.matching() will try to match all non-null fields with AND condition. We can change that ExampleMatcher.matchingAny() to make it as a OR condition.

public Flux<Customer> search(Customer customer){
    return this.repository.findAll(Example.of(customer, ExampleMatcher.matchingAny().withIgnoreCase()));
}

Request

http://localhost:8080/customer/search?firstName=dela&lastName=dacks

This creates a SQL query as shown below. So either first name should match or last name should match.

SELECT *
FROM   customer
WHERE  ( Upper(customer.first_name) = Upper(:firstName) )
       OR ( Upper(customer.last_name) = Upper(:lastName) )

StartsWith / EndsWith / Contains:

To get all the customers whose email id ends with .com, we can try this way.

public Flux<Customer> search(Customer customer){
    ExampleMatcher exampleObjectMatcher = ExampleMatcher.matching()
            .withMatcher("email", ExampleMatcher.GenericPropertyMatchers.endsWith());
    return this.repository.findAll(Example.of(customer, exampleObjectMatcher));
}

Request

http://localhost:8080/customer/search?email=com

It creates a SQL like this

SELECT *
FROM   customer
WHERE  ( customer.email LIKE '%' + :email )

Similarly you can explore starts with , contains, regex options etc.

Include Null Values:

What if the value itself is NULL in the DB? We can try this way to include null values as well.

public Flux<Customer> search(Customer customer){
    return this.repository.findAll(Example.of(customer, ExampleMatcher.matching().withIncludeNullValues()));
}

Request

http://localhost:8080/customer/search?firstName=sam

Response

[
   {
      "id":4,
      "firstName":null,
      "lastName":"Pinckstone",
      "email":"mpinckstone3@prlog.org",
      "country":"Philippines",
      "isActive":true
   }
]

SELECT *
FROM   customer
WHERE  ( customer.first_name IS NULL
          OR customer.first_name = :firstName )

Property Value Transformer:

Sometimes before doing the search, you might have to transform the data. For example, Lets imagine that we store the country name in our table. But on the UI, we have only country codes. (BR for Brazil, CH for China and IN for India…etc).

If I send the below request, it will return 0 records as we do not store our data in the exact format. So It has to be transformed.

http://localhost:8080/customer/search?country=BR

We can do the transformation as shown below. Here for country, if we get ‘BR‘, we look up for the corresponding value in our CountryMap and do the search.

private final Map<String, String> countryMap = Map.of(
        "BR", "Brazil",
        "CH", "China"
);

public Flux<Customer> search(Customer customer){
    ExampleMatcher matcher = ExampleMatcher.matching()
            .withTransformer("country", op -> op.map(c -> countryMap.getOrDefault(c, "UNKNOWN")));
    return this.repository.findAll(Example.of(customer, matcher));
}

Now If I send the request and I get the response.

[
   {
      "id":1,
      "firstName":"Tara",
      "lastName":"Wheeliker",
      "email":"twheeliker0@storify.com",
      "country":"Brazil",
      "isActive":false
   }
]

Summary:

We were able to successfully demonstrate the usage of R2DBC Query By Example API. Without exposing any methods in our Repository, we were able to build queries dynamically by passing the entity object via Example.

Learn more about R2DBC here.

The source code is available here.

Happy Coding 🙂