JPA Search using Spring Data Example
A very common use case is to easily find all or a subset of records from a database, often in combination with paging so that a user can iterate through the result. This has lead us to investigate the possibility of searching databases using Spring Data JPA.
In this article, I will use the MySQL Employees database via the docker container genschsa/mysql-employees, see my previous post for relevant instructions about this container. Entity model for Employee is given below.
Searching using Spring Data JPA queries
One initial approach is of course to use the built in Spring Data JPA support for searching based on declaring methods in your repository. Let’s say we want to find all employees based on their birthDate we can easily do that by declaring a query in our JPA repository interface. But what if we want to give our users the ability to search for first and last name as well? And then what if any of the parameters are passed as null? This quickly grows out of hand and you end up with a repository looking like this
And then an even more problematic service class
Obviously, you see where this is going. Any new field added to the search creates an unmaintainable increase in combinations of parameters.
Searching done easy using Example
Instead of explicitly covering each combination of parameter that can be passed by the client, we can instead rely on the built in support for querying using an example entity. This then takes care of the problem of creating the correct SQL based on our search criterias (exact, like, etc.) and what values has been set.
Search Criteria
First we need to define what criterias to use when searching for our records, this is done by creating an ExampleMatcher. This is used to instruct Spring Data JPA how to perform the search, matchingAll or matchingAny, what fields to include in the search and which to exclude, and finally how each field should be handled in the actual search, exact or contains etc.
When declaring our search criteria, we must take care to explicitly cover each field of the entity as either an included or an excluded field. Otherwise it will be included in the search and not produce the result we expect.
Creating an Example
The next step is to setup our Example, i.e. an example that records in the database must match to be included in the result. Above we have have specified three different attributes that should be included in the search and we set them on line 22–26.
Spring Data JPA then supports converting the example entity to proper SQL given the search criteria specified and it even knows how to handle null values, removing a lot of problems from us when developing our search solution.
At this point, care should be taken to make sure that the database isn’t overloaded, perhaps utilizing and index and enforcing that attribute as a required parameter.
Performing searches
We are now ready to start querying our database for records and then we can inspect the generated SQL to see how each combination is handled.
As can be seen, Spring Data JPA automatically adapts to what fields are set and it creates conditions based on the ExampleMatcher criteria we have defined.
Designing and performing database searches using Example and ExampleMatcher makes it a lot easier to provide rich search experiences based on JPA and traditional SQL databases and takes care of a lot of the pains for us.
Limitations
Searching using Example is great but it does come with some limitations. For starters, it does not support searching in sub collections using JOIN statements. It is also somewhat limited in property matchers available, but this can of course be extended freely.
For more advanced searches than just querying one single table, we have to turn to Specifications. More on that in a later post!
