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

Self-paced environment setup

If you don't already have a Google Account (Gmail or Google Apps), you must create one. Sign-in to Google Cloud Platform console (console.cloud.google.com) and create a new project:

Remember the project ID, a unique name across all Google Cloud projects (the name above has already been taken and will not work for you, sorry!). It will be referred to later in this codelab as PROJECT_ID.

Next, you'll need to enable billing in the Cloud Console in order to use Google Cloud resources.

Running through this codelab shouldn't cost you more than a few dollars, but it could be more if you decide to use more resources or if you leave them running (see "cleanup" section at the end of this document).

New users of Google Cloud Platform are eligible for a $300 free trial.

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.