How to use the query dynamically in spring boot repository using @Query?

Question

How to use the below query dynamically in spring boot repository using @Query(value="SELECT * FROM do_not_track WHERE (user_id=7 ) AND ('2022-06-25' BETWEEN from_date AND to_date) OR ('2022-06-30' BETWEEN from_date AND to_date)",nativeQuery = true)

SELECT * FROM do_not_track WHERE (user_id=7 ) AND ('2022-06-25' BETWEEN from_date AND to_date) OR ('2022-06-30' BETWEEN from_date AND to_date);

How to write the above query using jpa method? I have used as given below but it's not working.

findByUser_userIdAndFromDateBetweenOrToDateBetween(Long userId, Date fromDate, Date toDate,
            Date fromDate2, Date toDate2)

Answer 1

The @Query annotation allows you to 'bind' a SQL statement to a method and also allows you to include placeholders in your query.

To create such placeholder values, at first you want to annotate the arguments you pass to the repository method with @Param("someName"), where 'someName' is the name of the placeholder that will later be used in the query.

Inside the query you can reference to a specific parameter using :someName.

In the end your repository method and query could look something like this:

@Query("SELECT * FROM do_not_track WHERE (user_id = :userId) AND (date 
BETWEEN :fromDate AND :toDate) AND (date BETWEEN :fromDate2 AND :toDate2)")
findByUser(@Param("userId") Long userId,
           @Param("fromDate") Date fromDate, 
           @Param("toDate") Date toDate,
           @Param("fromDate2") Date fromDate2, 
           @Param("toDate2") Date toDate2)