Spring data @Query: If optional Boolean param is given check size of other String column in where clause

Question

i trying to build a @Query in one of my repositories which gets a optional Boolean param named "hasComment" in my example. If the Boolean is given/exists i want to make a length check on annother character varying column ("comment") in my where clause.

Currently im having the following code which i didn't expect to work (and isn't of course) but it should show the way i was imagine how it could work.

@Query("SELECT t "
    + "FROM Test t "
        + "WHERE "
        + "(:from IS NULL OR t.startTs >= :from) "
        + "AND (:to IS NULL OR t.endTs <= :to) "
        + "AND ((:hasComment) IS NULL OR ("
        +   "(CASE WHEN :hasComment = true THEN length(t.comment) > 0)"
        +   "OR (CASE WHEN :hasComment = false THEN length(t.comment) = 0 OR t.comment IS NULL)"
        + ")"
Page<Test> find(@Param("from") Instant from, @Param("to") Instant to,
        @Param("hasComment") Boolean hasComment, Pageable pageable);

Can anybody help me out? I just can't really find informations yet how to build this query and even if this is possible at all with a @Query...

Answer 1

I don't think it is possible to use a CASE inside a WHERE condition. Regarding your query there is a workaround

"AND (:to IS NULL OR t.endTs <= :to) "
    + "AND ((:hasCategory) IS NULL OR ("
    +   "(CASE WHEN :hasCategory = true THEN length(t.comment) > 0)"
    +   "OR (CASE WHEN :hasCategory = false THEN length(t.comment) = 0 OR t.comment IS NULL)"
    + ")"

to

AND ((:hasCategory) IS NULL OR (
   (:hasCategory=true AND length(t.comment)>0) OR 
   (:hasCategory = false AND length(t.comment) = 0) OR 
   (:hasCategory = false AND t.comment IS NULL))
)