A Simple 10-step tutorial: How to create Data Source Java connectivity from JCS WLS to ADW on Oracle Cloud?

5 min readMay 23, 2019

Keyword: Oracle Cloud Service, Java Cloud Service, Weblogic Administration Console, Oracle Autonomous Data Warehouse

Introduction

In this post, I’ll explain a 10-step simple tutorial on how to add a Generic Data Source from an administration console of Weblogic Server 12.2.x to make a 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.

Why do we need Data sources?

In WebLogic Server, you configure database connectivity by adding data sources to your WebLogic domain. WebLogic JDBC data sources provide database access and database connection management. Each data source contains a pool of database connections that are created when the data source is created and at server startup.

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.

How to create JDBC data source connection from Weblogic to ADW cloud service?

1. Go to the Weblogic administration console
URL: http://<hostname>:7001/console

Check and enable “Lock & Edit” as shown below.

2. From domain structure, click “Data Soruces”.

3. Click on option “New” and select “Generic Data Source”.

4. Create a New JDBC Data Source by giving a name, select scope as “Global” and Database Type as “Oracle”. Proceed by clicking “Next”.

5. Select the Database Driver as below, Oracle’s Driver (Thin) for Service connections; Versions: Any, Click Next.

6. Click Next, check “Supports Global Transactions”, “One-Phase Commit. Click Next.

7. For the next step, got to the Wallet credential location, open tnsnames.ora file.

Copy the port, host and service name from the file. (ref. example as below)

Give Database name as the service name “ofsaadw_low”, Host and port can be used as a dummy value, as we are going to change the string in the next screen. Give the Database username and password.

Click Next.