Connect to Oracle Autonomous database from jdbc using 19c syntax with wallet

Connecting to an Oracle Autonomous Database using a wallet and JDBC drivers is a secure and efficient way to access data stored in the cloud. This guide will walk you through the necessary steps to establish a connection between your Java application and an Oracle Autonomous Database instance.

Step 1: Download and Install Oracle JDBC Drivers

The first step is to download the Oracle JDBC drivers for your operating system. You can download the latest version of the Oracle JDBC driver from the Oracle website.

After downloading the driver, extract the zip file to a directory of your choice.

Step 2: Create an Autonomous Database Instance and Download the Wallet

To create an Autonomous Database instance, log in to your Oracle Cloud Infrastructure Console and navigate to the Autonomous Transaction Processing page.

Once you have created an Autonomous Database instance, you need to download the database wallet. The wallet contains the security credentials necessary to establish a secure connection between your application and the database.

To download the wallet, click the Download button in the Wallet section of the Autonomous Database Details page. Select the appropriate wallet type for your operating system.

Step 3: Extract the Wallet

After downloading the wallet, extract the contents to a directory of your choice. The contents of the wallet should include several files, including a cwallet.sso file, a ewallet.p12 file, a tnsnames.ora and a sqlnet.ora file.

Step 4: Configure Your Environment

Before you can establish a connection to the Autonomous Database, you need to set up your environment. This involves configuring the sqlnet.ora file to specify the directory where the wallet file is extracted

The sqlnet.ora file should be modified to contain the location where the wallet is located. The default "~/network/admin" needs to be replaced

sqlnet.ora
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="/root/Downloads/wallet"))) 

Replace <wallet-directory> with the path to the directory containing the extracted wallet files.

Step 5: Configure Your Java Application

To establish a connection to the Autonomous Database from your Java application, you need to configure the JDBC driver to use the wallet.

To be absolutely sure, you can put the oraclepki.jar, ordt_core.jar, osdt_cert.jar and ojdbc8.jar in the java CLASSPATH

export CLASSPATH=/ojdbc8-full/oraclepki.jar:/ojdbc8-full/osdt_cert.jar:/ojdbc8-full/osdt_core.jar:/ojdbc8-full/ojdbc8.jar:$CLASSPATH

Here is an example of how to establish a connection to the Autonomous Database using the JDBC driver and the wallet:

import java.security.Security;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
import java.sql.ResultSet;
import java.sql.DatabaseMetaData;
public class expressTest2 {
  public static void main(String[] args) throws SQLException{
    Properties props = new Properties();
    props.setProperty("user", "admin");
    props.setProperty("password", "Saturday_123");
    props.setProperty("oracle.net.ssl_version", "1.2");
    //props.setProperty("oracle.net.wallet_location", "file:/u04/ssh_express/cwallet.sso");
    try {
        Class.forName("oracle.jdbc.driver.OracleDriver");
    } catch (ClassNotFoundException e) {
    //     TODO Auto-generated catch block
               e.printStackTrace();
                     }
                         Security.addProvider(new oracle.security.pki.OraclePKIProvider());
                            // This url syntax is from ojdbc8.jar from Oracle 18c and above.
                            String url3 = "jdbc:oracle:thin:@east_low?TNS_ADMIN=/root/Downloads/wallet";
                            Connection con = DriverManager.getConnection(url3, props);
                        DatabaseMetaData dmeta = con.getMetaData();
                        ResultSet rs = dmeta.getSchemas("%", "%");
                        while (rs.next()) {
                                System.out.println("SCHEMA = " + rs.getString(1));
                        }

          }
       }

Replace <host> with the hostname of your Autonomous Database instance, and <service_name> with the name of your database service.

Step 6: Compile and run

Eg. javac expressTest2.java

java expressTest2

Conclusion

By following these steps, you can establish a secure and efficient connection to an Oracle Autonomous Database instance using a wallet and JDBC drivers. This method provides a high level of security and reliability, allowing you to access your data in the cloud with confidence.