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.
| 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) |
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
[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
[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
[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!
[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)
[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!
[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!
When multiple JDK 8 and 11 etc. are installed You can set the version you want to use by 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
[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)
・ 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
・ 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
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 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
[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
[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
-$ JAVA_HOME/jre/lib/security/java.security file settings
[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
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 and UCP Downloads page https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html
This time, I will place it in the $ HOME/oracle directory
[opc@oci-inst01 ~]$ cd ＄HOME/oracle
[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
[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 ****
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:
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
Download DataSourceSample.java for JDBC Thin connection test from "JDBC code sample" and test it. -GitHub: JDBC Code Sample
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) + " ");
}
}
}
}
-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
-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
・ ・ ・
-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
[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
[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
[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
Download UCPSample.java for JDBC Thin connection test from "JDBC code sample" and test it. -GitHub: JDBC Code Sample
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());
}
}
}
}
[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
[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
[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
● Download -JDBC and UCP Downloads page -JCE Unlimited Strength Jurisdiction Policy Files -JDBC Code Sample
-JDBC Thin Connection and Wallet · Java Connectivity with Autonomous Database (ATP or ADW) using 19c and 18.3 JDBC
Recommended Posts