Skip to content

Commit 1f9edfd

Browse files
dmitry-sdmitry-s
authored
Add Cloud SQL R2DBC sample (GoogleCloudPlatform#3784)
1 parent 3accf96 commit 1f9edfd

File tree

11 files changed

+618
-0
lines changed

11 files changed

+618
-0
lines changed

‎cloud-sql/r2dbc/README.md‎

Lines changed: 97 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,97 @@
1+
# Connecting to Cloud SQL - MySQL and Postgres
2+
3+
## Before you begin
4+
5+
1. If you haven't already, set up a Java Development Environment (including google-cloud-sdk and
6+
maven utilities) by following the [Java setup guide](https://cloud.google.com/java/docs/setup) and
7+
[creating a project](https://cloud.google.com/resource-manager/docs/creating-managing-projects#creating_a_project).
8+
9+
1. You can use MySQL or PostgreSQL instance for this sample.
10+
Create a 2nd Gen Cloud SQL Instance by following corresponding instructions:
11+
[MySQL](https://cloud.google.com/sql/docs/mysql/create-instance) /
12+
[PostgreSQL](https://cloud.google.com/sql/docs/postgres/create-instance).
13+
Note the connection string, database user, and database password that you create.
14+
15+
1. Create a database for your application by following corresponding instructions:
16+
[MySQL](https://cloud.google.com/sql/docs/mysql/create-manage-databases) /
17+
[PostgreSQL](https://cloud.google.com/sql/docs/postgres/create-manage-databases).
18+
Note the database name.
19+
20+
1. Assign your connection details in the following format:
21+
22+
```
23+
r2dbc:gcp:<'mysql' or 'postgres'>://<user>:<password>@<connection_name>/<db_name>
24+
```
25+
to an environment variable `CLOUD_SQL_CONNECTION_STRING`.
26+
27+
Example for MySQL:
28+
```sh
29+
export CLOUD_SQL_CONNECTION_STRING=r2dbc:gcp:mysql://user:123456@my-project:us-central1:r2dbctest/testdb
30+
```
31+
32+
Example for PostgreSQL:
33+
```sh
34+
export CLOUD_SQL_CONNECTION_STRING=r2dbc:gcp:postgres://user:123456@my-project:us-central1:r2dbctest/testdb
35+
```
36+
37+
## Schema
38+
39+
The schema will be created automatically when the application starts.
40+
41+
## Running locally
42+
43+
To run this application locally, run the following command inside the project folder:
44+
45+
```sh
46+
mvn spring-boot:run
47+
```
48+
49+
Navigate to `http://localhost:8080` to verify your application is running correctly.
50+
51+
## Deploy to Google App Engine Standard
52+
53+
To run on GAE-Standard, create an AppEngine project by following the setup for these
54+
[instructions](https://cloud.google.com/appengine/docs/standard/java/quickstart#before-you-begin)
55+
and verify that
56+
[appengine-maven-plugin](https://cloud.google.com/java/docs/setup#optional_install_maven_or_gradle_plugin_for_app_engine)
57+
has been added in your build section as a plugin.
58+
59+
Edit `src/main/appengine/app.yaml` to set `CLOUD_SQL_CONNECTION_STRING` to your connection string.
60+
61+
The following command will deploy the application to your Google Cloud project:
62+
```bash
63+
mvn clean package appengine:deploy
64+
```
65+
66+
## Deploy to Cloud Run
67+
68+
See the [Cloud Run documentation](https://cloud.google.com/run/docs/configuring/connect-cloudsql)
69+
for more details on connecting a Cloud Run service to Cloud SQL.
70+
71+
1. Create an environment variable with your GCP project id:
72+
```sh
73+
export PROJECT_ID=[YOUR_PROJECT_ID]
74+
```
75+
76+
1. Build the container image and push it to Google Container Registry (GCR):
77+
78+
```sh
79+
mvn compile com.google.cloud.tools:jib-maven-plugin:2.5.2:build \
80+
-Dimage=gcr.io/$PROJECT_ID/r2dbc-sample
81+
```
82+
83+
1. Deploy the service to Cloud Run:
84+
85+
```sh
86+
gcloud run deploy r2dbc-sample \
87+
--image gcr.io/$PROJECT_ID/r2dbc-sample \
88+
--platform managed \
89+
--memory 512Mi \
90+
--set-env-vars CLOUD_SQL_CONNECTION_STRING=$CLOUD_SQL_CONNECTION_STRING
91+
```
92+
Take note of the URL output at the end of the deployment process.
93+
94+
1. Navigate to the URL noted in Step 2.
95+
96+
For more details about using Cloud Run see http://cloud.run.
97+
Review other [Java on Cloud Run samples](../../../run/).

‎cloud-sql/r2dbc/pom.xml‎

Lines changed: 117 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,117 @@
1+
<?xml version="1.0" encoding="UTF-8"?>
2+
<project xmlns="http://maven.apache.org/POM/4.0.0"
3+
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
4+
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
5+
<modelVersion>4.0.0</modelVersion>
6+
<!--
7+
The parent pom defines common style checks and testing strategies for our samples.
8+
Removing or replacing it should not affect the execution of the samples in anyway.
9+
The real parent is spring-boot-starter-parent, which you can find in the
10+
`dependencyManagement` section.
11+
-->
12+
<parent>
13+
<groupId>com.google.cloud.samples</groupId>
14+
<artifactId>shared-configuration</artifactId>
15+
<version>1.0.18</version>
16+
</parent>
17+
<groupId>com.example.cloudsql</groupId>
18+
<artifactId>r2dbc-sample</artifactId>
19+
<version>0.0.1-SNAPSHOT</version>
20+
<name>r2dbc-sample</name>
21+
<description>Demo project for Cloud SQL R2DBC</description>
22+
23+
<properties>
24+
<java.version>11</java.version>
25+
<maven.compiler.target>11</maven.compiler.target>
26+
<maven.compiler.source>11</maven.compiler.source>
27+
</properties>
28+
29+
<dependencies>
30+
<dependency>
31+
<groupId>org.springframework.boot</groupId>
32+
<artifactId>spring-boot-starter-webflux</artifactId>
33+
</dependency>
34+
<dependency>
35+
<groupId>org.springframework.boot</groupId>
36+
<artifactId>spring-boot-starter-thymeleaf</artifactId>
37+
</dependency>
38+
<dependency>
39+
<groupId>org.springframework.data</groupId>
40+
<artifactId>spring-data-r2dbc</artifactId>
41+
<version>1.1.4.RELEASE</version>
42+
</dependency>
43+
<dependency>
44+
<groupId>io.r2dbc</groupId>
45+
<artifactId>r2dbc-pool</artifactId>
46+
<version>0.8.4.RELEASE</version>
47+
</dependency>
48+
49+
<!-- MySQL dependencies start -->
50+
<dependency>
51+
<groupId>dev.miku</groupId>
52+
<artifactId>r2dbc-mysql</artifactId>
53+
<version>0.8.2.RELEASE</version>
54+
</dependency>
55+
<dependency>
56+
<groupId>com.google.cloud.sql</groupId>
57+
<artifactId>cloud-sql-connector-r2dbc-mysql</artifactId>
58+
<version>1.1.0</version>
59+
</dependency>
60+
<!-- MySQL dependencies end -->
61+
62+
<!-- Postgres dependencies start -->
63+
<dependency>
64+
<groupId>io.r2dbc</groupId>
65+
<artifactId>r2dbc-postgresql</artifactId>
66+
<version>0.8.5.RELEASE</version>
67+
</dependency>
68+
<dependency>
69+
<groupId>com.google.cloud.sql</groupId>
70+
<artifactId>cloud-sql-connector-r2dbc-postgres</artifactId>
71+
<version>1.1.0</version>
72+
</dependency>
73+
<!-- Postgres dependencies end -->
74+
</dependencies>
75+
76+
<build>
77+
<plugins>
78+
<plugin>
79+
<groupId>org.springframework.boot</groupId>
80+
<artifactId>spring-boot-maven-plugin</artifactId>
81+
<executions>
82+
<execution>
83+
<id>repackage</id>
84+
<goals>
85+
<goal>repackage</goal>
86+
</goals>
87+
</execution>
88+
</executions>
89+
<configuration>
90+
<mainClass>com.example.cloudsql.r2dbcsample.R2dbcSampleApplication</mainClass>
91+
</configuration>
92+
</plugin>
93+
<plugin>
94+
<groupId>com.google.cloud.tools</groupId>
95+
<artifactId>appengine-maven-plugin</artifactId>
96+
<version>2.2.0</version>
97+
<configuration>
98+
<version>GCLOUD_CONFIG</version>
99+
<projectId>GCLOUD_CONFIG</projectId>
100+
</configuration>
101+
</plugin>
102+
</plugins>
103+
</build>
104+
105+
<dependencyManagement>
106+
<dependencies>
107+
<dependency>
108+
<groupId>org.springframework.boot</groupId>
109+
<artifactId>spring-boot-starter-parent</artifactId>
110+
<version>2.3.4.RELEASE</version>
111+
<scope>import</scope>
112+
<type>pom</type>
113+
</dependency>
114+
</dependencies>
115+
</dependencyManagement>
116+
117+
</project>

‎cloud-sql/r2dbc/src/main/appengine/app.yaml‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,4 @@
1+
runtime: java11
2+
instance_class: F2
3+
env_variables:
4+
CLOUD_SQL_CONNECTION_STRING: "r2dbc:pool:gcp:<'mysql' or 'postgres'>://<user>:<password>@<connection_name>/<db_name>"

‎cloud-sql/r2dbc/src/main/java/com/example/cloudsql/r2dbcsample/CommandLineRunnerSchemaCreator.java‎

Lines changed: 55 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,55 @@
1+
/*
2+
* Copyright 2020 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.r2dbcsample;
18+
19+
import io.r2dbc.spi.ConnectionFactory;
20+
import org.slf4j.Logger;
21+
import org.slf4j.LoggerFactory;
22+
import org.springframework.beans.factory.annotation.Autowired;
23+
import org.springframework.boot.CommandLineRunner;
24+
import org.springframework.data.r2dbc.core.DatabaseClient;
25+
import org.springframework.stereotype.Component;
26+
27+
@Component
28+
public class CommandLineRunnerSchemaCreator implements CommandLineRunner {
29+
30+
private static final Logger LOGGER =
31+
LoggerFactory.getLogger(CommandLineRunnerSchemaCreator.class);
32+
33+
@Autowired
34+
private ConnectionFactory connectionFactory;
35+
36+
@Override
37+
public void run(String... args) {
38+
runDdl("CREATE TABLE IF NOT EXISTS vote ( "
39+
+ "vote_id SERIAL NOT NULL, "
40+
+ "time_cast timestamp NOT NULL, "
41+
+ "candidate CHAR(6) NOT NULL, "
42+
+ "PRIMARY KEY (vote_id) );");
43+
}
44+
45+
private void runDdl(String schema) {
46+
DatabaseClient client = DatabaseClient.create(connectionFactory);
47+
48+
client.execute(schema)
49+
.fetch()
50+
.rowsUpdated()
51+
.block();
52+
53+
LOGGER.info("Executed DDL: " + schema);
54+
}
55+
}

‎cloud-sql/r2dbc/src/main/java/com/example/cloudsql/r2dbcsample/MainController.java‎

Lines changed: 43 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,43 @@
1+
/*
2+
* Copyright 2020 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.r2dbcsample;
18+
19+
import org.springframework.beans.factory.annotation.Autowired;
20+
import org.springframework.stereotype.Controller;
21+
import org.springframework.ui.Model;
22+
import org.springframework.web.bind.annotation.RequestMapping;
23+
import org.thymeleaf.spring5.context.webflux.IReactiveDataDriverContextVariable;
24+
import org.thymeleaf.spring5.context.webflux.ReactiveDataDriverContextVariable;
25+
26+
@Controller
27+
public class MainController {
28+
29+
@Autowired
30+
private VoteRepository voteRepository;
31+
32+
@RequestMapping("/")
33+
public String index(final Model model) {
34+
IReactiveDataDriverContextVariable votes =
35+
new ReactiveDataDriverContextVariable(voteRepository.findAll(), 1);
36+
model.addAttribute("votes", votes);
37+
38+
model.addAttribute("tabCount", voteRepository.countWhereCandidateEquals("TABS"));
39+
model.addAttribute("spaceCount", voteRepository.countWhereCandidateEquals("SPACES"));
40+
41+
return "index";
42+
}
43+
}

‎cloud-sql/r2dbc/src/main/java/com/example/cloudsql/r2dbcsample/R2dbcSampleApplication.java‎

Lines changed: 59 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,59 @@
1+
/*
2+
* Copyright 2020 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.r2dbcsample;
18+
19+
import io.r2dbc.pool.ConnectionPool;
20+
import io.r2dbc.pool.ConnectionPoolConfiguration;
21+
import io.r2dbc.spi.ConnectionFactories;
22+
import io.r2dbc.spi.ConnectionFactory;
23+
import java.time.Duration;
24+
import org.springframework.beans.factory.annotation.Value;
25+
import org.springframework.boot.SpringApplication;
26+
import org.springframework.boot.autoconfigure.SpringBootApplication;
27+
import org.springframework.context.annotation.Bean;
28+
import org.springframework.context.annotation.Configuration;
29+
import org.springframework.data.r2dbc.config.AbstractR2dbcConfiguration;
30+
import org.springframework.data.r2dbc.repository.config.EnableR2dbcRepositories;
31+
32+
@SpringBootApplication
33+
@Configuration
34+
@EnableR2dbcRepositories
35+
public class R2dbcSampleApplication extends AbstractR2dbcConfiguration {
36+
37+
@Value("${connectionString}")
38+
private String connectionString;
39+
40+
public static void main(String[] args) {
41+
SpringApplication.run(R2dbcSampleApplication.class, args);
42+
}
43+
44+
@Override
45+
@Bean
46+
public ConnectionFactory connectionFactory() {
47+
//connectionString looks like this:
48+
//r2dbc:gcp:mysql://user:123456@my-project:us-central1:r2dbctest/
49+
ConnectionFactory connectionFactory = ConnectionFactories.get(connectionString);
50+
ConnectionPoolConfiguration configuration = ConnectionPoolConfiguration
51+
.builder(connectionFactory)
52+
.maxIdleTime(Duration.ofMillis(1000))
53+
.maxSize(20)
54+
.build();
55+
56+
return new ConnectionPool(configuration);
57+
}
58+
}
59+

0 commit comments

Comments
 (0)