HQL, SQL - Get whole object (Get distance)

Question

I have the following Query and I got a List with the datatype Double. In this query I have as parameter the Longitude and the Latitude from a specific Location and also the distance. With the "distance" paramater I can select the range between the different locations.

My issue is now, I would like to get a whole "City" - objects in my result List and not only a list from the differenct distance (double). How should I modify that I got a list from my Entity "City" and not only Floats?

    public List<Double> findCityAroundSelectedCity(double longitude, double latitude, float distance) {

    String queryString = "SELECT (6371 * acos (cos(radians(" + latitude
            + ")) * cos(radians(latitude)) * cos(radians(longitude) - radians(" + longitude + "))  + sin(radians("
            + latitude + ")) * sin(radians(latitude)))) AS distance FROM City c HAVING distance < " + distance
            + " ORDER BY distance";

    Query query = entityManager.createNativeQuery(queryString);

    List<Double> list = null;
    list = query.getResultList();

    return list;
}

Thank you very much for all help

Answer 1

Add SELECT * in the query, and specify your entity as the second parameter of the method createNativeQuery()...

Try a SQL query, similar to this:

SELECT *
FROM ( SELECT *, ( 6371 * ACOS( COS( RADIANS( :lat ) ) * COS( RADIANS( latitude ) ) *
                   COS( RADIANS( longitude ) - RADIANS( :lng ) ) + SIN( RADIANS( :lat ) ) *
                   SIN( RADIANS( latitude ) ) ) ) AS distance
       FROM City ) c
WHERE distance < :dis
ORDER BY distance

And the Java part:

public List<City> findCityAroundSelectedCity( double longitude, double latitude,
                                              float distance )
{
   // queryString should be a private static final, outside this method...
   return (List<City>) entityManager.createNativeQuery( queryString, City.class )
      .setParameter( "lat", latitude ).setParameter( "lng", longitude )
      .setParameter( "dis", distance ).getResultList();
}

BTW, this will return the source city as well, if it is in the table...