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
        }

        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 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:
                                throw new SQLException("not enough parameters");
                        }


                        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) {
                                throw new ServletException(e);
                        } finally {
                                try {
                                        if (conn != null)
                                                conn.close();
                                } catch (Throwable t) {}
                        }
                } else {
                        response.sendError(400, "not enough parameters");
                }
        }

}
1	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	public enum Requested {
31	BY_NAME,
32	BY_LOCATION,
33	NONE
34	}
35
36
37	protected String getStations(Connection conn, double latitude, double longitude, String name, Requested method) throws SQLException {
38	String SQL = "";
39	PreparedStatement stmt = null;
40	ResultSet res = null;
41
42	StringBuffer buff = new StringBuffer();
43
44	buff.append("<?xml version=\"1.0\" ?>\n");
45	buff.append("<stations>\n");
46
47	try
48	{
49
50	switch (method)
51	{
52	case BY_LOCATION:
53	//inner select is workaround from not being able to use a calculated column directly in where clause
54	SQL = "SELECT * FROM ( "+
55	" SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, " +
56	" earth_distance( ll_to_earth(latitude,longitude), ll_to_earth(?,?))::int AS calcdist " +
57	" FROM trainstations " +
58	" WHERE latitude IS NOT NULL AND longitude IS NOT NULL " +
59	" ) AS trainstations2 " +
60	"ORDER BY calcdist ASC " +
61	"LIMIT 4 ";
62	stmt = conn.prepareStatement(SQL);
63	stmt.setDouble(1, latitude);
64	stmt.setDouble(2, longitude);
65
66	break;
67	case BY_NAME:
68	SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,0.0 " +
69	"FROM trainstations " +
70	"WHERE name ILIKE ? AND latitude IS NOT NULL AND longitude IS NOT NULL " +
71	"ORDER BY name ";
72	stmt = conn.prepareStatement(SQL);
73	stmt.setString(1, "%" + name + "%");
74	break;
75	default:
76	throw new SQLException("not enough parameters");
77	}
78
79
80
81
82	res = stmt.executeQuery();
83
84	while (res.next()) {
85	buff.append("<station>\n");
86
87	buff.append("<id>").append( res.getInt(1) ).append("</id>\n");
88	buff.append("<name>").append( res.getString(2) ) .append("</name>\n");
89	buff.append("<latitude>").append( res.getDouble(3) ) .append("</latitude>\n");
90	buff.append("<longitude>").append( res.getDouble(4) ) .append("</longitude>\n");
91	res.getString(5);
92	buff.append("<fjerntog>").append( !res.wasNull() ) .append("</fjerntog>\n");
93	res.getString(6);
94	buff.append("<stog>").append( !res.wasNull() ) .append("</stog>\n");
95	buff.append("<calcdist>").append( res.getInt(7) ) .append("</calcdist>\n");
96
97	buff.append("</station>\n");
98	}
99	} finally {
100	if (res != null && !res.isClosed())
101	res.close();
102	if (stmt != null && !stmt.isClosed())
103	stmt.close();
104	}
105	buff.append("</stations>\n");
106	return buff.toString();
107	}
108
109	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
110
111	double longitude = 0.0;
112	double latitude = 0.0;
113	String name = "";
114
115	Requested method = Requested.NONE;
116
117	if (request.getParameter("latitude") != null && request.getParameter("latitude") != null) {
118	latitude = Double.parseDouble( request.getParameter("latitude") );
119	longitude = Double.parseDouble( request.getParameter("longitude") );
120	method = Requested.BY_LOCATION;
121	}
122
123	if (request.getParameter("name") != null) {
124	name = request.getParameter("name");
125	method = Requested.BY_NAME;
126	}
127
128
129	if (method != Requested.NONE) {
130
131	Connection conn = null;
132	try {
133	conn = DBConnection.getConnection();
134
135	String xml = getStations(conn, latitude, longitude, name, method);
136
137	response.setContentType("text/xml");
138	response.getWriter().print(xml);
139
140	conn.close();
141	conn = null;
142
143	} catch (Exception e) {
144	throw new ServletException(e);
145	} finally {
146	try {
147	if (conn != null)
148	conn.close();
149	} catch (Throwable t) {}
150	}
151	} else {
152	response.sendError(400, "not enough parameters");
153	}
154	}
155
156	}