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;

/**
 * Servlet implementation class LocateStations
 */
public class LocateStations extends HttpServlet {
        private static final long serialVersionUID = 1L;

        Logger logger = Logger.getLogger( LocateStations.class.toString() );


        protected String transformToIntList(String input) {             
                String strings[] = input.split(",");
                
                StringBuffer sb = new StringBuffer();
                sb.append("(");
                for (int i = 0; i<strings.length; i++) {
                        if (i>0) {
                                sb.append(",");
                        }
                        sb.append( Integer.parseInt(strings[i])); //by doing the integer conversion we ensure that it really is a integer
                }
                sb.append(")");
                return sb.toString();           
        }


        protected PreparedStatement createStatement(Connection conn, HttpServletRequest req) throws SQLException {

                PreparedStatement stmt = null;
                String SQL;

                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, stationcode_metro, 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 ";
                        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, stationcode_metro, address, 0.0 " + 
                        "FROM trainstations " + 
                        "WHERE name ILIKE ? AND enabled = true " +
                        "ORDER BY name ";

                        String name = req.getParameter("name").trim();
                        stmt = conn.prepareStatement(SQL);
                        stmt.setString(1, name + "%");
                } else if (req.getParameter("list") != null) {
                        String list = transformToIntList( req.getParameter("list"));
                        SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro, address,0.0 " + 
                        "FROM trainstations " + 
                        "WHERE id IN " + list + " AND enabled = true " +
                        "ORDER BY name ";
                        stmt = conn.prepareStatement(SQL);
                } 

                return stmt;
        }


        protected String formatResultset(PreparedStatement stmt) throws SQLException {
                ResultSet res = null;

                StringBuffer buff = new StringBuffer();

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

                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("<regional>").append( !res.wasNull() ) .append("</regional>\n");
                                res.getString(6);
                                buff.append("<strain>").append( !res.wasNull() ) .append("</strain>\n");
                                res.getString(7);
                                buff.append("<metro>").append( !res.wasNull() ).append("</metro>\n");
                                
                                String address = res.getString(8);
                                buff.append("<address>").append( res.wasNull() ? "" : address ).append("</address>");                           
                                buff.append("<calcdist>").append( res.getInt(9) ) .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();
        }

        @Override
        protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

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

                        PreparedStatement stmt = createStatement(conn, request);
                        if (stmt != null){
                                String xml = formatResultset(stmt);

                                response.setContentType("text/xml");
                                response.getWriter().print(xml);
                        } else {
                                response.sendError(400, "not enough parameters");               
                        }


                } 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) {}
                }
        }

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