Connect a Spring Boot app to Cloud SQL

1. Before you begin

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. You can use Cloud SQL with either Cloud SQL for MySQL or Cloud SQL for PostgreSQL.

In this codelab, you will learn how to set up a Cloud SQL for MySQL instance and then update a Spring Boot app to use the Cloud SQL instance as its backend storage. The Spring Boot Starter for Google Cloud SQL provides an autoconfigured DataSource, allowing you to easily take advantage of Cloud SQL with minimal changes to your code. This codelab uses the Spring Petclinic source code.

Prerequisites

• Familiarity with Java programming language and tools
• Knowledge of standard Linux text editors, such as Vim, Emacs, and nano

What you'll do

• Use Cloud SQL in your Spring Boot app.

What you'll need

• A Google Cloud project
• A browser, such Google Chrome or Firefox

2. Setup and requirements

Self-paced environment setup

1. Sign-in to the Google Cloud Console and create a new project or reuse an existing one. If you don't already have a Gmail or Google Workspace account, you must create one.

• The Project name is the display name for this project's participants. It is a character string not used by Google APIs. You can always update it.
• The Project ID is unique across all Google Cloud projects and is immutable (cannot be changed after it has been set). The Cloud Console auto-generates a unique string; usually you don't care what it is. In most codelabs, you'll need to reference your Project ID (typically identified as PROJECT_ID). If you don't like the generated ID, you might generate another random one. Alternatively, you can try your own, and see if it's available. It can't be changed after this step and remains for the duration of the project.
• For your information, there is a third value, a Project Number, which some APIs use. Learn more about all three of these values in the documentation.

Caution: A project ID is globally unique and can't be used by anyone else after you've selected it. You are the only user of that ID. Even if a project is deleted, the ID can't be used again

Note: If you use a Gmail account, you can leave the default location set to No organization. If you use a Google Workspace account, choose a location that makes sense for your organization.

2. Next, you'll need to enable billing in the Cloud Console to use Cloud resources/APIs. Running through this codelab won't cost much, if anything at all. To shut down resources to avoid incurring billing beyond this tutorial, you can delete the resources you created or delete the project. New Google Cloud users are eligible for the $300 USD Free Trial program.

Activate Cloud Shell

1. From the Cloud Console, click Activate Cloud Shell .

If this is your first time starting Cloud Shell, you're presented with an intermediate screen describing what it is. If you were presented with an intermediate screen, click Continue.

It should only take a few moments to provision and connect to Cloud Shell.

This virtual machine is loaded with all the development tools needed. It offers a persistent 5 GB home directory and runs in Google Cloud, greatly enhancing network performance and authentication. Much, if not all, of your work in this codelab can be done with a browser.

Once connected to Cloud Shell, you should see that you are authenticated and that the project is set to your project ID.

2. Run the following command in Cloud Shell to confirm that you are authenticated:

gcloud auth list

Command output

 Credentialed Accounts
ACTIVE  ACCOUNT
*       <my_account>@<my_domain.com>

To set the active account, run:
    $ gcloud config set account `ACCOUNT`

Note: The gcloud command-line tool is the powerful and unified command-line tool in Google Cloud. It comes preinstalled in Cloud Shell. You will notice its support for tab completion. For more information, see gcloud command-line tool overview.

3. Run the following command in Cloud Shell to confirm that the gcloud command knows about your project:

gcloud config list project

Command output

[core]
project = <PROJECT_ID>

If it is not, you can set it with this command:

gcloud config set project <PROJECT_ID>

Command output

Updated property [core/project].

Note: If you're completing this tutorial outside of Cloud Shell, follow Set up Application Default Credentials.

3. Set up a Cloud SQL for MySQL instance

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

$ gcloud sql instances create my-instance

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

2. Now create a database that you will use for the Petclinic app:

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

You can also access and configure the instance via the Cloud Console.

A Cloud SQL instance is zonal. It means it'll be running in one of the Google Cloud zones. In this codelab, you don't specify the zone or region. The command line will automatically use us-central as the region and pick a zone to host your database. For more information, see gcloud sql instances create.

3. Get the instance connection name in the format project-id:zone-id:instance-id by running the following command. You will use this later in configuring your Spring Boot app.

$ gcloud sql instances describe my-instance | grep connectionName

4. Clone and test the Petclinic app locally

1. You will now clone the Petclinic app locally.

$ git clone https://github.com/spring-projects/spring-petclinic

$ cd spring-petclinic

2. Make sure the correct Java version is set and run the Petclinic app locally.

$ export JAVA_HOME=/usr/lib/jvm/java-17-openjdk-amd64/

$ ./mvnw spring-boot:run

3. Click Web Preview in Cloud Shell , then select Preview on port 8080.

You should see the Petclinic homepage as shown here in your browser:

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

5. Use Cloud SQL in Petclinic

Update the Maven pom.xml file

Update the pom.xml file as shown here. The starter provides an autoconfigured 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>com.google.cloud</groupId>
          <artifactId>spring-cloud-gcp-dependencies</artifactId>
          <version>4.1.4</version>
          <type>pom</type>
          <scope>import</scope>
          </dependency>
      </dependencies>
    </dependencyManagement>
    <dependencies>
      ...
      <!-- Add CloudSQL Starter for MySQL -->
      <dependency>
        <groupId>com.google.cloud</groupId>
        <artifactId>spring-cloud-gcp-starter-sql-mysql</artifactId>
      </dependency>
      ...
    </dependencies>
</project>

Update application-mysql.properties

1. 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

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.5+.
spring.sql.init.mode=always  

2. Finally, enable a Cloud SQL for MySQL profile in the Spring Boot app 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

6. Run the app in Cloud Shell

1. You can start the Spring Boot app normally with the Spring Boot plugin:

$ ./mvnw -DskipTests spring-boot:run

2. Once the app has started, click Web Preview in the Cloud Shell toolbar, then select Preview on port 8080.

You should see the Spring Petclinic homepage again as shown here in your browser:

Note: The Spring Cloud GCP starter you added automatically configured the Cloud SQL for MySQL connection URL. By default, Cloud SQL servers are not exposed via a public IP address. The Spring Cloud GCP starter also used a special SSL socket factory that automatically established a secure connection to the Cloud SQL database server.

3. 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 app, you can delete the Cloud SQL instance using the following command:

$ gcloud sql instances delete my-instance

7. Congratulations

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

Learn More

• Cloud SQL documentation
• Spring Cloud GCP
• Spring Cloud GCP GitHub repository
• Java on Google Cloud