How to Avoid Nightmares with JDBC & Oracle Autonomous Database Connection

How It Started?

Recently, I received a request to showcase how easy a Java Application can connect to Oracle Autonomous Database (ADB) via Java Database Connectivity (JDBC). Even though I'm not a seasoned Java developer, my first thought was, "How hard is it going to be?" I got my Java Integrated Development Environment (IDE) with Java Runtime Environment (JRE) 1.8. I am fueled with high enthusiasm and hit the execute. Here is the terminal result:

I got an error and I started to panic. I tried to debug, followed the steps in Oracle Help Center and got some pointers from my buddies. Finally, I managed to make it work after a few trial-and-error attempts. It was not a straightforward experience. Therefore, I decided to share my experience in 3 easy steps and hopefully it will helps you avoid the nightmare.

3 Mindful Easy Steps

Get the right JDBC Driver and Companion JARs

JAR stands for Java ARchive. When you want to perform a JDBC connection with ADB and Java application, you need additional JARs. Some of these JARs are for private keys, certification and the JDBC connection string. This is to ensure proper handshake between the ADB and your Java application as ADB has encryption in transit feature. Data will be encrypted before transmission and will be decrypted and verified when arrived with proper authentication. You can download the required Oracle JDBC driver and companion JARs at Oracle Database JDBC driver and Companion Jar Downloads.

• Remember to select full. It is bundled with other JARs which you needed for the connection
• You can choose either JDK11 and JDK17 or JDK8 and JDK11

Add those JARs in your IDE

• Decompress the file or untar the tar.gz file. You can use Unix command (e.g. ?tar -xvf ojdbc11-full.tar.gz) or you can use file achieve utility like 7-zip for Windows platform

• Once you have decompressed the tar.gz file you will have a folder containing multiple JARs like it is shown here on the right

• Now, you can add those JARs as your External JAR in your IDE

• In the animation, my IDE is Eclipse. I added the JAR reference by referring to the ojdbc11.jar which is located in my decompressed folder. You will notice that once you have added ojdbc11.jar, 3 files will be added as well (osdt_core.jar, odst_cert.jar, oraclepki.jar). Those files are for handshake between the ADB and your Java application as elaborated in section above

ADB Wallet & JDBC String Configuration

If you have experience using ADB, you might be familiar with this step on how to locate and download ADB wallet. Just to ensure we have it all covered, let me cover it quickly.

1) Locate your ADB wallet

• My ADB name is called "nasilemak". As shown in animation above, you can download your ADB wallet by click on "DB Connection", then click on "Download wallet". Provide a password and you will be able to download to your local drive
• The wallet is in zip file format. Unzip it and we will need this folder location for JDBC string configuration later

2) Configure the JDBC Connection String with ADB, the format as follow:

jdbc:oracle:thin:@[Service Name]?TNS_ADMIN=[ADB Wallet Path]

There are 2 parameters you need to specify:

Service Name: In your decompressed ADB wallet folder, locate a file named "tnsnames.ora". If your ADB is data warehouse you will find 3 types: high, medium, low. If your ADB is Transaction Processing or JSON you will find 5 types: tpurgent, tp, high, medium, low. Different types of service allow you to choose and select the optimize connection base on resources, concurrency and parallel queries run. You can find more information about this in Database Service Names for Autonomous Database Warehouse or Database Service Names for Autonomous Transaction Processing and Autonomous JSON Database. For now, you can choose either one.

Below is one of tnsnames.ora example based on ADB Data warehouse. Service Name can be either nasilemak_high, nasilemak_low or nasilemak_medium.

ADB Wallet Path: The decompressed ADB Wallet folder

Once you have that 2 parameters, you can construct the string and it will looks like this:

jdbc:oracle:thin:@nasilemak_high?TNS_ADMIN=C:/Users/kualim/eclipse-workspace/JDBCExample/bin/Wallet_nasilemak

Now you have the JDBC string constructed, you can now place in your Java code together with your username and password parameters and hit execute. You should be able to connect to ADB.

You can refer to the sample Java code below by creating a new Java project in your IDE and replace with your parameters:

• [%Service Name%]: Service Name from tnsnames.ora file
• [%Walle Folder Path%]: The decompressed ADB Wallet folder
• [%User Name %]: Your ADB user name
• [%Password%]: Your ADB password

import java.sql.*


public class JDBCExample {


? ? public static void main(String[] args) {
? ? ??
? ? ? ? try (Connection conn = DriverManager.getConnection(
? ? ? ? ? ? ? ? "jdbc:oracle:thin:@[%Service Name%]?TNS_ADMIN=[%Wallet Folder Path%]","[%User Name%]","[%Password%]")) {


? ? ? ? ? ? if (conn != null) {
? ? ? ? ? ? ? ? System.out.println("Hooray!!! I'm connected to the database!");
? ? ? ? ? ? } else {
? ? ? ? ? ? ? ? System.out.println("Boo Hoo!!.I failed to connect!");
? ? ? ? ? ? }
? ? ? ? ??
? ? ? ? ? ? conn.close();
? ? ? ? } catch (SQLException e) {
? ? ? ? ? ? System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
? ? ? ? } catch (Exception e) {
? ? ? ? ? ? e.printStackTrace();
? ? ? ? }


? ? }
}        

Key Takeaways

• Get the right JDBC Driver and Companion JARs, remember to always select the FULL version
• 3 additional files (osdt_core.jar, odst_cert.jar, oraclepki.jar) will be added along when add external reference
• JDBC string will need to point where the ADB Wallet folder is located

I hope this article might help you breeze through some steps on how to setup your Java application JDBC with ADB without having nightmares. Happy coding!!