CODEWITHSUNDEEP
spring-data-jpa
Stephane
Stephane

Reputation: 12750

How to have a @Query with a possibly null parameter

I have a @Query that gives me trouble. I'd like to get all webpages that have a given parent, even if the given parent is null.

The parent can be set but it can also not be set.

I've been trying this:

@Query("SELECT w FROM Webpage w WHERE w.parent = :parent OR (parent IS NULL AND :parent.id < '1')) ORDER BY w.listOrder")
public Page<Webpage> findByParent(@Param("parent") Webpage parent, Pageable page);

And this:

@Query("SELECT w FROM Webpage w WHERE w.parent = :parent OR (parent IS NULL AND :parent IS NULL)) ORDER BY w.listOrder")
public Page<Webpage> findByParent(@Param("parent") Webpage parent, Pageable page);

But I get the exception:

Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract
com.thalasoft.learnintouch.data.jpa.domain.Webpage
com.thalasoft.learnintouch.data.jpa.repository.WebpageRepository.findByParentAndNameAndNotGarbage(com.thalasoft.learnintouch.data.jpa.domain.Webpage,java.lang.String)!

I'm using the spring 3.2.9.RELEASE and spring-data-jpa 1.6.1.RELEASE version.

Any idea on how I can select on a given parent, even if the given parent is null ?

Kind Regards,

Stephane Eybert

Upvotes: 3

Views: 16847

Answers (1)

Chris Savory
Chris Savory

Reputation: 2755

If the validation on the provider will not let you pass nulls, you can split the query into two. You'll have to have logic in the caller to figure out which one to call.

Query1 (param is not null):

@Query("SELECT w FROM Webpage w WHERE w.parent = :parent ORDER BY w.listOrder")
public Page<Webpage> findByParent(@Param("parent") Webpage parent, Pageable page);

Query2 (param is null):

@Query("SELECT w FROM Webpage w WHERE w.parent is null ORDER BY w.listOrder")
public Page<Webpage> findByNullParent(Pageable page);

I would recommend reading some more of the docs on your provider to see how to pass nulls in.

Upvotes: 4

Related Questions