Spring : JPA to convert native SQL to non entity pojo

Question

I have native SQL which returns the collection of objects and i would like to get the results as collection of objects(a pojo class which is non entity)

is it possible to get the results from native SQL as collection of non entity?

I am using spring jpa 1.10

Are you looking for [SqlResultSetMapping](http://docs.oracle.com/javaee/5/api/javax/persistence/SqlResultSetMapping.html)? It works only with JPA 2.1 versions or above. — harshavmb, Jun 08 '17 at 05:55
and if not using JPA 2.1 then it depends on your JPA provider, but you dont say which one you use. I know that DataNucleus supports such transformation pre-JPA 2.1 — Neil Stockton, Jun 08 '17 at 06:19

score 1 · Answer 1 · answered Jun 08 '17 at 05:58

1

There is no way to mapping non-entity classes in JPA 1.

Since JPA 2.1, you can use ConstructorResult, Used in conjunction with the SqlResultSetMapping annotation to map the SELECT clause of a SQL query to a constructor.

Here is the example

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 Jun 08 '17 at 05:58

Liping Huang

4,378
4
29
46

Thanks is it possible to have namedquery and map the pojo? – Joe Jun 08 '17 at 06:03
@SAR http://www.concretepage.com/hibernate/native_query_hibernate_annotation might help you – harshavmb Jun 08 '17 at 06:08
1

@harshavmb thanks but what i have is NON ENTITY, and that is where the issue comes up :) – Joe Jun 08 '17 at 06:11
@SAR Actually you just need to configure the query name. and for NamedQuery, there is a resultClass parameter( as remember, you can have a try ). – Liping Huang Jun 08 '17 at 06:11
@LipingHuang No, it did not work, i have tried that too. :), the issues is that it is not entity, that is why all these issues came up – Joe Jun 08 '17 at 06:12
@LipingHuang sure Thank you so much – Joe Jun 08 '17 at 06:20
@SAR and my interesting is you add the namedquery in a non-entity? – Liping Huang Jun 08 '17 at 06:30
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/146132/discussion-between-sar-and-liping-huang). – Joe Jun 08 '17 at 06:31

score 0 · Answer 2 · answered Sep 18 '19 at 17:08

0

Mapping NativeQuery results into a POJO - this is JPA independent solution using @JsonFormat and ObjectMapper, detailed with code sample to what @darshan-patel already mentioned.

answered Sep 18 '19 at 17:08

dinesh salve

197
2
5

Spring : JPA to convert native SQL to non entity pojo

2 Answers2