Spring Data JPA Query by Example

1. Introduction

In this tutorial, we’ll learn how to query data with the Spring Data Query by Example API.

First, we’ll define the schema of the data we want to query. Next, we’ll examine a few of the relevant classes from Spring Data. Finally, we’ll run through a few examples.

Let’s get started!

2. The Test Data

Our test data is a list of passenger names, as well as the seat they occupied:

3. Domain

Let’s create the Spring Data Repository we’ll need, and provide our domain class and id type.

To begin, we’ll model our Passenger as a JPA entity:

@Entity
class Passenger {

    @Id
    @GeneratedValue
    @Column(nullable = false)
    private Long id;

    @Basic(optional = false)
    @Column(nullable = false)
    private String firstName;

    @Basic(optional = false)
    @Column(nullable = false)
    private String lastName;

    @Basic(optional = false)
    @Column(nullable = false)
    private int seatNumber;

    // constructor, getters etc.
}

Instead of using JPA, we could’ve modeled it as another abstraction.

4. Query by Example API

First, let’s take a look at the JpaRepository interface. As we can see, it extends the QueryByExampleExecutor interface to support query by example:

public interface JpaRepository<T, ID>
  extends PagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T> {}

This interface introduces more variants of the find() method that we’re familiar with from Spring Data. However, each method also accepts an instance of Example:

public interface QueryByExampleExecutor<T> {
    <S extends T> Optional<S> findOne(Example<S> var1);
    <S extends T> Iterable<S> findAll(Example<S> var1);
    <S extends T> Iterable<S> findAll(Example<S> var1, Sort var2);
    <S extends T> Page<S> findAll(Example<S> var1, Pageable var2);
    <S extends T> long count(Example<S> var1);
    <S extends T> boolean exists(Example<S> var1);
}

Next, the Example interface exposes methods to access the probe and the ExampleMatcher.

It’s important to realize that the probe is the instance of our Entity:

public interface Example<T> {

    static <T> org.springframework.data.domain.Example<T> of(T probe) {
        return new TypedExample(probe, ExampleMatcher.matching());
    }

    static <T> org.springframework.data.domain.Example<T> of(T probe, ExampleMatcher matcher) {
        return new TypedExample(probe, matcher);
    }

    T getProbe();

    ExampleMatcher getMatcher();

    default Class<T> getProbeType() {
        return ProxyUtils.getUserClass(this.getProbe().getClass());
    }
}

In short, our probe and ExampleMatcher together specify our query.

5. Limitations

Like all things, the Query by Example API has some limitations:

• Nesting and grouping statements aren’t supported. For example:  (firstName = ?0 and lastName = ?1) or seatNumber = ?2
• String matching only includes exact, case-insensitive, starts, ends, contains, and regex
• All types other than String are exact-match only

Now that we’re a little more familiar with the API and its limitations, let’s dive into some examples.

6. Examples

6.1. Case-Sensitive Matching

Let’s start with a simple example, and talk about the default behavior:

@Test
public void givenPassengers_whenFindByExample_thenExpectedReturned() {
    Example<Passenger> example = Example.of(Passenger.from("Fred", "Bloggs", null));

    Optional<Passenger> actual = repository.findOne(example);

    assertTrue(actual.isPresent());
    assertEquals(Passenger.from("Fred", "Bloggs", 22), actual.get());
}

In particular, the static Example.of() method builds an Example using ExampleMatcher.matching().

In other words, an exact match will be performed on all non-null properties of Passenger. Thus, the matching is case-sensitive on String properties.

However, it wouldn’t be too useful if all we could do was an exact match on all non-null properties.

This is where the ExampleMatcher comes in. By building our own ExampleMatcher, we can customize the behavior to suit our needs.

6.2. Case-Insensitive Matching

With that in mind, let’s have a look at another example, this time using withIgnoreCase() to achieve case-insensitive matching:

@Test
public void givenPassengers_whenFindByExampleCaseInsensitiveMatcher_thenExpectedReturned() {
    ExampleMatcher caseInsensitiveExampleMatcher = ExampleMatcher.matchingAll().withIgnoreCase();
    Example<Passenger> example = Example.of(Passenger.from("fred", "bloggs", null),
      caseInsensitiveExampleMatcher);

    Optional<Passenger> actual = repository.findOne(example);

    assertTrue(actual.isPresent());
    assertEquals(Passenger.from("Fred", "Bloggs", 22), actual.get());
}

In this example, notice that we first called ExampleMatcher.matchingAll(); it has the same behavior as ExampleMatcher.matching(), which we used in the previous example.

6.3. Custom Matching

We can also tune the behavior of our matcher on a per-property basis and match any property using ExampleMatcher.matchingAny():

@Test
public void givenPassengers_whenFindByExampleCustomMatcher_thenExpectedReturned() {
    Passenger jill = Passenger.from("Jill", "Smith", 50);
    Passenger eve = Passenger.from("Eve", "Jackson", 95);
    Passenger fred = Passenger.from("Fred", "Bloggs", 22);
    Passenger siya = Passenger.from("Siya", "Kolisi", 85);
    Passenger ricki = Passenger.from("Ricki", "Bobbie", 36);

    ExampleMatcher customExampleMatcher = ExampleMatcher.matchingAny()
      .withMatcher("firstName", ExampleMatcher.GenericPropertyMatchers.contains().ignoreCase())
      .withMatcher("lastName", ExampleMatcher.GenericPropertyMatchers.contains().ignoreCase());

    Example<Passenger> example = Example.of(Passenger.from("e", "s", null), customExampleMatcher);

    List<Passenger> passengers = repository.findAll(example);

    assertThat(passengers, contains(jill, eve, fred, siya));
    assertThat(passengers, not(contains(ricki)));
}

6.4. Ignoring Properties

We may also only want to query on a subset of our properties.

We achieve this by ignoring some properties using ExampleMatcher.ignorePaths(String… paths):

@Test
public void givenPassengers_whenFindByIgnoringMatcher_thenExpectedReturned() {
    Passenger jill = Passenger.from("Jill", "Smith", 50); 
    Passenger eve = Passenger.from("Eve", "Jackson", 95); 
    Passenger fred = Passenger.from("Fred", "Bloggs", 22);
    Passenger siya = Passenger.from("Siya", "Kolisi", 85);
    Passenger ricki = Passenger.from("Ricki", "Bobbie", 36);

    ExampleMatcher ignoringExampleMatcher = ExampleMatcher.matchingAny()
      .withMatcher("lastName", ExampleMatcher.GenericPropertyMatchers.startsWith().ignoreCase())
      .withIgnorePaths("firstName", "seatNumber");

    Example<Passenger> example = Example.of(Passenger.from(null, "b", null), ignoringExampleMatcher);

    List<Passenger> passengers = repository.findAll(example);

    assertThat(passengers, contains(fred, ricki));
    assertThat(passengers, not(contains(jill));
    assertThat(passengers, not(contains(eve)); 
    assertThat(passengers, not(contains(siya)); 
}

7. Conclusion

In this article, we demonstrated how to use the Query by Example API.

We learned how to use Example and ExampleMatcher, along with the QueryByExampleExecutor interface to query a table using an example data instance.

As always, all the source code is available over on GitHub.