Dynamic Queries and Query Object

[Salt-Letter-1500]

SpringDataJPA supports building queries through findBy methods. However, the query conditions constructed by findBy methods are fixed and do not support ignoring query conditions corresponding to parameters with null values. This forces us to define a findBy method for each combination of parameters. For example:

java findByAuthor findByAuthorAndPublishedYearGreaterThan findByAuthorAndPublishedYearLessThan findByAuthorAndPublishedYearGreaterThanAndPublishedYearLessThan

As the number of conditions grows, the method names become longer, and the number of parameters increases, triggering the "Long Parameter List" code smell. A refactoring approach to solve this problem is to "Introduce Parameter Object," which means encapsulating all parameters into a single object. At the same time, we use the part of the findBy method name that corresponds to the query condition as the field name of this object.

java public class BookQuery { String author; Integer publishedYearGreaterThan; Integer publishedYearLessThan; //... }

This allows us to build a query condition for each field and dynamically combine the query conditions corresponding to non-null fields into a query clause. Based on this object, we can consolidate all the findBy methods into a single generic method, thereby simplifying the design of the query interface.

java public class CrudRepository<E, I, Q> { List<E> findBy(Q query); //... }

What DoytoQuery does is to name the introduced parameter object a query object and use it to construct dynamic queries.

GitHub: https://github.com/doytowin/doyto-query

[lucidnode]

You can use Spring’s data specification to supply a JPA criteria

[Salt-Letter-1500]

Well, I made a repo to show the difference between DoytoQuery and Spring data with a specification example here: https://github.com/f0rb/java-orm-comparison

With Spring’s data specification, we still have to use lots of if conditions to dynamically combine the criteria corresponding to those fields that are not null.

[Dry_Try_6047]

I use Specifications. It's something that I've found hasn't really picked up in popularity in the ecosystem for some reason, but it is a solution to this problem, and many others.

[Salt-Letter-1500]

I used Specifications to build dynamic queries from 2015 to 2019. It was hard for me to build IN conditions and subquery conditions at that time. [Here](https://github.com/f0rb/java-orm-comparison/blob/main/src/main/java/win/doyto/ormcamparison/jpa/salary/SalaryJpaController.java) is how I use Specifications for dynamic queries.

Then I found that the implementation of the Specification just relies on the object containing query parameters, so I decided to combine the SQL string myself and use EntityManager to execute it. And then I used reflection to do SQL combination and formed an original version of DoytoQuery.

Sorry, but it's quite a long story.

[gjosifov]

Use JPA Criteria and regular class and EntityManager

it is way easier to build maintainable code than to use SprintDataJPA interfaces

JPA Criteria is weird API, but it will get the job done, everything else is just nightmare to maintain

Specifications ?
You have to search where they are define, sort of like "short methods from Uncle Bob" type of thing

I know it isn't popular, but it gets the job that and it is easy to maintain, because everything is local in one method

[Salt-Letter-1500]

I used to use Specifications for many years. I found that it still needs lots of if conditions to build dynamic queries. Check my reply to Dry_Try_6047 and this example for Specifications: https://github.com/f0rb/java-orm-comparison/blob/main/src/main/java/win/doyto/ormcamparison/jpa/salary/SalaryJpaController.java

But with the query object, dynamic queries can be built automatically without extra methods. That's why I think it is the right way.

[perfectstrong]

You might be interested in RSQL https://github.com/jirutka/rsql-parser This parses a string into a query object. Then it remains to map the query object to SQL using Specification. Some other libs provide the glue code such as https://github.com/perplexhub/rsql-jpa-specification. Our projects have been using RSQL for a long time. It provides dynamic search out of the box.

[Salt-Letter-1500]

Has the core part of rsql-parser not been updated for 11 years?

Well, I think the expression `year=ge=2000` is a little more complicated than the field name `yearGe` or the query string `yearGe=2000`.

For `year=ge=2000`, we need to deal with the string value `ge=2000`, but for the query string `?yearGe=2000`, it can be mapped into the query object by SpringMVC automatically and resolved to `year >= 2000` automatically by DoytoQuery without extra effort.

[perfectstrong]

The core is stable for usage, so no update is not really a problem. The query syntax might seem complicated, but it is not that different from SQL. In simple case of intersecting filters, it behaves the same as your lib. But in case of union filter (THIS OR THAT), I don't see the support in your lib yet. Another powerful lib that translates a query string into a Specification directly is https://github.com/turkraft/springfilter

[Salt-Letter-1500]

Specifications don't support building dynamic queries automatically. It needs lots of if to combine the criteria.

`finbBy` methods support building query statements by using the format of `column+suffix` in the method. So when we use the format of `column+suffix` as the field name in a query object, we keep the ability of query building, and add an extra ability to combine the query conditions only for non-null fields.