Four Facets of Database Connectivity for Java Applications

Java applications require a well-formed and valid connection string along with the database credentials (username and password) to successfully establish connections to the Oracle Database. Even though, creating a connection string is easier, we see several issues posted on stackoverflow due to incorrect connection string. The goal of this blog is to highlight five ways to create connection string with their use cases.

The connection string depends on whether the database operation is performed through a simple TCP connection or one that requires mutual TLS. The connection string also changes if it requires Kerberos or RADIUS etc. A mutual TLS connection may use, either Oracle Wallets or JKS that require additional connection properties to retrieve the certificates and establish the connection to the Oracle Database. Connection string plays a crucial role while enabling high availability (HA) as there are certain connection properties which take care of number of retries, wait time, load balancing, etc., Therefore, one solution may not work for all scenarios. 

In this blog post, we will discuss five different ways of creating a connection string. Long form connection string, Easy Connect, Easy Connect Plus, TNS Alias, and using ojdbc.properties. The use cases provide general guidelines on when and how to use these connection strings.  

1. Long Form Connection String    
 
2. Easy Connect (EZConnect)    

3. Easy Connect Plus (EZConnectPlus)

4. Using TNS Alias  

Appendix: Using ojdbc.properties file for connection properties 

Long Form Connection String:

Long form connection string is recommended when forming a connection string with High Availability (HA) capabilities. The important connection descriptors related to timeouts, retries, multiple hosts, and load balance etc., are better readable in this format. Also, it is easy to create a DESCRIPTION_LIST and ADDRESS_LIST reflecting the enterprise level database setup with Real Application Cluster (RAC), Active Data Guard (ADG) or Data Guard (DG) consisting of primary and secondary hosts with service names. 

Use case: We recommend that customers use Long Form connection URL as shown in the example to successfully connect during basic startup, failover, and relocate. The connection parameters RETRY_COUNT, RETRY_DELAY, CONNECT_TIMEOUT, and TRANSPORT_CONNECT_TIMEOUT allow connection requests to wait for service availability and to connect successfully. Note that wait periods are typical during maintenance operations. Customers who want to leverage Application Continuity (AC) or Transparent Application Continuity (TAC) are highly recommended to use this connection string.

Example: The values for retries, timeouts etc., mentioned in this example are good starting points. You must change these based on your setup. 

Easy Connect (EZConnect): 

This is a simple form of connection string supporting TCP connections only. It is a simple string formed with essential information such as host name, port number, and service name. 

Use case: This should be used if you have to perform a quick connection verification to either an on-premise database or an Oracle Database XE that involves TCP connections only. Note that, Easy Connect does not provide any high availability (HA) support as it does not take any HA related connection properties. So, the suggestion is not to use Easy Connect when you are using features such as Application Continuity (AC) or Transparent Application Continuity (TAC). 

Example: jdbc:oracle:thin:@host_name:port_number/service_name

Easy Connect Plus (EZConnect Plus): 

Easy Connect Plus is added in Oracle JDBC 19c to overcome the shortcomings of Easy Connect. Easy Connect Plus supports TCPS connections, multiple hosts and ports, and connection properties can be passed as name-value pair. Earlier, connection properties could be set only at the data source programmatically, but, Easy Connect Plus allows appending connection properties to the connection string. Use question mark sign (?) to indicate the start of the name-value pairs, ampersand (&) as a delimiter, and backslash (\) to escape special characters. Refer to the technical brief Oracle Database Easy Connect Plus for examples and more details. 

Use case: Easy Connect Plus makes it easy to pass connection properties required for mutual TLS connections, wallets, load balancing, connection timeouts, DRCP, and network buffer size tuning. etc. Technically, it does provide High Availability if the required connection properties are used, but testing is required to validate your combination of parameters. So, we recommend using Long Form Connection URL for HA which has been validated and verified by many customers.

Using TNS Alias: 

The SQL*Net configuration file tnsnames.ora contains name-value pairs of database service names also known as TNS Alias associated with their connection string. The file can be located on the client side and the server side. The default location of the file tnsnames.ora on the server side is ORACLE_HOME/NETWORK/ADMIN directory. On the client side, the tnsnames.ora location is specified by the environment variable or connection property TNS_ADMIN. If the TNS_ADMIN is not specified then, it is assumed that the client side tnsnames.ora doesn't exist. On the JDBC side, TNS_ADMIN can be set in multiple ways. (1) It can be set as a system variable or environment variable. (2) Starting from Oracle JDBC 18c, it can be set as a connection property passed as part of the connection URL, (3) It can be set as a connection property programmatically. 

Use case: Using TNS Alias simplifies connecting to Oracle Autonomous Database when mutual TLS is required. Oracle Autonomous Database offers five preconfigured database services to choose from based on your workload. If TESTDB is the name of your database then, TESTDB_HIGH, TESTDB_LOW, TESTDB_MEDIUM, TESTDB_TP, TESTDB_TPURGENT are the pre-configured database services. The connection string for each of these services is present in tnsnames.ora file which is part of the client credentials zip file (wallet_TESTDB.zip) that is downloaded for establishing connections. Refer to QuickStart with Autonomous Database for more details. 

Example: 

Appendix:

Using ojdbc.properties file for connection properties: 

There are many instances when a Java application requires a connection property to be set to enable a capability. As listed in the OracleConnection Javadoc, there are network related properties, DB authentication properties, TLS/SSL properties, performance related, HA related etc., Rather than making changes to add these connection properties in the code, it is easy to add and maintain in a ojdbc.properties file. The ojdbc.properties file was introduced in Oracle JDBC 18c and the easiest way to use is, to place it in the directory where tnsnames.ora file resides. The JDBC driver searches for ojdbc.properties file in the directory set using TNS_ADMIN and picks up the connection properties while establishing connections. You can also use a different name to the file and place it in a different location but, you will need to provide the path and name of the file using a property (oracle.jdbc.config.file).

Use case: Oracle Autonomous Database comes with a pre-populated ojdbc.properties file that has properties to use either Oracle Wallets or JKS. If you are using TNS Alias then, JDBC driver will be using ojdbc.properties file to pick up the connection properties. Using an ojdbc.properties file simplifies maintenance; you only have to change this file to modify or add connection properties. The ojdbc.properties file can be used with any of the four connection forms discussed above. 

Example: Here is the snippet of ojdbc.properties file that is present in the client credentials (wallet_TESTDB.zip) for any Autonomous Database. 

Now that you are familiar with five different ways to form a connection string, you can leverage any one of these or a combination of these based on your purpose to easily establish a connection to the Oracle Database. For everything related to Oracle JDBC and UCP, refer to www.oracle.com/jdbc