[JAVA] I tried connecting to Oracle Autonomous Database 21c with JDBC Thin

Autonomous Database requires a secure connection using Transport Layer Security (TLSv1.2). Java applications that use the JDBC Thin driver require an Oracle Wallet or Java KeyStore (JKS). So, download DataSourceSample.java and UCPSample.java from JDBC Code Sample and check that you can connect to JDBC Thin.

■ Environment

item version
OS Oracle Linux Server release 7.9
Autonomous Database 21c(21.1.0.0.0)
Oracle Instant Client 12.2.0.1, 19.8.0.0.0, or 21.1.0.0.0
JDK JDK8(openjdk 1.8.0) or JDK11(openjdk 11.0.9.11)
JDBC ojdbc8 (Oracle Database 21c (21.1) drivers)

■ Advance preparation

Make sure that you can connect to the hr schema with sqlplus and SQL in advance. Set the Autonomous Database, hr schema, and Oracle Client in advance by referring to the following. -Reference: I tried to create an HR schema by connecting to Autonomous Database 21c with sqlplus

● hr schema connection test

[opc@oci-inst01 ~]$ sqlplus hr/Password@atp_high

	SQL*Plus: Release 12.2.0.1.0 Production on Wed Jan 20 01:23:22 2021

	Copyright (c) 1982, 2016, Oracle.  All rights reserved.

	Last Successful login time: Tue Jan 19 2021 15:46:29 +00:00

	Connected to:
	Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production

SQL> select count(*) from employees;

	COUNT(*)
	----------
		107

■ JDK 8 installation

● Java 8 (JDK) Runtime installation

[root@oci-inst01 opc]# yum install java-1.8.0-openjdk
Loaded plugin:langpacks, ulninfo
Resolving dependencies
	-->Performing transaction confirmation.
	--->Package java-1.8.0-openjdk.x86_64 1:1.8.0.275.b01-0.el7_Install 9

● Java 8 (JDK) Development Kit installation

[root@oci-inst01 opc]# yum install java-1.8.0-openjdk-devel
Loaded plugin:langpacks, ulninfo
Resolving dependencies
	-->Performing transaction confirmation.
	--->Package java-1.8.0-openjdk-devel.x86_64 1:1.8.0.275.b01-0.el7_Install 9
	-->Dependency resolution finished.

Resolved the dependency

	========================================================================================================================================================
Package architecture version repository capacity
	========================================================================================================================================================
During installation:
	java-1.8.0-openjdk-devel                    x86_64                    1:1.8.0.275.b01-0.el7_9                      ol7_latest                    9.7 M

Transaction summary
	========================================================================================================================================================
Installation 1 package

Total download capacity: 9.7 M
Installation capacity: 40 M
	Is this ok [y/d/N]:  y
	Downloading packages:
	java-1.8.0-openjdk-devel-1.8.0.275.b01-0.el7_9.x86_64.rpm                                                                        | 9.7 MB  00:00:00
	Running transaction check
	Running transaction test
	Transaction test succeeded
	Running transaction
During installation: 1:java-1.8.0-openjdk-devel-1.8.0.275.b01-0.el7_9.x86_64                                                                 1/1
During verification: 1:java-1.8.0-openjdk-devel-1.8.0.275.b01-0.el7_9.x86_64                                                                 1/1

Installation:
	java-1.8.0-openjdk-devel.x86_64 1:1.8.0.275.b01-0.el7_9

Has completed!

● Java Version confirmation

[opc@oci-inst01 ~]$ java -version
	openjdk version "1.8.0_275"
	OpenJDK Runtime Environment (build 1.8.0_275-b01)
	OpenJDK 64-Bit Server VM (build 25.275-b01, mixed mode)

■ JDK 11 installation

● Java 11 (JDK) Runtime installation

[opc@oci-inst01 ~]$ sudo yum install -y java-11-openjdk
Loaded plugin:langpacks, ulninfo
Resolving dependencies
		-->Performing transaction confirmation.
		--->Package java-11-openjdk.x86_64 1:11.0.9.11-2.0.1.el7_Install 9
		-->I'm dealing with dependencies: java-11-openjdk-headless(x86-64) = 1:11.0.9.11-2.0.1.el7_9 packages: 1:java-11-openjdk-11.0.9.11-2.0.1.el7_9.x86_64
		-->Performing transaction confirmation.
		--->Package java-11-openjdk-headless.x86_64 1:11.0.9.11-2.0.1.el7_Install 9
		-->Dependency resolution finished.

Resolved the dependency

		========================================================================================================================================================
Package architecture version repository capacity
		========================================================================================================================================================
During installation:
		java-11-openjdk                             x86_64                    1:11.0.9.11-2.0.1.el7_9                      ol7_latest                    224 k
Dependency related installation:
		java-11-openjdk-headless                    x86_64                    1:11.0.9.11-2.0.1.el7_9                      ol7_latest                     39 M

Transaction summary
		========================================================================================================================================================
Installation 1 package(+1 dependency package)

Total download capacity: 39 M
Installation capacity: 165 M
		Downloading packages:
		(1/2): java-11-openjdk-11.0.9.11-2.0.1.el7_9.x86_64.rpm                                                                          | 224 kB  00:00:00
		(2/2): java-11-openjdk-headless-11.0.9.11-2.0.1.el7_9.x86_64.rpm                                                                 |  39 MB  00:00:01
		--------------------------------------------------------------------------------------------------------------------------------------------------------
35 MB in total/s |  39 MB  00:00:01
		Running transaction check
		Running transaction test
		Transaction test succeeded
		Running transaction
During installation: 1:java-11-openjdk-headless-11.0.9.11-2.0.1.el7_9.x86_64                                                                 1/2
During installation: 1:java-11-openjdk-11.0.9.11-2.0.1.el7_9.x86_64                                                                          2/2
During verification: 1:java-11-openjdk-11.0.9.11-2.0.1.el7_9.x86_64                                                                          1/2
During verification: 1:java-11-openjdk-headless-11.0.9.11-2.0.1.el7_9.x86_64                                                                 2/2

Installation:
		java-11-openjdk.x86_64 1:11.0.9.11-2.0.1.el7_9

Installed dependency related:
		java-11-openjdk-headless.x86_64 1:11.0.9.11-2.0.1.el7_9

Has completed!

● Java 8 (JDK) Development Kit installation

[opc@oci-inst01 ~]$ yum install java-11-openjdk-devel
Loaded plugin:langpacks, ulninfo
You must be root to run this command.
		[opc@oci-inst01 ~]$ sudo yum install java-11-openjdk-devel
Loaded plugin:langpacks, ulninfo
Resolving dependencies
		-->Performing transaction confirmation.
		--->Package java-11-openjdk-devel.x86_64 1:11.0.9.11-2.0.1.el7_Install 9
		-->Dependency resolution finished.

Resolved the dependency

		========================================================================================================================================================
Package architecture version repository capacity
		========================================================================================================================================================
During installation:
		java-11-openjdk-devel                     x86_64                     1:11.0.9.11-2.0.1.el7_9                      ol7_latest                     3.3 M

Transaction summary
		========================================================================================================================================================
Installation 1 package

Total download capacity: 3.3 M
Installation capacity: 5.2 M
		Is this ok [y/d/N]: y
		Downloading packages:
		java-11-openjdk-devel-11.0.9.11-2.0.1.el7_9.x86_64.rpm                                                                           | 3.3 MB  00:00:00
		Running transaction check
		Running transaction test
		Transaction test succeeded
		Running transaction
During installation: 1:java-11-openjdk-devel-11.0.9.11-2.0.1.el7_9.x86_64                                                                    1/1
During verification: 1:java-11-openjdk-devel-11.0.9.11-2.0.1.el7_9.x86_64                                                                    1/1

Installation:
		java-11-openjdk-devel.x86_64 1:11.0.9.11-2.0.1.el7_9

Has completed!

■ Switching Java versions

When multiple JDK 8 and 11 etc. are installed You can set the version you want to use by switching.

● Java version switching

You can switch the JAVA version by selecting the selection number displayed by the alternatives command.

[opc@oci-inst01 ~]$ sudo alternatives --config java

There are 2 programs'java'To provide.

Select command
	-----------------------------------------------
	*+ 1           java-1.8.0-openjdk.x86_64 (/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.275.b01-0.el7_9.x86_64/jre/bin/java)
	2           java-11-openjdk.x86_64 (/usr/lib/jvm/java-11-openjdk-11.0.9.11-2.0.1.el7_9.x86_64/bin/java)

Press Enter to select the current[+]Or enter the selection number:2

● Java version check

[opc@oci-inst01 ~]$ java -version
	openjdk version "11.0.9.1" 2020-11-04 LTS
	OpenJDK Runtime Environment 18.9 (build 11.0.9.1+1-LTS)
	OpenJDK 64-Bit Server VM 18.9 (build 11.0.9.1+1-LTS, mixed mode, sharing)

■ Setting environment variables

● For JDK 8 (openjdk 1.8.0)

・ Confirmation of JAVA_HOME path

[opc@oci-inst01 jvm]$ dirname $(readlink $(readlink $(which java)))
	/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.275.b01-0.el7_9.x86_64/jre/bin

・ Setting environment variables

export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.275.b01-0.el7_9.x86_64
export PATH=$PATH:$JAVA_HOME/bin
export CLASSPATH=.:$JAVA_HOME/jre/lib:$JAVA_HOME/lib:$JAVA_HOME/lib/tools.jar 
export JRE_HOME=$JAVA_HOME/jre

● For JDK 11 (openjdk 11.0.9.1)

・ Confirmation of JAVA_HOME path

[opc@oci-inst01 ~]$ dirname $(readlink $(readlink $(which java)))
	/usr/lib/jvm/java-11-openjdk-11.0.9.11-2.0.1.el7_9.x86_64/bin

・ Setting environment variables

export JAVA_HOME=/usr/lib/jvm/jre-11-openjdk-11.0.9.11-2.0.1.el7_9.x86_64
export PATH=$PATH:$JAVA_HOME/bin
export CLASSPATH=.:$JAVA_HOME/jre/lib:$JAVA_HOME/lib:$JAVA_HOME/lib/tools.jar 
export JRE_HOME=$JAVA_HOME/jre

■ JDK 8 additional settings

If your JDK version is smaller than JDK8u162, you need to download the JCE Unlimited Strength Jurisdiction Policy Files. See the README file for installation notes. Download the JCE file from "Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files 8 Download". Not required for JDK11, JDK10, or JDK9

● JCE Unlimited Strength Jurisdiction Policy Files settings

  1. Download

・ JCE Unlimited Strength Jurisdiction Policy Files https://www.oracle.com/java/technologies/javase-jce8-downloads.html

[root@oci-inst01 tmp]# ls -l
16 in total
	-rw-rw-r--.1 opc opc 12417 January 18 04:39 jce_policy-8.zip
  1. Unzip the zip
[root@oci-inst01 tmp]# unzip jce_policy-8.zip
	Archive:  jce_policy-8.zip
	creating: UnlimitedJCEPolicyJDK8/
	inflating: UnlimitedJCEPolicyJDK8/US_export_policy.jar
	inflating: UnlimitedJCEPolicyJDK8/local_policy.jar
	inflating: UnlimitedJCEPolicyJDK8/README.txt
  1. US_export_policy.jar settings Place files under $ JAVA_HOME/jre/lib/security of JDK8 (java-1.8.0)
[root@oci-inst01 tmp]# ls -l UnlimitedJCEPolicyJDK8/
24 in total
	-rw-r--r--.1 root root 7921 July 1 2020 README.txt
	-rw-r--r--.1 root root 5373 June 19 2020 US_export_policy.jar
	-rw-r--r--.1 root root 5372 June 19 2020 local_policy.jar

[root@oci-inst01 UnlimitedJCEPolicyJDK8]# mv UnlimitedJCEPolicyJDK8/US_export_policy.jar local_policy.jar /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.275.b01-0.el7_9.x86_64/jre/lib/security

[root@oci-inst01 UnlimitedJCEPolicyJDK8]# ls -l /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.275.b01-0.el7_9.x86_64/jre/lib/security
76 in total
	-rw-r--r--.1 root root 5373 June 19 2020 US_export_policy.jar
	-rw-r--r--.1 root root 2488 December 15 18:10 blacklisted.certs
	lrwxrwxrwx.1 root root 41 January 18 05:12 cacerts -> ../../../../../../../etc/pki/java/cacerts
	-rw-r--r--.1 root root 2567 December 15 18:10 java.policy
	-rw-r--r--.1 root root 47872 January 1 2014 java.security
	-rw-r--r--.1 root root 5372 June 19 2020 local_policy.jar
	-rw-r--r--.1 root root 139 December 15 18:14 nss.cfg
	drwxr-xr-x.4 root root 38 January 18 05:12 policy

● Wallet location settings

-$ JAVA_HOME/jre/lib/security/java.security file settings

  1. Check java.security file
[opc@oci-inst01 ~]$ ls -l /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.275.b01-0.el7_9.x86_64/jre/lib/security/java.security
	-rw-r--r--.1 root root 47872 January 1 2014 java.security
  1. java.security file settings ・ Add the following line to the end

security.provider.14=oracle.security.pki.OraclePKIProvider

[root@oci-inst01 security]# vi /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.275.b01-0.el7_9.x86_64/jre/lib/security/java.security
[root@oci-inst01 security]# tail -2 java.security
	#Wallet Loction
	security.provider.14=oracle.security.pki.OraclePKIProvider

■ JDBC installation

● Download ojdbc8-full.tar.gz

-JDBC and UCP Downloads page https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html

● JDBC (ojdbc8) deployment

This time, I will place it in the $ HOME/oracle directory

  1. Move the location directory
[opc@oci-inst01 ~]$ cd $HOME/oracle
  1. tar.gz decompression arrangement
[opc@oci-inst01 oracle]$ tar zxvf /tmp/ojdbc8-full.tar.gz
[opc@oci-inst01 oracle]$ ls -l ojdbc8-full/
12144 in total
    drwxr-xr-x.2 opc opc 4096 January 14 19:52 .
    drwxrwxr-x.8 opc opc 4096 January 20 12:56 ..
    -rw-r--r--.1 opc opc 5903 January 14 19:51 BASIC_LICENSE.txt
    -rw-r--r--.1 opc opc 3324 January 15 10:54 README.txt
    -rw-r--r--.1 opc opc 11515 January 14 19:51 ojdbc.policy
    -rw-r--r--.1 opc opc 5040465 January 14 19:51 ojdbc8.jar
    -rw-r--r--.1 opc opc 198469 January 14 19:51 ons.jar
    -rw-r--r--.1 opc opc 306476 January 14 19:51 oraclepki.jar
    -rw-r--r--.1 opc opc 1664450 January 14 19:51 orai18n.jar
    -rw-r--r--.1 opc opc 210338 January 14 19:51 osdt_cert.jar
    -rw-r--r--.1 opc opc 312230 January 14 19:51 osdt_core.jar
    -rw-r--r--.1 opc opc 345036 January 14 19:51 rsi.jar
    -rw-r--r--.1 opc opc 32169 January 14 19:51 simplefan.jar
    -rw-r--r--.1 opc opc 1788363 January 14 19:51 ucp.jar
    -rw-r--r--.1 opc opc 265864 January 14 19:51 xdb.jar
    -r--r--r--.1 opc opc 262415 August 2 2018 xdb6.jar
    -rw-r--r--.1 opc opc 1951430 January 14 19:51 xmlparserv2.jar
  1. Version confirmation
[opc@oci-inst01 ojdbc8-full]$ java -jar ./ojdbc8-full/ojdbc8.jar -version
	    Oracle 21.1.0.0.0 JDBC 4.2 compiled with javac 1.8.0_271 on Fri_Oct_09_09:20:04_PDT_2020
    #Default Connection Properties Resource
    #Wed Jan 20 12:57:33 GMT 2021

    ***** JCE UNLIMITED STRENGTH IS INSTALLED ****

■ Java KeyStore usage settings

To connect to Autonomous Transaction Processing using the Java KeyStore (JKS) and the 18.3 JDBC Thin driver, complete the following steps: Set JKS-related connection properties: Add JKS-related connection properties to the ojdbc.properties file. The keyStore and truststore passwords are the passwords that are specified when downloading the client credentials .zip file from the Autonomous Transaction Processing Services Console. To use an SSL connection instead of an Oracle wallet, specify the keystore and truststore files and their passwords in the ojdbc.properties file as follows:

● ojdbc.properties file settings

Set password in ojdbc.properties file under $ TNS_ADMIN

[opc@oci-inst01 ~]$ cd $TNS_ADMIN
[opc@oci-inst01 ~]$ pwd 
	/home/opc/oracle/instantclient_19_8/network/admin
[opc@oci-inst01 admin]$ cat ojdbc.properties
	# Connection property while using Oracle wallets.
	oracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=${TNS_ADMIN})))
	# FOLLOW THESE STEPS FOR USING JKS
	# (1) Uncomment the following properties to use JKS.
	# (2) Comment out the oracle.net.wallet_location property above
	# (3) Set the correct password for both trustStorePassword and keyStorePassword.
	# It's the password you specified when downloading the wallet from OCI Console or the Service Console.
	#javax.net.ssl.trustStore=${TNS_ADMIN}/truststore.jks
	#javax.net.ssl.trustStorePassword=<password_from_console>
	#javax.net.ssl.keyStore=${TNS_ADMIN}/keystore.jks
	#javax.net.ssl.keyStorePassword=<password_from_console>
	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

■ JDBC Thin connection: DataSourceSample.java

Download DataSourceSample.java for JDBC Thin connection test from "JDBC code sample" and test it. -GitHub: JDBC Code Sample

● DataSourceSample.java settings

Set the TNS connection name and hr schema password according to the environment in the following 3 lines of the downloaded DataSourceSample.java.

final static String DB_URL="jdbc:oracle:thin:@atp_high";
final static String DB_USER = "hr";
final static String DB_PASSWORD = "Password";

・ DataSourceSample.java sample

[opc@oci-inst01 work]$ cat DataSourceSample.java

/* Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved.*/
/*
   DESCRIPTION    
   The code sample shows how to use the DataSource API to establish a connection
   to the Database. You can specify properties with "setConnectionProperties".
   This is the recommended way to create connections to the Database.
   Note that an instance of oracle.jdbc.pool.OracleDataSource doesn't provide
   any connection pooling. It's just a connection factory. A connection pool,
   such as Universal Connection Pool (UCP), can be configured to use an
   instance of oracle.jdbc.pool.OracleDataSource to create connections and 
   then cache them.
    
    Step 1: Enter the Database details in this file. 
            DB_USER, DB_PASSWORD and DB_URL are required
    Step 2: Run the sample with "ant DataSourceSample"
  
   NOTES
    Use JDK 1.7 and above
   MODIFIED    (MM/DD/YY)
    nbsundar    02/17/15 - Creation 
 */

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import oracle.jdbc.pool.OracleDataSource;
import oracle.jdbc.OracleConnection;
import java.sql.DatabaseMetaData;

public class DataSourceSample {  
  // The recommended format of a connection URL is the long format with the
  // connection descriptor.
// final static String DB_URL= "jdbc:oracle:thin:@myhost:1521/myorcldbservicename";
final static String DB_URL="jdbc:oracle:thin:@atp_high";


  // For ATP and ADW - use the TNS Alias name along with the TNS_ADMIN when using 18.3 JDBC driver
  // final static String DB_URL="jdbc:oracle:thin:@wallet_dbname?TNS_ADMIN=/Users/test/wallet_dbname";
  // In case of windows, use the following URL 
  // final static String DB_URL="jdbc:oracle:thin:@wallet_dbname?TNS_ADMIN=C:\\Users\\test\\wallet_dbname";
  final static String DB_USER = "hr";
  final static String DB_PASSWORD = "Password";

 /*
  * The method gets a database connection using 
  * oracle.jdbc.pool.OracleDataSource. It also sets some connection 
  * level properties, such as,
  * OracleConnection.CONNECTION_PROPERTY_DEFAULT_ROW_PREFETCH,
  * OracleConnection.CONNECTION_PROPERTY_THIN_NET_CHECKSUM_TYPES, etc.,
  * There are many other connection related properties. Refer to 
  * the OracleConnection interface to find more. 
  */
  public static void main(String args[]) throws SQLException {
    Properties info = new Properties();     
    info.put(OracleConnection.CONNECTION_PROPERTY_USER_NAME, DB_USER);
    info.put(OracleConnection.CONNECTION_PROPERTY_PASSWORD, DB_PASSWORD);          
    info.put(OracleConnection.CONNECTION_PROPERTY_DEFAULT_ROW_PREFETCH, "20");    
  

    OracleDataSource ods = new OracleDataSource();
    ods.setURL(DB_URL);    
    ods.setConnectionProperties(info);

    // With AutoCloseable, the connection is closed automatically.
    try (OracleConnection connection = (OracleConnection) ods.getConnection()) {
      // Get the JDBC driver name and version 
      DatabaseMetaData dbmd = connection.getMetaData();       
      System.out.println("Driver Name: " + dbmd.getDriverName());
      System.out.println("Driver Version: " + dbmd.getDriverVersion());
      // Print some connection properties
      System.out.println("Default Row Prefetch Value is: " + 
         connection.getDefaultRowPrefetch());
      System.out.println("Database Username is: " + connection.getUserName());
      System.out.println();
      // Perform a database operation 
      printEmployees(connection);
    }   
  }
 /*
  * Displays first_name and last_name from the employees table.
  */
  public static void printEmployees(Connection connection) throws SQLException {
    // Statement and ResultSet are AutoCloseable and closed automatically. 
    try (Statement statement = connection.createStatement()) {      
      try (ResultSet resultSet = statement
          .executeQuery("select first_name, last_name from employees")) {
        System.out.println("FIRST_NAME" + "  " + "LAST_NAME");
        System.out.println("---------------------");
        while (resultSet.next())
          System.out.println(resultSet.getString(1) + " "
              + resultSet.getString(2) + " ");       
      }
    }   
  } 
}

● Compile DataSourceSample.java

  1. run javac Compile DataSourceSample.java into class file

-Execution command

javac -classpath \
/home/opc/oracle/ojdbc8-full/ojdbc8.jar:\
/home/opc/oracle/ojdbc8-full/ucp.jar:\
/home/opc/oracle/ojdbc8-full/oraclepki.jar:\
/home/opc/oracle/ojdbc8-full/osdt_core.jar:\
/home/opc/oracle/ojdbc8-full/osdt_cert.jar:. \
-Doracle.net.tns_admin=/home/opc/oracle/instantclient_12_2/network/admin \
DataSourceSample.java

·Execution result Compile with javac command and confirm class file creation

[opc@oci-inst01 work]$ javac -classpath \
> /home/opc/oracle/ojdbc8-full/ojdbc8.jar:\
> /home/opc/oracle/ojdbc8-full/ucp.jar:\
> /home/opc/oracle/ojdbc8-full/oraclepki.jar:\
> /home/opc/oracle/ojdbc8-full/osdt_core.jar:\
> /home/opc/oracle/ojdbc8-full/osdt_cert.jar:. \
> -Doracle.net.tns_admin=/home/opc/oracle/instantclient_12_2/network/admin \
> DataSourceSample.java

[opc@oci-inst01 work]$ ls -l
	-rw-rw-r--.1 opc opc 3222 January 19 05:33 DataSourceSample.class
	-rw-rw-r--.1 opc opc 4297 January 19 05:33 DataSourceSample.java

● Execute DataSourceSample

-Execution command

java -classpath \
/home/opc/oracle/ojdbc8-full/ojdbc8.jar:\
/home/opc/oracle/ojdbc8-full/ucp.jar:\
/home/opc/oracle/ojdbc8-full/oraclepki.jar:\
/home/opc/oracle/ojdbc8-full/osdt_core.jar:\
/home/opc/oracle/ojdbc8-full/osdt_cert.jar:. \
-Doracle.net.tns_admin=/home/opc/oracle/instantclient_12_2/network/admin \
DataSourceSample

·Execution result

[opc@oci-inst01 work]$ java -classpath \
> /home/opc/oracle/ojdbc8-full/ojdbc8.jar:\
> /home/opc/oracle/ojdbc8-full/ucp.jar:\
> /home/opc/oracle/ojdbc8-full/oraclepki.jar:\
> /home/opc/oracle/ojdbc8-full/osdt_core.jar:\
> /home/opc/oracle/ojdbc8-full/osdt_cert.jar:. \
> -Doracle.net.tns_admin=/home/opc/oracle/instantclient_12_2/network/admin \
> DataSourceSample

    Driver Name: Oracle JDBC driver
    Driver Version: 21.1.0.0.0
    Default Row Prefetch Value is: 20
    Database Username is: HR

	FIRST_NAME  LAST_NAME
	---------------------
	Ellen Abel
	Sundar Ande
	Mozhe Atkinson
・ ・ ・

● Execution by setting OS environment variables

-classpath at javac and java runtime can also be set in the OS environment variable \ $ CLASSPATH as shown below -Doracle.net.tns_admin can also be set in the OS environment variable \ $ TNS_ADMIN

  1. CLASSPATH OS environment variable setting
[opc@oci-inst01 work]$ export CLASSPATH=.:$JAVA_HOME/jre/lib:$JAVA_HOME/lib:$JAVA_HOME/lib/tools.jar:/home/opc/oracle/ojdbc8-full/ojdbc8.jar:/home/opc/oracle/ojdbc8-full/ucp.jar:/home/opc/oracle/ojdbc8-full/oraclepki.jar:/home/opc/oracle/ojdbc8-full/osdt_core.jar:/home/opc/oracle/ojdbc8-full/osdt_cert.jar:. 
[opc@oci-inst01 work]$ export ORACLE_HOME=/home/opc/oracle/instantclient_12_2
[opc@oci-inst01 work]$ export TNS_ADMIN=$ORACLE_HOME/network/admin
  1. Compile Compile with javac command and confirm class file creation
[opc@oci-inst01 work]$ javac DataSourceSample.java
[opc@oci-inst01 work]$ ls -l
	-rw-rw-r--.1 opc opc 3222 January 19 10:55 DataSourceSample.class
	-rw-rw-r--.1 opc opc 4297 January 19 05:33 DataSourceSample.java
  1. Run
[opc@oci-inst01 work]$ java DataSourceSample
    Driver Name: Oracle JDBC driver
    Driver Version: 21.1.0.0.0
    Default Row Prefetch Value is: 20
    Database Username is: HR

	FIRST_NAME  LAST_NAME
	---------------------
	Ellen Abel
	Sundar Ande
	Mozhe Atkinson
・ ・ ・
	Eleni Zlotkey

■ JDBC Thin connection test: UCPSample.java

Download UCPSample.java for JDBC Thin connection test from "JDBC code sample" and test it. -GitHub: JDBC Code Sample

● UCPSample.java settings

Set the TNS connection name and hr schema password according to the environment in the following 3 lines of the downloaded UCPSample.java.

final static String DB_URL="jdbc:oracle:thin:@atp_high";
final static String DB_USER = "hr";
final static String DB_PASSWORD = "Password";

・ DataSourceSample.java sample

[opc@oci-inst01 work]$ vi UCPSample.java
[opc@oci-inst01 work]$ cat UCPSample.java
/* Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved.*/

/*
 DESCRIPTION
 The code sample demonstrates Universal Connection Pool (UCP) as a client
 side connection pool and does the following.
 (a)Set the connection factory class name to
 oracle.jdbc.pool.OracleDataSource before getting a connection.
 (b)Set the driver connection properties(e.g.,defaultNChar,includeSynonyms).
 (c)Set the connection pool properties(e.g.,minPoolSize, maxPoolSize).
 (d)Get the connection and perform some database operations.
 Step 1: Enter the Database details in DBConfig.properties file.
 USER, PASSWORD, UCP_CONNFACTORY and URL are required.
 Step 2: Run the sample with "ant UCPSample"
 NOTES
 Use JDK 1.7 and above
 MODIFIED    (MM/DD/YY)
 nbsundar    02/13/15 - Creation (Contributor - tzhou)
 */
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import oracle.ucp.jdbc.PoolDataSourceFactory;
import oracle.ucp.jdbc.PoolDataSource;

public class UCPSample {
  // final static String DB_URL="jdbc:oracle:thin:@myhost:1521/orclservicename";
  // Use the TNS Alias name along with the TNS_ADMIN - For ATP and ADW
  // final static String DB_URL="jdbc:oracle:thin:@dbname_alias?TNS_ADMIN=/Users/test/wallet_dbname";
  final static String DB_URL="jdbc:oracle:thin:@atp_high";
  final static String DB_USER = "hr";
  final static String DB_PASSWORD = "Password";
  final static String CONN_FACTORY_CLASS_NAME="oracle.jdbc.pool.OracleDataSource";

  /*
   * The sample demonstrates UCP as client side connection pool.
   */
  public static void main(String args[]) throws Exception {
    // Get the PoolDataSource for UCP
    PoolDataSource pds = PoolDataSourceFactory.getPoolDataSource();

    // Set the connection factory first before all other properties
    pds.setConnectionFactoryClassName(CONN_FACTORY_CLASS_NAME);
    pds.setURL(DB_URL);
    pds.setUser(DB_USER);
    pds.setPassword(DB_PASSWORD);
    pds.setConnectionPoolName("JDBC_UCP_POOL");

    // Default is 0. Set the initial number of connections to be created
    // when UCP is started.
    pds.setInitialPoolSize(5);

    // Default is 0. Set the minimum number of connections
    // that is maintained by UCP at runtime.
    pds.setMinPoolSize(5);

    // Default is Integer.MAX_VALUE (2147483647). Set the maximum number of
    // connections allowed on the connection pool.
    pds.setMaxPoolSize(20);

    // Default is 30secs. Set the frequency in seconds to enforce the timeout
    // properties. Applies to inactiveConnectionTimeout(int secs),
    // AbandonedConnectionTimeout(secs)& TimeToLiveConnectionTimeout(int secs).
    // Range of valid values is 0 to Integer.MAX_VALUE. .
    pds.setTimeoutCheckInterval(5);

    // Default is 0. Set the maximum time, in seconds, that a
    // connection remains available in the connection pool.
    pds.setInactiveConnectionTimeout(10);

    // Get the database connection from UCP.
    try (Connection conn = pds.getConnection()) {
      System.out.println("Available connections after checkout: "
          + pds.getAvailableConnectionsCount());
      System.out.println("Borrowed connections after checkout: "
          + pds.getBorrowedConnectionsCount());
      // Perform a database operation
      doSQLWork(conn);
    }
    catch (SQLException e) {
      System.out.println("UCPSample - " + "SQLException occurred : "
          + e.getMessage());
    }
    System.out.println("Available connections after checkin: "
        + pds.getAvailableConnectionsCount());
    System.out.println("Borrowed connections after checkin: "
        + pds.getBorrowedConnectionsCount());
  }

  /*
   * Creates an EMP table and does an insert, update and select operations on
   * the new table created.
   */
  public static void doSQLWork(Connection conn) {
    try {
      conn.setAutoCommit(false);
      // Prepare a statement to execute the SQL Queries.
      Statement statement = conn.createStatement();
      // Create table EMP
      statement.executeUpdate("create table EMP(EMPLOYEEID NUMBER,"
          + "EMPLOYEENAME VARCHAR2 (20))");
      System.out.println("New table EMP is created");
      // Insert some records into the table EMP
      statement.executeUpdate("insert into EMP values(1, 'Jennifer Jones')");
      statement.executeUpdate("insert into EMP values(2, 'Alex Debouir')");
      System.out.println("Two records are inserted.");

      // Update a record on EMP table.
      statement.executeUpdate("update EMP set EMPLOYEENAME='Alex Deborie'"
          + " where EMPLOYEEID=2");
      System.out.println("One record is updated.");

      // Verify the table EMP
      ResultSet resultSet = statement.executeQuery("select * from EMP");
      System.out.println("\nNew table EMP contains:");
      System.out.println("EMPLOYEEID" + " " + "EMPLOYEENAME");
      System.out.println("--------------------------");
      while (resultSet.next()) {
        System.out.println(resultSet.getInt(1) + " " + resultSet.getString(2));
      }
      System.out.println("\nSuccessfully tested a connection from UCP");
    }
    catch (SQLException e) {
      System.out.println("UCPSample - "
          + "doSQLWork()- SQLException occurred : " + e.getMessage());
    }
    finally {
      // Clean-up after everything
      try (Statement statement = conn.createStatement()) {
        statement.execute("drop table EMP");
      }
      catch (SQLException e) {
        System.out.println("UCPSample - "
            + "doSQLWork()- SQLException occurred : " + e.getMessage());
      }
    }
  }
}
  1. CLASSPATH OS environment variable setting
[opc@oci-inst01 work]$ export CLASSPATH=.:$JAVA_HOME/jre/lib:$JAVA_HOME/lib:$JAVA_HOME/lib/tools.jar:/home/opc/oracle/ojdbc8-full/ojdbc8.jar:/home/opc/oracle/ojdbc8-full/ucp.jar:/home/opc/oracle/ojdbc8-full/oraclepki.jar:/home/opc/oracle/ojdbc8-full/osdt_core.jar:/home/opc/oracle/ojdbc8-full/osdt_cert.jar:. 
[opc@oci-inst01 work]$ export ORACLE_HOME=/home/opc/oracle/instantclient_12_2
[opc@oci-inst01 work]$ export TNS_ADMIN=$ORACLE_HOME/network/admin
  1. Compile Compile with javac command and confirm class file creation
[opc@oci-inst01 work]$ javac UCPSample.java
[opc@oci-inst01 work]$ ls -l
	-rw-rw-r--.1 opc opc 4866 January 19 15:46 UCPSample.class
	-rw-rw-r--.1 opc opc 5675 January 19 15:46 UCPSample.java
  1. Run
[opc@oci-inst01 work]$ java UCPSample
	Available connections after checkout: 4
	Borrowed connections after checkout: 1
	New table EMP is created
	Two records are inserted.
	One record is updated.

	New table EMP contains:
	EMPLOYEEID EMPLOYEENAME
	--------------------------
	1 Jennifer Jones
	2 Alex Deborie

	Successfully tested a connection from UCP
	Available connections after checkin: 5
	Borrowed connections after checkin: 0

■ Reference

● Download -JDBC and UCP Downloads page -JCE Unlimited Strength Jurisdiction Policy Files -JDBC Code Sample

● Oracle Manual

-JDBC Thin Connection and Wallet · Java Connectivity with Autonomous Database (ATP or ADW) using 19c and 18.3 JDBC

Recommended Posts

I tried connecting to Oracle Autonomous Database 21c with JDBC Thin
I tried connecting to MySQL using JDBC Template with Spring MVC
Try connecting to the OCI Database (DBaaS) PDB with Java's JDBC Thin Driver. (Oracle Cloud Infrastructure)
Try connecting to Autonomous DB (ADW / ATP) with Java's JDBC Thin Driver. (OCI, Oracle Cloud Infrastructure)
Try connecting to the Autonomous Database with JDK6 (Java) + JDBC OCI Driver (type 2).
How to create a JDBC URL (Oracle Database, Thin)
[JDBC] I tried to access the SQLite3 database from Java.
I tried to chew C # (indexer)
I tried to interact with Java
I tried to chew C # (polymorphism: polymorphism)
I tried to get started with WebAssembly
I tried to implement ModanShogi with Kinx
I tried to verify AdoptOpenJDK 11 (11.0.2) with Docker image
I tried to make Basic authentication with Java
I tried to manage login information with JMX
I also tried WebAssembly with Nim and C
I tried to chew C # (basic of encapsulation)
I tried to break a block with java (1)
Settings for connecting to MySQL with Spring Boot + Spring JDBC
I tried what I wanted to try with Stream softly.
I tried to implement file upload with Spring MVC
I tried to read and output CSV with Outsystems
I tried to implement TCP / IP + BIO with JAVA
[Java 11] I tried to execute Java without compiling with javac
I tried to get started with Spring Data JPA
I tried to draw animation with Blazor + canvas API
I tried to implement Stalin sort with Java Collector
I tried to chew C # (reading and writing files)
roman numerals (I tried to simplify it with hash)
I tried to make an introduction to PHP + MySQL with Docker
I tried to create a java8 development environment with Chocolatey
I tried to modernize a Java EE application with OpenShift.
I tried to increase the processing speed with spiritual engineering
[Rails] I tried to create a mini app with FullCalendar
I tried to link chat with Minecraft server with Discord API
[Rails] I tried to implement batch processing with Rake task
I tried to automate LibreOffice Calc with Ruby + PyCall.rb (Ubuntu 18.04)
I tried to create a padrino development environment with Docker
I tried to get started with Swagger using Spring Boot
I tried upgrading from CentOS 6.5 to CentOS 7 with the upgrade tool
I tried to be able to pass multiple objects with Ractor
I tried DI with Ruby
I tried UPSERT with PostgreSQL.
I tried BIND with Docker
I tried to verify yum-cron
Connect to oracle with eclipse!
I tried to build the environment of PlantUML Server with Docker
I tried to implement the image preview function with Rails / jQuery
I tried to build an http2 development environment with Eclipse + Tomcat
I tried to implement flexible OR mapping with MyBatis Dynamic SQL
Connecting to a database with Java (Part 1) Maybe the basic method
I tried to reimplement Ruby Float (arg, exception: true) with builtin
I tried to make an Android application with MVC now (Java)
I tried to check the operation of gRPC server with grpcurl
I tried to generate a C language program source from cURL
I tried to make a group function (bulletin board) with Rails
[JDBC] I tried to make SQLite3 database access from Java into a method for each SQL statement.
I tried using JOOQ with Gradle
I tried to summarize iOS 14 support
I tried UDP communication with Java
I tried to explain the method