How to capture the value of a dynamically generated column in the database using JPA (hibernate)?

2 answers

Suppose I have the following JPA entity:

@Entity @Table(name = "places") public class Place {      @Id @GeneratedValue     private Long id;     private String name;     private double lat;     private double lng;      // Getters and setters } 

And a Spring repository called PlacesRepository which have a native query:

public interface PlacesRepository extends CrudRepository<Place, Long> {      @Query(         value = "SELECT *, DISTANCE(lat,lng,:myLat,:myLng) AS distance " +                 "FROM places ORDER BY distance",         nativeQuery = true     )     List<Place> nearestPlaces(         @Param("myLat") double myLat,         @Param("myLng") double myLng     );  } 

That DISTANCE function is just to exemplify a native database function. Since I'm using PostGIS, it would be equivalent to ST_Distance. It returns the distance using the coordinates of each record and those of the method.

How do I capture that "distance" field without having to store it in the table??? I don't want to store it because it is dynamic. I just want to return it with the entity as a json.

All answers to this question, which has the identifier 61222955

The best answer:

An option here is to use HQL, not a native query. For example, you can create a simple result class:

class PlaceToDistancePair {     private Place place;     private double distance;     public PlaceToDistancePair(Place place, double distance) {         this.place = place;         this.distance = distance;     } // getters and setters } 

and then your @Query will look somehow like this:

@Query(         value = "SELECT new PlaceToDistancePair(p, DISTANCE(lat,lng,:myLat,:myLng)) FROM places p") List<PlaceToDistancePair> placeToDistanceList; 

You can find more about select new here and also there you will find select new map construction, which maybe can also be suitable for you to map Place to distance without PlaceToDistancePair class.

However, in case your function (e.g. ST_Distance) is not registered in the hibernate dialect, you need to register it yourself. Check here how Vlad does this in the "Registering the SQL function with the Hibernate Dialect" section.

Also an option for you is to use CriteriaBuilder API, which is much more flexible, but requires more code. Read here for more

Maybe you try this:

List list = em           .createQuery("SELECT FUNCTION("'"+myLat+"'"+",'"+myLng+"')"                   + " FROM schemaName")           .getResultList(); 

source excample from web site

In this situation you get data from your own function. Is possible set to this function all query question which you need. Not only DISTANCE but all data which you would like to get.

Last questions

how do i remove the switch on my home screen?
how to edit the JS date and time to update atuomatically?
How to utilize data stored in a multidimensional array
Powermockito not mocking URL constructor in URI.toURL() method
Android Bluetooth LE Scanner only scans when phone's Location is turned on in some devices
docker wordpress container can't connect to mysql container
How can I declare a number in java that is more than 64-bits? [duplicate]
Optaplanner solutionClass entityCollectionProperty should never return null error when simple JSON object passed to controller
Anylogic, get the time a pedestrain is in a queue
How do I fix this syntax issue with my .flex file?
Optimizing query in PHP
How to find the highest number of a column and print two columns of that row in R?
Ideas on “Error: Type com.google.firebase.iid.zzav is referenced as an interface from com.google.firebase.messaging.zzd”?
JCIFS SmbFile.exists() and SmbFile.isDirectory() return false when it exists and I can listFiles()
PHP total order
Laravel booking system design
neural net - undefined column selected
How to indicate y axis does not start from 0 in ggplot?
Fragments in backStack
Spinner how to change the data