Spring JPA @Query : check if parameter is null before to use it - bad hibernate translation?

Question

I am currently facing an issue with JPA Query. I have a class Event and a class Race, an event has a list of races. Event and Race have an attribute type Point from lib org.locationtech.jts.geom.Point to handle Geometry object of my PostgreSQL (extended with PostGis) database.

public class Event {

    @OrderBy("rank")
    @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL, mappedBy = "event", orphanRemoval = true)
    protected List<Race> races;

    private Point locapoint;

    ...

}

public class Race {

   @ManyToOne(fetch = FetchType.LAZY, optional = false)
   @JsonIgnore
   private Event event;

   private boolean hasAddressEvent;

   private Point locapoint; //null if hasAddressEvent is true

   ...

}

This query works fine in PGSQL (extended with Postgis) :

SELECT distinct e.* FROM Event e inner join Race r on e.id = r.event_id 
where ((:radius is null or :point is null) 
or (r.has_address_event = TRUE and st_distancesphere(:point, e.locapoint) < :radius)
or (r.has_address_event = FALSE and st_distancesphere(:point, r.locapoint) < :radius))

it returns all events that have at least one race located in the circle drawn with these two entries :point (example of value : ST_Point(3.2727,50.2788)) and :radius (integer that represents meters).

Problem is to use it in @Query JPA in my EventRepository :

@Query(value = "SELECT distinct e.* "
            + "FROM Event e inner join Race r on e.id = r.event_id " +
            "where " +
            "((:radius is null or :point is null) " +
            "or (r.has_address_event = TRUE and st_distancesphere(:point, e.locapoint) < :radius) " +
            "or (r.has_address_event = FALSE and st_distancesphere(:point, r.locapoint) < :radius))"
            , nativeQuery = true)
    Page<Event> search(Pageable page, @Param("point") Point point, @Param("radius") Integer radius);

It works only if point and radius params are not null. For example if radius is null, I have this error :

PSQLException: ERROR: operator does not exist : double precision < bytea

It's like if conditions where not short-circuited : even if the first condition returns false it continues to treat the condition.

(:radius is null or :point is null)

With some research, I found this : http://randomthoughtsonjavaprogramming.blogspot.com/2015/10/making-mistakes-in-jpql.html

This lets me think that the problem is my query is turning in SQL and completly changing my condition's structure.

I tried to do equivalent query in JPQL (instead of native PGSQL) and it's the same result.

How can I fix it or just workaround.

Sorry for my english Thanks in advance, Thomas

EDIT :

As suggest @hqrd, I can do the null condition treatment inside my service and use two differents queries :

default Page<Event> search(Pageable page,  Point point,  Integer radius) {
    if (point == null || radius == null) {
        return findAll(page);
    } else {
        return searchAround(page, point, radius);
    }

}

It will be a good solution for the example I introduced in my post. But this query is just a POC, my final query will be more sophisticated (with more conditions, with 6 parameters that can potentially be null).

hqrd hqrd · Accepted Answer · 2020-12-11T11:01:37

I don't know how to solve this in the @Query, but I suggest you split your issue using java.

@Query(value = "SELECT distinct e.* FROM Event e inner join Race r on e.id = r.event_id " +
        "where (r.has_address_event = TRUE and st_distancesphere(:point, e.locapoint) < :radius) " +
        "or (r.has_address_event = FALSE and st_distancesphere(:point, r.locapoint) < :radius))"
        , nativeQuery = true)
Page<Event> searchAround(Pageable page, @Param("point") Point point, @Param("radius") Integer radius);

default Page<Event> search(Pageable page,  Point point,  Integer radius) {
    if (point == null || radius == null) {
        return findAll(page);
    } else {
        return searchAround(page, point, radius);
    }

}

This way you have more control on your requests.

Spring JPA @Query : check if parameter is null before to use it - bad hibernate translation?

1 Answers