How to write jpa method from sql query?

Question

How to write the below query as jpa method using findBy...?

SELECT * FROM user WHERE (user_id=1) AND ('2022-03-04' BETWEEN from_date AND to_date) OR ('2022-03-10' BETWEEN from_date AND to_date);

Answer 1

I'm assuming that from_date and to_date are columns on your table. If that's the case, pass in the value of 2022-03-04 and 2022-03-10 as variables of the same date type as the column in your table to the function that is making this query.

eg. if the column stores LocalDate then pass in those dates as variables of type LocalDate.

SELECT u FROM User u WHERE u.id = 1 AND ( (:firstInputDate BETWEEN u.from_date AND u.to_date) OR (:secondInputDate BETWEEN u.from_date AND u.to_date) )

However, this seems much more complicated. My recommendation is that you just do this query as a native query.

All you would do is annotate the repository method like so

@Query(
  value = "SELECT * FROM user WHERE (user_id=1) AND ('2022-03-04' BETWEEN from_date AND to_date) OR ('2022-03-10' BETWEEN from_date AND to_date)",
  nativeQuery = true
)

and one last thing. Your query is missing an essential bracket. Let me explain. I'll break down the predicates

a = user_id = 1
b = ('2022-03-04' BETWEEN from_date AND to_date)
c = ('2022-03-10' BETWEEN from_date AND to_date)

Your whole query is then: select * ... where a and b or c

Conjunctions (and) is performed before disjunctions (or) which means that your query evaluates to select * ... where (a and b) or c. From context, it doesn't seem like this is what you were going for. Because this means that the query is true if the user id is 1 and the first date predicate is true. It is also true if the second date predicate is true even if their user id is not 1

Instead, you need these essential brackets making the query select * ... where a and (b or c)