1 |
torben |
272 |
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 |
torben |
279 |
|
10 |
torben |
272 |
import javax.servlet.ServletException; |
11 |
|
|
import javax.servlet.http.HttpServlet; |
12 |
|
|
import javax.servlet.http.HttpServletRequest; |
13 |
|
|
import javax.servlet.http.HttpServletResponse; |
14 |
|
|
|
15 |
torben |
279 |
|
16 |
torben |
272 |
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 |
torben |
273 |
" SELECT imei,handle,groupname,latitude,longitude, " + |
61 |
|
|
" extract(epoch from now())::int - extract(epoch from updated)::int AS lastupdate, " + |
62 |
torben |
274 |
" earth_distance( ll_to_earth(latitude,longitude), ll_to_earth(?,?))::int AS calcdist " + |
63 |
torben |
273 |
" FROM droidradar " + |
64 |
|
|
" WHERE imei <> ? AND updated > now() - interval '15 minutes' " + |
65 |
|
|
" ) AS droidradar2 " + |
66 |
torben |
272 |
"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 |
torben |
279 |
buff.append("<?xml version=\"1.0\" ?>\n"); |
77 |
torben |
272 |
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 |
torben |
273 |
buff.append("<lastupdate>").append(res.getInt(6) ) .append("</lastupdate>\n"); |
95 |
torben |
272 |
|
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 |
torben |
279 |
conn = DBConnection.getConnection(); |
123 |
torben |
272 |
|
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 |
|
|
} |