Return custom object from Spring Data with Native Query

Question

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.

Consumerism

Here's a good example. – Holbrooke

Please refer to my answer in #29083249 – Aleciaaleck

Does this answer your question? Spring Data JPA map the native query result to Non-Entity POJO – Walkon

@Lu55 this question already has an answer since 2017. – Consumerism

Answer 1

Found the answer on another post. Basically I used SqlResultSetMapping along with ConstructorResult (no other way worked out) with a special attention to a comment on the accepted answer of the mentioned post: you need to add the @NamedNativeQuery annotation to the entity of the used interface AND prepend the entity's name with a . otherwise it won't work.

Example:

@Entity
@Table(name = "grupo_setorial")
@SqlResultSetMapping(
        name = "mapeamentoDeQuadrantes",
        classes = {
                @ConstructorResult(
                        targetClass = Coordenada.class,
                        columns = {
                                @ColumnResult(name = "latitude"),
                                @ColumnResult(name = "longitude")
                        }
                )
        }
)
@NamedNativeQuery(
        name = "GrupoCensitario.obterPerimetroDosSetores",
        query = "SELECT latitude as latitude, longitude as longitude FROM coordenadas where id_setor IN (:setores)",
        resultSetMapping = "mapeamentoDeQuadrantes"
)
public class GrupoCensitario {

Consumerism

@Manu it's been some time I did this so I honestly can't remember the "prepend the entity's name with a ." part, but I pasted above what is currently in production. – Consumerism

The Prepend entity name part clarified since it is important. If your nativequery name is obterPerimetroDosSetores and the entity name is GrupoCensitario, then you need to name it GrupoCensitario.obterPerimetroDosSetores in the @NamedNativeQuery for Spring to be able to figure out from the repo that the obterPerimetroDosSetores query is in the GrupoCensitario class. Hope that helps. – Woorali

For the 1 come after me: You will face this issue soon (need to add @Query(nativeQuery=true) for the function in repo) and can you put this thing above some where else: No, complier don't like it, put in entity pls #49056584 – Smoot

Answer 2

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.

Stearoptene

Interesting stuff. I'll give it a try before accepting as the answer. But I wonder: what if I didn't have an Entity? I wouldn't be able to use org.springframework.data.repository.*. In such case I would say johncena's answer is the one. – Consumerism

@SidneydeMoraes PersonSummary is not an entity – Stearoptene

@SidneydeMoraes if you don't have entities at all, why use spring-data repositories anyway? This finder can only act as a complementary method to an already created Spring data repository. Otherwise I would go with plain jdbcTemplate+rowmapper solution . – Stearoptene

I'd like to keep the convenience of using an interface repository with a single annotation such as @Query instead of having to deal with injection of Entity Managers and all that stuff. – Consumerism

Sorry, @Stearoptene but it didnt work. I found another way. Thanks for taking your time to see this. – Consumerism

In project description: "the tests which make use of native queries fail" because it doesn't work. – Coterie

@celebes thanks for the comment. I updated the response. – Stearoptene

The ticket you link to is fixed for versions 1.10.12 and 1.11.8 too, but still the given code doesn't work.. :-( According to the author you need Hibernate Hibernate 5.2.11 or newer too.. – Cilice

Note that you might have issues for camelCase fields. Don't forget to double-quote name column aliases. (Had this issue on postgres). – Stubstad

@Itsalllas the query doesn't have to project subfields of Person class. there can be fields like avgAge – Hookup

Answer 3

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

Strawboard

I found that SqlResultSetMapping natively works only with Entities and wouldn't work for non-entity POJO's as seen on this post. I'll give it a try with ConstructorResult. – Consumerism

So, ConstructorResult is the way to go. Please check the answer I added. – Consumerism

Answer 4

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.

Nicodemus

Answer 5

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.

Sputter

I appreciate your contribution but this question already has an accepted answer. Moreover the question relates to returning custom value objects instead of entities, which is the regular usage of spring-data repositories. – Consumerism

@reneta is native queries fast? – Fico

@KumaresanPerumal - I am not sure, I do not think there is a speed difference - the native queries are usually used when you have a more complex query or unusual query to make. – Sputter

Answer 6

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

Eulaliaeulaliah

did not see it ;( – Eulaliaeulaliah