'Return custom object from Spring Data with Native Query

My question is based on another post. How can I achieve the same with a native query? Native queries do not allow JPQL thus do not allow new instances either.

My POJO.

class Coordinates {

    private final BigDecimal latitude
    private final BigDecimal longitude

    ...
}

My database table contains coordinates for cities perimeter, so there are three columns: city_name, latitude, longitude. Each city contains lots (really, LOTS) of perimeter coordinates that will be used to build a shadow area in Google Maps.

I intend to build a simple native query on that table that should return a list of coordinates.

javaspringjpaspring-bootspring-data


Solution 1:[1]

This is https://jira.spring.io/browse/DATAJPA-980 and Here is a project that demonstrates the issue.

@Query(value = "SELECT name AS name, age AS age FROM Person", nativeQuery = true)
List<PersonSummary> findAllProjectedNativeQuery();

It is fixed in the Spring Data JPA 2.0 GA (Kay) release which comes with Hibernate 5.2.11.

The issue is also fixed for Spring Data 1.10.12 (Ingalls) and 1.11.8 (Hopper) but will need to be run on Hibernate 5.2.11 to work.

Solution 2:[2]

You will have to use sql result set mapping which is part of JPA.

Solution 3:[3]

If you are using a recent version of spring-data and also making use of the Repositories, I personally think that the answer from Itsallas leads to the right solution.

I actually did't now about (Spring Data) Projections yet and needed a moment to understand what he was showing in his example.

Therefore I just want to add a link to the Spring Data JPA - Reference Documentation, have a look at the Projections chapter.

Spring Data query methods usually return one or multiple instances of the aggregate root managed by the repository. However, it might sometimes be desirable to create projections based on certain attributes of those types. Spring Data allows modeling dedicated return types, to more selectively retrieve partial views of the managed aggregates.

Solution 4:[4]

The answer I found:

public interface UserEventRepository extends JpaRepository<UserEvent, Long> {

    List<UserEvent> findAllByUserId(Long userId);

    @Query(value = "SELECT user_id FROM user_event ue " +
                   "WHERE ue.user_id = :userId", nativeQuery = true)
    List<Long> findUserIdByEventId(@Param("userId") Long userId);
}

That way we return List of Long - list of ids. The key here is that we are setting the nativeQuery property to true. The value itself is the query we want to be executed.

I hope that helps. It seems a clear solution.

Solution 5:[5]

The projections solutions was the best. (query by id just an example, you can just use extends CRUD Operations)

Just add the Interface as response to the query as

Example repository :

@Query(select * from tableA where tableA = :id)
DisplayLangLongI findTableAbyId(@Param(value = "id") Long id)

Example Interface (DisplayLangLongI.java)

public interface DisplayLangLongI () {
        BigDecimal getLatitude();
        BigDecimal getLongitude();
        ...... (you can add more)
    }

In interface, you can select which parameter/object just to display

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 johncena
Solution 3 morecore
Solution 4 Reneta
Solution 5 Sh4m

Related Questions