thoerup/droidradarservice/LocationService.java

package dk.thoerup.droidradarservice;

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;


public class LocationService extends HttpServlet {
        private static final long serialVersionUID = 1L;


        public LocationService() {
                super();
                // TODO Auto-generated constructor stub
        }
        
        
        protected void updateDb(Connection conn, long imei, double latitude, double longitude, String handle, String group) throws SQLException {
                PreparedStatement stmt = null;
                try {
                        stmt = conn.prepareStatement("UPDATE droidradar SET handle=?, groupname=?, latitude=?, longitude=?, updated=now()  WHERE  imei=?");
                        stmt.setString(1, handle);
                        stmt.setString(2, group);
                        stmt.setDouble(3, latitude);
                        stmt.setDouble(4, longitude);
                        stmt.setLong(5, imei);
                        
                        int count = stmt.executeUpdate();
                        
                        
                        System.out.println("count" + count);
                        
                        if (count < 1) {
                                stmt.close();
                                stmt = conn.prepareStatement("INSERT INTO droidradar (imei,handle,groupname,latitude,longitude,updated) VALUES (?,?,?,?,?, now() )" );
                                stmt.setLong(1, imei);
                                stmt.setString(2, handle);
                                stmt.setString(3, group);
                                stmt.setDouble(4, latitude);
                                stmt.setDouble(5, longitude);
                                stmt.execute();
                        }
                } finally {
                        if (stmt != null && !stmt.isClosed())
                                stmt.close();
                }
        }
        
        protected String locateDroids(Connection conn, long imei, 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 imei,handle,groupname,latitude,longitude, " +
                                                   "                     extract(epoch from now())::int - extract(epoch from updated)::int  AS lastupdate, " + 
                                                   "                     earth_distance( ll_to_earth(latitude,longitude), ll_to_earth(?,?))::int AS calcdist " + 
                                                   "               FROM droidradar " +
                           "               WHERE imei <> ? AND updated  > now() - interval '15 minutes'  " +
                                                   "       ) AS droidradar2 " +
                           "WHERE calcdist < 64000 " +
                                               "ORDER BY calcdist ASC ";
                
                System.out.println(SQL);
                
                PreparedStatement stmt = null;
                ResultSet res = null;
                
                StringBuffer buff = new StringBuffer();
                
                buff.append("<?xml version=\"1.0\" ?>\n");
                buff.append("<droids>\n");
                try
                {
                        stmt = conn.prepareStatement(SQL);
                        stmt.setDouble(1, latitude);
                        stmt.setDouble(2, longitude);
                        stmt.setLong(3, imei);
                        
                        res = stmt.executeQuery();
                        
                        while (res.next()) {
                                buff.append("<droid>\n");
                                buff.append("<imei>").append(res.getLong(1) ) .append("</imei>\n");
                                buff.append("<handle>").append(res.getString(2) ) .append("</handle>\n");
                                buff.append("<group>").append(res.getString(3) ) .append("</group>\n");
                                buff.append("<latitude>").append(res.getString(4) ) .append("</latitude>\n");
                                buff.append("<longitude>").append(res.getString(5) ) .append("</longitude>\n");
                                buff.append("<lastupdate>").append(res.getInt(6) ) .append("</lastupdate>\n");                          
                                
                                buff.append("</droid>\n");                              
                        }
                } finally {
                        if (res != null && !res.isClosed())
                                res.close();
                        if (stmt != null && !stmt.isClosed())
                                stmt.close();
                }
                buff.append("</droids>\n");
                return buff.toString();
        }

        protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
                
                long imei = Long.parseLong(request.getParameter("imei"));
                
                double latitude = Double.parseDouble( request.getParameter("latitude"));
                double longitude = Double.parseDouble( request.getParameter("longitude"));
                
                
                String handle = request.getParameter("handle");
                String group = request.getParameter("group");
                
                
                Connection conn = null;
                try {
                        conn = DBConnection.getConnection();
                        
                        updateDb(conn, imei, latitude, longitude, handle, group);
                        
                        String xml = locateDroids(conn, imei, 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	package dk.thoerup.droidradarservice;
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
10	import javax.servlet.ServletException;
11	import javax.servlet.http.HttpServlet;
12	import javax.servlet.http.HttpServletRequest;
13	import javax.servlet.http.HttpServletResponse;
14
15
16	public class LocationService extends HttpServlet {
17	private static final long serialVersionUID = 1L;
18
19
20	public LocationService() {
21	super();
22	// TODO Auto-generated constructor stub
23	}
24
25
26	protected void updateDb(Connection conn, long imei, double latitude, double longitude, String handle, String group) throws SQLException {
27	PreparedStatement stmt = null;
28	try {
29	stmt = conn.prepareStatement("UPDATE droidradar SET handle=?, groupname=?, latitude=?, longitude=?, updated=now() WHERE imei=?");
30	stmt.setString(1, handle);
31	stmt.setString(2, group);
32	stmt.setDouble(3, latitude);
33	stmt.setDouble(4, longitude);
34	stmt.setLong(5, imei);
35
36	int count = stmt.executeUpdate();
37
38
39	System.out.println("count" + count);
40
41	if (count < 1) {
42	stmt.close();
43	stmt = conn.prepareStatement("INSERT INTO droidradar (imei,handle,groupname,latitude,longitude,updated) VALUES (?,?,?,?,?, now() )" );
44	stmt.setLong(1, imei);
45	stmt.setString(2, handle);
46	stmt.setString(3, group);
47	stmt.setDouble(4, latitude);
48	stmt.setDouble(5, longitude);
49	stmt.execute();
50	}
51	} finally {
52	if (stmt != null && !stmt.isClosed())
53	stmt.close();
54	}
55	}
56
57	protected String locateDroids(Connection conn, long imei, double latitude, double longitude) throws SQLException {
58	//inner select is workaround from not being able to use a calculated column directly in where clause
59	final String SQL = "SELECT * FROM ( "+
60	" SELECT imei,handle,groupname,latitude,longitude, " +
61	" extract(epoch from now())::int - extract(epoch from updated)::int AS lastupdate, " +
62	" earth_distance( ll_to_earth(latitude,longitude), ll_to_earth(?,?))::int AS calcdist " +
63	" FROM droidradar " +
64	" WHERE imei <> ? AND updated > now() - interval '15 minutes' " +
65	" ) AS droidradar2 " +
66	"WHERE calcdist < 64000 " +
67	"ORDER BY calcdist ASC ";
68
69	System.out.println(SQL);
70
71	PreparedStatement stmt = null;
72	ResultSet res = null;
73
74	StringBuffer buff = new StringBuffer();
75
76	buff.append("<?xml version=\"1.0\" ?>\n");
77	buff.append("<droids>\n");
78	try
79	{
80	stmt = conn.prepareStatement(SQL);
81	stmt.setDouble(1, latitude);
82	stmt.setDouble(2, longitude);
83	stmt.setLong(3, imei);
84
85	res = stmt.executeQuery();
86
87	while (res.next()) {
88	buff.append("<droid>\n");
89	buff.append("<imei>").append(res.getLong(1) ) .append("</imei>\n");
90	buff.append("<handle>").append(res.getString(2) ) .append("</handle>\n");
91	buff.append("<group>").append(res.getString(3) ) .append("</group>\n");
92	buff.append("<latitude>").append(res.getString(4) ) .append("</latitude>\n");
93	buff.append("<longitude>").append(res.getString(5) ) .append("</longitude>\n");
94	buff.append("<lastupdate>").append(res.getInt(6) ) .append("</lastupdate>\n");
95
96	buff.append("</droid>\n");
97	}
98	} finally {
99	if (res != null && !res.isClosed())
100	res.close();
101	if (stmt != null && !stmt.isClosed())
102	stmt.close();
103	}
104	buff.append("</droids>\n");
105	return buff.toString();
106	}
107
108	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
109
110	long imei = Long.parseLong(request.getParameter("imei"));
111
112	double latitude = Double.parseDouble( request.getParameter("latitude"));
113	double longitude = Double.parseDouble( request.getParameter("longitude"));
114
115
116	String handle = request.getParameter("handle");
117	String group = request.getParameter("group");
118
119
120	Connection conn = null;
121	try {
122	conn = DBConnection.getConnection();
123
124	updateDb(conn, imei, latitude, longitude, handle, group);
125
126	String xml = locateDroids(conn, imei, latitude, longitude);
127
128	response.setContentType("text/xml");
129	response.getWriter().print(xml);
130
131	conn.close();
132	conn = null;
133
134
135	} catch (Exception e) {
136	throw new ServletException(e);
137	} finally {
138	try {
139	if (conn != null)
140	conn.close();
141	} catch (Throwable t) {}
142	}
143
144	}
145
146	}