Use JDBC to connect to HANA database instance in SAP Cloud Platform

This blog is divided into two parts.

Part 1

1. Introduce how to create a HANA database instance in SAP Cloud Platform (called SCP for short in the remaining part of this blog)

2. Develop a Java Web application and deploy it to SCP. The application uses JDBC to access HANA database instance created in previous step.

Part 2

Develop a Java Web application and deploy to an On-Premise system under Corporate Network. This Java application gains access to the HANA databse instance in SCP by leveraging SAP Cloud Connector.

The source code used in this blog could be found from my github. You can also get it via neo SDK.

Create a HANA database instance in SCP

1. Create a new instance in SCP Cockpit:

Specify ID, user and password. Database ID will be used later.

Once created, the status turns to STARTED. You can also add url of Development Tools into favourate, as we will use it later as well.

Now this database instance is ready for use. Next step is to develop a Java application using JDBC to access it.

Access HANA database instance via JDBC

Import the application in my github into Eclipse. Three major Java files:

Person.java

It defines the Person model consists of three member attributes: id, firstName and lastName, and corresponding getter and setter method. This class is so called POJO(Plain Old Java Object), which means there is no specific constraints or framework( for example EJB ) applied to it.

PersonDAO.java

As its name implies : DAO – Data Access Object。It’s responsible to connect HANA database instance via JDBC, create database table named as T_PERSONS, and insert records into created table.

PersistenceWithJDBCServlet.java

Implement a simple UI to get user input and call PersonDAO to insert record into connected HANA database table.

The JDBC DataSource instance is gained via JNDI. Such instance is passed into DAO constructor as importing parameter. All subsequent JDBC operation are done by it.

The configuration for DefaultDB in line 28 above is done in web.xml:

Deploy this application in SCP with name jerryjdbc.

Till now, the Java application still does not know which database instance in SCP could be connected.

As a result we should bind the application to the very HANA instance hana01 created previously. The binding could be established by the below UI:

Once binding is done, we can now insert a Person record Jerry Wang to HANA instance via Servlet UI:

Access the Java application in mobile phone, and the record Jerry Wang inserted just now could be found:

Connects HANA database instance in Cloud from On-Premise system

Now we try the other way round. I deploy the Java application to local server which runs in SAP Chengdu office under Corporate Network. It will connect HANA database instance in SCP with help of Cloud Connector again.

Cloud Connector Configuration for connection from On-Premise to Cloud

I create a new HANA instance jerrydemo for this scenario. Log on Cloud Connector, click tab “On-Premise to Cloud”, create a new Service Channel：

Assign the new created HANA instance jerrydemo to this Channel:

Write down the Channel port number 32215.

Change the Java server setting in On-Premise system to point to HANA instance in SCP instead

No code change is needed for Java application but just server setting is necessary to change. Make changes in file connection.properties:

The idea is to point database connection of this Server to the Service Channel just created in Cloud Connector, so that the HANA instance in SCP behind Cloud Connector could be reached.

◈ javax.persistence.jdbc.url

the url localhost:32215 points to the Service Channel in Cloud Connector. The HANA instance jerrydemo is assigned to this channel. The url fragment “currentschema=SYSTEM” means the database table created by JDBC will be stored under SYSTEM schema.

◈ javax.persistenc.jdbc.user / password

The user and password for database instance jerrydemo.

So far all configuration are done.

Launch localhost Web server in On-Premise system, insert two records:

Open SAP Cloud Platform HANA Development Tool, check from SYSTEM schema and could observe the two records inserted by the Web application running in On-Premise system.