Complex JPA Queries using Specifications
As shown in my previous article on searching using JPA, Examples is a fast and easy way to search for records in a database. But it comes with limitations, most notably it can’t search in child collections. To support this, we have to turn to JPA Specifications.
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.
Title shown below, Salary has an identical structure with from and to.
Let’s assume we have a use case where we want to find employees with a simple search term, i.e. passing a String would search for employees using the names and that we also want to support explicit filters such as birthDate, hireDate, title and salary.
Repository and Specification
To perform searches using Specifications, we must first adapt our JPA Repository to extend from JpaSpecificationExecutor and create a Specification.
And the secret sauce, our Specification.
Quite a lot to unpack here, let’s go through it step by step.
Line #17 and #20 — Predicate
Specifications works by combining predicates and our job is to create them according to our use case. Here we specify that if birthDate or hireDate is set, it should match using an equal statement.
Line #24 and #50 — Salary
If salary is set, we should find employees that currently match the provided salary. But since forcing users to specify a exact salary is bad design, we check for matches between 90% and 110% of the user provided value.
First we create a Join between our Employee and Salary and specify what type of join operation should be performed, in this case an INNER join should be performed so that we find an employee that has this salary.
Finally, on line #60 we create a predicate saying that the employee must have a salary between min and max and that it should only consider the currently active salary, modeled as to having a value of 9999–01–01.
Line #25 and #65 — Title
Similar to salary but we perform a like search instead.
Line #31 — Search Term
Finally we check if the user has provided a searchTerm which we use to match against both first and last name. This is done by creating two individual predicates, one for each attribute, and then combine to create one or predicate.
Line #48 — Predicate result
The last step is to create a final predicate specifying that a row in the database must match all individual predicates, i.e. and.
Creating and Using our Specification
Generated SQL
If we run this code, we get the following SQL
As can be seen, our Specification has been used to generate the conditions we expect in the SQL issued towards the database. A few caveats are very important to take note of.
Distinct passed on to database
Once we perform a join we need to instruct Hibernate to not return an instance for each row in the result; this is because the join will create multiple rows in the result set.
What we actually want here is for Hibernate/JPA to not return an instance for each row but when using a Specification, we can’t provide a hint to JPA not to send the distinct on to the database (HINT_PASS_DISTINCT_THROUGH).
Paging increases load on database
If the result contains more rows than what fits in the requested page, a second query with count and distinct is issued to find the total number of rows. This could create heavy load on the database so care need to be taken that it isn’t overloaded.
n+1 loading child entities
If the call on line #21 to EmployeeMapper::map would map all salaries and titles for the current employee, JPA would issue one more select statement towards the database for each Employee in the result. This has the potential to overload the database and it degrades response times considerably.
This is because our Specification does not contain a fetch (root.fetch()), this is possible to add but it will not work with paging since the count query isn’t compatible with the fetch.
