Skip to content

Commit 481fe97

Browse files
kurtisvgkurtisvg
authored
Updated CloudSQL samples. (GoogleCloudPlatform#989)
* Updated CloudSQL samples. * Updated pom.xml for new postgres location. * Address feedback. * Update comment.
1 parent f48dea1 commit 481fe97

File tree

8 files changed

+31
-69
lines changed

8 files changed

+31
-69
lines changed

appengine-java8/postgres/README.md renamed to appengine-java8/cloudsql-postgres/README.md

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
11
# Postgre SQL sample for Google App Engine J8
22

3-
<a href="https://console.cloud.google.com/cloudshell/open?git_repo=https://github.com/GoogleCloudPlatform/java-docs-samples&page=editor&open_in_editor=appengine-java8/postgres/README.md">
3+
<a href="https://console.cloud.google.com/cloudshell/open?git_repo=https://github.com/GoogleCloudPlatform/java-docs-samples&page=editor&open_in_editor=appengine-java8/cloudsql-postgres/README.md">
44
<img alt="Open in Cloud Shell" src ="http://gstatic.com/cloudssh/images/open-btn.png"></a>
55

66
This sample demonstrates how to use [PostgreSql](https://cloud.google.com/sql/) on Google App

appengine-java8/postgres/pom.xml renamed to appengine-java8/cloudsql-postgres/pom.xml

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -48,7 +48,6 @@
4848
<!-- [END properties] -->
4949

5050
<dependencies>
51-
<!-- Parent POM defines ${appengine.sdk.version} (updates frequently). -->
5251
<dependency>
5352
<groupId>com.google.appengine</groupId>
5453
<artifactId>appengine-api-1.0-sdk</artifactId>

appengine-java8/postgres/src/main/java/com/example/appengine/postgresql/PostgreSqlServlet.java renamed to appengine-java8/cloudsql-postgres/src/main/java/com/example/appengine/postgresql/PostgreSqlServlet.java

Lines changed: 13 additions & 39 deletions
Original file line numberDiff line numberDiff line change
@@ -17,12 +17,8 @@
1717
package com.example.appengine.postgresql;
1818

1919
import com.google.common.base.Stopwatch;
20-
2120
import java.io.IOException;
2221
import java.io.PrintWriter;
23-
import java.net.Inet4Address;
24-
import java.net.Inet6Address;
25-
import java.net.InetAddress;
2622
import java.sql.Connection;
2723
import java.sql.DriverManager;
2824
import java.sql.PreparedStatement;
@@ -31,7 +27,6 @@
3127
import java.sql.Timestamp;
3228
import java.util.Date;
3329
import java.util.concurrent.TimeUnit;
34-
3530
import javax.servlet.ServletException;
3631
import javax.servlet.annotation.WebServlet;
3732
import javax.servlet.http.HttpServlet;
@@ -42,7 +37,7 @@
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")
4742
public class PostgreSqlServlet extends HttpServlet {
4843

@@ -52,11 +47,11 @@ public 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
}

appengine-java8/postgres/src/main/webapp/WEB-INF/appengine-web.xml renamed to appengine-java8/cloudsql-postgres/src/main/webapp/WEB-INF/appengine-web.xml

File renamed without changes.

appengine-java8/postgres/src/main/webapp/WEB-INF/web.xml renamed to appengine-java8/cloudsql-postgres/src/main/webapp/WEB-INF/web.xml

File renamed without changes.

‎appengine-java8/cloudsql/pom.xml‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -48,7 +48,6 @@
4848
<!-- [END properties] -->
4949

5050
<dependencies>
51-
<!-- Parent POM defines ${appengine.sdk.version} (updates frequently). -->
5251
<dependency>
5352
<groupId>com.google.appengine</groupId>
5453
<artifactId>appengine-api-1.0-sdk</artifactId>
@@ -78,8 +77,9 @@
7877
</dependency>
7978
<dependency>
8079
<groupId>com.google.cloud.sql</groupId>
81-
<artifactId>mysql-socket-factory</artifactId> <!-- mysql-socket-factory-connector-j-6 if using 6.x.x -->
82-
<version>1.0.4</version>
80+
<!-- If using MySQL 6.x driver, use mysql-socket-factory-connector-j-6 instead -->
81+
<artifactId>mysql-socket-factory</artifactId>
82+
<version>1.0.5</version>
8383
</dependency>
8484
<!-- [END dependencies] -->
8585
</dependencies>

‎appengine-java8/cloudsql/src/main/java/com/example/appengine/cloudsql/CloudSqlServlet.java‎

Lines changed: 13 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -44,20 +44,22 @@
4444
// [START example]
4545
@SuppressWarnings("serial")
4646
// With @WebServlet annotation the webapp/WEB-INF/web.xml is no longer required.
47-
@WebServlet(name = "CloudSQL", description = "CloudSQL: Write low order IP address to Cloud SQL",
47+
@WebServlet(name = "CloudSQL",
48+
description = "CloudSQL: Write timestamps of visitors to Cloud SQL",
4849
urlPatterns = "/cloudsql")
4950
public class CloudSqlServlet extends HttpServlet {
5051
Connection conn;
5152

5253
@Override
5354
public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException,
5455
ServletException {
55-
final String createTableSql = "CREATE TABLE IF NOT EXISTS visits ( visit_id INT NOT NULL "
56-
+ "AUTO_INCREMENT, user_ip VARCHAR(46) NOT NULL, timestamp DATETIME NOT NULL, "
57-
+ "PRIMARY KEY (visit_id) )";
58-
final String createVisitSql = "INSERT INTO visits (user_ip, timestamp) VALUES (?, ?)";
59-
final String selectSql = "SELECT user_ip, timestamp FROM visits ORDER BY timestamp DESC "
60-
+ "LIMIT 10";
56+
57+
final String createTableSql = "CREATE TABLE IF NOT EXISTS visits ( "
58+
+ "visit_id SERIAL NOT NULL, ts timestamp NOT NULL, "
59+
+ "PRIMARY KEY (visit_id) );";
60+
final String createVisitSql = "INSERT INTO visits (ts) VALUES (?);";
61+
final String selectSql = "SELECT ts FROM visits ORDER BY ts DESC "
62+
+ "LIMIT 10;";
6163

6264
String path = req.getRequestURI();
6365
if (path.startsWith("/favicon.ico")) {
@@ -67,37 +69,24 @@ public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOExc
6769
PrintWriter out = resp.getWriter();
6870
resp.setContentType("text/plain");
6971

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-
8172
Stopwatch stopwatch = Stopwatch.createStarted();
8273
try (PreparedStatement statementCreateVisit = conn.prepareStatement(createVisitSql)) {
8374
conn.createStatement().executeUpdate(createTableSql);
84-
statementCreateVisit.setString(1, userIp);
85-
statementCreateVisit.setTimestamp(2, new Timestamp(new Date().getTime()));
75+
statementCreateVisit.setTimestamp(1, new Timestamp(new Date().getTime()));
8676
statementCreateVisit.executeUpdate();
8777

8878
try (ResultSet rs = conn.prepareStatement(selectSql).executeQuery()) {
8979
stopwatch.stop();
9080
out.print("Last 10 visits:\n");
9181
while (rs.next()) {
92-
String savedIp = rs.getString("user_ip");
93-
String timeStamp = rs.getString("timestamp");
94-
out.print("Time: " + timeStamp + " Addr: " + savedIp + "\n");
82+
String timeStamp = rs.getString("ts");
83+
out.println("Visited at time: " + timeStamp);
9584
}
96-
out.println("Elapsed: " + stopwatch.elapsed(TimeUnit.MILLISECONDS));
9785
}
9886
} catch (SQLException e) {
9987
throw new ServletException("SQL error", e);
10088
}
89+
out.println("Query time (ms):" + stopwatch.elapsed(TimeUnit.MILLISECONDS));
10190
}
10291

10392
@Override

‎appengine-java8/pom.xml‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -41,6 +41,7 @@
4141
<module>appidentity</module>
4242
<module>bigtable</module>
4343
<module>cloudsql</module>
44+
<module>cloudsql-postgres</module>
4445
<module>datastore</module>
4546
<module>datastore-indexes</module>
4647
<module>datastore-indexes-exploding</module>
@@ -62,7 +63,6 @@
6263
<module>metadata</module>
6364
<module>multitenancy</module>
6465
<module>oauth2</module>
65-
<module>postgres</module>
6666
<module>pubsub</module>
6767
<module>requests</module>
6868
<module>remote-client</module>

0 commit comments

Comments
 (0)