1717package com .example .appengine .postgresql ;
1818
1919import com .google .common .base .Stopwatch ;
20-
2120import java .io .IOException ;
2221import java .io .PrintWriter ;
23- import java .net .Inet4Address ;
24- import java .net .Inet6Address ;
25- import java .net .InetAddress ;
2622import java .sql .Connection ;
2723import java .sql .DriverManager ;
2824import java .sql .PreparedStatement ;
3127import java .sql .Timestamp ;
3228import java .util .Date ;
3329import java .util .concurrent .TimeUnit ;
34-
3530import javax .servlet .ServletException ;
3631import javax .servlet .annotation .WebServlet ;
3732import javax .servlet .http .HttpServlet ;
4237@ SuppressWarnings ("serial" )
4338// With @WebServlet annotation the webapp/WEB-INF/web.xml is no longer required.
4439@ WebServlet (name = "PostgreSQL" ,
45- description = "PostgreSQL: Write low order IP address to PostgreSQL" ,
40+ description = "PostgreSQL: Write timestamps of visitors to PostgreSQL" ,
4641 urlPatterns = "/postgresql" )
4742public class PostgreSqlServlet extends HttpServlet {
4843
@@ -52,11 +47,11 @@ pub
6D40
lic class PostgreSqlServlet extends HttpServlet {
5247 public void doGet (HttpServletRequest req , HttpServletResponse resp ) throws IOException ,
5348 ServletException {
5449
55- final String createTableSql = "CREATE TABLE IF NOT EXISTS visits ( visit_id SERIAL NOT NULL, "
56- + "user_ip VARCHAR(46) NOT NULL, ts timestamp NOT NULL, "
50+ final String createTableSql = "CREATE TABLE IF NOT EXISTS visits ( "
51+ + "visit_id SERIAL NOT NULL, ts timestamp NOT NULL, "
5752 + "PRIMARY KEY (visit_id) );" ;
58- final String createVisitSql = "INSERT INTO visits (user_ip, ts) VALUES (?, ?);" ;
59- final String selectSql = "SELECT user_ip, ts FROM visits ORDER BY ts DESC "
53+ final String createVisitSql = "INSERT INTO visits (ts) VALUES (?);" ;
54+ final String selectSql = "SELECT ts FROM visits ORDER BY ts DESC "
6055 + "LIMIT 10;" ;
6156
6257 String path = req .getRequestURI ();
@@ -67,55 +62,34 @@ public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOExc
6762 PrintWriter out = resp .getWriter ();
6863 resp .setContentType ("text/plain" );
6964
70 - // store only the first two octets of a users ip address
71- String userIp = req .getRemoteAddr ();
72- InetAddress address = InetAddress .getByName (userIp );
73- if (address instanceof Inet6Address ) {
74- // nest indexOf calls to find the second occurrence of a character in a string
75- // an alternative is to use Apache Commons Lang: StringUtils.ordinalIndexOf()
76- userIp = userIp .substring (0 , userIp .indexOf (":" , userIp .indexOf (":" ) + 1 )) + ":*:*:*:*:*:*" ;
77- } else if (address instanceof Inet4Address ) {
78- userIp = userIp .substring (0 , userIp .indexOf ("." , userIp .indexOf ("." ) + 1 )) + ".*.*" ;
79- }
80-
8165 Stopwatch stopwatch = Stopwatch .createStarted ();
8266 try (PreparedStatement statementCreateVisit = conn .prepareStatement (createVisitSql )) {
8367 conn .createStatement ().executeUpdate (createTableSql );
84- statementCreateVisit .setString (1 , userIp );
85- statementCreateVisit .setTimestamp (2 , new Timestamp (new Date ().getTime ()));
68+ statementCreateVisit .setTimestamp (1 , new Timestamp (new Date ().getTime ()));
8669 statementCreateVisit .executeUpdate ();
8770
8871 try (ResultSet rs = conn .prepareStatement (selectSql ).executeQuery ()) {
8972 stopwatch .stop ();
9073 out .print ("Last 10 visits:\n " );
9174 while (rs .next ()) {
92- String savedIp = rs .getString ("user_ip" );
9375 String timeStamp = rs .getString ("ts" );
94- out .println ("Time: " + timeStamp + " Addr : "savedIp );
76+ out .println ("Visited at time : " + timeStamp );
9577 }
96- out .println ("Elapsed: " + stopwatch .elapsed (TimeUnit .MILLISECONDS ));
9778 }
9879 } catch (SQLException e ) {
9980 throw new ServletException ("SQL error" , e );
10081 }
82+ out .println ("Query time (ms):" + stopwatch .elapsed (TimeUnit .MILLISECONDS ));
10183 }
10284
10385 @ Override
10486 public void init () throws ServletException {
87+ String url = System .getProperty ("postgresql" );
88+ log ("connecting to: " + url );
10589 try {
106- String url = System .getProperty ("postgresql" );
107- log ("connecting to: " + url );
108- try {
109- Class .forName ("org.postgresql.Driver" );
110- conn = DriverManager .getConnection (url );
111- } catch (ClassNotFoundException e ) {
112- throw new ServletException ("Error loading JDBC Driver" , e );
113- } catch (SQLException e ) {
114- throw new ServletException ("Unable to connect to PostGre" , e );
115- }
116-
117- } finally {
118- // Nothing really to do here.
90+ conn = DriverManager .getConnection (url );
91+ } catch (SQLException e ) {
92+ throw new ServletException ("Unable to connect to PostgreSQL" , e );
11993 }
12094 }
12195}
0 commit comments