Connecting to an Autonomous Database using JDBC Thin client

I have discussed in my earlier posts about establishing SQL * Plus and using the SQLcl tool details. Today's blog post will take you through JDBC Thin client configuration details to be able to successfully connect with an autonomous database service. Will primarily focus on mandatory JDK, JDBC versions and client connect credentials requirement details. Let's get started.

To begin with, any Java application that uses JDBC Thin driver requires an Oracle wallet or JavaKeyStore(JKS). Luckily, these files are readily available on OCI console for download. Refer my earlier blog post to learn about downloading client connect credentials files and JavakeyStore files.

Prerequisites for JDBC Thin Driver

Below is the list of perquisites for JDBC Thin client connection:

• Download Connect Credentials as explained in the previous post
• Validate JDK version for security, if you are on less than JDK8u162
• Check JDBC driver version
• JDBC driver 18.3 or higher simplifies security credentials wallet connection configuration.
• Specify the wallet directory in the JDBC connect string.
• Include the wallet directory location in the JDBC url if no TNS_ADMIN environmental variable is set.

Connection string url

Part of client connect credentials downloads, you will have tnsnames.ora files which contains tns service names and connection strings. Below is an example of high level connect string entry:

dbname_high= (description=
      (address=(protocol=tcps)(port=1522)(host=adw.example.oraclecloud.com))(connect_data=(service_name=adw_jdbctest_high.oraclecloud.com))(security=(ssl_server_cert_dn="CN=adw.oraclecloud.com,OU=Oracle
      US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")))

JDBC 18.3 Thin Driver connection string example, for Linux platform:

DB_URL="jdbc:oracle:thin:@dbname_high?TNS_ADMIN=/Users/test/wallet_dbname"

Using JavaKeyStore (JKS)

Below example shows the JDBC Thin driver connectivity using JKS to an ADB:

DB_URL="jdbc:oracle:thin:@dbname_high?TNS_ADMIN=/Users/test/wallet_dbname"

Subsequently, add the JKS related connection properties to ojdbc.properties file. To use SSL connectivity over Oracle Wallet, below properties should be set to ojdbc.properties files:

# Properties for using Java KeyStore (JKS)
oracle.net.ssl_server_dn_match=true
javax.net.ssl.trustStore==${TNS_ADMIN}/truststore.jks
javax.net.ssl.trustStorePassword=password
javax.net.ssl.keyStore==${TNS_ADMIN}/keystore.jks
javax.net.ssl.keyStorePassword=password

Once the properties are set, compile and run using the below example:

java –classpath ./lib/ojdbc8.jar:./lib/ucp.jar UCPSample

To go through your firewall with HTTP proxy requirement to connect to an internet, use JDBC Thin client 18.1 or higher. So, add the following to your tnsnames.ora file to go through HTTP proxy:

ADWC1_high =
       (description=
             (address=
                   (https_proxy=proxyhostname)(https_proxy_port=80)(protocol=tcps)(port=1522)(host=adw.example.oraclecloud.com)
             )
             (connect_data=(service_name=adwc1_high.adw.oraclecloud.com)
             )
             (security=(ssl_server_cert_dn="adw.example.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")
             )
       )

In nutshell, we have walk through over JDBC thin driver client connection to an ADB requirements and prerequisites.