[eclipselink-users] Running a Native Query with Non-Entity results, poss

["Tim Hollosy" <hollosyt@xxxxxxxxx>]

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
entity.

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
interesting.

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
bla,bla2,bla3
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) {
                               finalResults.add(new
OurTransferObject((String)res.get(0),(String)res.get(1),(String)res.get(2)));
                       }

                       return finalResults;
               }

               return null;


       }



Thanks,
Tim