1. If you haven't already, set up a Java Development Environment (including google-cloud-sdk and

maven utilities) by following the [java setup guide](https://cloud.google.com/java/docs/setup) and

[create a project](https://cloud.google.com/resource-manager/docs/creating-managing-projects#creating_a_project).

1. Create a 2nd Gen Cloud SQL Instance by following these

[instructions](https://cloud.google.com/sql/docs/mysql/create-instance). Note the connection string,

database user, and database password that you create.

1. Create a database for your application by following these

[instructions](https://cloud.google.com/sql/docs/mysql/create-manage-databases). Note the database

name.

1. Create a service account with the 'Cloud SQL Client' permissions by following these

[instructions](https://cloud.google.com/sql/docs/mysql/connect-external-app#4_if_required_by_your_authentication_method_create_a_service_account).

Download a JSON key to use to authenticate your connection.

1. Use the information noted in the previous steps:

export GOOGLE_APPLICATION_CREDENTIALS=/path/to/service/account/key.json

export CLOUD_SQL_CONNECTION_NAME='<MY-PROJECT>:<INSTANCE-REGION>:<MY-DATABASE>'

export DB_USER='my-db-user'

export DB_PASS='my-db-pass'

export DB_NAME='my_db'

Note: Saving credentials in environment variables is convenient, but not secure - consider a more

secure solution such as [Cloud KMS](https://cloud.google.com/kms/) to help keep secrets safe.

To run this application locally, run the following command inside the project folder:

mvn jetty:run

Navigate towards `http://127.0.0.1:8080` to verify your application is running correctly.

To run on GAE-Standard, create an AppEngine project by following the setup for these

[instructions](https://cloud.google.com/appengine/docs/standard/java/quickstart#before-you-begin)

and verify that

[appengine-maven-plugin](https://cloud.google.com/java/docs/setup#optional_install_maven_or_gradle_plugin_for_app_engine)

has been added in your build section as a plugin.

The following command will run the application locally in the the GAE-development server:

mvn appengine:run

First, update `src/main/webapp/WEB-INF/appengine-web.xml` with the correct values to pass the

environment variables into the runtime.

Next, the following command will deploy the application to your Google Cloud project:

mvn appengine:deploy

<project>

<modelVersion>4.0.0</modelVersion>

<packaging>war</packaging>

<version>1.0-SNAPSHOT</version>

<groupId>com.example.cloudsql</groupId>

<artifactId>tabs-vs-spaces</artifactId>

<!--

<parent>

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

<artifactId>shared-configuration</artifactId>

<version>1.0.10</version>

</parent>

<properties>

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

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

<failOnMissingWebXml>false</failOnMissingWebXml>

</properties>

<dependencies>

<dependency>

<groupId>javax.servlet</groupId>

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

<version>3.1.0</version>

<type>jar</type>

<scope>provided</scope>

</dependency>

<dependency>

<groupId>javax.servlet</groupId>

<artifactId>jstl</artifactId>

<version>1.2</version>

</dependency>

<dependency>

<groupId>mysql</groupId>

<artifactId>mysql-connector-java</artifactId>

<version>8.0.11</version>

</dependency>

<dependency>

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

<artifactId>mysql-socket-factory-connector-j-8</artifactId>

<version>1.0.11</version>

</dependency>

<dependency>

<groupId>com.zaxxer</groupId>

<artifactId>HikariCP</artifactId>

<version>3.1.0</version>

</dependency>

</dependencies>

<build>

<plugins>

<plugin>

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

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

<version>9.4.10.v20180503</version>

<configuration>

<scanIntervalSeconds>1</scanIntervalSeconds>

</configuration>

</plugin>

<!-- Only required for AppEngine Deployments -->

<plugin>

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

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

<version>1.3.2</version>

</plugin>

</plugins>

</build>

</project>

package com.example.cloudsql;

import com.zaxxer.hikari.HikariConfig;

import com.zaxxer.hikari.HikariDataSource;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.util.logging.Logger;

import javax.servlet.ServletContextEvent;

import javax.servlet.ServletContextListener;

import javax.servlet.annotation.WebListener;

import javax.sql.DataSource;

@WebListener("Creates a connection pool that is stored in the Servlet's context for later use.")

public class ConnectionPoolContextListener implements ServletContextListener {

private static final Logger LOGGER = Logger.getLogger(IndexServlet.class.getName());

// Saving credentials in environment variables is convenient, but not secure - consider a more

// secure solution such as https://cloud.google.com/kms/ to help keep secrets safe.

private static final String CLOUD_SQL_INSTANCE_NAME = System.getenv("CLOUD_SQL_INSTANCE_NAME");

private static final String DB_USER = System.getenv("DB_USER");

private static final String DB_PASS = System.getenv("DB_PASS");

private static final String DB_NAME = System.getenv("DB_NAME");

private DataSource createConnectionPool() {

// [START cloud_sql_mysql_connection_pool]

// The configuration object specifies behaviors for the connection pool.

HikariConfig config = new HikariConfig();

// Configure which instance and what database user to connect with.

config.setJdbcUrl(String.format("jdbc:mysql:///%s", DB_NAME));

config.setUsername(DB_USER); // e.g. "root", "postgres"

config.setPassword(DB_PASS); // e.g. "my-password"

// For Java users, the Cloud SQL JDBC Socket Factory can provide authenticated connections.

// See https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory for details.

config.addDataSourceProperty("socketFactory", "com.google.cloud.sql.mysql.SocketFactory");

config.addDataSourceProperty("cloudSqlInstance", CLOUD_SQL_INSTANCE_NAME);

config.addDataSourceProperty("useSSL", "false");

// ... Specify additional connection properties here.

// [START_EXCLUDE]

// [START cloud_sql_limit_connections]

// maximumPoolSize limits the total number of concurrent connections this pool will keep. Ideal

// values for this setting are highly variable on app design, infrastructure, and database.

config.setMaximumPoolSize(5);

// minimumIdle is the minimum number of idle connections Hikari maintains in the pool.

// Additional connections will be established to meet this value unless the pool is full.

config.setMinimumIdle(5);

// [END cloud_sql_limit_connections]

// [START cloud_sql_connection_timeout]

// setConnectionTimeout is the maximum number of milliseconds to wait for a connection checkout.

// Any attempt to retrieve a connection from this pool that exceeds the set limit will throw an

// SQLException.

config.setConnectionTimeout(10000); // 10 seconds

// idleTimeout is the maximum amount of time a connection can sit in the pool. Connections that

// sit idle for this many milliseconds are retried if minimumIdle is exceeded.

config.setIdleTimeout(600000); // 10 minutes

// [END cloud_sql_connection_timeout]

// [START cloud_sql_connection_backoff]

// Hikari automatically delays between failed connection attempts, eventually reaching a

// maximum delay of `connectionTimeout / 2` between attempts.

// [END cloud_sql_connection_backoff]

// [START cloud_sql_connection_lifetime]

// maxLifetime is the maximum possible lifetime of a connection in the pool. Connections that

// live longer than this many milliseconds will be closed and reestablished between uses. This

// value should be several minutes shorter than the database's timeout value to avoid unexpected

// terminations.

config.setMaxLifetime(1800000); // 30 minutes

// [END cloud_sql_connection_lifetime]

// [END_EXCLUDE]

// Initialize the connection pool using the configuration object.

DataSource pool = new HikariDataSource(config);

// [END cloud_sql_mysql_connection_pool]

return pool;

}

private void createTable(DataSource pool) throws SQLException {

// Safely attempt to create the table schema.

try (Connection conn = pool.getConnection()) {

PreparedStatement createTableStatement = conn.prepareStatement(

"CREATE TABLE IF NOT EXISTS votes ( "

+ "vote_id SERIAL NOT NULL, time_cast timestamp NOT NULL, candidate CHAR(6) NOT NULL,"

+ " PRIMARY KEY (vote_id) );"

);

createTableStatement.execute();

}

}

@Override

public void contextDestroyed(ServletContextEvent event) {

// This function is called when the Servlet is destroyed.

HikariDataSource pool = (HikariDataSource) event.getServletContext().getAttribute("my-pool");

if (pool != null) {

pool.close();

}

}

@Override

public void contextInitialized(ServletContextEvent event) {

// This function is called when the application starts and will safely create a connection pool

// that can be used to connect to.

DataSource pool = (DataSource) event.getServletContext().getAttribute("my-pool");

if (pool == null) {

pool = createConnectionPool();

event.getServletContext().setAttribute("my-pool", pool);

}

try {

createTable(pool);

} catch (SQLException ex) {

throw new RuntimeException("Unable to verify table schema. Please double check the steps"

+ "in the README and try again.", ex);

}

}

}