How to Create Dynamic SQL Queries with Spring Boot
Transcript:
Hi friends! In this video, we will learn how to create dynamic SQL queries in Spring Boot with Spring Data JPA. I will use a project I created for this tutorial. You can find it on GitHub. It is a minimalistic bookshop application. The main entity class is ‘book,’ and we also have some auxiliary classes such as ‘category,’ ‘format,’ and ‘language.’
They also have the book repository and book service. In a real-world bookshelf application, users apply multiple filters to find the book they need. But we don't want to write separate queries for various combinations of these filters, right? This is where dynamic SQL queries come into play.
As the name suggests, they enable us to create SQL queries dynamically based on the user input. To build dynamic SQL queries with JPA, we can use the ‘Specification’ interface. It helps us build multiple predicates over the entity. These predicates can be combined to create queries with flexible WHERE clauses.
To make use of JPA specification, let's make our book repository interface extend the JPA specification executor. Here it is. Our book repository extends both the JPA repository and specification. Now, we need to create a specification class. Let's call it BookSpecification.
Here, we will create our filters, which are going to be custom specifications. Let’s start with the first method and define a filter for category. We need to return the specification for the Book entity class and call this method hasCategoryName. We pass a String for the category name and write a return statement. We return the root, which is our book, along with the query and criteriaBuilder. The criteriaBuilder is the most important part as it provides numerous methods.
We use equal() and pass the root (our book), get the category, and then extract its name. This will be our filter. The book entity has a category field, and the category has a name field. We extract the category and then the name we are looking for.
But in most cases, we need multiple filters, not just one. So let’s add additional filters: price, language, and format. We start with the price filter by creating a method called hasPriceLessThanOrEqualTo and passing the price parameter. The logic is similar—we retrieve the price field from the root and use the lessThanOrEqualTo method from the criteriaBuilder.
Currently, we are using strings to reference entity fields, such as category name or price. While this works, it's not the safest approach because changes in field names can break the specification. Instead, we could use the JPA static model generator for type-safe queries, but we will keep it simple in this demo.
Our filters are ready. Now, let's move to the book service and write a method for finding books based on the filters. Users may apply none, some, or all filters, but we only need one method to handle all these cases. We pass String categoryName, String languageName, String formatName, and a price parameter.
We create a specification called spec, initially set to null to handle cases where no filters are applied. Then, we check each parameter—if it's not null, we add it to the specification using the and method with the corresponding filter from BookSpecification.
Finally, we return the list of books using bookRepository.findAll(spec). If the specification is empty, we return all books. Otherwise, we return a filtered list based on the applied criteria.
Now, it’s time to test it. In the book service test, I have pre-written some tests to speed up the demo. The setup is simple: we first find books by category while passing null for other filters. We test different filter combinations, including category and format, category and language, and all four filters together. We also check that an empty list is returned when there are no matches and verify that all books are returned when no filters are applied.
Let’s run the tests. All tests have passed! And that’s how we create dynamic SQL queries in Spring Boot. Don't forget to like this video, subscribe to our channel, and see you next time!
