Skip to main content

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

In JPA I think you are suppose to get an Object Array (Object[]) back from a
native SQL query when you do not give a class, odd that we are returning a
List (Vector).  I agree that a Map is more useful, (this is actually what
the native EclipseLink Session API returns).  I would suggest you log an
enhancement request in Bugzilla for a query hint that determines the result
type, (i.e. Array, List, Map, Value).

Tim Hollosy wrote:
> 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

--- James Sutherland
 EclipseLink ,
Wiki: EclipseLink , TopLink 
Forums: TopLink , EclipseLink 
Book: Java Persistence 
View this message in context:
Sent from the EclipseLink - Users mailing list archive at

Back to the top