Google Cloud SQL is a fully-managed database service that makes it easy to set up, maintain, manage, and administer your relational databases on Google Cloud Platform.

You can use Cloud SQL with either MySQL or PostgreSQL.

In this codelab, you will learn how to setup a Cloud SQL (MySQL) instance and then update a Spring Boot application to use the Cloud SQL instance as its back-end storage.

The Spring Boot starter for Google Cloud SQL provides an auto-configured DataSource, allowing you to easily take advantage of Google Cloud SQL with minimal changes to your code.

This codelab uses the Spring PetClinic source code.

What you'll learn

What you'll need

How will you use use this tutorial?

Read it through only Read it and complete the exercises

How would you rate your experience with building HTML/CSS web apps?

Novice Intermediate Proficient

How would you rate your experience with using Google Cloud Platform services?

Novice Intermediate Proficient

Codelab-at-a-conference setup

If you see a "request account button" at the top of the main Codelabs window, click it to obtain a temporary account. Otherwise ask one of the staff for a coupon with username/password.

These temporary accounts have existing projects that are set up with billing so that there are no costs associated for you with running this codelab.

Note that all these accounts will be disabled soon after the codelab is over.

Use these credentials to log into the machine or to open a new Google Cloud Console window https://console.cloud.google.com/. Accept the new account Terms of Service and any updates to Terms of Service.

Here's what you should see once logged in:

When presented with this console landing page, please select the only project available. Alternatively, from the console home page, click on "Select a Project" :

After Cloud Shell launches, you can use the command line to create a new CloudSQL instance:

$ gcloud sql instances create my-instance

Once this operation completes, your instance will be ready to be use.

Now create a database that you will use for the Spring Pet Clinic application:

$ gcloud sql databases create petclinic --instance my-instance

You can also access and configure the instance via the cloud console.

Get the Instance Connection Name of the instance in the format project-id:zone-id:instance-id by running the following command. You will use this later in configuring your Spring Boot application.

$ gcloud sql instances describe my-instance |grep connectionName

You will now clone and test the Spring Pet Clinic locally:

$ git clone https://github.com/spring-projects/spring-petclinic
$ cd spring-petclinic
$ ./mvnw spring-boot:run

Access the Web Preview on the top right of the cloud shell as shown here and preview on port 8080.

You should see the Spring Pet Clinic home page as shown here in your browser:

Play around and add data. The application uses an in-memory HyperSQL database. You will now switch from HyperSQL to using Cloud SQL as your database.

Update Maven pom.xml

Update your pom.xml as shown here. The starter provides an auto-configured DataSource object to connect to your Cloud SQL database. You can use vim,nano,or emacs to edit the file.

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" ...>
    ...
    <!-- Add Spring Cloud GCP Dependency BOM -->
    <dependencyManagement>
        <dependencies>
          <dependency>
          <groupId>org.springframework.cloud</groupId>
          <artifactId>spring-cloud-gcp-dependencies</artifactId>
          <version>1.0.0.RC1</version>
          <type>pom</type>
          <scope>import</scope>
          </dependency>
      </dependencies>
    </dependencyManagement>
    <dependencies>
      ...
      <!-- Add CloudSQL Starter for MySQL -->
      <dependency>
        <groupId>org.springframework.cloud</groupId>
        <artifactId>spring-cloud-gcp-starter-sql-mysql</artifactId>
      </dependency>
      ...
    </dependencies>
    <repositories>
      <!-- Use Spring Milestone Repository -->
      <repository>
        <id>repository.spring.milestone</id>
        <name>Spring Milestones Repository</name>
        <url>http://repo.spring.io/milestone</url>
      </repository>
    </repositories>
</project>

Update application-mysql.properties

Replace the content of src/main/resources/application-mysql.properties with the following properties. You'll need to set the Instance Connection Name from the earlier step.

src/main/resources/application-mysql.properties

database=mysql

# Delete the rest of the original content of the file and replace with the following:
spring.cloud.gcp.sql.database-name=petclinic
spring.cloud.gcp.sql.instance-connection-name=YOUR_CLOUD_SQL_INSTANCE_CONNECTION_NAME

# Initialize the database since the newly created Cloud SQL database has no tables. The following flag is for Spring Boot 2.
spring.datasource.initialization-mode=always

Finally, enable MySQL profile in the Spring Boot application by adding mysql to application.properties' spring.profiles.active property:

src/main/resources/application.properties

# Keep the content of the file the same
...

# In the last line, add mysql to the spring.profiles.active property
spring.profiles.active=mysql

You can start the Spring Boot application normally with the Spring Boot plugin:

$ ./mvnw -DskipTests spring-boot:run

Once the application has started, click on the Web Preview icon in the Cloud Shell toolbar and choose preview on port 8080.

You should see the Spring Pet Clinic home page again as shown here in your browser:

Add a pet owner entry.

(Optional) Verify that Cloud SQL has persisted the data

You can verify that the data you entered has been persisted to Cloud SQL as shown here. Hit Enter when prompted for a password.

$ gcloud sql connect my-instance -u root
Whitelisting your IP for incoming connection for 5 minutes...done.
Enter password: <Press Enter, there is no password by default>
...
mysql> use petclinic;
mysql> select * from owners;  

(Optional) Delete your Cloud SQL instance

Once you have stopped your application, you can delete the Cloud SQL instance using the following command:

$ gcloud sql instances delete my-instance 

You learned how to connect to Cloud SQL in your Spring Boot application!

Learn More

License

This work is licensed under a Creative Commons Attribution 2.0 Generic License.