Spring JPA. How to map from @Query(nativeQuery = true) to a POJO

Question

Have a query, let it be

select 1 "colName"

I want to map the result to a POJO type using Spring Data JPA.

Thus the picture is:

public interface MyAwesomeSuperInterface extends CrudRepository {
    @Query(value = "select 1 \"colName\"", nativeQuery = true)
    List<POJO> something();
}

And the question is HOW to map it to the POJO.class?

Following the common suggestions I assume I'll get:

No, I don't want to change it to JSQL and do a 'new POJO'.
Why? Because I have a complex sql query, which isn't reflectable to JSQL.
No, I will not bring up the query. I merely want to know how to map the upper example to a POJO using Spring Data. Thank you

score 5 · Accepted Answer · answered Jun 22 '20 at 13:17

5

You can use DTO projection with native queries:

// Projection Interface
public interface UserProjection {
    String getName();
    String getEmail();
    Integer getId();
    String getComment();
}

public interface UserRepository extends CrudRepository<User, Integer> {
    @Query(value = "select  u.name, u.email, c.comment from User u join 
                    Comment c on u.id = c.user_id where u.id in :ids", nativeQuery = true)
        List<UserProjection> getUserInterface(List<Integer> ids);
    }

This is one example I recently tried with DTO projections. This will Simply map result of the native query to UserProjection. For more information read: Spring Data JPA Projection support for native queries

answered Jun 22 '20 at 13:17

Mohd Waseem

1,244
2
15
36

And there I was hoping not to take this road. I'm currently giving this a shot, but the fact that I get my CustomPojoInterface, which is a TupleMap instance if I look at it, is something, which I wished to evade. It does work and does give me the possibility to map it to a regular pojo, but looks like a workaround. – Draaksward Jun 22 '20 at 13:24
Map data in `List – Eklavya Jun 22 '20 at 14:26
You need all aliases in the select clause otherwise interface projection won't work – Geyser14 Jan 04 '23 at 23:08

score -1 · Answer 2 · answered Jun 22 '20 at 13:03

-1

It should auto-convert from sql query to pojo you need to define the correct datatype in below example I am using List<User> as the query will return all the data from the table :

@Query("select * from User u")
List<User> findUsers();

If you specify columns then you need to specify constructor in pojo which accepts the same fields.

You can also use parameterized query :

@Query("select * from User u where u.user_id =: userId")
List<User> findUserById(@Param("userId") String userId);

You can also refer to this document : https://www.baeldung.com/spring-data-jpa-query

it will work hoping for good.

answered Jun 22 '20 at 13:03

Nitesh Sharma

545
3
14

1

again, NativeQuery – Draaksward Jun 22 '20 at 13:14
and pojo's instead of entities – Draaksward Jun 22 '20 at 13:16
'SqlResultSetMapping` maps to POJO class definition, and it requires `@NamedNativeQuery`, which brings two problems: a) you need to call it; b) you need to declare `@Query(name=theNameOfThatNamedQuery`), and it should resolve it in the end(but it didn't). – Draaksward Jun 22 '20 at 13:50
i guess you can also try something like this : [https://stackoverflow.com/questions/29082749/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) – Nitesh Sharma Jun 22 '20 at 13:50
which is the example I was refeering to when I stated "but it didn't" – Draaksward Jun 22 '20 at 13:56
In the same example you scroll a little there is another approach well if you get your answer I guess then there is no need for further discussion. – Nitesh Sharma Jun 22 '20 at 14:04
guess will have to stick to the projections approach. Thanks – Draaksward Jun 22 '20 at 14:06

Spring JPA. How to map from @Query(nativeQuery = true) to a POJO

2 Answers2