How-tos

Spring Data REST Application with Compose for MySQL on Bluemix

Share this post:

In this blog I’ll describe how to create a local Spring Data app with REST endpoints setup to query a Compose for MySQL database on Bluemix.

Prerequisites

You will need to have Java and Maven install and configured on your PATH.

Setting up the project

Setup Maven dependencies

In the directory you’ll be using for this project, create a pom.xml file with the following contents:


<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<modelVersion>4.0.0</modelVersion>

<groupId>com.example</groupId>
<artifactId>spring-data-mysql</artifactId>
<version>0.1.0</version>

<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.5.2.RELEASE</version>
</parent>

<properties>
<java.version>1.8</java.version>
</properties>

<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>

<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>

<repositories>
<repository>
<id>spring-releases</id>
<name>Spring Releases</name>
<url>https://repo.spring.io/libs-release</url>
</repository>
<repository>
<id>org.jboss.repository.releases</id>
<name>JBoss Maven Release Repository</name>
<url>https://repository.jboss.org/nexus/content/repositories/releases</url>
</repository>
</repositories>

<pluginRepositories>
<pluginRepository>
<id>spring-releases</id>
<name>Spring Releases</name>
<url>https://repo.spring.io/libs-release</url>
</pluginRepository>
</pluginRepositories>
</project>

In this file, we have included two dependencies you may be unfamiliar with:

  • spring-boot-starter-jpa: This dependency includes Repositories, Entities, Hibernate, and other compenents used in Spring Data.
  • mysql-connector-java: This dependency is for the MySQL JDBC driver.

Create Compose for MySQL database

Navigate to the Bluemix catalog. Under Services and Data & Analytics, select Compose for MySQL. Configure your database service if necessary, then click Create. You should see this screen when your service is ready:

Building the application

Connect to the database

Create an applications.properties file with the following contents:


spring.jpa.hibernate.ddl-auto=create
spring.datasource.url=jdbc:mysql://<DB-url>?useSSL=true
spring.datasource.username=<username>
spring.datasource.password=<password>

The first line (spring.jpa.hibernate.ddl-auto=create) says that the table should be created every time the application is launched. Other options for this propertiy are none, update, and create-drop.

The following lines are the database connection credentials. You can find the , , and on the Bluemix page you landed on after creating your Compose for MySQL database. From the text in the Connection String area, find the necessary information like this:


["mysql://<username>:<password>@<DB-url>"]

Next we will secure our connection to the database service with SSL. On the Bluemix page, copy the entire contents of the SSL Certificate area into a file named mysqlcert.crt in a directory outside your project.

Then use the following command to create a keystore from that certificate:


$ keytool -import -alias compose -file ./mysqlcert.crt -keystore ./mysqlstore -storetype pkcs12 -storepass mysqlpass

We will add the SSL configuration to our application later, once we have created the main method.

Create the Entity and Repository

Now we will create the class that Hibernate will use to make our database table. Create a User.java file with the following contents:


package example;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class User {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private long id;

private String firstName;
private String lastName;

protected User(){}

public User(String firstName, String lastName){
this.firstName = firstName;
this.lastName = lastName;
}

public String getFirstName(){
return firstName;
}

public String getLastName(){
return lastName;
}
}

The @Entity annotation on this class tells Hibernate to use it to create a table in your MySQL database. The id variable will serve as a primary key, and is automatically generated for each record added to the database.

Next create the interface for you repository in a UserRepository.java file with the contents:


package example;

import org.springframework.data.repository.CrudRepository;

public interface UserRepository extends CrudRepository<User, Long> {
//create any query methods you need here
}

By extending CrudRepository, we get all the methods we will need for this project. If you needed to add other queries, you could add them to the UserRepository interface where indicated.

 Create the Controller

In order to interact with the repository we have created, the application needs a Controller. Create a MainController.java file with the following contents:


package example;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

@Controller
@RequestMapping("/users")
public class MainController {

@Autowired
private UserRepository userRepository;

@RequestMapping(value = "/add/{firstName}/{lastName}", method = RequestMethod.GET)
public @ResponseBody String newUser(@PathVariable String firstName, @PathVariable String lastName){
User user = new User(firstName, lastName);
userRepository.save(user);

return String.format("User ('{07c2b926d154bd5dc241f595a572d3349d41d98f2484798a4a616f4fafe1ebc0}s {07c2b926d154bd5dc241f595a572d3349d41d98f2484798a4a616f4fafe1ebc0}s') saved!", firstName, lastName);
}

@RequestMapping(value = "/all", method = RequestMethod.GET)
public @ResponseBody Iterable<User>; getAllUsers(){
return userRepository.findAll();
}
}

This creates two endpoints:

  • /users/add/<firstName>/<lastName>: This endpoint allows you to add a user to the MySQL database. It responds with a text string telling you your request has succeeded.
  • /users/all: This endpoint returns a JSON array of all users stored in the MySQL database.

 Run the application

Create an Application.java file with the following contents:


package example;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Application {

public static void main(String[] args){

System.setProperty("javax.net.ssl.trustStore", "/Path/to/File/mysqlstore");
System.setProperty("javax.net.ssl.trustStorePassword", "mysqlpass");

SpringApplication.run(Application.class, args);
}
}

Note: We have also specified the path to the truststore we created earlier, as well its password.

Run the application with this Maven command:


$ mvn spring-boot:run

After your project finishes building, you should see a message saying it has started. After it has started, test both REST endpoints.

Test the first endpoint by adding a user named “John Smith.” You can add more if you choose, but be sure to add at least one.


$ curl http://localhost:8080/users/add/John/Smith
User ('John Smith') saved!

Test the second endpoint to see if your user was added.


$ curl http://localhost:8080/users/all
[{"firstName":"John","lastName":"Smith"}]

You should now have a working Spring Data application connected to a Compose for MySQL service on Bluemix!

More How-tos stories
May 7, 2019

We’ve Moved! The IBM Cloud Blog Has a New URL

In an effort better integrate the IBM Cloud Blog with the IBM Cloud web experience, we have migrated the blog to a new URL: www.ibm.com/cloud/blog.

Continue reading

May 6, 2019

Use IBM Cloud Certificate Manager to Obtain Let’s Encrypt TLS Certificates for Your Public Domains

IBM Cloud Certificate Manager now lets you obtain TLS certificates signed by Let’s Encrypt. Let’s Encrypt is an automated, ACME-protocol-based CA that issues free certificates valid for 90 days.

Continue reading

May 6, 2019

Are You Ready for SAP S/4HANA Running on Cloud?

Our clients tell us SAP applications are central to their success and strategy for cloud, with a deadline to refresh the business processes and move to SAP S/4HANA by 2025. Now is the time to assess, plan and execute the journey to cloud and SAP S/4HANA

Continue reading