JDBC Thin Connections and Wallets

Applications that use JDBC Thin driver require the Oracle database credentials including the Oracle wallets or Java Key Store (JKS) files when connecting to the Autonomous Transaction Processing. These files are included in the client credentials .zip file that is downloaded from the Autonomous Transaction Processing service console Administration tab (or by clicking DB Connection and then clicking Download).

There are several methods you can use to make connections using wallets:

  • Applications can provide support for wallets in their connection properties.

  • The wallet location can be included in a JDBC URL (this requires Oracle JDBC thin driver 18.1 or higher).

  • The wallet location can be set in the ojdbc.properties file (this requires Oracle JDBC thin driver 18.1 or higher).

  • Java properties can be set prior to starting the application (this requires Oracle JDBC thin driver 12.2.0.1 or higher).

See Download Client Credentials (Wallets) for information on downloading client credentials for Autonomous Transaction Processing.

Using Applications with Support for Wallets

Some applications allow you to choose a credentials file as part of the connection properties.

For example, in SQL Developer 18.3 and higher in the Connection Type field select the value Cloud Wallet that allows you to enter a credentials file in the Configuration File field. SQL Developer then presents a list of the available connections in the Service field (the connections are included in the credentials files).

If your application provides support for wallets or provides specific support for an Autonomous Transaction Processing connection, for example, Oracle SQL Developer, Oracle recommends that you use that type of connection.

Using a JDBC URL Connection String with JDBC Thin Driver

Applications that use JDBC Thin driver require the Oracle database credentials including the Oracle wallets or Java Key Store (JKS) files when connecting to an Autonomous Transaction Processing database. These files are included in the client credentials file that you download from the Autonomous Transaction Processing service console.

The StatementSample.java and UCPSample.java GitHub code samples may be used. The JDBCSampleData.sql script creates and populates the tables used by these code samples.

Note:

UCPSample requires ucp.jar (the Universal Connection Pool) in the classpath

You can download the Oracle JDBC drivers from Get Oracle JDBC drivers from the Oracle Technology Network.

Simplified Java Cloud Connectivity

With the JDBC Thin driver in Oracle database 18c (Release 18.3), connecting to an Autonomous Transaction Processing database has been simplified. The following considerations apply to both approaches (Oracle Wallet or JKS):

  • Use the tns alias in the JDBC URL in the Java code.

    For example, use, atpconnection_low. See Predefined Database Service Names for Autonomous Transaction Processing for more details on the tns alias that corresponds to your service. For example:

    DEFAULT_URL="jdbc:oracle:thin:@atpconnection_low"
  • Use TNS_ADMIN

    Use one of the following approaches to specify the location of the tnsnames.ora and ojdbc.properties files:

    • Through the TNS_ADMIN environment variable. For example:

      export TNS_ADMIN=
    • As system properties. For example:

      -Doracle.net.tns_admin=
    • Specified in the URL. For example:

      final static String
      DB_URL="jdbc:oracle:thin:@atpconnection_low?TNS_ADMIN=.";
  • Setting the driver configuration file ojdbc.properties for Oracle Wallet or Java Key Store.

Using Oracle Wallet

If you choose to use the Oracle Wallet for Java connectivity to Autonomous Transaction Processing using JDBC Thin, the ojdbc.properties file must be configured as follows:

oracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=${TNS_ADMIN})))

The auto-login wallet part of Autonomous Transaction Processing downloaded client credentials zip removes the need for your application to use username/password authentication.

Make sure to have oraclepki.jar , osdt_core.jar, and osdt_cert.jar, in the classpath as in the following invocation command:

java -cp ../ojdbc8.jar:./oraclepki.jar:./osdt_core.jar:./osdt_cert.jar:. StatementSample

Using Java Key Store

If you prefer using SSL connectivity instead of Oracle Wallet, the keystore and truststore files and their respective password must be specified in the ojdbc.properties file as follows:

oracle.net.tns_admin=.
javax.net.ssl.keyStore=keystore.jks
javax.net.ssl.keyStorePassword=password
javax.net.ssl.trustStore=truststore.jks
javax.net.ssl.trustStorePassword=password
oracle.net.ssl_server_dn_match=true

Note:

The password for the keyStorePassword and trustStorePassword is the password you enter when you download the wallet file. See Download Client Credentials (Wallets) for information on downloading the credentials zip file.

For example, a sample command invocation:

java -cp ../ojdbc8.jar:. StatementSample

Setting Java Properties with JDBC Thin Driver 12.2

If you are using the JDBC thin driver 12.2.0.2, Java properties must be set prior to starting the application. Properties are typically set in the startup script of the application.

If you are using Oracle 12.1.0.2 JDBC Thin Driver with JDK8, set the following properties:

Note:

These are Windows system examples. Add a / continuation character if you are setting –D properties on multiple lines on UNIX ( Linux or a Mac).
java 
-Doracle.net.tns_admin=/oracle/ATPC/wallet_sales
-Djavax.net.ssl.trustStore=truststore.jks
-Djavax.net.ssl.trustStorePassword=**********
-Djavax.net.ssl.keyStore=keystore.jks    
-Djavax.net.ssl.keyStorePassword=************   
-Doracle.net.ssl_server_dn_match=true    
-Doracle.net.ssl_version=1.2

If you are using Oracle 12.1.0.2 JDBC Thin Driver with JDK7, set the following properties:

java 
-Doracle.net.tns_admin=/home/user1/cloud   
-Djavax.net.ssl.trustStore=truststore.jks   
-Djavax.net.ssl.trustStorePassword=************   
-Djavax.net.ssl.keyStore=keystore.jks    
-Djavax.net.ssl.keyStorePassword=************   
-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

Where:

/home/user1/cloud is the directory where the wallet files were unzipped.

************ is the credential file password that you receive with the wallet zip file.

The following is an example of a Java application startup script that includes the properties you need to set to connect the database using a wallet and JDK 8:

java
-Doracle.net.tns_admin=/oracle/ATPC/wallet_sales
-Djavax.net.ssl.trustStore=truststore.jks
-Djavax.net.ssl.trustStorePassword=**********
-Djavax.net.ssl.keyStore=keystore.jks    
-Djavax.net.ssl.keyStorePassword=************
-Doracle.net.ssl_server_dn_match=true    
-Doracle.net.ssl_version=1.2
my_java_application

JDBC Thin Connections with an HTTP Proxy

If the client is behind a firewall and your network configuration requires an HTTP proxy to connect to the internet, you need to use the JDBC Thin Client 18.1 which enables connections through HTTP proxies.

To connect to Autonomous Transaction Processing through an HTTP proxy perform the following steps to update your tnsnames.ora file:

  1. Add the HTTP proxy hostname and port to the connection definitions in tnsnames.ora. You need to add the https_proxy and https_proxy_port parameters in the address section of connection definitions. For example, the following sets the HTTP proxy to proxyhostname and the HTTP proxy port to 80; replace these values with your HTTP proxy information:

ATPC1_high =
       (description=
             (address=
                   (https_proxy=proxyhostname)(https_proxy_port=80)(protocol=tcps)(port=1522)(host=atpc.example.oraclecloud.com)
             )
             (connect_data=(service_name=atpc1_high.atpc.oraclecloud.com)
             )
             (security=(ssl_server_cert_dn="atpc.example.oraclecloud.com,OU=Oracle BMCS US,O=Oracle Corporation,L=Redwood City,ST=California,C=US")
             )
       )

Note:

Configuring tnsnames.ora for the HTTP proxy may not be enough depending on your organization's network configuration and security policies. For example, some networks require a username and password for the HTTP proxy. JDBC Thin Client versions earlier than 18.1 also do not support connections through HTTP proxies. In such cases contact your network administrator to open outbound connections to hosts in the oraclecloud.com domain using port 1522 without going through an HTTP proxy.