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:
- Create CF HANA backing service:
In the terminal, run the following command:
$ cf create-service hana schema <Your Service Instance Name>
- 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>
- 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>
- 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
