Spring Cloud GCP using Spring Data JPA with MySQL 2nd Gen 5.7

Spring Cloud GCP adds integrations with Spring JDBC so you can run your MySQL or PostgreSQL databases in Google Cloud SQL using Spring JDBC, or other libraries that depend on it like Spring Data JPA. Here is an example of how using Spring Data JPA with "Spring Cloud GCP"

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 entities

EmployeeRest.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 startup

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