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.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

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,updated,  earth_distance( ll_to_earth(latitude,longitude), ll_to_earth(?,?)) 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("<updated>").append(res.getTimestamp(6) ) .append("</updated>\n");
                                buff.append("<distance>").append(res.getDouble(7) ) .append("</distance>\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 {
                        Context ctx = new InitialContext();
                        DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/test");
                        conn = ds.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	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			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			}