Dynamic query with Spring Data JPA and Search by Example of
Search all around!
The Problem 😠
The problem was simple, the query to the database should be based on the values supplied from the front-end. Sometimes the user would select or enter all the values or it is search based on certain values. Child’s play right 😎. Then without second thought created a repository and was stuck when its time to type in the query method. Wait what how would write the query methods.
I know i was doomed when the google forced me to head to the 2nd page of its search results 😯 and criteria api was the appropriate solution. It would do wonders for small set of vales to be queried on.
Checkout the complete code @ GitHub 💻
The Situation 😕
Lets consider a car catalog database with Make, Model and year of release.
So implementing criteria api will be easy with with few CriteriaObjects methods and done!, add!, commit!, push!. Ready for prod ⏳.
Lets spice it up, the POJO is added with some more properties lets say Horse Power, Turning Radius and lot more stuff which are to stored in database and we are again making up the changes with CriteriaObjects and its results in cycle for every new change.
To the rescue 👍
Here comes Query by Example to save us. It allows dynamic query building at runtime without specifying query fields. Just pass in the POJO, let JpaRepository handle it for you and problem solved.
Example.of also takes an matcher object where in we can specify to which columns we can ignore the case and search.
Time to Search ⏰
Let say we wanna search all the cars from the year 2015, all we have to do is pass the json with parameters.
Next search is by make and year
Limitations 🔐
We cannot query for conditions like between two ranges, example car make year between years 2010 to 2020.
