1. First we need a MySQL 2nd Gen 5.7 database to exist in our GCP account which I have previously created as shown below
2. Create a new project using Spring Initializer or how ever you like to create it BUT ensure you have the following dependencies in place. Here is an example of what my pom.xml looks like. In short add the following maven dependencies as per the image below
pom.xml
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.5.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
<spring-cloud-gcp.version>1.0.0.RELEASE</spring-cloud-gcp.version>
<spring-cloud.version>Finchley.SR1</spring-cloud.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-rest</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-gcp-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-gcp-starter-sql-mysql</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
</dependencies>
...
<dependencyManagement>
<dependencies>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-dependencies</artifactId>
<version>${spring-cloud.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-gcp-dependencies</artifactId>
<version>${spring-cloud-gcp.version}</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
3. Let's start by creating a basic Employee entity as shown below
Employee.java
package pas.apj.pa.sb.gcp;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import javax.persistence.*;
@Entity
@NoArgsConstructor
@AllArgsConstructor
@Data
@Table (name = "employee")
public class Employee {
@Id
@GeneratedValue (strategy = GenerationType.AUTO)
private Long id;
private String name;
}
4. Let's now add a Rest JpaRepository for our Entity
EmployeeRepository.java
package pas.apj.pa.sb.gcp;
import org.springframework.data.jpa.repository.JpaRepository;
public interface EmployeeRepository extends JpaRepository <Employee, Long> {
}
5. Let's create a basic RestController to show all our Employee entitiesEmployeeRest.java
package pas.apj.pa.sb.gcp;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import java.util.List;
@RestController
public class EmployeeRest {
private EmployeeRepository employeeRepository;
public EmployeeRest(EmployeeRepository employeeRepository) {
this.employeeRepository = employeeRepository;
}
@RequestMapping("/emps-rest")
public List<Employee> getAllemps()
{
return employeeRepository.findAll();
}
}
6. Let's create an ApplicationRunner to show our list of Employees as the applications starts up
EmployeeRunner.java
package pas.apj.pa.sb.gcp;
import org.springframework.boot.ApplicationArguments;
import org.springframework.boot.ApplicationRunner;
import org.springframework.stereotype.Component;
@Component
public class EmployeeRunner implements ApplicationRunner {
private EmployeeRepository employeeRepository;
public EmployeeRunner(EmployeeRepository employeeRepository) {
this.employeeRepository = employeeRepository;
}
@Override
public void run(ApplicationArguments args) throws Exception {
employeeRepository.findAll().forEach(System.out::println);
}
}
7. Add a data.sql file to create some records in the database at application startupdata.sql
insert into employee (name) values ('pas');
insert into employee (name) values ('lucia');
insert into employee (name) values ('lucas');
insert into employee (name) values ('siena');
8. Finally our "application.yml" file will need to be able to be able to connect to our MySQL instance running in GCP as well as set some properties for JPA as shown below
spring:
jpa:
hibernate:
ddl-auto: create-drop
use-new-id-generator-mappings: false
properties:
hibernate:
dialect: org.hibernate.dialect.MariaDB53Dialect
cloud:
gcp:
sql:
instance-connection-name: fe-papicella:australia-southeast1:apples-mysql-1
database-name: employees
datasource:
initialization-mode: always
hikari:
maximum-pool-size: 1
A couple of things in here which are important.
- Set the Hibernate property "dialect: org.hibernate.dialect.MariaDB53Dialect" otherwise without this when hibernate creates tables for your entities you will run into this error as Cloud SQL database tables are created using the InnoDB storage engine.
ERROR 3161 (HY000): Storage engine MyISAM is disabled (Table creation is disallowed).
- For a demo I don't need multiple DB connections so I set the datasource "maximum-pool-size" to 1
- Notice how I set the "instance-connection-name" and "database-name" which is vital for Spring Cloud SQL to establish database connections
8. Now we need to make sure we have a database called "employees" as per our "application.yml" setting.
9. Now let's run our Spring Boot Application and verify this working showing some output from the logs
- Connection being established
2018-10-08 10:54:37.333 INFO 89922 --- [ main] c.google.cloud.sql.mysql.SocketFactory : Connecting to Cloud SQL instance [fe-papicella:australia-southeast1:apples-mysql-1] via ssl socket.
2018-10-08 10:54:37.335 INFO 89922 --- [ main] c.g.cloud.sql.core.SslSocketFactory : First Cloud SQL connection, generating RSA key pair.
2018-10-08 10:54:38.685 INFO 89922 --- [ main] c.g.cloud.sql.core.SslSocketFactory : Obtaining ephemeral certificate for Cloud SQL instance [fe-papicella:australia-southeast1:apples-mysql-1].
2018-10-08 10:54:40.132 INFO 89922 --- [ main] c.g.cloud.sql.core.SslSocketFactory : Connecting to Cloud SQL instance [fe-papicella:australia-southeast1:apples-mysql-1] on IP [35.197.180.223].
2018-10-08 10:54:40.748 INFO 89922 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed.
- Showing the 4 Employee records
Employee(id=1, name=pas)
Employee(id=2, name=lucia)
Employee(id=3, name=lucas)
Employee(id=4, name=siena)
10. Finally let's make RESTful call as we defined above using HTTPie as follows
pasapicella@pas-macbook:~$ http :8080/emps-rest
HTTP/1.1 200
Content-Type: application/json;charset=UTF-8
Date: Mon, 08 Oct 2018 00:01:42 GMT
Transfer-Encoding: chunked
[
{
"id": 1,
"name": "pas"
},
{
"id": 2,
"name": "lucia"
},
{
"id": 3,
"name": "lucas"
},
{
"id": 4,
"name": "siena"
}
]
More Information
Spring Cloud GCP
https://cloud.spring.io/spring-cloud-gcp/
Spring Cloud GCP SQL demo (This one is using Spring JDBC)
https://github.com/spring-cloud/spring-cloud-gcp/tree/master/spring-cloud-gcp-samples/spring-cloud-gcp-sql-sample
