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:
  1. Enable Oracle Net Services (SQL*Net) for your service. See Enabling Oracle Net Services (SQL*Net) Access for Client Applications.
  2. 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 Downloading Client Credentials
  3. Set the path of tnsnames.ora file using oracle.net.tns_admin system property.
  4. Enable the server DN match by setting oracle.net.ssl_server_dn_match system property to true.
  5. Set the location for keystore and truststore JKS files, using javax.net.ssl.trustStore and javax.net.ssl.keyStore system properties.
  6. Set the password for JKS using javax.net.ssl.keyStorePassword and javax.net.ssl.trustStorePassword. Use the password provided while downloading the client credentials.
  7. Set the ssl property by setting oracle.net.ssl_version to 1.2.
  8. 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.