Connect Java With Hana Using Jdbc to Read Some Data | Eclipse ...

| HOW-TO PAPER

[ SAP HANA Tutorial ] mayato How-To Papers in the Area of Business Intelligence Title:

Connect JAVA with HANA using JDBC to read some data

Author(s):

Thorsten Füg

Topic:

SAP HANA

Published:

December 2012

Overview: Connection JAVA with HANA using JDBC to read some data This tutorial describes how to write a simple JAVA code using to read some data stored on SAP HANA. By the end of this tutorial, you will have developed a simple JAVA program, that reads the data stored in SAP HANA over a JDBC connection. Also you will create a simple UNION scenario in SAP HANA using the graphic editor of a calculation view. Prerequisites  You have access to an SAP HANA system.  You have SAP HANA Studio installed on your machine.  You have SAP HANA Clients installed on your machine.  You have installed Eclipse or another JAVA development workbench on your machine.  You have installed JAVA SDK or JDK on your machine to run Eclipse.

Topics In this tutorial you will learn how to: 1. Develop some simple JAVA code in Eclipse 2. Start a connection to SAP HANA using the JDBC driver 3. Build a simple UNION Scenario with SAP HANA graphical editor of calculation view The estimated completion time for this tutorial is 20 minutes. Using a simple JAVA code, you will learn the basic steps about how to connect SAP HANA from an external programming language such as JAVA over a JDBC driver.

Build some data in SAP HANA In the first step, you must create two tables in SAP HANA Studio with an identical structure and fill them with data: SQL code for table TEILNEHMER_A create column table ""."TEILNEHMER_A"( "TEILNEHMERNUMMER" INTEGER null, "VORNAME" VARCHAR (20) null, "NACHNAME" VARCHAR (20) null, "GEBURTSDATUM" DATE null, "FIRMA" VARCHAR (20) null); insert into ""."TEILNEHMER_A" values(1,'Max','Mustermann','01.01.1980','mayato GmbH'); insert into ""."TEILNEHMER_A" values(2,'Susi','Bernsen','12.08.1955','mayato GmbH'); insert into ""."TEILNEHMER_A" values(3,'Hans','Wurst','29.12.1978','mayato GmbH'); insert into ""."TEILNEHMER_A" values(4,'Kirsten','Balder','02.02.1982','mayato GmbH'); SQL code for table TEILNEHMER_B create column table ""."TEILNEHMER_B"( "TEILNEHMERNUMMER" INTEGER null, "VORNAME" VARCHAR (20) null, "NACHNAME" VARCHAR (20) null, "GEBURTSDATUM" DATE null, "FIRMA" VARCHAR (20) null); insert into ""."TEILNEHMER_B" values(5,'Karl','Karlsen','01.01.1991','SAP AG'); insert into ""."TEILNEHMER_B" values(6,'Martina','Hansen','17.06.1956','SAP AG'); insert into ""."TEILNEHMER_B" values(7,'Bernd','Bauer','19.11.1977','SAP AG'); insert into ""."TEILNEHMER_B" values(8,'Silke','Weinmann','01.04.1955','SAP AG');  Please replace with the name of your schema in the SQL code.

2

You can find the tables also in the schema TUTORIARLS.

Create a calculation view by using the internal graphical editor Right-click your package and select “Calculation View”.

After that, enter a name and a description and choose “Graphical” as the view type. Then press “Next”.

3

On the next screen, select the two tables you created in the previous section and press “Finish”.

After the window is displayed, choose UNION from the Tools Palette and connect the two tables, the union, and the output block using arrows.

4

Next, click the union block once and pull the attributes of the first table from the source box on the left into the target box using drag and drop. Afterwards, click the “Auto Map by Name” button. Then, you will see the image below.

In the next step, click on the output block and select for all attributes of the output table “Add as Attribute”.

5

Finally, save and activate your calculation view. Please note: If you get any errors in the activation about MDX and that the user is not authorized execute the following command for your schema: grant select on schema to _SYS_REPO with grant option; In this view, we are trying to “Union” the two tables “TEILNEHMER_A” and “TEILNEHMER_B” as you can see above. Now we have to launch eclipse IDE to write our java program.

6

Download Eclipse and JAVA You can download Eclipse from: http://www.eclipse.org/downloads/. Choose the installation file named Eclipse IDE for Java EE Developers. After that, you must also install JAVA: http://www.java.com/de/download/manual.jsp Important: Ensure that you install the same bit version as you choose for Eclipse. After you have downloaded both applications, install JAVA first. Afterwards extract the Eclipse folder. You can run Eclipse directly without any installation.

Develop some simple JAVA code in Eclipse If you are running Eclipse, right-click on the package explorer frame to create a new Java project. Choose a name and press “Finish”.

After that, right-click on your project folder and choose “Build Path” -> “Configure Build Path”. Then add the external JAR named ngdbc.jar located in the SAP HANA client folder on your system.

7

Afterwards, create a new class and mark the option “public static void main”.

Now you are ready to write the following code:

8

JAVA coding: Connection import java.sql.*; import java.text.SimpleDateFormat; public class Connection { public static void main(String args[]) { try { Class.forName("com.sap.db.jdbc.Driver"); java.sql.Connection conn= java.sql.DriverManager.getConnection("jdbc:sap://:30015","",""); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT * FROM \"_SYS_BIC\".\"tutorials.java/CALC_GRAPHIC\"" ); SimpleDateFormat sd = new SimpleDateFormat("dd.MM.yyyy"); while( rs.next() ) { System.out.print( rs.getString(1) + " | "); System.out.print( rs.getString(2) + " "); System.out.print( rs.getString(3) + " | "); System.out.print( sd.format(rs.getTimestamp(4)) + " | "); System.out.println( rs.getString(5) ); } rs.close() ; stmt.close() ; conn.close() ; } catch(Exception e) {

}

}

}

System.out.println(e);

Please note: replace and with your information! With ngdbc.jar of the HANA Client and the code above, you can connect to HANA: Class.forName("com.sap.db.jdbc.Driver"); java.sql.Connection conn = java.sql.DriverManager.getConnection("jdbc:sap://HANA:PORT", "username", "password");

9

Then we send a SQL statement to our calculation view located in the schema _SYS_BIC. With the SimpleDateFormat object, we can format the date stored in SAP HANA for output. We get a result set named “rs” from our SQL statement. Now you can read it out via loop. After you have execute the using the button

you will see the result on the console frame at the bottom.

References Part of this tutorial is based on the following source: http://scn.sap.com/docs/DOC-27057

10