JPA Query to select whether a field has value or not, or both

82
June 26, 2022, at 08:30 AM

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?

Rent Charter Buses Company
READ ALSO
How to execute methods after the OnCreateView?

How to execute methods after the OnCreateView?

I'm working on an Android app in Java where a class is called and measurements are made in the OnCreateView methodHowever I have to automate the sending of his measurements once finished

76
Can I capture accessibility events in Fire OS?

Can I capture accessibility events in Fire OS?

I have a Java Android app that captures accessibility events by overriding the onAccessibilityEvent() method in a custom AccessibilityService classIt works well on phones and tablets

62
Can we use github repository as an artifact while creating maven project?

Can we use github repository as an artifact while creating maven project?

I've implemented a maven library and published it to GitHubNow I wanted to use this repository as an archetype to create another maven project (without maven central repo) like below

70
Lookup in MongoDB using spring data

Lookup in MongoDB using spring data

I have a query that would be fairly simple using SQL databasesHowever, in MongoDB, I'm not sure on how to do it

55