Java Connectivity with Autonomous Database (ATP or ADW) using 19c and 18.3 JDBC

Oracle Autonomous Database is a family of products with each member of the family‚Ä® optimized by workload. Autonomous Data Warehouse (ADW) and Autonomous Transaction Processing (ATP) are the two products that have been released in 2018.

Java applications require Java Key Store (JKS) or Oracle wallets
to connect to ATP or ADW. These wallet files can be downloaded from the DB Connection tab. The enhancements in JDBC driver in DB 19c and 18.3 make Java connectivity to ATP or ADW very simple.

In 19c JDBC driver, Easy Connect Plus supports TCPS and connection to ATP or ADW is established easily by passing the JKS/wallet related connection properties in the connection string. In 18.3 JDBC driver, the property file ojdbc.properties which is part of the wallet zip file makes it easy to pass all the JKS and wallet related properties. Check out ojdbc.properties link
for its complete functionality. Follow the links below to establish a successful connection to ATP or ADW.

Prerequisites (Applicable for all of these)
Plain JDBC using Oracle Wallets                               
Tomcat  
Plain JDBC using JKS                                     
Trouble Shooting Tips                                     

Prerequisites

  • Step 1- Provision ATP: Sign in with your cloud credentials at cloud.oracle.com and create an ATP database by filling in few details. Refer to "Provisioning an Autonomous Transaction Processing" video for more details.

  • Step 2 - Download the Client Credentials: Click on "DB Connection" tab to get wallet_<dbname>.zip. Unzip it to a directory securely so that only authorized users have access to them. The contents of the zip file are described below.

      tnsnames.ora and sqlnet.ora: Network configuration files storing connect descriptors and SQL*Net client side configuration.

      cwallet.ora and ewallet.p12: Auto-open SSO wallet and PKCS12 file. PKCS12 file is protected by the wallet password provided in the UI.

      keystore.jks and truststore.jks: JKS Truststore and Keystore that is protected by the wallet passport provided while downloading the wallet.

      ojdbc.properties: Contains the wallet related connection property required for JDBC connection. This should be in the same path as tnsnames.ora.

  • Step 3 -  JDK version: If you are using JDK11, JDK10, or JDK9 then you don't need anything. If your JDK version is less than JDK8u162 then  then download the JCE Unlimited Strength Jurisdiction Policy Files. Refer to the README for installation notes.

  • Step 4 - JDBC Driver:
    - For 19.3 JDBC driver, download ojdbc10-full.tar.gz or ojdbc8-full.tar.gz and ucp.jar 
    - For 18.3 JDBC driver: download ojdbc8-full.tar.gz and ucp.jar
    You need oraclepki.jar, osdt_core.jar, and osdt_cert.jar for using Oracle wallets. These are also available on the respective download pages.
    Note: If you are using older JDBC driver 12.2.0.1 or 12.1.0.2 then follow the instructions on this page to connect.

 Plain JDBC using JKS files

  • Step 1: Make sure that prerequisites are met.

  • Step 2: Download DataSourceSample.java or UCPSample.java from JDBC code samples
    - Pass TNS alias (e.g.,
    dbname_medium found in tnsnames.ora) as part of the connection string.
    - Pass TNS_ADMIN property to the place where
    wallet_dbname.zip is unzipped. TNS_ADMIN specifies the path of the wallet files, ojdbc.properties, and tnsnames.ora
    Example: DB_URL="jdbc:oracle:thin:@dbname_medium?TNS_ADMIN=/users/test/wallet_dbname/"

  • Step 3: Add the JKS related connection properties to ojdbc.properties file. 
    # Use the following properties to use JKS, comment out the oracle.net.wallet_location property above
    # and set the correct password for both trustStorePassword and keyStorePassword.
    # It's the password provided while downloading the wallet credentials from the DB Connection tab

    oracle.net.ssl_server_dn_match=true
    javax.net.ssl.trustStore=${TNS_ADMIN}/truststore.jks
    javax.net.ssl.trustStorePassword=welcome1
    javax.net.ssl.keyStore=${TNS_ADMIN}/keystore.jks
    javax.net.ssl.keyStorePassword=welcome1


    Note: Make sure to comment the wallets related property in ojdbc.properties file
    # Connection property for Oracle Wallets
    # oracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=${TNS_ADMIN}))



    Note: With 19c JDBC driver, you can use Easy Connect Plus and pass all the connection properties as name-value pair in the connection string. You can open the tnsnames.ora and copy the hostname, servicename, and certificate details to form a connection string.

    Example:., jdbc:oracle:thin:@tcps://orclhostname:1522/orclservice?oracle.net.ssl_server_cert_dn=\"CN=adwc.uscom-east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US\"
    &javax.net.ssl.trustStore=/users/test/wallet_dbname/truststore.jks
    &javax.net.ssl.trustStorePassword=welcome1
    &javax.net.ssl.keyStore=/users/test/wallet_dbname/keystore.jks
    &javax.net.ssl.keyStorePassword=welcome1
    • Step 4: Compile and run the sample to get a successful connection

    • Sample Commands to Run
      [Linux]
      java -classpath ./lib/ojdbc8.jar:./lib/ucp.jar:. UCPSample

 Plain JDBC using Oracle Wallets

  • Step 1: Make sure that prerequisites are met.

  • Step 2: Download DataSourceSample.java or UCPSample.java from JDBC code samples
    - Pass TNS alias (e.g., dbname_medium found in tnsnames.ora) as part of the connection string.
    - Pass TNS_ADMIN property to the place where
    wallet_dbname.zip is unzipped. TNS_ADMIN specifies the path of the wallet files, ojdbc.properties, and tnsnames.ora
    Example: DB_URL="jdbc:oracle:thin:@dbname_medium?TNS_ADMIN=/users/test/wallet_dbname/"

  • Step 3: Wallet related property is pre-loaded in ojdbc.properties.
    oracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=${TNS_ADMIN}))


    Note: With 19c JDBC driver, you can use Easy Connect Plus and pass all the connection properties as name-value pair in the connection string. You can open the tnsnames.ora and copy the hostname, servicename, and certificate details to form a connection string etc.,

    Example:.,jdbc:oracle:thin:@tcps://orclhostname:1522/orclservice?wallet_location=/users/test/wallet_dbname
    &oracle.net.ssl_server_cert_dn=\"CN=adwc.uscom-east-1.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US\"

  • Step 4: Compile and run the sample to get a successful connection

  • Sample Commands to Run
    [Linux]
    java -classpath ./lib/ojdbc8.jar:./lib/ucp.jar:./lib/oraclepki.jar:./lib/osdt_core.jar:./lib/osdt_cert.jar:. UCPSample

Tomcat

  • Step 1: Make sure that prerequisites are met.

  • Step 2: Create a UCP Data source in context.xml. Refer to Tomcat_Servlet on Github for an example. Make sure to use the TNS alias as the service name and also TNS_ADMIN should point to the path where the client credentials are located. Sample context.xml is shown below.

  • Step 3: Place the 18.3 JDBC driver(ojdbc8.jar) and UCP(ucp.jar) at <TOMCAT_HOME>/webapps/UCPCloud/WEB-INF/lib. Also, If you are using Oracle Wallets then you also need oraclepki.jar, osdt_core.jar, and osdt_cert.jar in this location. 

  • Step 4: If you are using JKS then make sure to have JKS related property in ojdbc.properties file. Otherwise, you should have wallets related property.

  <Context docBase="UCPCloud" path="/UCPCloud"
   reloadable="true" source="org.eclipse.jst.jee.server:samplejdbcpage">

 <Resource name="tomcat/UCP_atp" auth="Container"
   factory="oracle.ucp.jdbc.PoolDataSourceImpl"
   type="oracle.ucp.jdbc.PoolDataSource"
   description="UCP Pool in Tomcat"
   connectionFactoryClassName="oracle.jdbc.pool.OracleDataSource"
   minPoolSize="5"
   maxPoolSize="50"
   initialPoolSize="15"
   user="jdbcuser"
   password="XXXXXXXXXXX"
   url="jdbc:oracle:thin:@jdbctest_medium?TNS_ADMIN=/Users/test/ATPTesting/wallet_JDBCTEST"
 />
</Context>

Web Logic Server v12.2.1.3

  • Step 1: Make sure that prerequisites are met.

  • Step 2: WLS v 12.2.1.3 is shipped with 12.2 JDBC driver. Replace it with 18.3 JDBC driver.  order to use the 18.3 JDBC .0.2 drivers. So, Create a UCP Data source in context.xml. Refer to Tomcat_Servlet on Github for an e Make sure to use the TNS alias as the service name and also TNS_ADMIN should point to the path where the client credentials are located. Sample context.xml is shown below.

  • Step 3: Create either a UCP Data Source or  Grid Link Data Source (AGL) or Generic Data Source with the following connection Click on "Test Configuration" to verify the connection to the database. Make sure that the connection is successful before proceeding with using this datasource in the Java servlet.
    URL.

  • Step 4: If you are using JKS then make sure to have JKS related property in ojdbc.properties file. Otherwise, you should have wallets related property.

Autonomous Database Resources

Other Developer Resources