Jpa Native Query get results

Question

I have created a native query

Query q=em.createNativeQuery("select *\n" +
    "from contaarticoli ca,articolo a\n" +
    "where ca.articolo=a.id\n" +
    "order by ca.qta desc\n" +
    "limit 1;");

In workbench i have created a view :

create view contaarticoli(articolo,qta)as
(
 select a.idarticolo as articolo,sum(a.quantita) as qta
 from articoliordine a
 group by a.idarticolo
);

When I do:

List<Object> lista=q.getResultList();
System.out.println(lista.get(0))

In output i have : [Ljava.lang.Object;@1c5b8a4

Why?

score 4 · Answer 1 · answered Feb 20 '14 at 16:54

4

Because you get back a List<Object[]>. Each array has the same number of columns, corresponding to the query columns.

answered Feb 20 '14 at 16:54

Tassos Bassoukos

16,017
2
36
40

Koitoer · Accepted Answer · 2014-02-20T17:25:04.103

Native queries executed with Query result and Object always until you define the resultClass property in the createNativeQuery method.

 createNativeQuery

Query createNativeQuery(java.lang.String sqlString,
                        java.lang.Class resultClass)

    Create an instance of Query for executing a native SQL query.

    Parameters:
        sqlString - a native SQL query string
        resultClass - the class of the resulting instance(s)

If you have a entity with the same name of the result fields of the query, add resultClass=Class.class and will create objects automatically

Are you are using a join between 2 tables, you can use @SqlResultSetMapping as it follows.

@SqlResultSetMapping(name="ScalarAndEntities",
    entities={
        @EntityResult(name="org.hibernate.test.annotations.query.Night", fields = {
            @FieldResult(name="id", column="nid"),
            @FieldResult(name="duration", column="night_duration"),
            @FieldResult(name="date", column="night_date"),
            @FieldResult(name="area", column="area_id")
        }),
        @EntityResult(name="org.hibernate.test.annotations.query.Area", fields = {
            @FieldResult(name="id", column="aid"),
            @FieldResult(name="name", column="name")
        })
    },
    columns={
        @ColumnResult(name="durationInSec")
    }
)

Use with

Query q = entityManager.createNativeQuery(sqlQuery, "ScalarAndEntities");

Read more about how to retrieve entities from nativequeries here ,

Update above, consider using SqlResultSetMapping to instead of obtain list of objects, return list of entities — Koitoer, Feb 20 '14 at 17:25
Thanks ..:) I have added the resultClass parameter and it works — Alex, Feb 20 '14 at 17:26
It is because in more complex queries when there are more fields than the entity fields, you should tell hibernate how to map those values, it is the reason of exsitence for SqlResultMapping, read the link I put very useful tool to map from native to entities not managed objects — Koitoer, Feb 20 '14 at 17:29

Jpa Native Query get results

2 Answers2