Mapping JPA Entity with result of native query

Question

I'm using Spring Data JPA to map entities and tables.

In my PostgreSQL DB, I have a table called declared_location, that stores locations with long and lat. I wrote a sql native query with postgre specific syntax in order to retrieve a list of declared_location, ordered by the relative distance to a given point :

  @Query(value="select *, 1.60934 * (point (:longRef, :latRef) <@> point (longitude, latitude)) as distance from declared_location order by distance", nativeQuery = true)
    List<DeclaredLocation> findAllSortedByDistance(@Param("longRef") double longRef, @Param("latRef") double latRef);

I'd like to map the distance calculated by Postgre (column "distance") to a field of my entity. That's where i'm stuck. Here is my entity:

@Entity
public class DeclaredLocation {

    @Id
    @Type(type = "uuid-char")
    @GeneratedValue(strategy = GenerationType.AUTO)
    private UUID id;

    private double latitude;

    private double longitude;

    private String address;

    @Column
    private Double relativeDistanceKm;

(...)
}

How should I tell Spring Data JPA to map the column distance created by postgre as a result of the native query to the field relativeDistanceKm of the entity? What is the right approach to do this?

SternK · Accepted Answer · 2020-02-17 13:05:08Z

2

You can use hibernate @Formula for this purpose.

@Entity
public class DeclaredLocation {
   // ...

   @Formula(value = "1.60934 * (point (13,52) <@> point (longitude, latitude))")
   private Double relativeDistanceKm;
}

answered Feb 17, 2020 at 13:05

SternK

13.3k34 gold badges40 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Julien Berthoud Over a year ago

Thanks, but actually I don't want to calculate the distance from a fix point, instead I'd like to pass the long and lat of the changing reference point as parameters of the query. (see my edit above). I didn't mention it in my initial post to avoid overcomplicating my question. In this scenario, i'm not sure that @Formula is helpful.

Collectives™ on Stack Overflow

Mapping JPA Entity with result of native query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related