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 java.util.logging.Level;
import java.util.logging.Logger;

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;

        Logger logger = Logger.getLogger( LocateStations.class.toString() );
        
        /**
         * @see HttpServlet#HttpServlet()
         */
        public LocateStations() {
                super();
                // TODO Auto-generated constructor stub
        }

        public enum Requested {
                BY_NAME,
                BY_LOCATION,
                NONE
        }


        protected String getStations(Connection conn, double latitude, double longitude, String name, Requested method) throws SQLException {
                String SQL = "";
                PreparedStatement stmt = null;
                ResultSet res = null;

                StringBuffer buff = new StringBuffer();

                buff.append("<?xml version=\"1.0\" ?>\n");
                buff.append("<stations>\n");    
                
                try
                {

                        switch (method)
                        {
                        case BY_LOCATION:
                                //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, " + 
                                                "                     earth_distance( ll_to_earth(latitude,longitude), ll_to_earth(?,?))::int AS calcdist " + 
                                                "               FROM trainstations " +
                                                "               WHERE enabled = true AND latitude IS NOT NULL AND longitude IS NOT NULL " +
                                                "       ) AS trainstations2 " +
                                                "ORDER BY calcdist ASC " + 
                                                "LIMIT 4 ";
                                stmt = conn.prepareStatement(SQL);
                                stmt.setDouble(1, latitude);
                                stmt.setDouble(2, longitude);

                                break;
                        case BY_NAME:
                                SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,0.0 " + 
                                        "FROM trainstations " + 
                                        "WHERE name ILIKE ? AND latitude IS NOT NULL AND longitude IS NOT NULL " +
                                        "ORDER BY name ";
                                stmt = conn.prepareStatement(SQL);
                                stmt.setString(1, "%" + name + "%");
                                break;
                        default:
                                // This should not be possible
                                logger.severe("getStations(): default switch case");
                        }


                        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 longitude = 0.0;
                double latitude = 0.0;
                String name = "";

                Requested method = Requested.NONE;

                if (request.getParameter("latitude") != null && request.getParameter("latitude") != null) { 
                        latitude = Double.parseDouble( request.getParameter("latitude") );
                        longitude = Double.parseDouble( request.getParameter("longitude") );
                        method = Requested.BY_LOCATION;
                }

                if (request.getParameter("name") != null) {
                        name = request.getParameter("name");
                        method = Requested.BY_NAME;
                }


                if (method != Requested.NONE) {

                        Connection conn = null;
                        try {
                                conn = DBConnection.getConnection();

                                String xml = getStations(conn, latitude, longitude, name, method);

                                response.setContentType("text/xml");
                                response.getWriter().print(xml);

                                conn.close();
                                conn = null;
 
                        } catch (Exception e) {                         
                                logger.log(Level.SEVERE, "Exception while finding stations", e);
                                response.sendError(500);                                                
                        } finally {
                                try {
                                        if (conn != null)
                                                conn.close();
                                } catch (Throwable t) {}
                        }
                } else {
                        response.sendError(400, "not enough parameters");
                }
        }

}
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	torben	348	import java.util.logging.Level;
9			import java.util.logging.Logger;
10	torben	292
11			import javax.servlet.ServletException;
12			import javax.servlet.http.HttpServlet;
13			import javax.servlet.http.HttpServletRequest;
14			import javax.servlet.http.HttpServletResponse;
15
16			import dk.thoerup.traininfoservice.DBConnection;
17
18			/**
19			* Servlet implementation class LocateStations
20			*/
21			public class LocateStations extends HttpServlet {
22			private static final long serialVersionUID = 1L;
23
24	torben	348	Logger logger = Logger.getLogger( LocateStations.class.toString() );
25
26	torben	347	/**
27			* @see HttpServlet#HttpServlet()
28			*/
29			public LocateStations() {
30			super();
31			// TODO Auto-generated constructor stub
32			}
33	torben	322
34	torben	347	public enum Requested {
35			BY_NAME,
36			BY_LOCATION,
37			NONE
38			}
39
40
41			protected String getStations(Connection conn, double latitude, double longitude, String name, Requested method) throws SQLException {
42	torben	322	String SQL = "";
43	torben	292	PreparedStatement stmt = null;
44			ResultSet res = null;
45	torben	347
46	torben	292	StringBuffer buff = new StringBuffer();
47	torben	347
48			buff.append("<?xml version=\"1.0\" ?>\n");
49			buff.append("<stations>\n");
50	torben	292
51			try
52			{
53	torben	347
54			switch (method)
55			{
56			case BY_LOCATION:
57			//inner select is workaround from not being able to use a calculated column directly in where clause
58			SQL = "SELECT * FROM ( "+
59			" SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, " +
60			" earth_distance( ll_to_earth(latitude,longitude), ll_to_earth(?,?))::int AS calcdist " +
61			" FROM trainstations " +
62	torben	377	" WHERE enabled = true AND latitude IS NOT NULL AND longitude IS NOT NULL " +
63	torben	347	" ) AS trainstations2 " +
64			"ORDER BY calcdist ASC " +
65			"LIMIT 4 ";
66			stmt = conn.prepareStatement(SQL);
67	torben	322	stmt.setDouble(1, latitude);
68			stmt.setDouble(2, longitude);
69	torben	347
70			break;
71			case BY_NAME:
72			SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,0.0 " +
73			"FROM trainstations " +
74			"WHERE name ILIKE ? AND latitude IS NOT NULL AND longitude IS NOT NULL " +
75			"ORDER BY name ";
76			stmt = conn.prepareStatement(SQL);
77	torben	322	stmt.setString(1, "%" + name + "%");
78	torben	347	break;
79			default:
80	torben	348	// This should not be possible
81			logger.severe("getStations(): default switch case");
82	torben	322	}
83	torben	347
84
85
86
87	torben	292	res = stmt.executeQuery();
88	torben	347
89	torben	292	while (res.next()) {
90			buff.append("<station>\n");
91
92	torben	309	buff.append("<id>").append( res.getInt(1) ).append("</id>\n");
93			buff.append("<name>").append( res.getString(2) ) .append("</name>\n");
94			buff.append("<latitude>").append( res.getDouble(3) ) .append("</latitude>\n");
95			buff.append("<longitude>").append( res.getDouble(4) ) .append("</longitude>\n");
96			res.getString(5);
97			buff.append("<fjerntog>").append( !res.wasNull() ) .append("</fjerntog>\n");
98			res.getString(6);
99			buff.append("<stog>").append( !res.wasNull() ) .append("</stog>\n");
100			buff.append("<calcdist>").append( res.getInt(7) ) .append("</calcdist>\n");
101	torben	347
102	torben	292	buff.append("</station>\n");
103			}
104			} finally {
105			if (res != null && !res.isClosed())
106			res.close();
107			if (stmt != null && !stmt.isClosed())
108			stmt.close();
109			}
110			buff.append("</stations>\n");
111			return buff.toString();
112			}
113
114			protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
115	torben	347
116			double longitude = 0.0;
117			double latitude = 0.0;
118			String name = "";
119
120			Requested method = Requested.NONE;
121
122			if (request.getParameter("latitude") != null && request.getParameter("latitude") != null) {
123	torben	322	latitude = Double.parseDouble( request.getParameter("latitude") );
124			longitude = Double.parseDouble( request.getParameter("longitude") );
125	torben	347	method = Requested.BY_LOCATION;
126			}
127
128			if (request.getParameter("name") != null) {
129			name = request.getParameter("name");
130			method = Requested.BY_NAME;
131			}
132
133
134			if (method != Requested.NONE) {
135
136			Connection conn = null;
137	torben	292	try {
138	torben	347	conn = DBConnection.getConnection();
139
140			String xml = getStations(conn, latitude, longitude, name, method);
141
142			response.setContentType("text/xml");
143			response.getWriter().print(xml);
144
145			conn.close();
146			conn = null;
147	torben	348
148			} catch (Exception e) {
149			logger.log(Level.SEVERE, "Exception while finding stations", e);
150			response.sendError(500);
151	torben	347	} finally {
152			try {
153			if (conn != null)
154			conn.close();
155			} catch (Throwable t) {}
156			}
157			} else {
158			response.sendError(400, "not enough parameters");
159	torben	292	}
160			}
161
162			}