How to connect to Oracle Cloud DDBB using JDBC Thin Driver and Oracle Wallet

Question

I am following the guide to connect from a java application using the IDE IntelliJ to an Oracle Cloud Database.

I meet the prerequisites since:

• I have a Database in Oracle Cloud service
• I downloaded the wallet and I placed the files in the src directory of my workspace.
• I am using last JDK 14
• I am using the ojdbc8.jar
• And I downloaded as well the oraclepki, osdt_cert, and osdt_core jars, all of them added as java libraries in my test project

The recommended BD_URL is never working. I always get: java.net.UnknownHostException and oracle.net.ns.NetException: The Network Adapter could not establish the connection

DB_URL= "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST=testgerard_high)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=testgerard_high)))"

Then I found in Oracle support that it could be added to the wallet directory, but the same issue.

DB_URL= "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST=testgerard_high)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=testgerard_high))(SECURITY = (MY_WALLET_DIRECTORY = src\\Wallet_testGerard)))"

If I switch to a connection string using 18.3 JDBC driver which should work for my settings, then I get the error: Invalid connection string format, a valid format is: "host:port:sid"

DB_URL="jdbc:oracle:thin:@testgerard_high?TNS_ADMIN=src\\Wallet_testGerard";

Finally, I have seen here a way to inform the wallet folder out of the BD_URL so I do not get the invalid format exception:

System.setProperty("oracle.net.tns_admin","src\\Wallet_testGerard");

Now it is trying to connect but it fails after 60 seconds with the exception:sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

I adapted an oracle example, here is my code:

import java.sql.SQLException;
import java.sql.DatabaseMetaData; 
import oracle.jdbc.pool.OracleDataSource;
import oracle.jdbc.OracleConnection;


public class OracleDataSourceSample
{
    final static String DB_URL="jdbc:oracle:thin:@testgerard_high";
    //final static String DB_URL="jdbc:oracle:thin:@testgerard_high?TNS_ADMIN=src\\Wallet_testGerard";
    //final static String DB_URL= "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(HOST=testgerard_high)(PORT=1521)(PROTOCOL=tcp))(CONNECT_DATA=(SERVICE_NAME=testgerard_high))(SECURITY = (MY_WALLET_DIRECTORY = src\\Wallet_testGerard)))";

    final static String DB_USER = "hr";
    final static String DB_PASSWORD = "hr";

    public static void main (String args[]) throws SQLException, ClassNotFoundException {

        System.setProperty("oracle.net.tns_admin","src\\Wallet_testGerard");
        Class.forName("oracle.jdbc.driver.OracleDriver");
        OracleDataSource ods = new OracleDataSource();
        ods.setURL(DB_URL);
        ods.setUser(DB_USER);
        ods.setPassword(DB_PASSWORD);

        // With AutoCloseable, the connection is closed automatically.
        try (OracleConnection connection = (OracleConnection)
                ods.getConnection()) {
            // Get the JDBC driver name and version
            DatabaseMetaData dbmd = connection.getMetaData();
            System.out.println("Driver Name: " + dbmd.getDriverName());
            System.out.println("Driver Version: " +
                    dbmd.getDriverVersion());
            System.out.println("Database Username is: " +
                    connection.getUserName());
        }
    }
}

Answer 1

Tested with stand alone jdk 8 and jdk 14 as well as Intellij Community edition(first run firewall blocked intellij).

    import java.io.IOException;
    import java.io.InputStream;
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.Properties;
    import oracle.jdbc.pool.OracleDataSource;
    import oracle.jdbc.OracleConnection;
    import java.sql.DatabaseMetaData;
    
    
    public class SalesConnection
    {
        final static String DB_URL="jdbc:oracle:thin:@oci_adw_high";
        final static String DB_USER = "xxxx";
        final static String DB_PASSWORD = "xxxxx";
    
        public static void main (String args[]) throws SQLException, ClassNotFoundException {
    
    
             System.setProperty("oracle.net.tns_admin","C:\\app\\oracle\\product\\19\\dbhome_1\\network\\admin");
             System.setProperty("oracle.jdbc.fanEnabled","false");
    
            Class.forName("oracle.jdbc.driver.OracleDriver");
            OracleDataSource ods = new OracleDataSource();
            ods.setURL(DB_URL);
            ods.setUser(DB_USER);
            ods.setPassword(DB_PASSWORD);
    
            // With AutoCloseable, the connection is closed automatically.
            try (OracleConnection connection = (OracleConnection)
                    ods.getConnection()) {
                // Get the JDBC driver name and version
                DatabaseMetaData dbmd = connection.getMetaData();
                System.out.println("Driver Name: " + dbmd.getDriverName());
                System.out.println("Driver Version: " +
                        dbmd.getDriverVersion());
                System.out.println("Database Username is: " +
                        connection.getUserName());
                printSales(connection);
            }
        }
    
        public static void printSales(Connection connection) throws SQLException {
            // Statement and ResultSet are AutoCloseable and closed automatically.
            try (Statement statement = connection.createStatement()) {
              try (ResultSet resultSet = statement
                  .executeQuery("select /* Java Console */PROD_ID, CUST_ID from sales fetch first 10 rows only ")) {
                System.out.println("PROD_ID" + "  " + "CUST_ID");
                System.out.println("---------------------");
                while (resultSet.next())
                  System.out.println(resultSet.getString(1) + " "
                      + resultSet.getString(2) + " ");
              }
            }
          }
}

Try to run this code change table and column names

Source of this code

Edit: Compiling and executing from command prompt

javac -cp "C:\ojdbc8-full\*;" SalesConnection.java

java -cp "C:\ojdbc8-full\*;" SalesConnection

Answer 2

ojdbc8-full.zip contains oraclepki.jar, osdt_core.jar and osdt_cert.jar which are required for connecting to Oracle autonomous database. However, you can get JDBC driver along with other additional libraries using these maven co-ordinates.

<dependencies>
  <dependency>
    <groupid>com.oracle.database.jdbc</groupid>
    <artifactid>ojdbc8-production</artifactid>
    <version>19.7.0.0</version>
    <type>pom</type>
  </dependency>
</dependencies>

Refer to the Maven Central Guide for more information