How to return a list of custom mode from a custom query created using @query

Question

I've a JPA DATA repository which contains a certain custom method, something like:

@Query(value=<quite-complex-query>, nativeQuery=true)
List<Object> myCustomRepoMethod();

Now the problem is that I'm returning a List<Object>, instead I want to return a list of MyCustomType, but it's not working, because the query is returning a few columns which apparently are not able to be mapped to MyCustomType's fields.

How can I solve this?

Possible duplicate of [Spring Data JPA map the result to Non-Entity POJO](http://stackoverflow.com/questions/29082749/spring-data-jpa-map-the-result-to-non-entity-pojo) — Alan Hay, Nov 03 '16 at 09:16

score 0 · Answer 1 · answered Nov 02 '16 at 16:42

0

Are you trying with @Query(value=<quite-complex-query>, nativeQuery=true) List<MyCustomType> myCustomRepoMethod();

answered Nov 02 '16 at 16:42

Kenry Sanchez

1,703
2
18
24

Of course, I tried also with that, but it gives me an error of conversion from `[Object[]]` to `[MyCustomType]`. – nbro Nov 02 '16 at 16:52
check out this doc http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.at-query – Kenry Sanchez Nov 02 '16 at 17:33
i am looking for the reason of the issue – Kenry Sanchez Nov 02 '16 at 17:33
maybe the problem is in the query.. can you put it?? – Kenry Sanchez Nov 02 '16 at 17:34
It's not in the query, which returns successfully what I'm querying. The problem is that the query is not aware of `MyCustomType`. – nbro Nov 02 '16 at 17:46

score 0 · Answer 2 · answered Nov 02 '16 at 17:24

0

You can use SqlResultSetMapping and ConstructorResult to achieve what you need.

This is an example of how to map your SQL result set to your POJO:

   Query q = em.createNativeQuery(
      "SELECT c.id, c.name, COUNT(o) as orderCount, AVG(o.price) AS avgOrder " +
      "FROM Customer c, Orders o " +
      "WHERE o.cid = c.id " +
      "GROUP BY c.id, c.name",
      "CustomerDetailsResult");

   @SqlResultSetMapping(
       name="CustomerDetailsResult",
       classes={
          @ConstructorResult(
               targetClass=com.acme.CustomerDetails.class,
                 columns={
                    @ColumnResult(name="id"),
                    @ColumnResult(name="name"),
                    @ColumnResult(name="orderCount"),
                    @ColumnResult(name="avgOrder", type=Double.class)
                    }
          )
       }
      )

answered Nov 02 '16 at 17:24

Paulius Matulionis

23,085
22
103
143

Could you please give an example in the context of Spring JPA repositories, which are interfaces.. – nbro Nov 02 '16 at 17:48
Put the `@SqlResultSetMapping` bit in your Entity class. Put the SQL as the value of the `@Query annotation`. – Alan Hay Nov 02 '16 at 18:21
@AlanHay Yes, but in the query above we have as second parameter `CustomerDetailsResult`... – nbro Nov 03 '16 at 09:04
See here: http://stackoverflow.com/questions/29082749/spring-data-jpa-map-the-result-to-non-entity-pojo Make it a Named Query and reference that from your Repository. – Alan Hay Nov 03 '16 at 09:12
@AlanHay Apparently I should specify the `@NamedNativeQuery` above the class of the entity I want to return, right? I did it and I also created a dedicated repository for the entity I want to return from my initial query, where I put the method which returns my entity. Now I'm having the problem: `Unsatisfied dependency expressed through field 'repoVariableAutowired'`. – nbro Nov 03 '16 at 12:26

How to return a list of custom mode from a custom query created using @query

2 Answers2