I need some advice with implementing spring JPA query. My query is complex, because of the input length.
As input I've:
I know that this can be solved by using standard PagingAndSortingRepository like:
Page<A> findAllByParam1AndParam2AndParam3Between...(@Nullable String param1, @Nullable String param2, @Nullable Integer param3,...)
but looking on how long my input is this solution looks unclear and I don't think that method with so many parameters is a good solution.
I was also thinking about findAll
by Example, but this is supporting equality, not the between condition.
Any other options?
The only way to go is by building a custom query with CriteriaBuilder?
In order to define SQL to execute for a Spring Data repository method, we can annotate the method with the @Query annotation — its value attribute contains the JPQL or SQL to execute. The @Query annotation takes precedence over named queries, which are annotated with @NamedQuery or defined in an orm. xml file.
With JPQL, you can use subqueries only in the WHERE and HAVING clauses but not in the SELECT and FROM clause. SQL, of course, allows you to use subqueries also in the SELECT and FROM clause.
Let's start with the JpaRepository – which extends PagingAndSortingRepository and, in turn, the CrudRepository. Each of these defines its own functionality: CrudRepository provides CRUD functions. PagingAndSortingRepository provides methods to do pagination and sort records.
Derived queries, i.e. those deriving the actual query from the method name, are the wrong tool for such long or complex queries because the resulting name becomes unusable.
Alternatives that you should consider are
using a fixed query provided in a @Query
annotation: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.at-query
using a named query where you provide the query on the entity: https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.named-queries
using a specification where you dynamically assemble the where clause: https://spring.io/blog/2011/04/26/advanced-spring-data-jpa-specifications-and-querydsl/
You have below options to do that. 1) use hql , for that it is mandatory to have all the tables mapped as jpa entities 2) is to use native queries, the downside here is that it affects the portability of the application, but if u r sure that ur application is not going to migrate on any other database then u can use this 3) is to use criteriabuilder
Edit-> Found our that @Query can be used for both SQL and JPQL to execute .
Please follow below link for more information
https://www.baeldung.com/spring-data-jpa-query
Usage of Query in some spring developer certification books also.
Hope that helps
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With