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,updated, earth_distance( ll_to_earth(latitude,longitude), ll_to_earth(?,?)) AS calcdist " + |
62 |
" from droidradar " + |
63 |
" WHERE imei <> ? AND updated > now() - interval '15 minutes' ) AS droidradar2 " + |
64 |
"WHERE calcdist < 64000 " + |
65 |
"ORDER BY calcdist ASC "; |
66 |
|
67 |
System.out.println(SQL); |
68 |
|
69 |
PreparedStatement stmt = null; |
70 |
ResultSet res = null; |
71 |
|
72 |
StringBuffer buff = new StringBuffer(); |
73 |
|
74 |
buff.append("<?xml version=\"1.0\"?>\n"); |
75 |
buff.append("<droids>\n"); |
76 |
try |
77 |
{ |
78 |
stmt = conn.prepareStatement(SQL); |
79 |
stmt.setDouble(1, latitude); |
80 |
stmt.setDouble(2, longitude); |
81 |
stmt.setLong(3, imei); |
82 |
|
83 |
res = stmt.executeQuery(); |
84 |
|
85 |
while (res.next()) { |
86 |
buff.append("<droid>\n"); |
87 |
buff.append("<imei>").append(res.getLong(1) ) .append("</imei>\n"); |
88 |
buff.append("<handle>").append(res.getString(2) ) .append("</handle>\n"); |
89 |
buff.append("<group>").append(res.getString(3) ) .append("</group>\n"); |
90 |
buff.append("<latitude>").append(res.getString(4) ) .append("</latitude>\n"); |
91 |
buff.append("<longitude>").append(res.getString(5) ) .append("</longitude>\n"); |
92 |
buff.append("<updated>").append(res.getTimestamp(6) ) .append("</updated>\n"); |
93 |
buff.append("<distance>").append(res.getDouble(7) ) .append("</distance>\n"); |
94 |
|
95 |
buff.append("</droid>\n"); |
96 |
} |
97 |
} finally { |
98 |
if (res != null && !res.isClosed()) |
99 |
res.close(); |
100 |
if (stmt != null && !stmt.isClosed()) |
101 |
stmt.close(); |
102 |
} |
103 |
buff.append("</droids>\n"); |
104 |
return buff.toString(); |
105 |
} |
106 |
|
107 |
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { |
108 |
|
109 |
long imei = Long.parseLong(request.getParameter("imei")); |
110 |
|
111 |
double latitude = Double.parseDouble( request.getParameter("latitude")); |
112 |
double longitude = Double.parseDouble( request.getParameter("longitude")); |
113 |
|
114 |
|
115 |
String handle = request.getParameter("handle"); |
116 |
String group = request.getParameter("group"); |
117 |
|
118 |
|
119 |
Connection conn = null; |
120 |
try { |
121 |
Context ctx = new InitialContext(); |
122 |
DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/test"); |
123 |
conn = ds.getConnection(); |
124 |
|
125 |
updateDb(conn, imei, latitude, longitude, handle, group); |
126 |
|
127 |
String xml = locateDroids(conn, imei, latitude, longitude); |
128 |
|
129 |
response.setContentType("text/xml"); |
130 |
response.getWriter().print(xml); |
131 |
|
132 |
conn.close(); |
133 |
conn = null; |
134 |
|
135 |
|
136 |
} catch (Exception e) { |
137 |
throw new ServletException(e); |
138 |
} finally { |
139 |
try { |
140 |
if (conn != null) |
141 |
conn.close(); |
142 |
} catch (Throwable t) {} |
143 |
} |
144 |
|
145 |
} |
146 |
|
147 |
} |