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

[James Sutherland <jamesssss@xxxxxxxxx>]

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
> 


-----
---
http://wiki.eclipse.org/User:James.sutherland.oracle.com James Sutherland 
http://www.eclipse.org/eclipselink/
 EclipseLink ,  http://www.oracle.com/technology/products/ias/toplink/
TopLink 
Wiki:  http://wiki.eclipse.org/EclipseLink EclipseLink , 
http://wiki.oracle.com/page/TopLink TopLink 
Forums:  http://forums.oracle.com/forums/forum.jspa?forumID=48 TopLink , 
http://www.nabble.com/EclipseLink-f26430.html EclipseLink 
Book:  http://en.wikibooks.org/wiki/Java_Persistence Java Persistence 
-- 
View this message in context: http://www.nabble.com/Running-a-Native-Query-with-Non-Entity-results%2C-possible-JPA-2-Idea-tp17155872p17186991.html
Sent from the EclipseLink - Users mailing list archive at Nabble.com.