Friday, October 9, 2020

Connect to Oracle ATP using Spring Boot - CLIFF NOTES

 

I am using Intellij IDEA. I am going to provide you links to give you guidance on the ultimate goal of connecting to the Oracle ATP Database, using Spring Boot. I created a simple Spring Boot Application using Ikhiloya Imokhai set up. I give you STEP by STEP (as shown to me by Malay Kumar Khawas) instructions on how to install the wallets on the Oracle Cloud Compute Engine. 

Some Assumptions - You have already downloaded the Oracle ATP Wallet. You have already connected to the Oracle ATP Database using SQL Developer and setup your databases. You have already installed Tomcat 9 or higher on the Oracle Cloud Compute Engine. You can connect to the Oracle Cloud Compute Engine using Putty.

REMEBER ITS THE CLIFF NOTES, SOME PIECES ARE MISSING....

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

FIRST GET DRIVERS -  

There is specific things you must do in order to get the drivers from the Oracle Maven Repository. Here is the link and I will provide the screen shots of my set up.

Get Oracle JDBC drivers and UCP from Oracle Maven Repository (without IDEs)

SET UP YOUR SETTINGS.XML FILE - 


SET UP YOUR ORACLE MAVEN REPOSITORY -


ADD YOUR DEPNEDENCY - 




ADD DataSourceConfig FILE - 




FILE DataSourceConfig - 

APPLICATION.PROPERTIES FILE - 


SET UP TEST APPLICATION FOR LOCAL USE, USE THE FOLLOWING LINK - 

Ikhiloya Imokhai

SET UP ORACLE ATP WALLETS ON THE FREE ORACLE CLOUD COMPUTE ENGINE - 

WALLETS FOR CONNECTION

Current Wallet Directory on PC - 

C:\Users\rober\OneDrive\Documents\OracleCloud\SafetyNetAccess\Wallets\SafetyNetAccessInfo\Wallet_SNADB.zip

Current KeyStore -

C:\Users\rober\OneDrive\Documents\OracleCloud\Keys\keysafetynetaccessinfo\OracleSNAInfoKey.ppk


STEP 1, Change to super user

sudo su - root

STEP 2, Make a Directory to copy Wallet - 

sudo mkdir -p /home/opc/SNADB_Wallet

STEP 3, change properties

sudo chmod 777 /home/opc/SNADB_Wallet

STEP 4, Copy Wallet to Cloud - 

pscp -i C:\Users\rober\OneDrive\Documents\OracleCloud\Keys\keysafetynetaccessinfo\OracleSNAInfoKey.ppk

C:\Users\rober\OneDrive\Documents\OracleCloud\SafetyNetAccess\Wallets\SafetyNetAccessInfo\Wallet_SNADB.zip root@<YOUR COMPUTE ENGINE IP ADDRESS>:/home/opc/SNADB_Wallet

STEP 5, make directory of unzip files

sudo mkdir -p /u01/tomcat/SNADB_Wallet

STEP 6, change properties

sudo chmod 777 /u01/tomcat/SNADB_Wallet

STEP 7, copy zip file

sudo cp /home/opc/SNADB_Wallet/Wallet_SNADB.zip /u01/tomcat/SNADB_Wallet/

STEP 8, change Directory

cd /u01/tomcat/SNADB_Wallet/

STEP 9, change properties

sudo chmod 777 Wallet_SNADB.zip

STEP 10, unzip file

unzip Wallet_SNADB.zip

STEP 11, change owner

sudo chown -R root:root /u01/tomcat/SNADB_Wallet

STEP 12, Change connection property in application.properties

spring.datasource.url= jdbc:oracle:thin:@snadb_medium?TNS_ADMIN=/u01/tomcat/SNADB_Wallet

HAPPY HACKING!