A dynamic SQL query refers to building a query on the fly before executing it. Not just replacing query parameters by their name or position, but also including a variable number of columns in the WHERE clause, and even joining tables based on specific conditions.
How would you implement a RESTful endpoint like /api/films?minRentalRate=0.5&maxRentalRate=4.99 where you need to retrieve films from a relational database?
You could take advantage of SpringDataJPA support for:
Let’s say later on you also need to search films based on movie categories, for instance, comedy, action, horror, etc.
You might think a couple of new repository methods would do the work. One for the new category filter and another to combine both, the rental rate range filter with the category filter. You would also need to find out which repository method to call based on the presence of the request parameters.
You soon realize this approach is error-prone and it doesn’t scale as the number of request parameters to search films increases.
This blog post covers generating dynamic SQL queries using SpringDataJPASpecification and CriteriaAPI including joining tables to filter and search data.
To see the dynamic queries generation in action, let’s implement this endpoint:
This is a sequence diagram to better understand the interactions between the classes included in this tutorial.
Retrieve films Sequence Diagram
Let’s discuss this flow following a bottom-up approach. First the relevant relational database tables, next the JPA entities, then the SpringDataJPA-based repositories, and so on up to the service and REST controller.
These are the JPA entities relevant to this post with the attributes that we will search on.
Notice the @OneToMany, @ManyToOne entity associations are LAZY loaded.
Important:EAGER fetching associations are simple to use but this strategy might load irrelevant data to your business use case. You’ll likely end up including many JOIN clauses in a SELECT statement impacting your application performance.
If there comes a need to add another request parameter to filter movies, all you have to do is wrap the request parameter value in the FilmSearchCriteria instance, and add a new JPASpecification to FilmSpecifications.
7. CONTROLLER CLASS
The controller does a few things:
Wraps the request parameters in an instance of FilmSearchCriteria. This code is easier to maintain than changing the retrieveFilms() method signature in the interface and implementation classes, and fixing the corresponding unit tests every time your team needs to add a new parameter to search on.
Executes the business logic via retrieveFilms() method.
Maps the internal model to the external interface.
Don’t expose your internal domain as your external interface. It might lead to API compatibility issues.
The application’s internal domain might evolve at a different pace than the contracted API responses.
Map or transform the internal domain and send the result as the API response.
8. DYNAMIC QUERIES IN ACTION
Let’s send a couple of requests to the /api/films endpoint.
This request included the minRentalRate and maxRentalRate parameters only.
Notice it generated only one predicate to the WHERE clause:
Let’s now send a second request with all the parameters.
It now generates three predicates to the WHERE clause:
And it also generates two more JOIN clauses so that it can search based on movie category:
Different calls to findAll(Specification<T>spec) method generate and execute different SQL statements depending on which and how many SpringDataJPA Specifications were instantiated.
Another thing I would like to point out is how Hibernate parametrizes String-like film0_.release_year and category2_.name. This a good approach to prevent SQL injection attacks. film0_.rental_rate used literal values because it’s numeric.
Stay tuned, I’ll cover the impact of having literal values and IN predicates in your SpringBoot applications when using JPQL statements or CriteriaAPI queries in another blog post.
This tutorial covers each component in your SpringBoot application to write flexible, and maintainable code when implementing dynamic queries using SpringDataJPASpecification.
Combining SpringDataJPASpecification and CriteriaAPI allow you to write type-safe dynamic queries to retrieve data from a relational database.
One drawback is that implementing JPACriteriaAPI queries is not as trivial as when using SQL.
Thanks for reading and as always, feedback is very much appreciated. If you found this post helpful and would like to receive updates when content like this gets published, sign up to the newsletter.
10. SOURCE CODE
Accompanying source code for this blog post can be found at: