Run your Cloud Foundry application with HANA-DB service on a local tomcat deployment

Run your Cloud Foundry application with HANA-DB service on a local tomcat deployment

As a Java developer that his application utilizes HANA-DB for its database needs and is deployed on SAP Cloud Foundry, I need to debug my code in all development cycles.

Though SAP Cloud Foundry supports remote debugging of your application via SSH connection as describe in https://docs.cloudfoundry.org/buildpacks/java/java-tips.html under “Debug Java Apps on Cloud Foundry” section. It can be a hassle to do all that for every debugging in the application development stage.

First let’s see how to use HANA Database via JDBC in Cloud Foundry Java application:

1. Create CF HANA backing service:

In the terminal, run the following command:

$ cf create-service hana schema <Your Service Instance Name>

2. Bind your Service to your application:

In the manifest.yml file, add <Your Service Instance Name> as a service to be bound to your application:

services:
 - <Your Service Instance Name>

3. Add Maven dependencies

You will have to add to your application dependencies ngdbc dependency which is the JDBC driver for HANA. Simply add the follow lines to your pom.xml file:

<!-- SAP HANA -->
<dependency>
    <groupId>com.sap.db.jdbc</groupId>
    <artifactId>ngdbc</artifactId>
    <version>1.111.1</version>
    <scope>runtime</scope>
    <exclusions>
        <exclusion> <!-- Exclude old mockito version -->
            <artifactId>mockito-all</artifactId>
            <groupId>org.mockito</groupId>
        </exclusion>
    </exclusions>
</dependency>

4. Connect to HANA Database via JDBC from your java code

In order to get your DataSource instance from spring-cloud connector you need to create a configuration class that extend org.springframework.cloud.config.java.AbstractCloudConfig and write a bean method that returns DataSource, like this one:

    @Bean
    public DataSource dataSource() {
        List<String> dataSourceNames = Arrays.asList("BasicDbcpPooledDataSourceCreator",
                "TomcatJdbcPooledDataSourceCreator", "HikariCpPooledDataSourceCreator",
                "TomcatDbcpPooledDataSourceCreator");
        DataSourceConfig dbConfig = new DataSourceConfig(dataSourceNames);
        DataSource ds = connectionFactory().dataSource(dbConfig);
        
        return ds;
    }

Sure you’ll need to add Spring Cloud Connector dependencies artifacts: spring-cloud-spring-service-connector & spring-cloud-cloudfoundry-connector from org.springframework.cloud group.

<!-- Spring Cloud Connector -->
<dependency>
    <groupId>org.springframework.cloud</groupId>
    <artifactId>spring-cloud-spring-service-connector</artifactId>
    <version>1.2.2.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework.cloud</groupId>
    <artifactId>spring-cloud-cloudfoundry-connector</artifactId>
    <version>1.2.2.RELEASE</version>
    <scope>runtime</scope>
</dependency>

Now that you have the DataSource you can inject it using spring framework to any class in your Java code and get the DB connection out of it:

Connection connection = dataSource.getConnection()

or in some more details:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.inject.Inject;
import javax.sql.DataSource;

public class hanaDbUseClass {

    @Inject
    private DataSource dataSource;

    private static final String TABLE_NAME = "TABLE_A";

    public Long getCountForId(Long id) throws SQLException {    	
        String query = String.format("SELECT \"COUNT\" FROM %s WHERE \"ID\"=?", TABLE_NAME);
        try (Connection connection = dataSource.getConnection(); PreparedStatement statement = connection.prepareStatement(query)) {
            statement.setLong(1, id);
            try (ResultSet result = statement.executeQuery()) {
                if (result.next()) {
                    return result.getLong(1);
                }
                logger.debug("ID not found");
                return null;
            }
        } catch (SQLException e) {
            throw new HanaException(e);
        }
    }
}

But how can you deploy it on tomcat that run in your local machine?

This isn’t straightforward as you will need special configuration for the tomcat to get the HANA-DB data source and connection.

spring-cloud-cloudfoundry-connector in default support some service connector as describe in https://cloud.spring.io/spring-cloud-connectors/spring-cloud-spring-service-connector.html but HANA-DB isn’t one of them L.

But no worry SAP provide and extension: Spring Cloud Connector for SAP HANA Cloud Platform (HCP) (https://github.com/SAP/spring-cloud-sap) You just need to add it to your application dependencies.

<dependency>
    <groupId>com.sap.hana.cloud</groupId>
    <artifactId>spring-cloud-cloudfoundry-hana-service-connector</artifactId>
    <version>1.0.4.RELEASE</version>
</dependency>
<dependency>
    <groupId>com.sap.hana.cloud</groupId>
    <artifactId>spring-cloud-sap-core</artifactId>
    <version>1.0.4.RELEASE</version>
</dependency>

Now you need to configure the VCAP_SERVICES environment variable in the tomcat.

Use your Cloud Foundry CLI command ‘env’ to find your cloud foundry application deployed value for VCAP_SERVICES.

$ cf env <Your application name>

Simply copy the response json, edit the ‘credentials’ for your HANA-DB host, port, username, password and url and you are good to go.

{
 "VCAP_SERVICES": {
  "hana": [
   {
    "binding_name": null,
    "credentials": {
     "driver": "com.sap.db.jdbc.Driver",
     "host": "10.xxx.xx.xx",
     "password": "xxx",
     "port": "3xxxx",
     "schema": "USR_XXX",
     "url": "jdbc:sap://10.xxx.xx.xx:3xxxx/?currentschema=USR_XXX",
     "user": "USR_XXX"
    },
    "instance_name": "Service name",
    "label": "hana",
    "name": "Service name",
    "plan": "schema",
    "provider": null,
    "syslog_drain_url": null,
    "tags": [
     "hana",
     "database",
     "relational"
    ],
    "volume_mounts": []
   }
  ]
 }
}

Good luck

Eyal