1

Pretty new with JPA Query

I am assigned to retrieve data from a postgresql database table, with three cases:

  • either a certain field has value or not
  • if a field has value
  • if a field has not value

I use a Boolean requestparam that can either be true, false or null and use it to query from the repository

At first, I separated the repository queries but for performance reasons, I am tasked to use only one repository query, using the @Query annotation

I am trying to use CASE WHEN without much success, here is what I came up with so far

@Query("SELECT e FROM Elements e"
+ " WHERE e.fieldOfInterest = "
+ " CASE WHEN :parameter IS NULL e.fieldOfInterest"
+ " WHEN :parameter = true THEN NOT NULL
+ " ELSE NULL END")
List<Elements> findWithNullableParam(@Param("parameter") Boolean parameter);

And I'm hit with the following error when compiling

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: NOT near line 1, column 161

I am aware that a WHERE field = NOT NULL will not be okay

I am considering using NULLIF() in my CASE block but I have no idea how it works yet

How do I formulate the query for my conditions to be met?

prout
  • 317
  • 5
  • 18
  • Does this answer your question? [Dynamic Queries in Spring Data JPA](https://stackoverflow.com/questions/27193337/dynamic-queries-in-spring-data-jpa) – pringi Feb 07 '22 at 14:43
  • @pringi sadly no, it does not. In my question, it is just a sample code, but I have many other nullable parameters in my function, and the rest of them are working, only for that particular field, it's not working because of the three conditions – prout Feb 07 '22 at 14:49
  • 1
    You problem is that you cannot use CASE statement for what you are trying to do. "NOT NULL" is not valid (is not a value). e.fieldOfInterest and NULL are values, so syntax in the first WHEN and in the ELSE are ok. I suggest you to rewrite without using case, using, for example, CriteriaBuilder or QueryDSL, or simplify the query by itself. – pringi Feb 07 '22 at 14:57

0 Answers0