Skip to Content

Connect Using the SAP HANA JDBC Driver

test
0 %
Connect Using the SAP HANA JDBC Driver
Details

Connect Using the SAP HANA JDBC Driver

September 9, 2021
Created by
March 29, 2020
Create and debug a Java application that connects to SAP HANA using the SAP HANA client.

You will learn

  • How to install Java
  • How to create and debug a Java application that queries a SAP HANA database
  • How to connect to SAP HANA in DBeaver using the SAP HANA JDBC driver
QR code

Prerequisites

  • You have completed the first 3 tutorials in this mission.

Java Database Connectivity (JDBC) provides an API for accessing databases from Java. An application written to the JDBC standard can be ported to other databases. Database vendors provide JDBC drivers for their database products.


Step 1: Install a JDK

Ensure that you have a Java Development Kit (JDK) installed and make sure it is accessible from your path. Details on which Java versions are supported can be found in SAP note 3006307 - SAP HANA Client Supported Platforms for 2.7 and Oracle Java SE Support Roadmap.

A few options include:

To verify that the JDK is correctly set up, run the following:

java -version
javac -version

If these commands fail, ensure that the folder they are located in, is included in your path.

Log on to answer question
Step 2: The SAP HANA JDBC driver

The SAP HANA driver for JDBC is a Multi-Release JAR file and as such supports multiple versions of Java. It is available in the client installation folder at C:\SAP\hdbclient\ngdbc.jar and in the maven repository.

maven
  1. Run the following command for version information. If needed, adjust the path to match the installation location on your machine.

    java -jar C:\SAP\hdbclient\ngdbc.jar -v
    
    java -jar ~/sap/hdbclient/ngdbc.jar -v
    
  2. Run the command again without the -v to open the configuration window, which provides driver information and the ability to set trace information:

    JDBC-Driver-Trace-Config

    The JDBC driver has a different version number than the rest of the SAP HANA interfaces.

    The trace options are further described at JDBC Tracing and Trace Options.

  3. Run the following and use either connection details stored in the user store or specify the connection details.

    java -jar C:\SAP\hdbclient\ngdbc.jar -k USER1UserKey -o encrypt=True -o validatecertificate=false -c "SELECT  * FROM HOTEL.CUSTOMER"
    
    or
    
    java -jar C:\SAP\hdbclient\ngdbc.jar -u USER1,Password1 -n your_host:your_port -o encrypt=True -o validatecertificate=false -c "SELECT  * FROM HOTEL.CUSTOMER"
    
    java -jar ~/sap/hdbclient/ngdbc.jar -u USER1,Password1 -n your_host:your_port -o encrypt=True -o validatecertificate=false -c "SELECT  * FROM HOTEL.CUSTOMER"
    
    Link text e.g., Destination screen

See JDBC Command-Line Connection Options for additional details on parameters of ngdbc.jar.

Log on to answer question
Step 3: Create a Java application that queries SAP HANA
  1. The following commands create a folder named java, enter the newly created directory, create a file named JavaQuery.java, and open the file in notepad.

    mkdir %HOMEPATH%\HANAClientsTutorial\java
    cd %HOMEPATH%\HANAClientsTutorial\java
    notepad JavaQuery.java
    

    Substitute pico below for your preferred text editor.

    mkdir -p $HOME/HANAClientsTutorial/java
    cd $HOME/HANAClientsTutorial/java
    pico JavaQuery.java
    
  2. Copy the following code into JavaQuery.java:

    import java.sql.*;
    import com.sap.db.jdbc.Driver;
    public class JavaQuery {
        public static void main(String[] argv) {
            System.out.println("Java version: " + com.sap.db.jdbc.Driver.getJavaVersion());
            System.out.println("Minimum supported Java version and SAP driver version number: " + com.sap.db.jdbc.Driver.getVersionInfo());
            Connection connection = null;
            try {  
                connection = DriverManager.getConnection(  
                    //Option 1, retrieve the connection parameters from the hdbuserstore
                    //The below URL gets the host, port and credentials from the hdbuserstore.
                    "jdbc:sap://dummy_host:0/?KEY=USER1UserKey&encrypt=true&validateCertificate=false");
    
                    //Option2, specify the connection parameters
                    //"jdbc:sap://10.11.123.134:39015/?encrypt=true&validateCertificate=false", "User1", "Password1");
    
                    //As of SAP HANA Client 2.6, connections on port 443 enable encryption by default
                    //validateCertificate should be set to false when connecting
                    //to an SAP HANA, express edition instance that uses a self-signed certificate.
    
                    //As of SAP HANA Client 2.7, it is possible to direct trace info to stdout or stderr
                    //"jdbc:sap://10.11.123.134:39015/?encrypt=true&validateCertificate=false&traceFile=stdout&traceOptions=CONNECTIONS", "User1", "Password1");
    
            }
            catch (SQLException e) {
                System.err.println("Connection Failed:");
                System.err.println(e);
                return;
            }
            if (connection != null) {
                try {
                    System.out.println("Connection to HANA successful!");
                    Statement stmt = connection.createStatement();
                    ResultSet resultSet = stmt.executeQuery("SELECT TITLE, FIRSTNAME, NAME from HOTEL.CUSTOMER;");
                    while (resultSet.next()) {
                        String title = resultSet.getString(1);
                        String firstName = resultSet.getString(2);
                        String lastName = resultSet.getString(3);
                        System.out.println(title + " " + firstName + " " + lastName);
                    }
                }
                catch (SQLException e) {
                    System.err.println("Query failed!");
                }
            }
        }
    }
    
  3. Compile the .java file into a .class file using the following command:

    javac -cp C:\SAP\hdbclient\ngdbc.jar;. JavaQuery.java
    
    javac -cp ~/sap/hdbclient/ngdbc.jar:. JavaQuery.java
    
  4. Run JavaQuery.class and indicate where the SAP HANA JDBC driver is located. Note that the host, port, UID and PWD will be retrieved from the hdbuserstore.

    java -classpath C:\SAP\hdbclient\ngdbc.jar;. JavaQuery
    
    java -classpath ~/sap/hdbclient/ngdbc.jar:. JavaQuery
    
    Java Query

See JDBC Connection Options in Java Code for additional details on the getConnection method of the DriverManager.

See Connect to SAP HANA Cloud via JDBC for additional details on the certificate used during the connection.

Log on to answer question
Step 4: Debug the application

Visual Studio Code provides plugins for Java and can be used to debug an application.

  1. If you have not already done so, download Visual Studio Code.

  2. If you have not already done so, in Visual Studio Code, choose File | Open Folder, and then select the HANAClientsTutorial folder.

    Workspace
  3. Open the file JavaQuery.java, and if asked, install the recommended extensions.

    Java extensions
  4. Once the Java Extension Pack has been installed, expand the Java Project Explorer and click on the + icon to add the JDBC driver as a referenced library.

    referenced libraries

    The JDBC driver is located at C:\SAP\hdbclient\ngdbc.jar.

  5. Place a breakpoint and then select Run | Start Debugging.

    Notice that the debug view becomes active.

    Notice that the program stops running at the breakpoint that was set. Step through the code by pressing F10 and observe the variable values in the variables pane.

    VS Code Debugging
Log on to answer question
Step 5: Browse SAP HANA using DBeaver

DBeaver is a free and open source database tool and can be used with the SAP HANA JDBC driver.

The following steps demonstrate how to configure DBeaver to connect to SAP HANA Cloud or SAP HANA, express edition using the JDBC driver.

  1. Download and install the community edition of DBeaver.

    Install DBeaver
  2. Create a new SAP HANA database connection.

    New Connection

    Specify the connection type and fill in the host and port.

    Connection Settings

    Click on Connection details to specify the connection name.

    Connection Details

    Click on Edit Driver Settings and choose to download the latest HANA JDBC driver.

    Driver Settings
  3. After finishing the wizard, the catalog of the database can be viewed and SQL statements can be executed.

    Query

    DBeaver can also be used to create an entity relationship (ER) diagram, perform a comparison of two selected objects, execute import and export operations, view spatial data on a map, and perform data analysis with its grouping and calc panels.

Congratulations! You have now created and debugged a Java application that connects to and queries an SAP HANA database and used the JDBC driver in a third party tool.

Which of the following statements are true?
×

Next Steps

Back to top