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