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.

Here's a good [example](https://github.com/spring-projects/spring-data-examples/tree/master/jpa/jpa21/src/main/java/example/springdata/jpa/resultsetmappings). — KH_AJU, Jul 05 '18 at 12:51
Please refer to my answer in https://stackoverflow.com/questions/29082749/spring-data-jpa-map-the-native-query-result-to-non-entity-pojo/55167437#55167437 — Waqas Memon, Mar 14 '19 at 17:01
Does this answer your question? [Spring Data JPA map the native query result to Non-Entity POJO](https://stackoverflow.com/questions/29082749/spring-data-jpa-map-the-native-query-result-to-non-entity-pojo) — Ilya Serbis, Nov 02 '20 at 12:52

Sidney de Moraes · Accepted Answer · 2017-09-22T17:55:51.650

19

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 {

edited Sep 22 '17 at 17:55

answered Mar 22 '17 at 03:44

Sidney de Moraes

993
3
11
29

@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. – Sidney de Moraes Sep 22 '17 at 17:56
1

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. – HopeKing Feb 02 '18 at 03:39
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 https://stackoverflow.com/questions/49056084/got-different-size-of-tuples-and-aliases-exception-after-spring-boot-2-0-0-rel – Nam Nguyễn Jun 25 '21 at 01:48

ltsallas · Answer 2 · 2017-11-16T09:15:50.193

10

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.

edited Nov 16 '17 at 09:15

answered Mar 20 '17 at 13:51

ltsallas

1,918
1
12
25

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. – Sidney de Moraes Mar 20 '17 at 14:41
@SidneydeMoraes PersonSummary is not an entity – ltsallas Mar 20 '17 at 14:59
@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 . – ltsallas Mar 20 '17 at 15:46
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. – Sidney de Moraes Mar 20 '17 at 18:11
Sorry, @ltsallas but it didnt work. I found another way. Thanks for taking your time to see this. – Sidney de Moraes Mar 22 '17 at 03:40
In project description: "the tests which make use of native queries fail" because it doesn't work. – Celebes Jun 07 '17 at 16:53
@celebes thanks for the comment. I updated the response. – ltsallas Jun 09 '17 at 13:44
1

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.. – Aritz Nov 16 '17 at 07:29
Note that you might have issues for camelCase fields. Don't forget to double-quote name column aliases. (Had this issue on postgres). – Taras Velykyy Jul 31 '18 at 08:35
@Itsalllas the query doesn't have to project subfields of Person class. there can be fields like avgAge – peja Jan 30 '19 at 01:34

score 3 · Answer 3 · answered Mar 20 '17 at 02:48

3

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

answered Mar 20 '17 at 02:48

johncena

102
6

2

I found that `SqlResultSetMapping` natively works only with Entities and wouldn't work for non-entity POJO's [as seen on this post](http://stackoverflow.com/questions/16420697/spring-data-jpa-how-can-query-return-non-entities-objects-or-list-of-objects). I'll give it a try with `ConstructorResult`. – Sidney de Moraes Mar 20 '17 at 14:58
So, `ConstructorResult` is the way to go. Please check the answer I added. – Sidney de Moraes Mar 22 '17 at 03:40

score 0 · Answer 4 · answered Sep 18 '18 at 13:29

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.

score 0 · Answer 5 · answered Jul 03 '19 at 06:29

0

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.

answered Jul 03 '19 at 06:29

Reneta

486
4
12

1

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. – Sidney de Moraes Jul 04 '19 at 16:38
@reneta is native queries fast? – Kumaresan Perumal Feb 13 '20 at 12:56
1

@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. – Reneta Feb 13 '20 at 13:33

score 0 · Answer 6 · edited Feb 22 '22 at 17:03

0

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

edited Feb 22 '22 at 17:03

Sh4m

1,424
12
30

answered Jun 18 '21 at 10:07

Swimcito

19
4

did not see it ;( – Swimcito Jun 22 '21 at 13:19

Return custom object from Spring Data with Native Query

6 Answers6

Linked