Write Search Queries Faster Than Ever with QBE 🚀

Hello everyone, I’m Yassine Abidar, a software engineer. In my first blog on Medium, we’ll explore the Spring data JPA Query By Example (QBE) feature — how it works, when to use it, and its limitations.

Photo by Chris Liverani on Unsplash

Spring data JPA QBE Docs :

Query by Example (QBE) is a user-friendly querying technique with a simple interface. It allows dynamic query creation and does not require you to write queries that contain field names. In fact, Query by Example does not require you to write queries by using store-specific query languages at all.

Query by Example

Introduction :

Before QBE, spring and spring data JPA provide a lot of options to query data dynamically such as :

• Using @Query method.
• Using Spring Data JPA Specification.
• Criteria API.

The QBE feature provides a way to decouple data filtering logic from the query processing engine.

It allows clients to dynamically access and fetch data while deciding which attributes to filter on. It provides a flexible and type-safe API without relying on JPA Criteria.

So, let’s learn by doing! I’ll start with a simple example :

1. The entity Model we are going to work with is :

@Getter
@Setter
@Table(name = "delivery_slots")
@Entity
public class DeliverySlotEntity {

 @Id
 @GeneratedValue(strategy = GenerationType.SEQUENCE)
 private Long id; 
 private LocalDate date; // The day of delivery
 private LocalTime startTime;
 private LocalTime endTime;
 @Enumerated(value = EnumType.STRING)
 private ShippingMode shippingMode;
 
 private int maxReservations; // Max clients allowed
 private int currentReservations = 0; // Number of clients booked
 
 public boolean isAvailable() {
  return currentReservations < maxReservations;
 }
}

2. The Repository :
If you are using JpaRepository, it already extends QueryByExampleExecutor. If not, you should extend the QueryByExampleExecutor interface.

@Repository
public interface DeliverySlotRepository extends ListCrudRepository<DeliverySlotEntity, Long>,
  QueryByExampleExecutor<DeliverySlotEntity> {
 
}

3. The service :
layer where we want to search by one of date and shipping mode or both without an extra code :

@Override
 public List<DeliverySlotDto> findByDateAndShippingModeQBE(
   String date,
   ShippingMode shippingMode) {
  

  LocalDate localDate = date != null ? LocalDate.parse(date) : null;
  DeliverySlotEntity exampleDeliverySlotProbe = new DeliverySlotEntity();
  exampleDeliverySlotProbe.setShippingMode(shippingMode);
  exampleDeliverySlotProbe.setDate(localDate);
  
  

  ExampleMatcher matcher = ExampleMatcher.matching()
    .withIgnoreNullValues()
    .withIgnorePaths("maxReservations", "currentReservations");

  
  Example<DeliverySlotEntity> example = Example.of(exampleDeliverySlotProbe, matcher);
  final var deliverySlotEntities = deliverySlotRepository
    .findAll(example);

  return toDto(deliverySlotEntities);
 }

a: In the first block, we are just creating our Probe (an instance of the entity) to use as an example.

b: In the second block, we create an ExampleMatcher, where we want to ignore null values so they are not included in the search. In our entity, there are two attributes, startTime and endTime, which have a default value of null, so we don’t want to search for them. Using “withIgnorePaths”, we tell QBE to ignore these two attributes. However, the default value is 0, not null.

c: We then create our Example<DeliverySlotEntity> using our matcher and probe, and finally, we call findAll(Example<S> example).

4. The Controller :

@GetMapping(value = ApiPath.V1)
 public List<DeliverySlotDto> findByDateAndShippingModeQBE(
     @RequestParam(required = false) String date,
     @RequestParam(required = false) ShippingMode shippingMode) {

  logger.info("Start find delivery slot by user shipping mode for
           date {} and shipping mode {}",
    date, shippingMode);

  final var byUserShippingModeQBE = deliverySlotService.
  findByDateAndShippingModeQBE(date, shippingMode);

  logger.info("End find delivery slot by user shipping mode for date 
   {} and shipping mode {}",
    date, shippingMode);

  return byUserShippingModeQBE;
 }

We can add pagination and sorting using the method available in the QueryByExampleExecutor interface:

<S extends T> Page<S> findAll(Example<S> example, Pageable pageable);

The ExampleMatcher provide a lot of matching strategy with String like

EXACT : Matches the exact string.
STARTING / ENDING : Matches string starting / ending with pattern.
CONTAINING : Matches string containing pattern.
REGEX : Treats strings as regular expression patterns.

For example the ExampleMatcher is configured to match users where:

• The firstname ends with the given value.
• The lastname starts with the given value, ignoring case sensitivity.

ExampleMatcher matcher = ExampleMatcher.matching()
  .withMatcher("firstname", endsWith())
  .withMatcher("lastname", startsWith().ignoreCase());
}

This should return all users whose firstname ends with the specified value and whose lastname starts with the specified value, regardless of case sensitivity.

QBE is great for simple dynamic queries, but for complex filtering (nested conditions, collections, range queries, etc.), you should use JPQL, Criteria API, or Spring Data Specifications.

For more details, dive into the documentation! Catch you in the next blog stay tuned for more 🚀!