CODEWITHSUNDEEP
postgresqlhibernatespring-data-jpanativequery
Julien Berthoud
Julien Berthoud

Reputation: 769

Mapping JPA Entity with result of native query

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?

Upvotes: 2

Views: 443

Answers (1)

SternK
SternK

Reputation: 13041

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;
}

Upvotes: 2

Related Questions