Skip to content

Commit f63f258

Browse files
shubha-rajanjackwotherspoonkurtisvg
authored
feat: Update Cloud SQL MySQL samples to include more connection methods (GoogleCloudPlatform#7054)
* update mysql sample to include new region tags * add license header * move connection code for TCP and connector to separate files * add functions sample * add missing license headers * Update region tag * reformat code * fix function sample * get function working * update instructions for deploying to Functions * Update cloud-sql/mysql/servlet/.env.yaml Co-authored-by: Jack Wotherspoon <jackwoth@google.com> * add imports and environment variables to sample * Update README.md * Update ConnectorConnectionPoolFactory.java * formatting * Update cloud-sql/mysql/servlet/src/main/java/com/example/cloudsql/ConnectorConnectionPoolFactory.java Co-authored-by: Kurtis Van Gent <31518063+kurtisvg@users.noreply.github.com> * Update TcpConnectionPoolFactory.java * Update cloud-sql/mysql/servlet/src/main/java/com/example/cloudsql/TemplateData.java Co-authored-by: Kurtis Van Gent <31518063+kurtisvg@users.noreply.github.com> * Factor out common methods in servlet and cloud function * combine DatabaseSetup and InputValidator classes * refactor cloud function to use initialization on demand * formatting * Update ConnectorConnectionPoolFactory.java * Update ConnectorConnectionPoolFactory.java * Update TcpConnectionPoolFactory.java Co-authored-by: Jack Wotherspoon <jackwoth@google.com> Co-authored-by: Kurtis Van Gent <31518063+kurtisvg@users.noreply.github.com>
1 parent 66103ab commit f63f258

File tree

12 files changed

+634
-182
lines changed

12 files changed

+634
-182
lines changed

‎cloud-sql/mysql/servlet/.env.yaml‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,7 @@
1+
INSTANCE_CONNECTION_NAME: <PROJECT-ID>:<INSTANCE-REGION>:INSTANCE-NAME>
2+
INSTANCE_UNIX_SOCKET: /cloudsql/<PROJECT-ID>:<INSTANCE-REGION>:INSTANCE-NAME>
3+
INSTANCE_HOST: '127.0.0.1'
4+
DB_PORT: 3306
5+
DB_USER: <YOUR_DB_USER_NAME>
6+
DB_PASS: <YOUR_DB_PASSWORD>
7+
DB_NAME: <YOUR_DB_NAME>

‎cloud-sql/mysql/servlet/README.md‎

Lines changed: 68 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -29,6 +29,56 @@ export DB_NAME='my_db'
2929
Note: Saving credentials in environment variables is convenient, but not secure - consider a more
3030
secure solution such as [Cloud KMS](https://cloud.google.com/kms/) or [Secret Manager](https://cloud.google.com/secret-manager/) to help keep secrets safe.
3131

32+
## Configure SSL Certificates
33+
For deployments that connect directly to a Cloud SQL instance with TCP,
34+
without using the Cloud SQL Proxy,
35+
configuring SSL certificates will ensure the connection is encrypted.
36+
1. Use the gcloud CLI to [download the server certificate](https://cloud.google.com/sql/docs/mysql/configure-ssl-instance#server-certs) for your Cloud SQL instance.
37+
- Get information about the service certificate:
38+
```
39+
gcloud beta sql ssl server-ca-certs list --instance=INSTANCE_NAME
40+
```
41+
- Create a server certificate:
42+
```
43+
gcloud beta sql ssl server-ca-certs create --instance=INSTANCE_NAME
44+
```
45+
- Download the certificate information to a local PEM file
46+
```
47+
gcloud beta sql ssl server-ca-certs list \
48+
--format="value(cert)" \
49+
--instance=INSTANCE_NAME > \
50+
server-ca.pem
51+
```
52+
53+
1. Use the gcloud CLI to [create and download a client public key certificate and client private key](https://cloud.google.com/sql/docs/mysql/configure-ssl-instance#client-certs)
54+
- Create a client certificate using the ssl client-certs create command:
55+
```
56+
gcloud sql ssl client-certs create CERT_NAME client-key.pem --instance=INSTANCE_NAME
57+
```
58+
- Retrieve the public key for the certificate you just created and copy it into the client-cert.pem file with the ssl client-certs describe command:
59+
```
60+
gcloud sql ssl client-certs describe CERT_NAME \
61+
--instance=INSTANCE_NAME \
62+
--format="value(cert)" > client-cert.pem
63+
```
64+
1. [Import the server certificate into a custom Java truststore](https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-using-ssl.html) using `keytool`:
65+
```
66+
keytool -importcert -alias MySQLCACert -file server-ca.pem \
67+
-keystore <truststore-filename> -storepass <password>
68+
```
69+
1. Set the `TRUST_CERT_KEYSTORE_PATH` and `TRUST_CERT_KEYSTORE_PASSWD` environment variables to the values used in the previous step.
70+
1. [Import the client certificate and key into a custom Java keystore](https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-using-ssl.html) using `openssl` and `keytool`:
71+
- Convert the client key and certificate files to a PKCS #12 archive:
72+
```
73+
openssl pkcs12 -export -in client-cert.pem -inkey client-key.pem \
74+
-name "mysqlclient" -passout pass:mypassword -out client-keystore.p12
75+
```
76+
- Import the client key and certificate into a Java keystore:
77+
```
78+
keytool -importkeystore -srckeystore client-keystore.p12 -srcstoretype pkcs12 \
79+
-srcstorepass <password> -destkeystore <keystore-filename> -deststoretype JKS -deststorepass <password>
80+
```
81+
1. Set the `CLIENT_CERT_KEYSTORE_PATH` and `CLIENT_CERT_KEYSTORE_PASSWD` environment variables to the values used in the previous step.
3282
## Deploying locally
3383
3484
To run this application locally, run the following command inside the project folder:
@@ -48,13 +98,19 @@ and verify that
4898
has been added in your build section as a plugin.
4999

50100

51-
### Development Server
101+
### App Engine Development Server
52102

53103
The following command will run the application locally in the the GAE-development server:
54104
```bash
55105
mvn appengine:run
56106
```
57107

108+
### Cloud Functions Development Server
109+
To run the application locally as a Cloud Function, run the following command:
110+
```
111+
mvn function:run -Drun.functionTarget=com.example.cloudsql.functions.Main
112+
```
113+
58114
### Deploy to Google App Engine
59115

60116
First, update `src/main/webapp/WEB-INF/appengine-web.xml` with the correct values to pass the
@@ -120,3 +176,14 @@ mvn clean package com.google.cloud.tools:jib-maven-plugin:2.8.0:build \
120176

121177
For more details about using Cloud Run see http://cloud.run.
122178
Review other [Java on Cloud Run samples](../../../run/).
179+
180+
### Deploy to Google Cloud Functions
181+
182+
To deploy the application to Cloud Functions, first fill in the values for required environment variables in `.env.yaml`. Then run the following command
183+
```
184+
gcloud functions deploy mysql-sample \
185+
--trigger-http \
186+
--entry-point com.example.cloudsql.functions.Main \
187+
--runtime java11 \
188+
--env-vars-file .env.yaml
189+
```

‎cloud-sql/mysql/servlet/pom.xml‎

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -92,6 +92,17 @@
9292
<version>1.1.3</version>
9393
<scope>test</scope>
9494
</dependency>
95+
<dependency>
96+
<groupId>com.google.cloud.functions.invoker</groupId>
97+
<artifactId>java-function-invoker</artifactId>
98+
<version>1.0.1</version>
99+
</dependency>
100+
<dependency>
101+
<groupId>com.google.cloud.functions</groupId>
102+
<artifactId>functions-framework-api</artifactId>
103+
<version>1.0.4</version>
104+
<scope>provided</scope>
105+
</dependency>
95106
</dependencies>
96107

97108
<build>
@@ -114,6 +125,23 @@
114125
<deploy.version>GCLOUD_CONFIG</deploy.version>
115126
</configuration>
116127
</plugin>
128+
<plugin>
129+
<!--
130+
Google Cloud Functions Framework Maven plugin
131+
132+
This plugin allows you to run Cloud Functions Java code
133+
locally. Use the following terminal command to run a
134+
given function locally:
135+
136+
mvn function:run -Drun.functionTarget=your.package.yourFunction
137+
-->
138+
<groupId>com.google.cloud.functions</groupId>
139+
<artifactId>function-maven-plugin</artifactId>
140+
<version>0.10.0</version>
141+
<configuration>
142+
<functionTarget>com.example.cloudsql.functions.Main</functionTarget>
143+
</configuration>
144+
</plugin>
117145
</plugins>
118146
</build>
119147
</project>

‎cloud-sql/mysql/servlet/src/main/java/com/example/cloudsql/ConnectionPoolContextListener.java‎

Lines changed: 6 additions & 100 deletions
Original file line numberDiff line numberDiff line change
@@ -16,11 +16,8 @@
1616

1717
package com.example.cloudsql;
1818

19-
import com.zaxxer.hikari.HikariConfig;
2019
import com.zaxxer.hikari.HikariDataSource;
2120
import edu.umd.cs.findbugs.annotations.SuppressFBWarnings;
22-
import java.sql.Connection;
23-
import java.sql.PreparedStatement;
2421
import java.sql.SQLException;
2522
import javax.servlet.ServletContext;
2623
import javax.servlet.ServletContextEvent;
@@ -34,101 +31,6 @@
3431
@WebListener("Creates a connection pool that is stored in the Servlet's context for later use.")
3532
public class ConnectionPoolContextListener implements ServletContextListener {
3633

37-
// Saving credentials in environment variables is convenient, but not secure - consider a more
38-
// secure solution such as https://cloud.google.com/kms/ to help keep secrets safe.
39-
private static final String INSTANCE_CONNECTION_NAME =
40-
System.getenv("INSTANCE_CONNECTION_NAME");
41-
private static final String DB_USER = System.getenv("DB_USER");
42-
private static final String DB_PASS = System.getenv("DB_PASS");
43-
private static final String DB_NAME = System.getenv("DB_NAME");
44-
45-
@SuppressFBWarnings(
46-
value = "USBR_UNNECESSARY_STORE_BEFORE_RETURN",
47-
justification = "Necessary for sample region tag.")
48-
private DataSource createConnectionPool() {
49-
// [START cloud_sql_mysql_servlet_create]
50-
// Note: For Java users, the Cloud SQL JDBC Socket Factory can provide authenticated connections
51-
// which is preferred to using the Cloud SQL Proxy with Unix sockets.
52-
// See https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory for details.
53-
54-
// The configuration object specifies behaviors for the connection pool.
55-
HikariConfig config = new HikariConfig();
56-
57-
// The following URL is equivalent to setting the config options below:
58-
// jdbc:mysql:///<DB_NAME>?cloudSqlInstance=<INSTANCE_CONNECTION_NAME>&
59-
// socketFactory=com.google.cloud.sql.mysql.SocketFactory&user=<DB_USER>&password=<DB_PASS>
60-
// See the link below for more info on building a JDBC URL for the Cloud SQL JDBC Socket Factory
61-
// https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory#creating-the-jdbc-url
62-
63-
// Configure which instance and what database user to connect with.
64-
config.setJdbcUrl(String.format("jdbc:mysql:///%s", DB_NAME));
65-
config.setUsername(DB_USER); // e.g. "root", "mysql"
66-
config.setPassword(DB_PASS); // e.g. "my-password"
67-
68-
config.addDataSourceProperty("socketFactory", "com.google.cloud.sql.mysql.SocketFactory");
69-
config.addDataSourceProperty("cloudSqlInstance", INSTANCE_CONNECTION_NAME);
70-
71-
// The ipTypes argument can be used to specify a comma delimited list of preferred IP types
72-
// for connecting to a Cloud SQL instance. The argument ipTypes=PRIVATE will force the
73-
// SocketFactory to connect with an instance's associated private IP.
74-
config.addDataSourceProperty("ipTypes", "PUBLIC,PRIVATE");
75-
76-
// ... Specify additional connection properties here.
77-
// [START_EXCLUDE]
78-
79-
// [START cloud_sql_mysql_servlet_limit]
80-
// maximumPoolSize limits the total number of concurrent connections this pool will keep. Ideal
81-
// values for this setting are highly variable on app design, infrastructure, and database.
82-
config.setMaximumPoolSize(5);
83-
// minimumIdle is the minimum number of idle connections Hikari maintains in the pool.
84-
// Additional connections will be established to meet this value unless the pool is full.
85-
config.setMinimumIdle(5);
86-
// [END cloud_sql_mysql_servlet_limit]
87-
88-
// [START cloud_sql_mysql_servlet_timeout]
89-
// setConnectionTimeout is the maximum number of milliseconds to wait for a connection checkout.
90-
// Any attempt to retrieve a connection from this pool that exceeds the set limit will throw an
91-
// SQLException.
92-
config.setConnectionTimeout(10000); // 10 seconds
93-
// idleTimeout is the maximum amount of time a connection can sit in the pool. Connections that
94-
// sit idle for this many milliseconds are retried if minimumIdle is exceeded.
95-
config.setIdleTimeout(600000); // 10 minutes
96-
// [END cloud_sql_mysql_servlet_timeout]
97-
98-
// [START cloud_sql_mysql_servlet_backoff]
99-
// Hikari automatically delays between failed connection attempts, eventually reaching a
100-
// maximum delay of `connectionTimeout / 2` between attempts.
101-
// [END cloud_sql_mysql_servlet_backoff]
102-
103-
// [START cloud_sql_mysql_servlet_lifetime]
104-
// maxLifetime is the maximum possible lifetime of a connection in the pool. Connections that
105-
// live longer than this many milliseconds will be closed and reestablished between uses. This
106-
// value should be several minutes shorter than the database's timeout value to avoid unexpected
107-
// terminations.
108-
config.setMaxLifetime(1800000); // 30 minutes
109-
// [END cloud_sql_mysql_servlet_lifetime]
110-
111-
// [END_EXCLUDE]
112-
113-
// Initialize the connection pool using the configuration object.
114-
DataSource pool = new HikariDataSource(config);
115-
// [END cloud_sql_mysql_servlet_create]
116-
return pool;
117-
}
118-
119-
private void createTable(DataSource pool) throws SQLException {
120-
// Safely attempt to create the table schema.
121-
try (Connection conn = pool.getConnection()) {
122-
String stmt =
123-
"CREATE TABLE IF NOT EXISTS votes ( "
124-
+ "vote_id SERIAL NOT NULL, time_cast timestamp NOT NULL, candidate CHAR(6) NOT NULL,"
125-
+ " PRIMARY KEY (vote_id) );";
126-
try (PreparedStatement createTableStatement = conn.prepareStatement(stmt);) {
127-
createTableStatement.execute();
128-
}
129-
}
130-
}
131-
13234
@Override
13335
public void contextDestroyed(ServletContextEvent event) {
13436
// This function is called when the Servlet is destroyed.
@@ -145,11 +47,15 @@ public void contextInitialized(ServletContextEvent event) {
14547
ServletContext servletContext = event.getServletContext();
14648
DataSource pool = (DataSource) servletContext.getAttribute("my-pool");
14749
if (pool == null) {
148-
pool = createConnectionPool();
50+
if (System.getenv("INSTANCE_HOST") != null) {
51+
pool = TcpConnectionPoolFactory.createConnectionPool();
52+
} else {
53+
pool = ConnectorConnectionPoolFactory.createConnectionPool();
54+
}
14955
servletContext.setAttribute("my-pool", pool);
15056
}
15157
try {
152-
createTable(pool);
58+
Utils.createTable(pool);
15359
} catch (SQLException ex) {
15460
throw new RuntimeException(
15561
"Unable to verify table schema. Please double check the steps"

‎cloud-sql/mysql/servlet/src/main/java/com/example/cloudsql/ConnectionPoolFactory.java‎

Lines changed: 57 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,57 @@
1+
/*
2+
* Copyright 2022 Google LLC
3+
*
4+
* Licensed under the Apache License, Version 2.0 (the "License");
5+
* you may not use this file except in compliance with the License.
6+
* You may obtain a copy of the License at
7+
*
8+
* http://www.apache.org/licenses/LICENSE-2.0
9+
*
10+
* Unless required by applicable law or agreed to in writing, software
11+
* distributed under the License is distributed on an "AS IS" BASIS,
12+
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13+
* See the License for the specific language governing permissions and
14+
* limitations under the License.
15+
*/
16+
17+
package com.example.cloudsql;
18+
19+
import com.zaxxer.hikari.HikariConfig;
20+
21+
public class ConnectionPoolFactory {
22+
23+
public static HikariConfig configureConnectionPool(HikariConfig config) {
24+
// [START cloud_sql_mysql_servlet_limit]
25+
// maximumPoolSize limits the total number of concurrent connections this pool will keep. Ideal
26+
// values for this setting are highly variable on app design, infrastructure, and database.
27+
config.setMaximumPoolSize(5);
28+
// minimumIdle is the minimum number of idle connections Hikari maintains in the pool.
29+
// Additional connections will be established to meet this value unless the pool is full.
30+
config.setMinimumIdle(5);
31+
// [END cloud_sql_mysql_servlet_limit]
32+
33+
// [START cloud_sql_mysql_servlet_timeout]
34+
// setConnectionTimeout is the maximum number of milliseconds to wait for a connection checkout.
35+
// Any attempt to retrieve a connection from this pool that exceeds the set limit will throw an
36+
// SQLException.
37+
config.setConnectionTimeout(10000); // 10 seconds
38+
// idleTimeout is the maximum amount of time a connection can sit in the pool. Connections that
39+
// sit idle for this many milliseconds are retried if minimumIdle is exceeded.
40+
config.setIdleTimeout(600000); // 10 minutes
41+
// [END cloud_sql_mysql_servlet_timeout]
42+
43+
// [START cloud_sql_mysql_servlet_backoff]
44+
// Hikari automatically delays between failed connection attempts, eventually reaching a
45+
// maximum delay of `connectionTimeout / 2` between attempts.
46+
// [END cloud_sql_mysql_servlet_backoff]
47+
48+
// [START cloud_sql_mysql_servlet_lifetime]
49+
// maxLifetime is the maximum possible lifetime of a connection in the pool. Connections that
50+
// live longer than this many milliseconds will be closed and reestablished between uses. This
51+
// value should be several minutes shorter than the database's timeout value to avoid unexpected
52+
// terminations.
53+
config.setMaxLifetime(1800000); // 30 minutes
54+
// [END cloud_sql_mysql_servlet_lifetime]
55+
return config;
56+
}
57+
}

0 commit comments

Comments
 (0)