Introduction
Lately we have been looking into the possibilities of tapping into a HANA database platform from our on premise Pivotal Cloud Foundry (PCF) PaaS for an upcoming project. There are a number of ways we can read data from HANA in PCF. One of them is to use a cloud connector which connects to HANA with the JDBC API. Luckily SAP have provided a HANA service connector for this:
If like myself you are a newbie to PCF then it may not seem obvious from the outset as to how to use it. In this blog I will demo how this can be utilized. At a high level we will do the following:
- Create a user provided service which will contain our HANA db connection details and credentials.
- Create a spring boot application which reads data from the HANA db and displays it in a simple html page.
- Bind the HANA db service to our application.
Prerequisites
- A HANA database
- A PCF instance
- Spring Tool Suite(STS)
HANA DB service
The first step is to create a user provided service in PCF for the HANA database. This will contain our connection details and user credentials for connecting to HANA.
See here for detailed information on creating user provided services in PCF - http://docs.pivotal.io/pivotalcf/1-7/cf-cli/getting-started.html#user-provided
- Login to your PCF cf CLI instance in command prompt and execute the following command:
cf cups hana-db -p "hostname, port, user, password, schema, url"
- This will prompt you to enter the hostname, port, user, password, schema and url for the HANA db that you want to connect to.
- The url should be in the form of jdbc:sap://<server>:<port>[/?<options>] e.g. "jdbc:sap://myserver:30015/?autocommit=false
- The port should be 3<instance number>15 for example 30015 if the instance is 00.
Note: the url parameter is important as this is what the HANA service connector will use in identifying the datasource.
Once created, if you log in to your Apps Manager you should see the below entry in your list of Services:
Demo spring boot application that uses the HANA service connector
- In STS create a new project as follows:
- File -> New -> Spring Starter Project
- Click Next and on the next screen click Finish
- This will create the project with below structure:
- Update the pom.xml to include the required dependencies (specifically the dependencies with the following artifactIds:
- spring-boot-starter-thymeleaf
- spring-cloud-cloudfoundry-connector
- spring-cloud-spring-service-connector
- spring-cloud-core
- spring-cloud-cloudfoundry-hana-service-connector
- commons-dbcp
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.pm.hana</groupId>
<artifactId>demo-hana-service-connector</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>demo-hana-service-connector</name>
<description>demo-hana-service-connector</description>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.3.6.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.7</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-thymeleaf</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-cloudfoundry-connector</artifactId>
<version>1.2.0.RELEASE</version>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-spring-service-connector</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.cloud</groupId>
<artifactId>spring-cloud-core</artifactId>
</dependency>
<dependency>
<groupId>com.sap.hana.cloud</groupId>
<artifactId>spring-cloud-cloudfoundry-hana-service-connector</artifactId>
<version>1.0.4.RELEASE</version>
</dependency>
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
- You should see the Maven Dependencies libraries are now updated.
- Add a manifest.yml file with the following details.
- Take a note of the "hana-db" defined under services. This will bind the hana-db service to this application.
applications:
- name: demo-hana-service-connector
instances: 1
host: demo-hana-service-connector
services:
- hana-db
env:
SPRING_PROFILES_DEFAULT: cloud
- Add the HANA JDBC Driver ngdbc.jar file in a folder called lib under src/main/resources. Note: if you have HANA Client installed you can get the ngdbc.jar from your Program Files.
- Add a Configuration class where we will define our dataSource bean. Note the "hana-db" service is passed as a String parameter to retrieve the dataSource. @Configuration indicates that the class can be used by the Spring IoC Container as a source of bean definitions. @Profile("cloud") ensures the configuration is loaded only in a cloud environment.
package com.pm.hana;
import javax.sql.DataSource;
import org.springframework.cloud.CloudException;
import org.springframework.cloud.config.java.AbstractCloudConfig;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Profile;
@Configuration
@Profile("cloud")
public class CloudConfig extends AbstractCloudConfig
{
@Bean
public DataSource dataSource()
{
DataSource retVal = null;
try
{
return connectionFactory().dataSource("hana-db");
}
catch (CloudException ex)
{
ex.printStackTrace();
}
return retVal;
}
}
- Create a simple HanaDatabase object class to store the database information we retrieve from the HANA db.
package com.pm.hana;
public class HanaDatabase {
String databaseName;
String usage;
public String getDatabaseName() {
return databaseName;
}
public void setDatabaseName(String databaseName) {
this.databaseName = databaseName;
}
public String getUsage() {
return usage;
}
public void setUsage(String usage) {
this.usage = usage;
}
}
- Create a Controller class to handle the web requests. Note that the dataSource defined in the CloudConfig class is autowired into our class. The home method is annotated with @Requestmapping("/home") to process requests to the "/home" path. Among other things this calls the getHanaDatabaseInfo method which will pull out data from the sys.m_database table and add to the model attributes before redirecting to the home.html page.
package com.pm.hana;
import java.net.URI;
import java.net.URISyntaxException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedHashMap;
import java.util.Map;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
@SpringBootApplication
@Controller
public class HomeController
{
private static Logger log = LoggerFactory.getLogger(HomeController.class);
@Autowired(required = false)
DataSource dataSource;
@RequestMapping("/home")
public String home(Model model)
{
Map<Class<?>, String> services = new LinkedHashMap<Class<?>, String>();
HanaDatabase hanaDatabase = null;
if (dataSource != null)
{
services.put(getClass(dataSource), toString(dataSource));
hanaDatabase = getHanaDatabaseInfo();
}
model.addAttribute("services", services.entrySet());
model.addAttribute("hanaDatabase", hanaDatabase);
return "home";
}
private HanaDatabase getHanaDatabaseInfo()
{
HanaDatabase hanaDatabase = null;
Connection conn = null;
try
{
conn = dataSource.getConnection();
Statement stmt = conn.createStatement();
ResultSet resultSet = stmt.executeQuery("select database_name, usage from sys.m_database");
resultSet.next();
hanaDatabase = new HanaDatabase();
hanaDatabase.setDatabaseName(resultSet.getString(1));
hanaDatabase.setUsage(resultSet.getString(2));
}
catch (SQLException ex)
{
log.info("SQLException: " + ex);
}
finally
{
if (conn != null)
{
try
{
conn.close();
}
catch (SQLException e) {} // we are screwed!
}
}
return hanaDatabase;
}
private String toString(DataSource dataSource)
{
if (dataSource == null)
{
return "<none>";
}
else
{
Connection conn = null;
try
{
conn = dataSource.getConnection();
DatabaseMetaData metaData = conn.getMetaData();
return stripCredentials(metaData.getURL());
}
catch (Exception ex)
{
return "<unknown> " + dataSource.getClass();
}
finally
{
if (conn != null)
{
try
{
conn.close();
}
catch (SQLException e) {
log.info("SQLException: " + e);
} // we are screwed!
}
}
}
}
private String stripCredentials(String urlString)
{
try
{
if (urlString.startsWith("jdbc:"))
{
urlString = urlString.substring("jdbc:".length());
}
URI url = new URI(urlString);
return new URI(url.getScheme(), null, url.getHost(), url.getPort(), url.getPath(), null, null).toString();
}
catch (URISyntaxException e)
{
System.out.println(e);
return "<bad url> " + urlString;
}
}
private static Class<?> getClass(Object obj)
{
if (obj != null)
{
return obj.getClass();
}
else
{
return null;
}
}
}
- Under src/main/resources add a templates folder and add the home.html file.
<!DOCTYPE HTML>
<html xmlns:th="http://www.thymeleaf.org">
<head>
<title>Getting Started: Serving Web Content</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<link rel="stylesheet" href="https://netdna.bootstrapcdn.com/bootstrap/3.1.1/css/bootstrap.min.css"/>
</head>
<body>
<div class="container">
<br/>
<div class="jumbotron">
<h1>PCF HANA Service Connector</h1>
<p>Demo PCF Spring application that uses <a href="http://projects.spring.io/spring-cloud">Spring Cloud</a> and
<a href="https://github.com/SAP/spring-cloud-sap/tree/master/spring-cloud-cloudfoundry-hana-service-connector">HANA Service Connector</a>
to connect to a HANA database</p>
</div>
<h2>Cloud Services</h2>
<table class="table table-striped">
<thead>
<tr>
<th>Service Connector Type</th>
<th>Connection address</th>
</tr>
</thead>
<tbody>
<tr th:each="service : ${services}">
<td><strong th:text="${service.key.name}"></strong></td>
<td th:text="${service.value}" />
</tr>
</tbody>
</table>
<h2>Database Info</h2>
<div class="row">
<strong class="col-sm-2">Database Name:</strong>
<p class="col-sm-10" th:text="${hanaDatabase.databaseName}">databaseName</p>
</div>
<div class="row">
<strong class="col-sm-2">Usage:</strong>
<p class="col-sm-10" th:text="${hanaDatabase.usage}">usage</p>
</div>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>
<script src="https://netdna.bootstrapcdn.com/bootstrap/3.1.1/js/bootstrap.min.js"></script>
</div>
</body>
</html>
- Your final project structure should look like this:
- Deploy the application to PCF by dragging the project to your PCF Server in STS( or via the cf CLI).
- In the next screen note that I have set the Memory Limit of the application to 4 GB. A memory limit of 4 GB resulted in heap memory size of 3GB. This is to allow for the heap size requirements of the HANA JDBC connection. At the default of 512 MB I was getting "Out of Memory - java heap space" errors.
- In the next screen you will see the available services that I can choose from to bind to the application. "hana-db" will be selected by default as specified in our manifest.yml file.
- You should see the below in your console logs after the application was deployed showing that the application has started:
2016-07-13 14:39:30.016 INFO 14 --- [ main] s.b.c.e.t.TomcatEmbeddedServletContainer : Tomcat started on port(s): 8080 (http)
2016-07-13 14:39:30.024 INFO 14 --- [ main] .p.h.DemoHanaServiceConnectorApplication : Started DemoHanaServiceConnectorApplication in 8.624 seconds (JVM running for 9.498)
healthcheck passed
Exit status 0
Container became healthy
[Application Running Check] - Application appears to be running - demo-hana-service-connector.
- Now lets go to our PCF Apps Manager again and inspect our new application
- As we can see the app is up
- If we go to the Services tab we can see that the hana-db service is bound to the app:
- And if we look at the Env variables tab we can now see the user provided credentials supplied by the hana-db service:
- Now lets test our application. Open https://demo-hana-service-connector.<host>/home.
We can see that it displays the Cloud Services and the Database Information that we read from the HANA db. Success!
While this was implemented on Pivotal Cloud Foundry, it should be applicable to any Cloud Foundry enabled PaaS.
References
Connect to SAP HANA via JDBC - SAP HANA Developer Guide for SAP HANA Studio - SAP Library
- SAP Managed Tags:
