ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From hydpradeep <pradeep.annamr...@ge.com>
Subject using iterate with UNION
Date Mon, 23 Mar 2009 07:22:00 GMT

Hi All
I am trying to pass list of MapPointBeans to the query. The MapPointBean
contains a latitude and longitude for one location. I want to dynamically
place the latitudes and longitudes in the query.
The query is not returning any records (see Query1). but when I remove the
iterate tag (see Query 2), its returning records. Pls tell me if there is
any thing wrong in the Query 1..

Query 1
--------
<statement id="searchlocation" parameterClass="java.util.List"
		resultClass="com.apps.spring.models.LocationBean">
                <iterate conjunction="UNION">
			<![CDATA[ 
				SELECT l.location_code as locationCode FROM location_schema.locations l
WHERE 
				(( 3959 * acos( cos(
				((#[].latitude# *3.1415926535897932684626433)/180) ) * cos( ((
				l.latitude*3.1415926535897932684626433)/180 ) ) * cos(
				((-l.longitude*3.1415926535897932684626433)/180 ) -
				((- #[].longitude# *3.1415926535897932684626433)/180) ) + sin(
				((#[].latitude# *3.1415926535897932684626433)/180) ) * sin(
				((l.latitude *3.1415926535897932684626433)/180 ) ) ) ) >
				7500)
				
			]]>
		</iterate>
</statement>


Its working fine when I remove the iterate tag(Query 2), but its not
returning any records when I use iterate tag
Query 2
-------
<select id="searchlocation" parameterClass="list"
		resultClass="com.penske.apps.locationservice.spring.models.LocationBean">

		<![CDATA[ 
			SELECT l.location_code as locationCode
			FROM psk_location.locations l 
			where (( 3959 * acos( cos( ((33.6193*3.1415926535897932684626433)/180) )
* 
			cos( (( l.latitude*3.1415926535897932684626433)/180 ) ) * 
			cos( ((-l.longitude*3.1415926535897932684626433)/180 ) -
((-111.9036*3.1415926535897932684626433)/180) ) + 
			sin( ((33.6193*3.1415926535897932684626433)/180) ) * sin( ((l.latitude
*3.1415926535897932684626433)/180 ) ) ) ) > 7500)
			UNION
			(SELECT l.location_code as locationCode
			FROM psk_location.locations l 
			where (( 3959 * acos( cos( ((32.8689*3.1415926535897932684626433)/180) )
* 
			cos( (( l.latitude*3.1415926535897932684626433)/180 ) ) * 
			cos( ((-l.longitude*3.1415926535897932684626433)/180 ) -
((-97.2381*3.1415926535897932684626433)/180) ) + 
			sin( ((32.8689*3.1415926535897932684626433)/180) ) * sin( ((l.latitude
*3.1415926535897932684626433)/180 ) ) ) ) > 7500))
				
			]]>

</select>

Here is the DAO method
public List searchLocation(LocationSearchBean locationSearchBean)
			throws LocationServiceDataAccessException {
		List lstLocations = null;
		MapPointBean mapPointBean = null;
		String radius = null;
		if (locationSearchBean != null) {
			
			lstLocations = new ArrayList();
			
			mapPointBean = new MapPointBean();
			mapPointBean.setLatitude(33.6193);
			mapPointBean.setLongitude(-111.9036);
			lstLocations.add(mapPointBean);
			
			mapPointBean = new MapPointBean();
			mapPointBean.setLatitude(32.8689);
			mapPointBean.setLongitude(-97.2381);
			lstLocations.add(mapPointBean);
			
		}
		try {
			List lst = getSqlMapClientTemplate().queryForList("searchlocation",
lstLocations);
			System.out.print("lst : " + lst);
		} catch (DataAccessException ex) {
			logger.error("In LocationDaoImpl - searchLocation ", ex);
			throw new LocationServiceDataAccessException(ex);
		}
		return lstLocations;
	}

public class MapPointBean implements Serializable {
	/**
	 * 
	 */
	private static final long serialVersionUID = 7904309409352994862L;

	private double latitude;

	private double longitude;

	/**
	 * @return the latitude
	 */
	public double getLatitude() {
		return latitude;
	}

	/**
	 * @param latitude the latitude to set
	 */
	public void setLatitude(double latitude) {
		this.latitude = latitude;
	}

	/**
	 * @return the longitude
	 */
	public double getLongitude() {
		return longitude;
	}

	/**
	 * @param longitude the longitude to set
	 */
	public void setLongitude(double longitude) {
		this.longitude = longitude;
	}
}

Pls let me know, if there is any thing wrong in the query

Thanks
Pradeep
-- 
View this message in context: http://www.nabble.com/using-iterate-with-UNION-tp22655295p22655295.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.


Mime
View raw message