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