Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » newbie: Native SQL Query(how to map this result to a pojo )
icon11.gif  newbie: Native SQL Query [message #986745] Wed, 21 November 2012 14:15 Go to next message
yihya hugirat is currently offline yihya hugirat
Messages: 2
Registered: November 2012
Junior Member
Query query = em.createNativeQuery( "SELECT zc_zip, ACOS( SIN(RADIANS(zc_lat)) * SIN(RADIANS(?))"
				+ "+COS(RADIANS(zc_lat)) * COS(RADIANS(?)) * COS(RADIANS(zc_lon)"
				+" - RADIANS(?))) * 6380 AS distance" 
				+" FROM zip_coordinates " 
				+"WHERE ACOS( SIN(RADIANS(zc_lat)) * SIN(RADIANS(?))+ COS(RADIANS(zc_lat)) * COS(RADIANS(?)) * COS(RADIANS(zc_lon)"
				+"- RADIANS(?))) * 6380 < ?  ORDER BY distance;");


it should return a list of zip codes and distances. the list should be from type

public class ZipDistance
{

	String zip;
	float distance;
}

Re: newbie: Native SQL Query [message #986835 is a reply to message #986745] Wed, 21 November 2012 21:20 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1019
Registered: July 2009
Senior Member
I assume ZipDistance has a construtor that takes a String and a float.

JPA native queries are used to return either scalar data or managed entities. So if you are going to use a native query, you will need to handle the scalar values yourself:
  List results = query.getResultList();
  List<ZipDistance> rebuiltResults = new ArrayList(result.size());
  for (Object[] row: results) {
    rebuiltResult.add(new ZipDistance((String)row[0],(Float)row[1]));
  }


Or, if you have a ZipCoords Entity with zip, lat and lon fields that maps to the zip_coordinates table you can then use JPQL to create your query using either the "FUNCTION" JPQL key word like " FUNCTION('SIN', FUNCTION('RADIANS', coords.lat))" or the EclipseLink specific "SQL" JPQL key word. Both are described hereBoth are described here:
http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/Support_for_Native_Database_Functions

Best Regards,
Chris
Re: newbie: Native SQL Query [message #987326 is a reply to message #986835] Mon, 26 November 2012 07:09 Go to previous message
yihya hugirat is currently offline yihya hugirat
Messages: 2
Registered: November 2012
Junior Member
Thank you for your replay it is very helpful.
i have implemented the first suggestion

Best regards
Yihya
Previous Topic:pool: 1 connection
Next Topic:Error on Test #2 JPA/DCN Example Code
Goto Forum:
  


Current Time: Tue Sep 16 07:34:45 GMT 2014

Powered by FUDForum. Page generated in 0.11183 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software