By using the data source functionality with JNDI, you do not need to register the vendor-specific JDBC driver class name and you can use logical names for URLs and other properties. This ensures that the code for opening database connections is portable to other environments.

The DataSource Interface and Oracle Implementation

A JDBC data source is an instance of a class that implements the standard javax.sql.DataSource interface:

public interface DataSource
{
   Connection getConnection() throws SQLException;
   Connection getConnection(String username, String password)
      throws SQLException;
   ...
}

Oracle implements this interface with the OracleDataSource class in the oracle.jdbc.pool package. The overloaded getConnection method returns a connection to the database.

To use other values, you can set properties using appropriate setter methods. For alternative user names and passwords, you can also use the getConnection method that takes these parameters as input. This would take priority over the property settings.

Properties of DataSource

The OracleDataSource class, as with any class that implements the DataSource interface, provides a set of properties that can be used to specify a database to connect to. These properties follow the JavaBeans design pattern.

The following tables list the OracleDataSource standard properties and Oracle extensions respectively.

Use the setConnectionProperties method to set the properties of the connection and the setConnectionCacheProperties method to set the properties of the connection cache.

If you are using the server-side internal driver, that is, the driverType property is set to kprb, then any other property settings are ignored.

If you are using the JDBC Thin or OCI driver, then note the following:

• A URL setting can include settings for user and password, as in the following example, in which case this takes precedence over individual user and password property settings:

  jdbc:oracle:thin:HR/hr@localhost:5221:orcl
  

• Settings for user and password are required, either directly through the URL setting or through the getConnection call. The user and password settings in a getConnection call take precedence over any property settings.

• If the url property is set, then any tnsEntry, driverType, portNumber, networkProtocol, serverName, and databaseName property settings are ignored.

• If the tnsEntry property is set, which presumes the url property is not set, then any databaseName, serverName, portNumber, and networkProtocol settings are ignored.

• If you are using an OCI driver, which presumes the driverType property is set to oci, and the networkProtocol is set to ipc, then any other property settings are ignored.

Also, note that getConnectionCacheName() will return the name of the cache only if the ConnectionCacheName property of the data source is set after caching is enabled on the data source.

This section shows an example of the most basic use of a data source to connect to a database, without using JNDI functionality. Note that this requires vendor-specific, hard-coded property settings.

Create an OracleDataSource instance, initialize its connection properties as appropriate, and get a connection instance, as in the following example:

OracleDataSource ods = new OracleDataSource();
ods.setDriverType("oci");
ods.setServerName("localhost");
ods.setNetworkProtocol("tcp");
ods.setDatabaseName(<database_name>);
ods.setPortNumber(5221);
ods.setUser("HR");
ods.setPassword("hr");
Connection conn = ods.getConnection();

Or, optionally, override the user name and password, as follows:

Connection conn = ods.getConnection("OE", "oe");

This section exhibits JNDI functionality in using data sources to connect to a database. Vendor-specific, hard-coded property settings are required only in the portion of code that binds a data source instance to a JNDI logical name. From that point onward, you can create portable code by using the logical name in creating data sources from which you will get your connection instances.

Initialize Data Source Properties

Create an OracleDataSource instance, and then initialize its properties as appropriate, as in the following example:

OracleDataSource ods = new OracleDataSource();
ods.setDriverType("oci");
ods.setServerName("localhost");
ods.setNetworkProtocol("tcp");
ods.setDatabaseName("816");
ods.setPortNumber(5221);
ods.setUser("HR");
ods.setPassword("hr");

Register the Data Source

Once you have initialized the connection properties of the OracleDataSource instance ods, as shown in the preceding example, you can register this data source instance with JNDI, as in the following example:

Context ctx = new InitialContext();
ctx.bind("jdbc/sampledb", ods);

Calling the JNDI InitialContext() constructor creates a Java object that references the initial JNDI naming context. System properties, which are not shown, instruct JNDI which service provider to use.

The ctx.bind call binds the OracleDataSource instance to a logical JNDI name. This means that anytime after the ctx.bind call, you can use the logical name jdbc/sampledb in opening a connection to the database described by the properties of the OracleDataSource instance ods. The logical name jdbc/sampledb is logically bound to this database.

The JNDI namespace has a hierarchy similar to that of a file system. In this example, the JNDI name specifies the subcontext jdbc under the root naming context and specifies the logical name sampledb within the jdbc subcontext.

The Context interface and InitialContext class are in the standard javax.naming package.

Open a Connection

To perform a lookup and open a connection to the database logically bound to the JNDI name, use the logical JNDI name. Doing this requires casting the lookup result, which is otherwise a Java Object, to OracleDataSource and then using its getConnection method to open the connection.

Here is an example:

OracleDataSource odsconn = (OracleDataSource)ctx.lookup("jdbc/sampledb");
Connection conn = odsconn.getConnection();

For a detailed list of connection properties that Oracle JDBC drivers support, see the Oracle Database JDBC Java API Reference.

To specify the role for the SYS login, use the internal_logon connection property. To log on as SYS, set the internal_logon connection property to SYSDBA or SYSOPER.

For a bequeath connection, we can get a connection as SYS by setting the internal_logon property. For a remote connection, we need additional password file setting procedures.

Before the JDBC Thin driver can connect to the database as SYSDBA, you must configure the user, because Oracle Database security system requires a password file for remote connections as an administrator. Perform the following:

The following example illustrates how to use the internal_logon and SYSDBA arguments to specify the SYS login. This example works regardless of the database's national-language settings of the database.

/** Example of bequeath connection **/
import java.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;
 
// create an OracleDataSource instance
OracleDataSource ods = new OracleDataSource();
 
// set neccessary properties
java.util.Properties prop = new java.util.Properties();
prop.put("user", "sys");
prop.put("password", "sys");
prop.put("internal_logon", "sysdba");
ods.setConnectionProperties(prop);
 
// the url for bequeath connection
String url = "jdbc:oracle:oci8:@";
ods.setURL(url);
 
// retrieve the connection
Connection conn = ods.getConnection();
... 

Some of the connection properties are for use with Oracle performance extensions. Setting these properties is equivalent to using corresponding methods on the OracleConnection object, as follows:

• Setting the defaultRowPrefetch property is equivalent to calling setDefaultRowPrefetch.

• Setting the remarksReporting property is equivalent to calling setRemarksReporting.

  See Also:

  "About Reporting DatabaseMetaData TABLE_REMARKS"

Example

The following example shows how to use the put method of the java.util.Properties class, in this case, to set Oracle performance extension parameters.

//import packages and register the driver
import java.sql.*;
import java.math.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;

//specify the properties object
java.util.Properties info = new java.util.Properties();
info.put ("user", "HR");
info.put ("password", "hr");
info.put ("defaultRowPrefetch","20");
info.put ("defaultBatchValue", "5");

//specify the datasource object 
OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:thin:@//localhost:5221/orcl");
ods.setUser("HR");
ods.setPassword("hr");
ods.setConnectionProperties(info);
...

This release of Oracle JDBC drivers supports Internet Protocol Version 6 (IPv6) addresses in the JDBC URL and machine names that resolve to IPv6 addresses. IPv6 is a new Network layer protocol designed by the Internet Engineering Task Force (IETF) to replace the current version of Internet Protocol, Internet Protocol Version 4 (IPv4). The primary benefit of IPv6 is a large address space, derived from the use of 128-bit addresses. IPv6 also improves upon IPv4 in areas such as routing, network auto configuration, security, quality of service, and so on.

If you want to use a literal IPv6 address in a URL, then you should enclose the literal address enclosed in a left bracket ([) and a right bracket (]). For example: [2001:0db8:7654:3210:FEDC:BA98:7654:3210]. So, a JDBC URL, using an IPv6 address will look like the following:

  jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)
    (HOST=[2001:0db8:7654:3210:FEDC:BA98:7654:3210])(PORT=5521))
    (CONNECT_DATA=(SERVICE_NAME=sales.example.com))

Table 8-3, shows the possible database specifiers, listing which JDBC drivers support each specifier.

Thin-style service names are supported only by the JDBC Thin driver. The syntax is:

@//host_name:port_number/service_name

For example:

jdbc:oracle:thin:HR/hr@//localhost:5221/orcl

Starting from Oracle Database 12c Release 1 (12.1.0.2), there is a new connection attribute RETRY_DELAY, which specifies the delay between connection retries in seconds. The following code snippet shows how to use this attribute:

(DESCRIPTION_LIST=
  (DESCRIPTION=
   (CONNECT_TIMEOUT=10)(RETRY_COUNT=3)(RETRY_DELAY=3)
   (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=tcp)(HOST=myhost1)(PORT=1521))
    (ADDRESS=(PROTOCOL=tcp)(HOST=myhost2)(PORT=1521)))
   (CONNECT_DATA=(SERVICE_NAME=example1.com)))
  (DESCRIPTION=
   (CONNECT_TIMEOUT=60)(RETRY_COUNT=1)(RETRY_DELAY=5)
   (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=tcp)(HOST=myhost3)(PORT=1521))
    (ADDRESS=(PROTOCOL=tcp)(HOST=myhost4)(PORT=1521)))
   (CONNECT_DATA=(SERVICE_NAME=example2.com))))

You can find the available TNSNAMES entries listed in the tnsnames.ora file on the client computer from which you are connecting. On Windows, this file is located in the ORACLE_HOME\NETWORK\ADMIN directory. On UNIX systems, you can find it in the ORACLE_HOME directory or the directory indicated in your TNS_ADMIN environment variable.

For example, if you want to connect to the database on host myhost as user HR with password hr that has a TNSNAMES entry of MyHostString, then write the following:

OracleDataSource ods = new OracleDataSource();
ods.setTNSEntryName("MyTNSAlias");
ods.setUser("HR");
ods.setPassword("hr");
ods.setDriverType("oci");
Connection conn = ods.getConnection();

The oracle.net.tns_admin system property must be set to the location of the tnsnames.ora file so that the JDBC Thin driver can locate the tnsnames.ora file. For example:

System.setProperty("oracle.net.tns_admin", "c:\\Temp");
String url = "jdbc:oracle:thin:@tns_entry";

java -Doracle.net.tns_admin=$ORACLE_HOME/network/admin

An example of database specifier using the Lightweight Directory Access Protocol (LDAP) syntax is as follows:

"jdbc:oracle:thin:@ldap://ldap.example.com:7777/sales,cn=OracleContext,dc=com"

When using SSL, change this to:

"jdbc:oracle:thin:@ldaps://ldap.example.com:7777/sales,cn=OracleContext,dc=com"

The JDBC Thin driver supports failover of a list of LDAP servers during the service name resolution process, without the need for a hardware load balancer. Also, client-side load balancing is supported for connecting to LDAP servers. A list of space separated LDAP URLs syntax is used to support failover and load balancing.

When a list of LDAP URLs is specified, both failover and load balancing are enabled by default. The oracle.net.ldap_loadbalance connection property can be used to disable load balancing, and the oracle.net.ldap_failover connection property can be used to disable failover.

An example, which uses failover, but with client-side load balancing disabled, is as follows:

Properties prop = new Properties();
String url = "jdbc:oracle:thin:@ldap://ldap1.example.com:3500/cn=salesdept,cn=OracleContext,dc=com/salesdb " +
"ldap://ldap2.example.com:3500/cn=salesdept,cn=OracleContext,dc=com/salesdb " +
"ldap://ldap3.example.com:3500/cn=salesdept,cn=OracleContext,dc=com/salesdb";

prop.put("oracle.net.ldap_loadbalance", "OFF" );
OracleDataSource ods = new OracleDataSource();
ods.setURL(url);
ods.setConnectionProperties(prop);

The JDBC Thin driver supports LDAP nonanonymous bind. A set of JNDI environment properties, which contains authentication information, can be specified for a data source. If an LDAP server is configured as not to allow anonymous bind, then authentication information must be provided to connect to the LDAP server. The following example shows a simple clear-text password authentication:

String url = "jdbc:oracle:thin:@ldap://ldap.example.com:7777/sales,cn=salesdept,cn=OracleContext,dc=com";

Properties prop = new Properties();
prop.put("java.naming.security.authentication", "simple");
prop.put("java.naming.security.principal","cn=salesdept,cn=OracleContext,dc=com");
prop.put("java.naming.security.credentials", "mysecret");

OracleDataSource ods = new OracleDataSource();
ods.setURL(url);
ods.setConnectionProperties(prop);

Since JDBC passes down the three properties to JNDI, the authentication mechanism chosen by client is consistent with how these properties are interpreted by JNDI. For example, if the client specifies authentication information without explicitly specifying the java.naming.security.authentication property, then the default authentication mechanism is "simple".