CODEWITHSUNDEEP
springspring-bootspring-data-jpageospatialpostgis
DCO
DCO

Reputation: 1292

Map entity field to select of named query

My Entity does contain a geo location. In my repository I am doing a custom query to get all messages in a specific radius of a given location. The distance of course differs on each query depending of the given point. So I dont want to persist the distance. I need to calculate the distance in my query. I want to add this calculated distance to my entity. Does anybody know how I can map something from my resultset to a entity field without peristing it. I know there is @Forumla annotation to do some simple things but this doesnt help me.

Query:

@Query(name = "MessageWithDistance",value = "SELECT m.*,cast(st_distance_sphere(m.location,:userLocation) as double precision) as distance FROM message_entity AS m WHERE st_within(m.location, :polygon)=TRUE",nativeQuery = true)
    List<MessageEntity> findWithin(@Param("userLocation") Point point, @Param("polygon") Polygon polygon);



@Entity
public class MessageEntity {

    @Id
    @GeneratedValue(strategy= GenerationType.IDENTITY,generator = "message_seq_gen")
    @SequenceGenerator(name = "message_seq_gen", sequenceName = "message_id_seq")
    private Long id;
    private LocalDateTime dateTime;

    @Column(name = "location",columnDefinition = "GEOMETRY(Point, 4326)")
    @Type(type = "jts_geometry")
    private Point location;

    private String message;

    @Transient
    private Double distance;

Upvotes: 0

Views: 215

Answers (1)

RJ.Hwang
RJ.Hwang

Reputation: 1903

Use spring-data-jpa projection to get want you want :

// define the dto interface
public interface MessageEntityDto {
           Long getId();
  LocalDateTime getDateTime();
          Point getLocation();
         String getMessage();
         Double getDistance();
}

@Query(value = "SELECT m.*, ... as distance FROM message_entity AS m ..., nativeQuery = true)
List<MessageEntityDto> findWithin(...);

Upvotes: 1

Related Questions