Skip to content

Commit e96d917

Browse files
shubha-rajanshubha-rajan
authored
feat: update SQL Server samples to include more connection methods (GoogleCloudPlatform#7098)
* feat: update SQL Server samples to include more connection methods
1 parent 02f3e2a commit e96d917

15 files changed

Lines changed: 579 additions & 164 deletions

File tree

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

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -181,7 +181,7 @@ mvn clean package com.google.cloud.tools:jib-maven-plugin:2.8.0:build \
181181

182182
To deploy the application to Cloud Functions, first fill in the values for required environment variables in `.env.yaml`. Then run the following command
183183
```
184-
gcloud functions deploy mysql-sample \
184+
gcloud functions deploy sql-sample \
185185
--trigger-http \
186186
--entry-point com.example.cloudsql.functions.Main \
187187
--runtime java11 \

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

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -92,6 +92,7 @@
9292
<version>1.1.3</version>
9393
<scope>test</scope>
9494
</dependency>
95+
<!-- Only required for Cloud Functions Deployments -->
9596
<dependency>
9697
<groupId>com.google.cloud.functions.invoker</groupId>
9798
<artifactId>java-function-invoker</artifactId>

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

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -171,7 +171,7 @@ mvn clean package com.google.cloud.tools:jib-maven-plugin:2.8.0:build \
171171

172172
To deploy the application to Cloud Functions, first fill in the values for required environment variables in `.env.yaml`. Then run the following command
173173
```
174-
gcloud functions deploy mysql-sample \
174+
gcloud functions deploy sql-sample \
175175
--trigger-http \
176176
--entry-point com.example.cloudsql.functions.Main \
177177
--runtime java11 \

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

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

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

Lines changed: 46 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -31,6 +31,34 @@ export DB_NAME='my_db'
3131
Note: Saving credentials in environment variables is convenient, but not secure - consider a more
3232
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.
3333

34+
## Configure SSL Certificates
35+
For deployments that connect directly to a Cloud SQL instance with TCP,
36+
without using the Cloud SQL Proxy,
37+
configuring SSL certificates will ensure the connection is encrypted.
38+
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.
39+
- Get information about the service certificate:
40+
```
41+
gcloud beta sql ssl server-ca-certs list --instance=INSTANCE_NAME
42+
```
43+
- Create a server certificate:
44+
```
45+
gcloud beta sql ssl server-ca-certs create --instance=INSTANCE_NAME
46+
```
47+
- Download the certificate information to a local PEM file
48+
```
49+
gcloud beta sql ssl server-ca-certs list \
50+
--format="value(cert)" \
51+
--instance=INSTANCE_NAME > \
52+
server-ca.pem
53+
```
54+
55+
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`:
56+
```
57+
keytool -importcert -alias MySQLCACert -file server-ca.pem \
58+
-keystore <truststore-filename> -storepass <password>
59+
```
60+
1. Set the `TRUST_CERT_KEYSTORE_PATH` and `TRUST_CERT_KEYSTORE_PASSWD` environment variables to the values used in the previous step.
61+
3462
## Deploying locally
3563
3664
To run this application locally, run the following command inside the project folder:
@@ -50,7 +78,7 @@ and verify that
5078
has been added in your build section as a plugin.
5179

5280

53-
### Development Server
81+
### App Engine Development Server
5482

5583
The following command will run the application locally in the the GAE-development server:
5684
```bash
@@ -59,6 +87,12 @@ mvn clean package appengine:run
5987

6088
Note: if the GAE development server fails to start, check that you are using a supported version of Java. Supported versions are Java 8 and Java 11.
6189

90+
### Cloud Functions Development Server
91+
To run the application locally as a Cloud Function, run the following command:
92+
```
93+
mvn function:run -Drun.functionTarget=com.example.cloudsql.functions.Main
94+
```
95+
6296
### Deploy to Google Cloud
6397

6498
First, update `src/main/webapp/WEB-INF/appengine-web.xml` with the correct values to pass the
@@ -125,5 +159,16 @@ mvn clean package com.google.cloud.tools:jib-maven-plugin:2.8.0:build \
125159
For more details about using Cloud Run see http://cloud.run.
126160
Review other [Java on Cloud Run samples](../../../run/).
127161

162+
### Deploy to Google Cloud Functions
163+
164+
To deploy the application to Cloud Functions, first fill in the values for required environment variables in `.env.yaml`. Then run the following command
165+
```
166+
gcloud functions deploy sql-sample \
167+
--trigger-http \
168+
--entry-point com.example.cloudsql.functions.Main \
169+
--runtime java11 \
170+
--env-vars-file .env.yaml
171+
```
172+
128173
### Cleanup
129174
To avoid incurring any charges, navigate to your project's [App Engine settings](https://console.cloud.google.com/appengine/settings) and click `Disable Application`. Also [delete your Cloud SQL Instance](https://cloud.google.com/sql/docs/mysql/delete-instance) if you no longer need it.

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

Lines changed: 13 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -52,7 +52,7 @@
5252
<dependency>
5353
<groupId>com.microsoft.sqlserver</groupId>
5454
<artifactId>mssql-jdbc</artifactId>
55-
<version>9.4.1.jre8</version>
55+
<version>10.2.0.jre8</version>
5656
</dependency>
5757
<dependency>
5858
<groupId>com.google.cloud.sql</groupId>
@@ -82,6 +82,18 @@
8282
<version>1.1.3</version>
8383
<scope>test</scope>
8484
</dependency>
85+
<!-- Only required for Cloud Functions Deployments -->
86+
<dependency>
87+
<groupId>com.google.cloud.functions.invoker</groupId>
88+
<artifactId>java-function-invoker</artifactId>
89+
<version>1.0.1</version>
90+
</dependency>
91+
<dependency>
92+
<groupId>com.google.cloud.functions</groupId>
93+
<artifactId>functions-framework-api</artifactId>
94+
<version>1.0.4</version>
95+
<scope>provided</scope>
96+
</dependency>
8597
</dependencies>
8698

8799
<build>

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

Lines changed: 7 additions & 93 deletions
Original file line numberDiff line numberDiff line change
@@ -16,10 +16,7 @@
1616

1717
package com.example.cloudsql;
1818

19-
import com.zaxxer.hikari.HikariConfig;
2019
import com.zaxxer.hikari.HikariDataSource;
21-
import java.sql.Connection;
22-
import java.sql.PreparedStatement;
2320
import java.sql.SQLException;
2421
import java.util.logging.Logger;
2522
import javax.servlet.ServletContextEvent;
@@ -40,94 +37,6 @@ public class ConnectionPoolContextListener implements ServletContextListener {
4037
private static final String DB_PASS = System.getenv("DB_PASS");
4138
private static final String DB_NAME = System.getenv("DB_NAME");
4239

43-
private DataSource createConnectionPool() {
44-
// [START cloud_sql_sqlserver_servlet_create]
45-
// Note: For Java users, the Cloud SQL JDBC Socket Factory can provide authenticated connections
46-
// which is preferred to using the Cloud SQL Proxy with Unix sockets.
47-
// See https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory for details.
48-
49-
// The configuration object specifies behaviors for the connection pool.
50-
HikariConfig config = new HikariConfig();
51-
52-
// The following is equivalent to setting the config options below:
53-
// jdbc:sqlserver://;user=<DB_USER>;password=<DB_PASS>;databaseName=<DB_NAME>;
54-
// socketFactoryClass=com.google.cloud.sql.sqlserver.SocketFactory;
55-
// socketFactoryConstructorArg=<INSTANCE_CONNECTION_NAME>
56-
57-
// See the link below for more info on building a JDBC URL for the Cloud SQL JDBC Socket Factory
58-
// https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory#creating-the-jdbc-url
59-
60-
// Configure which instance and what database user to connect with.
61-
config
62-
.setDataSourceClassName("com.microsoft.sqlserver.jdbc.SQLServerDataSource");
63-
config.setUsername(DB_USER); // e.g. "root", "sqlserver"
64-
config.setPassword(DB_PASS); // e.g. "my-password"
65-
config.addDataSourceProperty("databaseName", DB_NAME);
66-
67-
config.addDataSourceProperty("socketFactoryClass",
68-
"com.google.cloud.sql.sqlserver.SocketFactory");
69-
config.addDataSourceProperty("socketFactoryConstructorArg", INSTANCE_CONNECTION_NAME);
70-
71-
// ... Specify additional connection properties here.
72-
73-
// [START_EXCLUDE]
74-
75-
// [START cloud_sql_sqlserver_servlet_limit]
76-
// maximumPoolSize limits the total number of concurrent connections this pool will keep. Ideal
77-
// values for this setting are highly variable on app design, infrastructure, and database.
78-
config.setMaximumPoolSize(5);
79-
// minimumIdle is the minimum number of idle connections Hikari maintains in the pool.
80-
// Additional connections will be established to meet this value unless the pool is full.
81-
config.setMinimumIdle(5);
82-
// [END cloud_sql_sqlserver_servlet_limit]
83-
84-
// [START cloud_sql_sqlserver_servlet_timeout]
85-
// setConnectionTimeout is the maximum number of milliseconds to wait for a connection checkout.
86-
// Any attempt to retrieve a connection from this pool that exceeds the set limit will throw an
87-
// SQLException.
88-
config.setConnectionTimeout(10000); // 10 seconds
89-
// idleTimeout is the maximum amount of time a connection can sit in the pool. Connections that
90-
// sit idle for this many milliseconds are retried if minimumIdle is exceeded.
91-
config.setIdleTimeout(600000); // 10 minutes
92-
// [END cloud_sql_sqlserver_servlet_timeout]
93-
94-
// [START cloud_sql_sqlserver_servlet_backoff]
95-
// Hikari automatically delays between failed connection attempts, eventually reaching a
96-
// maximum delay of `connectionTimeout / 2` between attempts.
97-
// [END cloud_sql_sqlserver_servlet_backoff]
98-
99-
// [START cloud_sql_sqlserver_servlet_lifetime]
100-
// maxLifetime is the maximum possible lifetime of a connection in the pool. Connections that
101-
// live longer than this many milliseconds will be closed and reestablished between uses. This
102-
// value should be several minutes shorter than the database's timeout value to avoid unexpected
103-
// terminations.
104-
config.setMaxLifetime(1800000); // 30 minutes
105-
// [END cloud_sql_sqlserver_servlet_lifetime]
106-
107-
// [END_EXCLUDE]
108-
109-
// Initialize the connection pool using the configuration object.
110-
DataSource pool = new HikariDataSource(config);
111-
// [END cloud_sql_sqlserver_servlet_create]
112-
return pool;
113-
}
114-
115-
private void createTable(DataSource pool) throws SQLException {
116-
// Safely attempt to create the table schema.
117-
try (Connection conn = pool.getConnection()) {
118-
PreparedStatement createTableStatement = conn.prepareStatement(
119-
"IF NOT EXISTS ("
120-
+ "SELECT * FROM sysobjects WHERE name='votes' and xtype='U')"
121-
+ "CREATE TABLE votes ("
122-
+ "vote_id INT NOT NULL IDENTITY,"
123-
+ "time_cast DATETIME NOT NULL,"
124-
+ "candidate VARCHAR(6) NOT NULL,"
125-
+ "PRIMARY KEY (vote_id));"
126-
);
127-
createTableStatement.execute();
128-
}
129-
}
130-
13140
@Override
13241
public void contextDestroyed(ServletContextEvent event) {
13342
// This function is called when the Servlet is destroyed.
@@ -143,11 +52,16 @@ public void contextInitialized(ServletContextEvent event) {
14352
// that can be used to connect to.
14453
DataSource pool = (DataSource) event.getServletContext().getAttribute("my-pool");
14554
if (pool == null) {
146-
pool = createConnectionPool();
55+
if (System.getenv("INSTANCE_HOST") != null) {
56+
pool = TcpConnectionPoolFactory.createConnectionPool();
57+
} else {
58+
pool = ConnectorConnectionPoolFactory.createConnectionPool();
59+
}
14760
event.getServletContext().setAttribute("my-pool", pool);
14861
}
14962
try {
150-
createTable(pool);
63+
// from src/main/java/com/example/cloudsql/Utils.java
64+
Utils.createTable(pool);
15165
} catch (SQLException ex) {
15266
throw new RuntimeException("Unable to verify table schema. Please double check the steps"
15367
+ "in the README and try again.", ex);

‎cloud-sql/sqlserver/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_sqlserver_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_sqlserver_servlet_limit]
32+
33+
// [START cloud_sql_sqlserver_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_sqlserver_servlet_timeout]
42+
43+
// [START cloud_sql_sqlserver_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_sqlserver_servlet_backoff]
47+
48+
// [START cloud_sql_sqlserver_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_sqlserver_servlet_lifetime]
55+
return config;
56+
}
57+
}

‎cloud-sql/sqlserver/servlet/src/main/java/com/example/cloudsql/ConnectorConnectionPoolFactory.java‎

Lines changed: 74 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,74 @@
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+
// [START cloud_sql_sqlserver_servlet_connect_connector]
20+
21+
import com.zaxxer.hikari.HikariConfig;
22+
import com.zaxxer.hikari.HikariDataSource;
23+
import javax.sql.DataSource;
24+
25+
public class ConnectorConnectionPoolFactory extends ConnectionPoolFactory {
26+
27+
// Note: Saving credentials in environment variables is convenient, but not
28+
// secure - consider a more secure solution such as
29+
// Cloud Secret Manager (https://cloud.google.com/secret-manager) to help
30+
// keep secrets safe.
31+
private static final String INSTANCE_CONNECTION_NAME =
32+
System.getenv("INSTANCE_CONNECTION_NAME");
33+
private static final String DB_USER = System.getenv("DB_USER");
34+
private static final String DB_PASS = System.getenv("DB_PASS");
35+
private static final String DB_NAME = System.getenv("DB_NAME");
36+
37+
public static DataSource createConnectionPool() {
38+
// The configuration object specifies behaviors for the connection pool.
39+
HikariConfig config = new HikariConfig();
40+
41+
// The following is equivalent to setting the config options below:
42+
// jdbc:sqlserver://;user=<DB_USER>;password=<DB_PASS>;databaseName=<DB_NAME>;
43+
// socketFactoryClass=com.google.cloud.sql.sqlserver.SocketFactory;
44+
// socketFactoryConstructorArg=<INSTANCE_CONNECTION_NAME>
45+
46+
// See the link below for more info on building a JDBC URL for the Cloud SQL JDBC Socket Factory
47+
// https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory#creating-the-jdbc-url
48+
49+
// Configure which instance and what database user to connect with.
50+
config
51+
.setDataSourceClassName("com.microsoft.sqlserver.jdbc.SQLServerDataSource");
52+
config.setUsername(DB_USER); // e.g. "root", "sqlserver"
53+
config.setPassword(DB_PASS); // e.g. "my-password"
54+
config.addDataSourceProperty("databaseName", DB_NAME);
55+
56+
config.addDataSourceProperty("socketFactoryClass",
57+
"com.google.cloud.sql.sqlserver.SocketFactory");
58+
config.addDataSourceProperty("socketFactoryConstructorArg", INSTANCE_CONNECTION_NAME);
59+
60+
// The Java Connector provides SSL encryption, so it should be disabled
61+
// at the driver level.
62+
config.addDataSourceProperty("encrypt", "false");
63+
64+
// ... Specify additional connection properties here.
65+
// [START_EXCLUDE]
66+
configureConnectionPool(config);
67+
// [END_EXCLUDE]
68+
69+
// Initialize the connection pool using the configuration object.
70+
return new HikariDataSource(config);
71+
}
72+
}
73+
// [END cloud_sql_sqlserver_servlet_connect_connector]
74+

0 commit comments

Comments
 (0)