Query by Example

Spring Data JPA supports multiple techniques for fetching data from a database. In addition to Query Methods, Jakarta Persistence Query Language, and native SQL, it also provides support for Query by Example. This topic will focus on the Query by Example technique and how it is utilized with Spring Data JPA.

What is it?

Query by Example (QBE) is a user-friendly querying technique with a straightforward interface. It facilitates the creation of dynamic queries without the need of explicitly writing out field names, which allows for the retrieval of objects by their specific properties.

The main concept behind QBE involves creating an org.springframework.data.domain.Example object to be used by passing it to repository methods.

Example is actually an interface, and the actual concrete class that should be used is org.springframework.data.domain.TypedExample.

The Example object consists of two parts: the entity object, also known as the probe, and the ExampleMatcher object. The ExampleMatcher object determines how the Example object should be matched, offering options to ignore specific properties, consider case sensitivity, and more.

It's important to understand that the probe is the JPA entity containing the properties to be matched. The probe can be a simple entity or a more complex one. By default, fields with null values are disregarded, and string matching follows the defaults specified by the data store. The inclusion of properties in the QBE criteria depends on their nullability. Properties of primitive types are always included unless explicitly ignored by ExampleMatcher.

The ExampleMatcher object defines how the probe should be matched. It can be customized to ignore certain properties, consider case sensitivity, and more. Moreover, it can be reused across multiple Example objects.

The Example object is a combination of the probe and the ExampleMatcher object. It is passed to the repository methods to retrieve data. It's important to note that the Example object is immutable. To create Example instances, we can use the Example.of() factory method. It's worth mentioning that two variations of the Example.of() method are available: one that requires only the probe as a parameter and another that needs both the probe and the ExampleMatcher object. The former method creates an ExampleMatcher object with default settings.

How does it work?

Let's assume we have an entity class named Person with the following properties:

@Entity
public class Person {
    @Id
    @GeneratedValue
    private int id;
    private String firstName;
    private String lastName;
    private int age;
    // ... getters and setters omitted
}

Here is how we can create a probe and an Example.

// this is the probe
Person person = new Person();
person.setFirstName("Jane");

Example<Person> example = Example.of(person);

In this case, the ExampleMatcher object is created with the default settings. This means that the ExampleMatcher object will include firstName in the query, as it is non-null, as well as id and age because they are primitive types. It will, on the other hand, ignore lastName because it is null. Note that the primitive type is included with its default value, which in this case is 0. Also, note that by default, string matching is case-sensitive.

If the default settings are not enough, we can create an ExampleMatcher object and pass it to the Example.of() factory method.

Person person = new Person();
person.setFirstName("Jane");

ExampleMatcher matcher = ExampleMatcher.matching().withIgnorePaths("id", "age");

Example<Person> example = Example.of(person, matcher);

In this case, the ExampleMatcher object will ignore the id and age fields because we have explicitly directed it to do so. It will also exclude the lastName field because it is null. Consequently, the query will only consider the firstName field, case-sensitive.

Use cases and limitations

QBE is suitable for several use cases, including:

• Querying the data store with a set of static or dynamic constraints.

• Allowing for frequent refactoring of domain objects without concerns about breaking existing queries.

• Working independently of the underlying data store API.

However, QBE also has some limitations:

• It does not support nested or grouped property constraints, such as firstname = ?0 or (firstname = ?1 and lastname = ?2).

• It only supports exact, starts, contains, ends, and regex matching for strings, and exact matching for other property types.

More on matching the probe

Examples are not limited to default settings. We can specify our defaults for string matching, null handling, and property-specific settings by using the ExampleMatcher. The org.springframework.data.domain.ExampleMatcher is an interface that provides several methods to customize the matching behavior. Its default implementation is org.springframework.data.domain.TypedExampleMatcher.

Instances of ExampleMatcher can be either matchingAll() or matchingAny(), with settings that can be tuned using the with... methods in a fluent style. The with... methods return a copy of the ExampleMatcher instance with the specified setting.

The following table lists some of the ExampleMatcher methods accompanied by brief descriptions.

matching()

matchingAll()

withIgnoreCase()

withIgnoreCase(boolean)

Query by example in action

Now that we have covered the basics of Query by Example and its integration with Spring Data JPA, let's delve deeper into its inner workings.

Previously, we mentioned that once we have an Example object, we can pass it to repository methods to retrieve data. But where do these methods come from? This is where the org.springframework.data.repository.query.QueryByExampleExecutor interface comes into play. The interface org.springframework.data.jpa.repository.JpaRepository inherits from QueryByExampleExecutor, which allows us to implement custom repositories that extend it, thereby providing access to the QBE methods.

The QueryByExampleExecutor<T> interface offers the following methods:

<S extends T> Optional<S> findOne(Example<S> example)
<S extends T> Iterable<S> findAll(Example<S> example)
<S extends T> Iterable<S> findAll(Example<S> example, Sort sort)
<S extends T> Page<S> findAll(Example<S> example, Pageable pageable)
<S extends T> long count(Example<S> example)
<S extends T> boolean exists(Example<S> example)
<S extends T, R> R findBy(
    Example<S> example, Function<FluentQuery.FetchableFluentQuery<S>, R> queryFunction
)

These methods are self-explanatory. They all accept an Example object as a parameter, and some also accommodate additional parameters like Sort and Pageable. The last method is slightly different, as it takes an Example object and a Function as parameters. With the Function argument, we can control various aspects of the query execution derived from the Example. We achieve this by invoking different methods of FetchableFluentQuery passed as the second argument:

• sortBy allows you to specify the ordering of the result.

• as lets you specify the type to which you want the result to be transformed.

• project limits the queried attributes.

• first, firstValue, one, oneValue, all, page, stream, count, and exists define the type of result you want and dictate how the query should behave when more results than expected are available.

Let's take a look at some examples of how we can use the QueryByExampleExecutor interface.

Let's assume we have the following data in our database. Our database contains just the table person.

insert into person(id, first_name, last_name, age) values(1, 'John', 'Doe', 39);
insert into person(id, first_name, last_name, age) values(2, 'Jane', 'Doe', 36);
insert into person(id, first_name, last_name, age) values(3, 'Paul', null, 25);
insert into person(id, first_name, last_name, age) values(4, 'Mark', 'Smith', 29);

We first need to create a repository that extends the QueryByExampleExecutor interface.

@Repository
public interface PersonRepository extends
    CrudRepository<Person, Integer>,
    QueryByExampleExecutor<Person> {}

Simply extending the QueryByExampleExecutor is not enough, as it does not allow for the creation of a bean of type PersonRepository. By also extending the CrudRepository, we go around that problem and also get access to generic Create, Read, Update, and Delete operations.

As we do not need the operations provided by the JpaRepository, we'll allow our interface to extend only the other two.

The following code will retrieve a Person entity that matches the following criteria:

• firstName is paul matched exactly, case-insensitive

• lastName is null

• id, age are ignored

public Optional<Person> findPersonByFirstName() {
    var person = new Person();
    person.setFirstName("paul");
    var matcher = ExampleMatcher.matching()
            .withIgnorePaths("id", "age")
            .withNullHandler(ExampleMatcher.NullHandler.INCLUDE)
            .withStringMatcher(ExampleMatcher.StringMatcher.EXACT)
            .withIgnoreCase();
    Example<Person> example = Example.of(person, matcher);
    return personRepository.findOne(example);
}

The SQL generated is as follows:

select
    p1_0.id,
    p1_0.age,
    p1_0.first_name,
    p1_0.last_name 
from
    person p1_0 
where
    lower(p1_0.first_name)=? 
    and p1_0.last_name is null 
fetch
    first ? rows only

If we look carefully at the generated SQL, we can identify the matcher configuration: id and age are not in the where clause, last name has to be null, first name is matched exactly and case-insensitive.

Note that p1_0 is the auto-generated alias given to the person table.

The last line specifies the number of initial rows to be returned, which is one in our case.

The object returned will be: Person{id=3, firstName='Paul', lastName='null', age=25}

Let's take a look at another example where we aim to retrieve all Person entities matching the following criteria:

• firstName starts with MA, case-insensitive

• lastName ends with OE, case-insensitive

• results are ordered by age in ascending order

public List<Person> findAllPeople() {
    var probe = new Person();
    probe.setFirstName("MA");
    probe.setLastName("OE");
    var startsWithIgnoreCase = ExampleMatcher.GenericPropertyMatchers
            .startsWith().ignoreCase();
    var endsWithIgnoreCase = ExampleMatcher.GenericPropertyMatchers
            .endsWith().ignoreCase();
    var example = Example.of(probe,
            ExampleMatcher.matchingAny()
                    .withIgnorePaths("id", "age")
                    .withMatcher("firstName", startsWithIgnoreCase)
                    .withMatcher("lastName", endsWithIgnoreCase)
    );
    return personRepository.findBy(example,
            q -> q
                    .sortBy(Sort.by("age").ascending())
                    .all()
    );
}

The SQL generated is as follows:

select
    p1_0.id,
    p1_0.age,
    p1_0.first_name,
    p1_0.last_name 
from
    person p1_0 
where
    lower(p1_0.last_name) like ? escape '\' 
    or lower(p1_0.first_name) like ? escape '\' 
order by
    p1_0.age

Once again, a careful examination reveals the matcher settings: id and age are not present in the where clause, and both last name and first name are matched case-insensitive using the like operator. Results are sorted by age in ascending order.

The objects returned will be:

Person{id=4, firstName='Mark', lastName='Smith', age=29}
Person{id=2, firstName='Jane', lastName='Doe', age=36}
Person{id=1, firstName='John', lastName='Doe', age=39}

Conclusion

In this topic, we have covered the basics of Query by Example and its application with Spring Data JPA. We have also seen how we can customize the matching behavior of the Example object through the ExampleMatcher object. Finally, we delved into the use of the QueryByExampleExecutor interface for data retrieval from a database.