thoerup/traininfoservice/LocateStations.java

package dk.thoerup.traininfoservice;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import dk.thoerup.traininfoservice.DBConnection;

/**
 * Servlet implementation class LocateStations
 */
public class LocateStations extends HttpServlet {
        private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public LocateStations() {
        super();
        // TODO Auto-generated constructor stub
    }

        protected String getStations(Connection conn, double latitude, double longitude) throws SQLException {
                //inner select is workaround from not being able to use a calculated column directly in where clause
                final String SQL = "SELECT * FROM ( "+
                                                   "               SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, " + 
                                                   "                     earth_distance( ll_to_earth(latitude,longitude), ll_to_earth(?,?))::int AS calcdist " + 
                                                   "               FROM trainstations " +
                                                   "               WHERE latitude IS NOT NULL AND longitude IS NOT NULL " +
                                                   "       ) AS trainstations2 " +
                                               "ORDER BY calcdist ASC " + 
                                               "LIMIT 4 ";
                
                System.out.println(SQL);
                
                PreparedStatement stmt = null;
                ResultSet res = null;
                
                StringBuffer buff = new StringBuffer();
                
                buff.append("<?xml version=\"1.0\" ?>\n");
                buff.append("<stations>\n");
                try
                {
                        stmt = conn.prepareStatement(SQL);
                        stmt.setDouble(1, latitude);
                        stmt.setDouble(2, longitude);
                        
                        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");
                                buff.append("<calcdist>").append( res.getInt(7) ) .append("</calcdist>\n");
                                
                                buff.append("</station>\n");                            
                        }
                } finally {
                        if (res != null && !res.isClosed())
                                res.close();
                        if (stmt != null && !stmt.isClosed())
                                stmt.close();
                }
                buff.append("</stations>\n");
                return buff.toString();
        }

        protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
                
                double latitude = Double.parseDouble( request.getParameter("latitude") );
                double longitude = Double.parseDouble( request.getParameter("longitude") );
                                
                Connection conn = null;
                try {
                        conn = DBConnection.getConnection();
                        
                        String xml = getStations(conn, latitude, longitude);
                        
                        response.setContentType("text/xml");
                        response.getWriter().print(xml);
                        
                        conn.close();
                        conn = null;
                        
                } catch (Exception e) {
                        throw new ServletException(e);
                } finally {
                        try {
                                if (conn != null)
                                        conn.close();
                        } catch (Throwable t) {}
                }
        }

}
1	torben	292	package dk.thoerup.traininfoservice;
2
3			import java.io.IOException;
4			import java.sql.Connection;
5			import java.sql.PreparedStatement;
6			import java.sql.ResultSet;
7			import java.sql.SQLException;
8
9			import javax.servlet.ServletException;
10			import javax.servlet.http.HttpServlet;
11			import javax.servlet.http.HttpServletRequest;
12			import javax.servlet.http.HttpServletResponse;
13
14			import dk.thoerup.traininfoservice.DBConnection;
15
16			/**
17			* Servlet implementation class LocateStations
18			*/
19			public class LocateStations extends HttpServlet {
20			private static final long serialVersionUID = 1L;
21
22			/**
23			* @see HttpServlet#HttpServlet()
24			*/
25			public LocateStations() {
26			super();
27			// TODO Auto-generated constructor stub
28			}
29
30			protected String getStations(Connection conn, double latitude, double longitude) throws SQLException {
31			//inner select is workaround from not being able to use a calculated column directly in where clause
32			final String SQL = "SELECT * FROM ( "+
33	torben	309	" SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, " +
34	torben	292	" earth_distance( ll_to_earth(latitude,longitude), ll_to_earth(?,?))::int AS calcdist " +
35			" FROM trainstations " +
36	torben	301	" WHERE latitude IS NOT NULL AND longitude IS NOT NULL " +
37	torben	292	" ) AS trainstations2 " +
38			"ORDER BY calcdist ASC " +
39			"LIMIT 4 ";
40
41			System.out.println(SQL);
42
43			PreparedStatement stmt = null;
44			ResultSet res = null;
45
46			StringBuffer buff = new StringBuffer();
47
48			buff.append("<?xml version=\"1.0\" ?>\n");
49			buff.append("<stations>\n");
50			try
51			{
52			stmt = conn.prepareStatement(SQL);
53			stmt.setDouble(1, latitude);
54			stmt.setDouble(2, longitude);
55
56			res = stmt.executeQuery();
57
58			while (res.next()) {
59			buff.append("<station>\n");
60
61	torben	309	buff.append("<id>").append( res.getInt(1) ).append("</id>\n");
62			buff.append("<name>").append( res.getString(2) ) .append("</name>\n");
63			buff.append("<latitude>").append( res.getDouble(3) ) .append("</latitude>\n");
64			buff.append("<longitude>").append( res.getDouble(4) ) .append("</longitude>\n");
65			res.getString(5);
66			buff.append("<fjerntog>").append( !res.wasNull() ) .append("</fjerntog>\n");
67			res.getString(6);
68			buff.append("<stog>").append( !res.wasNull() ) .append("</stog>\n");
69			buff.append("<calcdist>").append( res.getInt(7) ) .append("</calcdist>\n");
70	torben	292
71			buff.append("</station>\n");
72			}
73			} finally {
74			if (res != null && !res.isClosed())
75			res.close();
76			if (stmt != null && !stmt.isClosed())
77			stmt.close();
78			}
79			buff.append("</stations>\n");
80			return buff.toString();
81			}
82
83			protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
84
85			double latitude = Double.parseDouble( request.getParameter("latitude") );
86			double longitude = Double.parseDouble( request.getParameter("longitude") );
87
88			Connection conn = null;
89			try {
90			conn = DBConnection.getConnection();
91
92			String xml = getStations(conn, latitude, longitude);
93
94			response.setContentType("text/xml");
95			response.getWriter().print(xml);
96
97			conn.close();
98			conn = null;
99
100			} catch (Exception e) {
101			throw new ServletException(e);
102			} finally {
103			try {
104			if (conn != null)
105			conn.close();
106			} catch (Throwable t) {}
107			}
108			}
109
110			}