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 - 

Configuring the Oracle JDBC Drivers In a Spring Boot Project

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!

JQUERY - GET SELECTED TEXT AND VALUE

ERROR Handling for COMBO BOX or SELECTED BOX -

<select size="1" id="cmbSTHour" name="cmbSTHour" class="recordData">
<option selected value="0"></option>
<option value="01">01</option>
<option value="02">02</option>
<option value="03">03</option>
<option value="04">04</option>
<option value="05">05</option>
<option value="06">06</option>
<option value="07">07</option>
<option value="08">08</option>
<option value="09">09</option>
<option value="10">10</option>
<option value="11">11</option>
<option value="12">12</option>
<option value="13">13</option>
<option value="14">14</option>
<option value="15">15</option>
<option value="16">16</option>
<option value="17">17</option>
<option value="18">18</option>
<option value="19">19</option>
<option value="20">20</option>
<option value="21">21</option>
<option value="22">22</option>
<option value="23">23</option>
<option value="24">24</option>
</select>

Get the selected value
$('#cmbSTHour').val() == 0

Get the Selected Text
$('#cmbSTHour option:selected').text()

Reference -
https://learn.jquery.com/using-jquery-core/faq/how-do-i-get-the-text-value-of-a-selected-option/

JAVASCRIPT, JQUERY ERROR HANDLING - KEEP IT SIMPLE

Part of Keeping things simple is coding yourself.... You don't have to use frameworks to do SIMPLE things..... Here is a Javascript, Jquery error handler on a button click,. Calls a servlet on the server....... KIS!

//GLOBAL VARIABLES

var G_MSGTITLE;
G_MSGTITLE = "_________________________________________\n\n";
G_MSGTITLE += "CLIENT VALIDATION ERRORS\n";
G_MSGTITLE += "This form was not submitted because of the following error(s). \n";
G_MSGTITLE += "Please correct these error(s) and re-submit.\n";
G_MSGTITLE += "_________________________________________\n\n";

var G_MSG;

/**
*   Function Name:  setloadVCvoucherrecord()
*   Description :   Sets the jQuery controls
*   Parameters:     None.
*   Return:         None.
*/
function setloadVCvoucherrecord(){

    $(document).ready(function() {

        //BUTTON TO GO TO UPDATE Cancer Care Agreement
        $('#UpdateCCA').click( function () {
         
            try{
                var fileIndex = 0;
                G_MSG = "";

                $.each(table3.row('.selected').data(), function(index, value){

                    if ( index===0 ){
                        fileIndex = value;
                    }

                });             

                //Add Parameter to form
                $("#CCAfileIndex").val(fileIndex);                                               

                if (fileIndex === 0 ){
                    G_MSG += "Error - No file selected.\n";
                }
             
                if (G_MSG.length > 0)
                    throw G_MSG;
             
                //Keep Form on Same window
                $('#frmVoucherRecord').on('submit',function(){
                   $(this).attr("target", "_parent");
                   $(this).attr( "action", "UpdateCancerCareAgreement");
                });               

            }catch( err ){

                alert(G_MSGTITLE + err );
                return false;

            }             
         
    });     
        //END OF BUTTON TO GO TO UPDATE Cancer Care Agreement
     }); 

}

INSTALL OCI/JAVA/TOMCAT WEB SERVER - ORACLE CLOUD

PREREQUISITE

Create SSH keys

Creating a Instance (Hint: accept the defaults, it's FREE)

Install OCI (Oracle Cloud Infrastructure) 

Log into Oracle Cloud Compute Instance using putty - 

Reference - https://pypi.org/project/oci-cli/

RUN (Press enter and accept all the defaults) -

[opc@instance-20200119-1236 ~]$ bash -c "$(curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh)"

RUN -

[opc@instance-20200119-1236 ~]$ oci --help

OCI SETUP IS COMPLETE

INSTALL JAVA -

Sergio gives a GREAT explanation of how to install the java JDK.

I recommend to ONLY install JDK 8 and JDK 11. They both work well with the Oracle JDBC drivers to connect to your Oracle ATP.

https://blogs.oracle.com/developers/how-to-install-oracle-java-in-oracle-cloud-infrastructure 

DON'T FORGET TO SET PATH 

Key commands -

Java Root /usr/java/jdk1.8.0_231-amd64/bin

SET PATH -

[opc@instance-20200119-1236 ~]$ export PATH=/usr/java/jdk1.8.0_231-amd64/bin:$PATH

SET JAVA_HOME -

[opc@instance-20200119-1236 ~]$ export JAVA_HOME=/usr/java/jdk1.8.0_231-amd64

Set default JAVA -

[opc@instance-20200119-1236 ~]$ sudo alternatives --config java

INSTALL TOMCAT -

Run (Installs Web Server) -
[opc@instance-20200119-1236 bin]$ sudo yum install -y tomcat tomcat-webapps tomcat-admin-webapps

Run (Installs Doco) -
[opc@instance-20200119-1236 bin]$ sudo yum install tomcat-docs-webapp tomcat-javadoc

ENABLE TOMCAT SERVICE -

[opc@instance-20200119-1236 bin]$ sudo systemctl enable tomcat

START SERVICE - 

[opc@instance-20200119-1236 bin]$ sudo systemctl start tomcat

TOMCAT OTHER COMMANDS -

STOP SERVICE - 

[opc@instance-20200119-1236 bin]$ sudo systemctl stop tomcat

RESTART SERVICE - 

[opc@instance-20200119-1236 bin]$ sudo systemctl restart tomcat

ADD ACCESS THROUGH LINUX FIREWALL - 

[opc@instance-20200119-1236 bin]$ sudo firewall-cmd --permanent --zone=public --add-port=8080/tcp

RELOAD FIREWALL - 

[opc@instance-20200119-1236 bin]$ sudo firewall-cmd --reload

ADD "INGRESS" RULE ON VCN (Virtual Cloud Network) port 8080 - 

TEST WEB SERVER - 

CREATING THE SSH KEYS IN WINDOWS - ORACLE CLOUD

Open Windows PowerShell - 

Change to your "Key" directory

Type the command - "ssh-keygen -b 2048 -t rsa -f <FILENAME>" . Leave the passphrase empty. 

ssh-keygen -b 2048 -t rsa -f  OracleInstanceKey

Convert private key to putty, open puttygen and load the private key generated.

Open private key in putty format.

Save private key in putty format, leave passphrase empty.

Use the same file name, OracleInstanceKey.ppk

Final Look -

Use the OracleInstanceKey.pub contents to copy and paste in instance 

Paste here in setting up instance -

Connect to you instance using putty, IP from instance - 

Use opc ID - 

The file created .ppk format - 

Press "Open" button