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

Engine Flexible

* If you haven't already, Download and initialize the [Cloud SDK](https://cloud.google.com/sdk/)

`gcloud init`

* If you haven't already, Create an App Engine app within the current Google Cloud Project

`gcloud app create`

* If you haven't already, Setup

[Application Default Credentials](https://developers.google.com/identity/protocols/application-default-credentials)

`gcloud auth application-default login`

* [Create an instance](https://cloud.google.com/sql/docs/postgresql/create-instance)

* [Create a Database](https://cloud.google.com/sql/docs/postgresql/create-manage-databases)

* [Create a user](https://cloud.google.com/sql/docs/postgresql/create-manage-users)

* Note the **Instance connection name** under Overview > properties

Looks like: `projectID:region:instance`

$ mvn clean jetty:run -DINSTANCE_CONNECTION_NAME=instanceConnectionName -Duser=root -Dpassword=myPassowrd -Ddatabase=myDatabase

$ mvn clean appengine:deploy -DINSTANCE_CONNECTION_NAME=instanceConnectionName -Duser=root

-Dpassword=myPassword -Ddatabase=myDatabase

* [Delete your Instance](https://cloud.google.com/sql/docs/postgresql/delete-instance)

<project>

<modelVersion>4.0.0</modelVersion>

<packaging>war</packaging>

<version>1.0-SNAPSHOT</version>

<groupId>com.example.flexible</groupId>

<artifactId>flexible-postgres</artifactId>

<parent>

<artifactId>appengine-flexible</artifactId>

<groupId>com.google.cloud</groupId>

<version>1.0.0</version>

<relativePath>..</relativePath>

</parent>

<!-- [START properties] -->

<properties>

<!-- INSTANCE_CONNECTION_NAME from Cloud Console > SQL > Instance Details

<!-- [START_EXCLUDE silent] -->

<maven.compiler.target>1.8</maven.compiler.target>

<maven.compiler.source>1.8</maven.compiler.source>

<failOnMissingWebXml>false</failOnMissingWebXml> <!-- REQUIRED -->

<jetty>9.4.4.v20170414</jetty>

<!-- [END_EXCLUDE] -->

<INSTANCE_CONNECTION_NAME>newagent-25039:asia-northeast1:la</INSTANCE_CONNECTION_NAME>

<user>postgres</user>

<password>la123456</password>

<database>postgres</database>

<sqlURL>jdbc:postgresql://google/${database}?useSSL=false&amp;socketFactoryArg=${INSTANCE_CONNECTION_NAME}&amp;socketFactory=com.google.cloud.sql.postgres.SocketFactory&amp;user=${user}&amp;password=${password}</sqlURL>

</properties>

<!-- [END properties] -->

<dependencies>

<dependency>

<groupId>com.google.api-client</groupId>

<artifactId>google-api-client</artifactId>

<version>1.23.0</version>

</dependency>

<dependency>

<groupId>com.google.api-client</groupId>

<artifactId>google-api-client-appengine</artifactId>

<version>1.23.0</version>

</dependency>

<dependency>

<groupId>com.google.api-client</groupId>

<artifactId>google-api-client-servlet</artifactId>

<version>1.23.0</version>

</dependency>

<dependency>

<groupId>javax.servlet</groupId>

<artifactId>javax.servlet-api</artifactId>

<version>3.1.0</version>

<type>jar</type>

<scope>provided</scope>

</dependency>

<!-- [START dependencies] -->

<dependency>

<groupId>org.postgresql</groupId>

<artifactId>postgresql</artifactId>

<version>42.1.4.jre7</version>

</dependency>

<dependency>

<groupId>com.google.cloud.sql</groupId>

<artifactId>postgres-socket-factory</artifactId>

<version>1.0.4</version>

</dependency>

<!-- [END dependencies] -->

</dependencies>

<build>

<resources>

<resource>

<directory>src/main/resources</directory>

<filtering>true</filtering>

</resource>

</resources>

<!-- for hot reload of the web application -->

<outputDirectory>${project.build.directory}/${project.build.finalName}/WEB-INF/classes</outputDirectory>

<plugins>

<plugin>

<groupId>com.google.cloud.tools</groupId>

<artifactId>appengine-maven-plugin</artifactId>

<version>1.3.1</version>

</plugin>

<plugin>

<groupId>org.eclipse.jetty</groupId>

<artifactId>jetty-maven-plugin</artifactId>

<version>${jetty}</version>

</plugin>

</plugins>

</build>

</project>

runtime: java

env: flex

handlers:

- url: /.*

script: this field is required, but ignored

automatic_scaling:

min_num_instances: 1

max_num_instances: 2

package com.example.postgres;

import com.google.common.base.Stopwatch;

import java.io.IOException;

import java.io.PrintWriter;

import java.net.Inet4Address;

import java.net.Inet6Address;

import java.net.InetAddress;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Timestamp;

import java.util.Date;

import java.util.Properties;

import java.util.concurrent.TimeUnit;

import javax.servlet.ServletException;

import javax.servlet.annotation.WebServlet;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

@SuppressWarnings("serial")

@WebServlet(name = "postgresql", value = "")

public class PostgresSqlServlet extends HttpServlet {

Connection conn;

@Override

public void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException,

ServletException {

final String createTableSql = "CREATE TABLE IF NOT EXISTS visits ( visit_id SERIAL NOT NULL, "

+ "user_ip VARCHAR(46) NOT NULL, ts timestamp NOT NULL, "

+ "PRIMARY KEY (visit_id) );";

final String createVisitSql = "INSERT INTO visits (user_ip, ts) VALUES (?, ?);";

final String selectSql = "SELECT user_ip, ts FROM visits ORDER BY ts DESC "

+ "LIMIT 10;";

String path = req.getRequestURI();

if (path.startsWith("/favicon.ico")) {

return; // ignore the request for favicon.ico

}

PrintWriter out = resp.getWriter();

resp.setContentType("text/plain");

// store only the first two octets of a users ip address

String userIp = req.getRemoteAddr();

InetAddress address = InetAddress.getByName(userIp);

if (address instanceof Inet6Address) {

// nest indexOf calls to find the second occurrence of a character in a string

// an alternative is to use Apache Commons Lang: StringUtils.ordinalIndexOf()

userIp = userIp.substring(0, userIp.indexOf(":", userIp.indexOf(":") + 1)) + ":*:*:*:*:*:*";

} else if (address instanceof Inet4Address) {

userIp = userIp.substring(0, userIp.indexOf(".", userIp.indexOf(".") + 1)) + ".*.*";

}

Stopwatch stopwatch = Stopwatch.createStarted();

try (PreparedStatement statementCreateVisit = conn.prepareStatement(createVisitSql)) {

conn.createStatement().executeUpdate(createTableSql);

statementCreateVisit.setString(1, userIp);

statementCreateVisit.setTimestamp(2, new Timestamp(new Date().getTime()));

statementCreateVisit.executeUpdate();

try (ResultSet rs = conn.prepareStatement(selectSql).executeQuery()) {

stopwatch.stop();

out.print("Last 10 visits:\n");

while (rs.next()) {

String savedIp = rs.getString("user_ip");

String timeStamp = rs.getString("ts");

out.println("Time: " + timeStamp + " Addr: " + savedIp);

}

out.println("Elapsed: " + stopwatch.elapsed(TimeUnit.MILLISECONDS));

}

} catch (SQLException e) {

throw new ServletException("SQL error", e);

}

}

@Override

public void init() throws ServletException {

String url;

Properties properties = new Properties();

try {

properties.load(

getServletContext().getResourceAsStream("/WEB-INF/classes/config.properties"));

url = properties.getProperty("sqlUrl");

} catch (IOException e) {

log("no property", e); // Servlet Init should never fail.

return;

}

log("connecting to: " + url);

try {

Class.forName("org.postgresql.Driver");

conn = DriverManager.getConnection(url);

} catch (ClassNotFoundException e) {

throw new ServletException("Error loading JDBC Driver", e);

} catch (SQLException e) {

throw new ServletException("Unable to connect to PostGre", e);

} finally {

// Nothing really to do here.

}

}

}

sqlUrl=${sqlURL}