Is there a way in Spring data to dynamically form the where clause?
What I want to do is have a method (which is like the findBy / get method) which runs a WHERE and AND using the mentioned properties which are NOT NULL.
For example,
Consider the object Person [firstName, lastName, age, gender]
Our method looks something like this
findBy_IfNotNullFirstName_AndIfNotNullLastName_AndIfNotNullAge_AndIfNotNullGender(String firstName, String lastName, Integer age, String gender)
Thanks.
Using EntityManager methods createQuery or createNativeQuery , you can create a Query object dynamically at run time (see "Using Java"). Using the Query methods getResultList , getSingleResult , or executeUpdate you can execute the query (see "Executing a Query").
Take a look at JPA Specification and Predicate, and Even better QueryDSL, there both supported by spring data repositories. This article provide an example: http://spring.io/blog/2011/04/26/advanced-spring-data-jpa-specifications-and-querydsl/
A simpler option is to test if the parameter is null right in the JPQL query:
Exemple from my project:
@Query("select m from MessageEntity m " +
"join fetch m.demandeAnalyseEntities d " +
"where (:patientId is null or d.noPtn= :patientId) " +
" and " +
" ( :labNbr is null or d.noLab= :labNbr) " +
" and " +
" ( :reqDate is null or d.dteReq= :reqDate) " +
" and " +
" ( :reqNum is null or d.noReq= :reqNum) "
)
List<MessageEntity> findMessagesWithDemandesOnly(@Param("patientId") Long pid,
@Param("labNbr") Integer labNo,
@Param("reqDate") String reqDate,
@Param("reqNum") Integer reqNum,
Pageable pageable);
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