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.
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');- 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=dacksCase 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
}
]- SQL
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 🙂
