Query by Example in Spring Data
We are currently using Spring Data JPA in a project and really love it. Lately we came across a feature we sort of oversaw so far in Spring Data’s excellent documentation and we definitely wanted to share with you.
Before we start, we first have to create some context. We use Spring Data in a Spring Boot based JSF application. We often have the requirement to show data in a Data-Table (for that we use PrimeFaces) based on the field values of a search form. In our code we follow the approach to create an empty domain object in the view class acting as our backing object that provides us with the search parameters.
Here is an example. This is our view class:
As you might notice, the
@ViewScope is a custom component that has been developed on our side. It basically works the same as
@ViewScoped, only difference is it was implemented based on Spring. The
AnimalRepository is a Spring Data JPA repository and
AnimalDataModel is a PrimeFaces LazyDataModel.
The relation between the
AnimalDataModel and the
AnimalRepository is especially important to notice. The
AnimalRepository being the mediator between the model and the data mapping layer, that is, it creates and executes the DB queries. The
AinmalDataModel has to use the
AnimalRepository for making paged queries. Let’s look at the
AnimalDataModel implementation (teaser: it’s not complete yet):
It’s not necessary for this article to be familar with PrimeFaces
LazyDataModel. It is basically an abstraction allowing for lazy-loaded data table paging. Usually, the paging logic is found in the
load implementation we need to retrieve the page of animals to be displayed and the total number of elements which is necessary in the view to show the correct paging indicators in our GUI. PrimeFaces hands over the offset (
first parameter) and the
pageSize (representing whatever is set in the XHTML/JSP for the
So what we would need to do is to make in fact two database queries. One query for the data of the current page, and another COUNT query resulting in the total number of elements found (the overall number, independent on the current page).
Spring Data has a nice abstraction that is perfect for this use-case: the Page class.
Page represents a sub-set of the total result and it has a
getTotalElements() method returning the number of total elements. What’s even better, the
Page class is directly supported in Spring Data repositories. That means you can use
Page as return type in your self-defined query methods. PagingAndSortingRepository comes with a
findAll variant returning a
Let’s see how we could implement our
load method with that knowledge:
As you can see, the
Page abstraction is perfect for this use-case. We get a sub-list of animals from our repository and can also access the total number of elements.
PageRequest gets the page number, not the 0-based offset, as first argument in the constructor. We can’t simply pass the
first argument given by PrimeFaces but have to calculate the current 0-based page number.
There is one down-side with the above approach. We need to create the query based on the data that we get from the
AnimalSearchForm object. We cheated a bit in our example because we queried the
species property only. However, when you have to build more complex queries because you need to incorporate more fields from the search form, we would need to dynamically construct a criteriaq query.
Before we go further, let us have a look at the
AnimalSearchForm has scope
@TabScope, again a custom Spring-scope of our project. Besides that, the class only has a single property with an empty
Animal instance. In the UI, the data from the search form is bound against that object:
Long story short, for such a use-case what we really want is to define a query based on domain object properties. We do not want to dig into complex dynamic criteria construction in our
load() method implementation. But guess what, Spring Data JPA comes with a solution for that.
Query by Example API
Query by Example (QBE) is a pattern targeting to query the database without ever even constructing a query. The query is enitrely generated based on a given example instance. Let’s say I wanted to query for
Animal instances, I could provide a probe
Animal instance that has some properties set. Those properties will be take into account as query parameters during the automatic query generation that is done by Spring Data in the background.
If a Spring Data repository wants to support QBE, it needs to extend the QueryByExampleExecutor interface. Once the repository descends from this interface, it can use common query methods like
count etc. all taking an
Example instance as an argument.
With this knowledge we can rewrite our example from above to
We removed the part that directly accessed the
form.getAnimal().getSpecies() property with the creation of the
Example instance. With this instance, the
findAll() method (from
QueryByExampleExecutor) is called together with a
PageRequest to find the approriate animals. As you see, when we add another field to our search form in the UI, there are nearly no changes necessary in the
The only thing to consider is the
ExampleMatcher instance that is used to create the
Example instance. In our case, we wanted to ignore null properties from our
Animal instance. We also wanted to support a case-insensitive search. All those query-related requirements need to be specified when constructing the
ExampleMatcher, that’s exactly its purpose.
species property we can use a more advanced matcher configuration, all queries will automatically be appended with ‘%’. We chose the Java 8 lambda syntax for that, an alternative way is to use the methods defined in ExampleMatcher.GenericPropertyMatchers to do exactly that:
This article showed how to use the Query By Example (QBE) API in Spring Data. The QBE pattern can come in handy for certain use-cases that require to run queries with query parameters based on a given domain object. In this article we showed a real-world use-case based on PrimeFaces LazyDataModels.