CODEWITHSUNDEEP
javamysqlhibernaterepository
Naoki Kokubyakuin
Naoki Kokubyakuin

Reputation: 35

Map SQL query data back into Java Object in Repository

I started using Hibernate to do queries and I noticed that if I want to use repository to execute queries, that I have to use Entity in the repository to map the value that returned database.

Is there anyway for me to run query in any repository and return the data and map into java class with same name.

Like

@Query(value = "SELECT location_id, (3959 * acos (cos(radians(:lat)) * cos(radians(l.latitude)) * cos(radians(l.longitude) - radians(:lon))  + sin(radians(:lat)) * sin(radians(l.latitude)))) AS distance FROM location l having distance < :mile order by distance limit 0, :limitNum", nativeQuery = true)
List<LocationDistance> searchLocationByRadius(@Param("lat") double lat, @Param("lon") double lon, @Param("mile") double distance, @Param("limitNum") int limit);

So the location_id and distance field returned from Query will be auto map into LocationDistance object.

Upvotes: 0

Views: 781

Answers (1)

Patrycja Wegrzynowicz
Patrycja Wegrzynowicz

Reputation: 737

You have 2 options:

  1. Switch to JPQL query and use a constructor in a query like:
@Query(value = "SELECT new example.LocationDistance(location_id, distance_calculation) " +
               "FROM location ...")
List<LocationDistance> searchLocationByRadius(@Param("lat") double lat, @Param("lon") double lon, @Param("mile") double distance, @Param("limitNum") int limit);

In this approach, you can't use cos/sin/radians functions directly as they are not supported by JPQL. Instead, you need to use JPQL function to call them, like:

function('cos', tab.column)

Or if you use hibernate, you can register a function with a hibernate dialect.

  1. Switch to @SqlResultSetMapping and @NamedNativeQuery

First, you need to add @SqlResultSetMapping and @NamedNativeQuery annotations to your entity class.

@SqlResultSetMapping(
           name="myMapping",
           classes={
              @ConstructorResult(
                   targetClass=LocationDistance.class,
                     columns={
                        @ColumnResult(name="location_id"),
                        @ColumnResult(name="distance")
                        }
              )
           }
)
@NamedNativeQuery(name="Location.searchLocationByRadius", 
        resultSetMapping="myMapping", 
        query="...")
@Entity
public class Location {
  ...
}

Second, you need to add a method to your spring repository interface named exactly as the name of your @NamedNativeQuery.

@Repository
public interface LocationRepository extends JpaRepository<Location, Long> {
    List<LocationDistance> searchLocationByRadius(...);
}

Upvotes: 1

Related Questions