[FIXED] What are the advantages of using Spring JPA Specifications over direct queries

Issue

I am currently working on a project where I have to retrieve some rows from the database based on some filters (I also have to paginate them).

My solution was to make a function that generates the queries and to query the database directly (it works and it's fast)

When I presented this solution to the senior programmer he told me this is going to work but it's not a long-term solution and I should rather use Spring Specifications.

Now here comes my questions :

1. Why is Spring Specifications better than generating a query?

2. Is a query generated by Spring Specifications faster than a normal query?

3. Is it that big of a deal to use hard-coded queries ?

4. Is there a better approach to this problem ?

I have to mention that the tables in the database don't store a lot of data, the biggest one (which will be queried the least) has around 134.000 rows after 1 year since the application was launched. The tables have indexes on the rows that we will use to filter.

Solution

1. A "function that generates the queries" sounds like building query strings by concatenating smaller parts based on conditions. Even presuming this is a JPQL query string and not a native SQL string that would be DB dependent, there are several problems:

• you lose the IDEs help if you ever refactor your entities
• not easy to modularize and reuse parts of the query generation logic (eg. if you want to extract a method that adds the same conditions to a bunch of different queries with different joins and aliases for the tables)
• easy to break the syntax of the query by a typo (eg. "a=b" + "and c=d")
• more difficult to debug
• if your queries are native SQL then you also become dependent on a database (eg. maybe you want your integration tests to run on an in-memory DB while the production code is on a regular DB)
• if in your project all the queries are generated in a way but yours is generated in a different way (without a good reason) then maintenance of the will be more difficult

2. JPA frameworks generate optimized queries for most common use cases, so generally speaking you'll get at least the same speed from a Specification query as you do from a native one. There are times when you need to write native SQL to further optimize a query but these are exceptional cases.
3. Yes, it's bad practice that makes maintenance a nightmare

Answered By - GabiM