Connect with JDBC Thin Driver and UCP

This section describes the software prerequisites and property settings required for connecting to Oracle Database Exadata Express Cloud Service using JDBC thin driver.

Topics

Software Requirements for JDBC Thin and UCP Connections

Before connecting with JDBC Thin driver to the Oracle Database Exadata Express Cloud Service, you must satisfy the following software requirements.

You must have JDK 8 (any version JDK8u71 or higher) or JDK 7 (JDK7u80).
Download the JCE Unlimited Strength Jurisdiction Policy Files from here. Follow installation notes in the README.
Download the 12.1.0.2 JDBC Thin driver (ojdbc7.jar ) and UCP (ucp.jar) from Oracle Technology Network.

Note:
You must use a patched version of 12.1.0.2 JDBC Thin driver (ojdbc7.jar) from Oracle Technology Cloud Download .

See JDBC/UCP Connectivity for Java SE, Java EE Containers, and Java Cloud Service and JDBC/UCP Connectivity for Java IDEs for more details.

Property Settings for JDBC Thin Driver and UCP

Make sure you have satisfied software prerequisites and property setting requirements, before connecting with JDBC Thin driver and UCP to Oracle Database Exadata Express Cloud Service.

Note:

These properties can be set as system properties or connection properties. For further details on setting these as connection level properties, see Oracle Database JDBC Java API Reference 12c Release 2(12.2.0.1).

To set properties as system properties:

Enable Oracle Net Services (SQL*Net) for your service. See Enable Oracle Net Services (SQL*Net) Access for Client Applications.
Make sure to place all required configuration files tnsnames.ora, truststore.jks, and keystore.jks (created while enabling Oracle Net Services (SQL*Net) access for client applications) to an accessible location. These credentials are downloaded by clicking Download Client Credentials from the Service Console. See Download Client Credentials
Set the path of tnsnames.ora file using oracle.net.tns_admin system property.
Enable the server DN match by setting oracle.net.ssl_server_dn_match system property to true.
Set the location for keystore and truststore JKS files, using javax.net.ssl.trustStore and javax.net.ssl.keyStore system properties.
Set the password for JKS using javax.net.ssl.keyStorePassword and javax.net.ssl.trustStorePassword. Use the password provided while downloading the client credentials.
Set the ssl property by setting oracle.net.ssl_version to 1.2.
If using JDK7, set oracle.net.ssl_cipher_suites=(TLS_RSA_WITH_AES_256_CBC_SHA256)

Examples to set properties using –D option for Java:

Using 12.1.0.2 JDBC Thin Driver with JDK8:

java -Doracle.net.tns_admin=/home/user1/cloud   
-Djavax.net.ssl.trustStore=truststore.jks
-Djavax.net.ssl.trustStorePassword=password   
-Djavax.net.ssl.keyStore=keystore.jks    
-Djavax.net.ssl.keyStorePassword=password   
-Doracle.net.ssl_server_dn_match=true    
-Doracle.net.ssl_version=1.2 
SelectTest

Using 12.1.0.2 JDBC Thin Driver with JDK7:

java -Doracle.net.tns_admin=/home/user1/cloud   
-Djavax.net.ssl.trustStore=truststore.jks   
-Djavax.net.ssl.trustStorePassword=password   
-Djavax.net.ssl.keyStore=keystore.jks    
-Djavax.net.ssl.keyStorePassword=password   
-Doracle.net.ssl_server_dn_match=true    
-Doracle.net.ssl_version=1.2
-Doracle.net.ssl_cipher_suites=TLS_RSA_WITH_AES_256_CBC_SHA256 
SelectTest

Replace “password” in the above examples with your wallet password provided during wallet download.

Sample code for UCPSample.java:

import java.sql.Connection; 
import java.sql.DatabaseMetaData; 
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;

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


public class UCPSample {
 // Connection URL. 'dbaccess' is from 'tnsnames.ora' file
  final static String DB_URL= "jdbc:oracle:thin:@dbaccess";
  final static String DB_USER = "pdb_admin";
  final static String DB_PASSWORD = "password";
  final static String CONN_FACTORY_CLASS_NAME = "oracle.jdbc.pool.OracleDataSource";


public static void main(String args[]) throws SQLException {
  // 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");

 // Set pool properties
 pds.setInitialPoolSize(5);
 pds.setMinPoolSize(5);
 pds.setMaxPoolSize(20);

 // Get a connection to the cloud database
 try (Connection connection = pds.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());

    // Statement and ResultSet are AutoCloseable and closed automatically.
  try (Statement statement = connection.createStatement()) {
    try (ResultSet resultSet = statement
           .executeQuery("select sysdate from dual")) {
     while (resultSet.next())
       System.out.println("Today's Date is: " + resultSet.getString(1));
    }
   }
   System.out.println("Successfully connected to a cloud database");
  }
 }
}

See JDBC/UCP Connectivity for Java SE, Java EE Containers, and Java Cloud Service and JDBC/UCP Connectivity for Java IDEs for more details.