Skip to main content

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index] [List Home]
[eclipselink-users] Running a Native Query with Non-Entity results, possible JPA 2 Idea

This afternoon, I played around with NativeQueries with a non-entity
result (i.e. a mish-mash of scalar columns).

First off, the JPA spec wants you to use a @SqlResultSetMapping with
some column attributes. You then specify the name of your
@SqlResultSetMapping when you call createNativeQuery.

I don't really care for that for a couple of reasons. The first is
they expect you to put it in any ol' random entity so that the JPA
implementation can pick it up. That idea just smells fishy to me,
since by their nature these adhoc queries don't map to any specific

Second, as far as I can tell you don't get anything for free by
specifying this @SqlResultMapping, you still have to fetch the columns
by index name from the results. So unless I'm missing all you get is
some boiler plate code hidden somewhere in your entities!

The other option is to call createNativeQuery with just the SQL
String. When calling query.getResultList() later you get a List of
Vectors back from EclipseLink's implementation.

You can then loop through it and fetch each column by column index.

At the bottom of this message is a common use case, populating a
"Transfer Object" that is just a POJO. It's a bit more code than I
like to write and much of it is boilerplate stuff that's not

What I would like to see in JPA 2 would be the ability to either
return a List of Maps keyed on column aliases, or the ability to pass
a POJO in and using reflection map aliases to property names. I
believe it would be possible to do the former now _if_ there was same
way to get at results by alias name rather than column index.

Is this a good idea? Is it beyond the scope of the "persistence" part
of JPA? Does anyone have a better way to accomplish the same thing
now? Am i totally off base?

Begin Code

       public List<OurTransferObject> getQueryResults()
               List<OurTransferObject> finalResults;
               Query q = getEntityManager().createNativeQuery("SELECT
from bla");

               q.setParameter(1, tag);
               List<Vector> results = (List<Vector>)q.getResultList();

               if(results !=null && !results.isEmpty())
                       finalResults = new ArrayList<OurTransferObject>();
                       for (Vector res : results) {

                       return finalResults;

               return null;



Back to the top