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.
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 your pom.xml as shown here. The starter provides an auto-configured
DataSource object to connect to your Cloud SQL database. You can use
emacs to edit the file.
<?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>
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.
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
# 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.
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;
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!
This work is licensed under a Creative Commons Attribution 2.0 Generic License.