--- android/TrainInfoService/src/dk/thoerup/traininfoservice/LocateStations.java	2009/11/01 16:35:48	492
+++ android/TrainInfoService/src/dk/thoerup/traininfoservice/LocateStations.java	2010/09/16 14:04:28	1061
@@ -1,32 +1,37 @@
 package dk.thoerup.traininfoservice;
 
+import java.io.ByteArrayOutputStream;
 import java.io.IOException;
-import java.sql.Connection;
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
 import java.sql.SQLException;
-
 import java.util.logging.Level;
 import java.util.logging.Logger;
 
 import javax.servlet.ServletException;
+import javax.servlet.annotation.WebServlet;
 import javax.servlet.http.HttpServlet;
 import javax.servlet.http.HttpServletRequest;
 import javax.servlet.http.HttpServletResponse;
 
+import org.simpleframework.xml.Serializer;
+import org.simpleframework.xml.core.Persister;
+
+import dk.thoerup.android.traininfo.common.StationBean;
+
 /**
  * Servlet implementation class LocateStations
  */
+@WebServlet(urlPatterns={"/LocateStations"})
 public class LocateStations extends HttpServlet {
 	private static final long serialVersionUID = 1L;
 
 	Logger logger = Logger.getLogger( LocateStations.class.toString() );
 
+	StationDAO stationDao = new StationDAO();
+
 
-	
 	protected String transformToIntList(String input) {		
 		String strings[] = input.split(",");
-		
+
 		StringBuffer sb = new StringBuffer();
 		sb.append("(");
 		for (int i = 0; i<strings.length; i++) {
@@ -40,98 +45,55 @@
 	}
 
 
-	protected PreparedStatement createStatement(Connection conn, HttpServletRequest req) throws SQLException {
-
-		PreparedStatement stmt = null;
-		String SQL;
-
+	protected StationBean getStations(HttpServletRequest req) throws SQLException {
+		StationBean stations = null;
 		if (req.getParameter("latitude") != null && req.getParameter("latitude") != null) {
-			//inner select is workaround from not being able to use a calculated column directly in where clause
-			SQL = "SELECT * FROM ( "+
-			"               SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, address," + 
-			"                     earth_distance( ll_to_earth(latitude,longitude), ll_to_earth(?,?))::int AS calcdist " + 
-			"               FROM trainstations " +
-			"               WHERE enabled = true " +
-			"       ) AS trainstations2 " +
-			"ORDER BY calcdist ASC " + 
-			"LIMIT 4 ";
+			
+			Statistics.getInstance().incrementStationLookupsLocation();
+			
 			double latitude = Double.parseDouble( req.getParameter("latitude") );
 			double longitude = Double.parseDouble( req.getParameter("longitude") );
-			stmt = conn.prepareStatement(SQL);
-			stmt.setDouble(1, latitude);
-			stmt.setDouble(2, longitude);
-		} else if (req.getParameter("name") != null) {
-			SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, address, 0.0 " + 
-			"FROM trainstations " + 
-			"WHERE name ILIKE ? AND enabled = true " +
-			"ORDER BY name ";
+			stations = stationDao.getByLocation(latitude, longitude);
 
+		} else if (req.getParameter("name") != null) {
+			Statistics.getInstance().incrementStationLookupsName();
 			String name = req.getParameter("name").trim();
-			stmt = conn.prepareStatement(SQL);
-			stmt.setString(1, name + "%");
+			stations = stationDao.getByName(name);
+
 		} else if (req.getParameter("list") != null) {
+			Statistics.getInstance().incrementStationLookupsFavorites();
 			String list = transformToIntList( req.getParameter("list"));
-			SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,address,0.0 " + 
-			"FROM trainstations " + 
-			"WHERE id IN " + list + " AND enabled = true " +
-			"ORDER BY name ";
-			stmt = conn.prepareStatement(SQL);
-		} 
-
-		return stmt;
+			stations = stationDao.getByList(list);
+		}
+		return stations;
 	}
 
 
-	protected String formatResultset(PreparedStatement stmt) throws SQLException {
-		ResultSet res = null;
+	protected String formatStations(StationBean stations) throws ServletException {
 
-		StringBuffer buff = new StringBuffer();
+		Serializer serializer = new Persister();
 
-		buff.append("<?xml version=\"1.0\" ?>\n");
-		buff.append("<stations>\n");	
+		ByteArrayOutputStream out = new ByteArrayOutputStream();
 
-		try
-		{
-			res = stmt.executeQuery();
-
-			while (res.next()) {
-				buff.append("<station>\n");
-
-				buff.append("<id>").append( res.getInt(1) ).append("</id>\n");
-				buff.append("<name>").append( res.getString(2) ) .append("</name>\n");
-				buff.append("<latitude>").append( res.getDouble(3) ) .append("</latitude>\n");
-				buff.append("<longitude>").append( res.getDouble(4) ) .append("</longitude>\n");
-				res.getString(5);
-				buff.append("<fjerntog>").append( !res.wasNull() ) .append("</fjerntog>\n");
-				res.getString(6);
-				buff.append("<stog>").append( !res.wasNull() ) .append("</stog>\n");
-				
-				String address = res.getString(7);
-				buff.append("<address>").append( res.wasNull() ? "" : address ).append("</address>");
-				buff.append("<calcdist>").append( res.getInt(8) ) .append("</calcdist>\n");
-
-				buff.append("</station>\n");				
-			}
-		} finally {
-			if (res != null && !res.isClosed())
-				res.close();
-			if (stmt != null && !stmt.isClosed())
-				stmt.close();
+		try {
+			serializer.write(stations, out);
+		} catch (Exception e) {
+			throw new ServletException(e);
 		}
-		buff.append("</stations>\n");
-		return buff.toString();
+		
+		return out.toString();
 	}
 
 	@Override
 	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
 
-		Connection conn = null;
+
 		try {
-			conn = DBConnection.getConnection();
+			StationBean stations = getStations(request);
+
 
-			PreparedStatement stmt = createStatement(conn, request);
-			if (stmt != null){
-				String xml = formatResultset(stmt);
+			if (stations != null){
+				String xml = formatStations(stations);
 
 				response.setContentType("text/xml");
 				response.getWriter().print(xml);
@@ -143,13 +105,7 @@
 		} catch (Exception e) {				
 			logger.log(Level.SEVERE, "Exception while finding stations", e);
 			response.sendError(500);						
-		} finally {
-			try {
-				if (conn != null) {
-					conn.close();
-				}
-			} catch (Exception t) {}
-		}
+		} 
 	}
 
 }