Skip to Content

Connect to Data Lake Relational Engine Using the JDBC Driver

Create and debug a Java application that connects to data lake Relational Engine.
You will learn
  • How to create and debug a Java application that connects to and queries a data lake Relational Engine database
  • How to connect to a data lake Relational Engine in DBeaver
danielvaDan van LeeuwenApril 18, 2022
Created by
danielva
May 13, 2021
Contributors
danielva

Prerequisites

  • You have completed the first tutorial in this group.

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

    Ensure that you have a Java Development Kit (JDK) installed and make sure it is accessible from your path. Details of the driver and supported versions can be found at JDBC Drivers and Oracle Java SE Support Roadmap.

    A few options include:

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

    Shell
    Copy
    java -version
    javac -version
    

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

    The following command will install Java on openSUSE Leap 15.2.

    Shell (Linux)
    Copy
    sudo zypper install java-11-openjdk-devel
    
  • Step 2

    The SAP IQ JDBC driver is a type 2 driver, which means it has a native (non-Java) component. For additional details see Type 2 driver – Native-API driver. The driver is located in %IQDIR17%\Java\sajdbc4.jar on Microsoft Windows and $IQDIR17/java/sajdbc4.jar on Linux. The native component is at %IQDIR17%\Bin64\dbjdbc17.dll on Microsoft Windows and $IQDIR17\lib64\libdbjdbc17.so on Linux.

    A native JDBC driver called jConnect is also provided. This tutorial focuses on the SAP IQ JDBC driver.

    See JDBC Drivers for additional details.

  • Step 3
    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.

      Shell (Microsoft Windows)
      Copy
      mkdir %HOMEPATH%\DataLakeClientsTutorial\java
      cd %HOMEPATH%\DataLakeClientsTutorial\java
      notepad JavaQuery.java
      
      Shell (Linux)
      Copy
      mkdir -p ~/DataLakeClientsTutorial/java
      cd ~/DataLakeClientsTutorial/java
      pico JavaQuery.java
      
    2. Copy the following code into JavaQuery.java:

      Java
      Copy
      import java.sql.*;
      public class JavaQuery {
          public static void main(String[] argv) {
              Connection connection = null;
              try {
                  //Option 1
                  connection = DriverManager.getConnection("jdbc:sqlanywhere:uid=USER1;pwd=Password1;Host=XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXX.iq.hdl.trial-XXXX.hanacloud.ondemand.com:443;ENC='TLS{tls_type=rsa;direct=yes}'");
      
                  //Option 2, the connection properties can be loaded from an ODBC datasource.
                  //connection = DriverManager.getConnection("jdbc:sqlanywhere:DSN=HC_HDL_Trial;LOG=myLog.log");  
                  //The LOG option is helpful when diagnosing connection issues.
              }
              catch (SQLException e) {
                  System.err.println("Connection Failed:");
                  System.err.println(e);
                  return;
              }
              if (connection != null) {
                  try {
                      System.out.println("Connection to data lake Relational Engine successful!");
                      Statement stmt = connection.createStatement();
                      ResultSet resultSet = stmt.executeQuery("SELECT TITLE, FIRSTNAME, NAME FROM 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. Update the host value in the connection string.

    4. Compile the .java file into a .class file using the following command:

      Shell (Microsoft Windows)
      Copy
      javac -cp %IQDIR17%\Java\sajdbc4.jar;. JavaQuery.java
      
      Shell (Linux)
      Copy
      javac -cp $IQDIR17/java/sajdbc4.jar:. JavaQuery.java
      
    5. Run JavaQuery.class and indicate where the JDBC driver is located.

      Shell (Microsoft Windows)
      Copy
      java -classpath %IQDIR17%\Java\sajdbc4.jar;. JavaQuery
      
      Shell (Linux)
      Copy
      java -classpath $IQDIR17/java/sajdbc4.jar:. JavaQuery
      
      Java Query

    See JDBC Program Structure for additional details.

  • Step 4

    Visual Studio Code can run and debug a Java application. It is a lightweight but powerful source code editor available on Microsoft Windows, macOS, and Linux.

    1. If required, Download Visual Studio Code.

    2. In Visual Studio Code, choose File | Open Folder and then add the DataLakeClientsTutorial 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 %IQDIR17%\Java\sajdbc4.jar on Microsoft Windows and $IQDIR17/java/sajdbc4.jar on Linux.

    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
  • Step 5

    DBeaver is a free and open source database tool and can be used with the data lake Relational Engine JDBC driver.

    The following steps demonstrate how to configure DBeaver to connect to data lake Relational Engine.

    1. Download and install the community edition of DBeaver.

      Install DBeaver
    2. In DBeaver, select Database | Driver Manager | New to create a new driver configuration.

      New Driver
    3. Under the Libraries tab, specify Java and native components that make up the driver.

      Libraries

      Select sajdbc4.jar and choose Find Class and press OK to save the new driver definition.

      On Linux, if an error occurs indicating that the library cannot load, ensure that source IQ.sh has been added to your environment as described at Step 5: Install the data lake IQ client.

    4. Select Database | New Database Connection | All and select the newly created driver definition.

      Database connection
    5. Provide the JDBC URL and credentials and press the Test Connection button. An example of the URL is shown below.

      JDBC URL
      Copy
      jdbc:sqlanywhere:uid=HDLADMIN;pwd=MyPasword1;Host=XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXX.iq.hdl.trial-XXXX.hanacloud.ondemand.com:443;ENC='TLS{tls_type=rsa;direct=yes}'
      
      Database connection main tab
    6. A SQL Console can now be opened and queries can be executed.

      SQL
      Copy
      SELECT TITLE, FIRSTNAME, NAME FROM CUSTOMER;
      
      query in DBeaver

    Congratulations! You have now created and debugged a Java application that connects to and queries a data lake Relational Engine database and used the driver in DBeaver.

    Which of the following statements are true?

Back to top