Overview of Oracle Data Source Support for JNDI

The JNDI standard provides a way for applications to find and access remote services and resources. These services can be any enterprise services. However, for a JDBC application, these services would include database connections and services.

JNDI allows an application to use logical names in accessing these services, removing vendor-specific syntax from application code. JNDI has the functionality to associate a logical name with a particular source for a desired service.

All Oracle JDBC data sources are JNDI-referenceable. The developer is not required to use this functionality, but accessing databases through JNDI logical names makes the code more portable.

Features and Properties of Data Sources

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 standardjavax.sql.DataSource interface:

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

Oracle implements this interface with theOracleDataSource class in theoracle.jdbc.pool package. The overloadedgetConnection 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 thegetConnection method that takes these parameters as input. This would take priority over the property settings.

Properties of DataSource

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

Table 8-1 andTable 8-2 listOracleDataSource properties. The properties inTable 8-1 are standard properties according to the Sun Microsystems specification. The properties inTable 8-2 are Oracle extensions.

TheOracleDataSource class implements the following setter and getter methods for the standard properties:

• public synchronized void setDatabaseName(String dbname)

• public synchronized String getDatabaseName()

• public synchronized void setDataSourceName(String dsname)

• public synchronized String getDataSourceName()

• public synchronized void setDescription(String desc)

• public synchronized String getDescription()

• public synchronized void setNetworkProtocol(String np)

• public synchronized String getNetworkProtocol()

• public synchronized void setPassword(String pwd)

• public synchronized void setPortNumber(int pn)

• public synchronized int getPortNumber()

• public synchronized void setServerName(String sn)

• public synchronized String getServerName()

• public synchronized void setUser(String user)

• public synchronized String getUser()

TheOracleDataSource class implements the followingsetXXX andgetXXX methods for the Oracle extended properties:

• String getConnectionCacheName()

• java.util.Properties getConnectionCacheProperties()

• void setConnectionCacheProperties(java.util.Properties cp)

• java.util.Properties getConnectionProperties()

• void setConnectionProperties(java.util.Properties cp)

  Note:

  Use thesetConnectionProperties method to set the properties of the connection and thesetConnectionCacheProperties method to set the properties of the connection cache.

  For more information about the properties of the connection refer to"Supported Connection Properties".

  For more information about the properties of the connection refer to"Connection Cache Properties".

• boolean getConnectionCachingEnabled()

• void setImplicitCachingEnabled()

• String getDriverType()

• void setDriverType(String dt)

• String getURL()

• void setURL(String url)

• String getTNSEntryName()

• void setTNSEntryName(String tns)

• boolean getNativeXA()

• void setNativeXA(boolean nativeXA)

• String getONSConfiguration()

• void setONSConfiguration(String onsConfig)

If you are using the server-side internal driver, that is, thedriverType property is set tokprb, 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 foruser andpassword, as in the following example, in which case this takes precedence over individualuser andpassword property settings:

  jdbc:oracle:thin:scott/tiger@localhost:1521:orcl

• Settings foruser andpassword are required, either directly through the URL setting or through thegetConnection call. Theuser andpassword settings in agetConnection call take precedence over any property settings.

• If theurl property is set, then anytnsEntry,driverType,portNumber,networkProtocol,serverName, anddatabaseName property settings are ignored.

• If thetnsEntry property is set, which presumes theurl property is not set, then anydatabaseName,serverName,portNumber, andnetworkProtocol settings are ignored.

• If you are using an OCI driver, which presumes thedriverType property is set tooci, and thenetworkProtocol is set toipc, then any other property settings are ignored.

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

Creating aData Source Instance and Connecting

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 anOracleDataSource 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("dlsun999");ods.setNetworkProtocol("tcp");ods.setDatabaseName("816");ods.setPortNumber(1521);ods.setUser("scott");ods.setPassword("tiger");Connection conn = ods.getConnection();

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

Connection conn = ods.getConnection("bill", "lion");

Creating aData Source Instance, Registering with JNDI, and Connecting

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 anOracleDataSource instance, and then initialize its properties as appropriate, as in the following example:

OracleDataSource ods = new OracleDataSource();ods.setDriverType("oci");ods.setServerName("dlsun999");ods.setNetworkProtocol("tcp");ods.setDatabaseName("816");ods.setPortNumber(1521);ods.setUser("scott");ods.setPassword("tiger");

Register the Data Source

Once you have initialized the connection properties of theOracleDataSource instanceods, 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 JNDIInitialContext() 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.

Thectx.bind call binds theOracleDataSource instance to a logical JNDI name. This means that anytime after thectx.bind call, you can use the logical namejdbc/sampledb in opening a connection to the database described by the properties of theOracleDataSource instanceods. The logical namejdbc/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 subcontextjdbc under the root naming context and specifies the logical namesampledb within thejdbc subcontext.

TheContext interface andInitialContext class are in the standardjavax.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 JavaObject, toOracleDataSource and then using itsgetConnection method to open the connection.

Here is an example:

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

SupportedConnection Properties

For a detailed list of connection properties that Oracle JDBC drivers support, see the Javadoc.

Using Roles for SYS Login

To specify the role for theSYS login, use theinternal_logon connection property. To logon asSYS, set theinternal_logon connection property toSYSDBA orSYSOPER.

For a bequeath connection, we can get a connection asSYS by setting theinternal_logon property. For a remote connection, we need additional password file setting procedures.

Configuring Database Remote Login

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

1. Set a password file on the server-side or on the remote database, using theorapwd password utility. You can add a password file for usersys as follows:

   • In UNIX

     orapwd file=$ORACLE_HOME/dbs/orapw entries=200Enter password:password

   • In Microsoft Windows

     orapwd file=%ORACLE_HOME%\database\PWDsid_name.ora entries=200Enter password:password

   file must be the name of the password file.password is the password for the userSYS. It can be altered using theALTER USER statement in SQL Plus. You should setentries to a value higher than the number of entries you expect.

   The syntax for the password file name is different on Microsoft Windows and UNIX.

   See Also:

   Oracle Database Administrator's Guide

2. Enable remote login assysdba. This step grantsSYSDBA andSYSOPER system privileges to individual users and lets them connect as themselves.

   Stop the database, and add the following line toinitservice_name.ora, in UNIX, orinit.ora, in Microsoft Windows:

   remote_login_passwordfile=exclusive

   Theinitservice_name.ora file is located atORACLE_HOME/dbs/ and also atORACLE_HOME/admin/db_name/pfile/. Ensure that you keep the two files synchronized.

   Theinit.ora file is located at%ORACLE_BASE%\ADMIN\db_name\pfile\.

3. Change the password for theSYS user. This is an optional step.

   PASSWORD sys       Changing password for sysNew password:passwordRetype new password:password

4. Verify whetherSYS has theSYSDBA privilege.

   SQL> select * from v$pwfile_users;USERNAME                         SYSDB        SYSOP----------------------           ---------    ---------SYS                              TRUE         TRUE

5. Restart the remote database.

//This example works regardless of language settings of the database. /** case of remote connection using sys **/import java.sql.*;import oracle.jdbc.*;import oracle.jdbc.pool.*;// create an OracleDataSourceOracleDataSource ods = new OracleDataSource();// set connection propertiesjava.util.Properties prop = new java.util.Properties();prop.put("user", "sys");prop.put("password", "sys");prop.put("internal_logon", "sysoper");ods.setConnectionProperties(prop);// set the url// the url can use oci driver as well as:// url = "jdbc:oracle:oci8:@inst1"; the inst1 is a remote databaseString url = "jdbc:oracle:thin:@//myHost:1521/service_name";ods.setURL(url);// get the connectionConnection conn = ods.getConnection();...

Bequeath Connection and SYS Logon

The following example illustrates how to use theinternal_logon andSYSDBA arguments to specify theSYS 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 instanceOracleDataSource ods = new OracleDataSource(); // set neccessary propertiesjava.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 connectionString url = "jdbc:oracle:oci8:@";ods.setURL(url); // retrieve the connectionConnection conn = ods.getConnection();...

Properties for Oracle Performance Extensions

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

• Setting thedefaultRowPrefetch property is equivalent to callingsetDefaultRowPrefetch.

• Setting theremarksReporting property is equivalent to callingsetRemarksReporting.

  See Also:

  "DatabaseMetaData TABLE_REMARKS Reporting"

• Setting thedefaultBatchValue property is equivalent to callingsetDefaultExecuteBatch

  See Also:

  "Oracle Update Batching"

Example

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

//import packages and register the driverimport java.sql.*;import java.math.*;import oracle.jdbc.*;import oracle.jdbc.pool.OracleDataSource;//specify the properties objectjava.util.Properties info = new java.util.Properties();info.put ("user", "scott");info.put ("password", "tiger");info.put ("defaultRowPrefetch","20");info.put ("defaultBatchValue", "5");//specify the datasource object OracleDataSource ods = new OracleDataSource();ods.setURL("jdbc:oracle:thin:@//myhost:1521/orcl");ods.setUser("scott");ods.setPassword("tiger");ods.setConnectionProperties(info);...

Note:

• The brackets indicate that theusername/password pair is optional.

• kprb, the internal server-side driver, uses an implicit connection. Database URLs for the server-side driver end after thedriver_type.

Note:

• Starting Oracle Database 10g, Oracle Service IDs are not supported.

• Starting Oracle Database 10g, Oracle no longer supports Oracle Names as a naming method.

Table 8-3 Supported Database Specifiers

url="jdbc:oracle:thin:@(DESCRIPTION=  (LOAD_BALANCE=on)(ADDRESS_LIST=  (ADDRESS=(PROTOCOL=TCP)(HOST=host1) (PORT=1521)) (ADDRESS=(PROTOCOL=TCP)(HOST=host2)(PORT=1521))) (CONNECT_DATA=(SERVICE_NAME=service_name)))"

"jdbc:oracle:oci:@(DESCRIPTION=  (ADDRESS=(PROTOCOL=TCP)(HOST=cluster_alias)    (PORT=1521))    (CONNECT_DATA=(SERVICE_NAME=service_name)))"

"jdbc:oracle:thin:scott/tiger@//myhost:1521/myservicename"

"jdbc:oracle:oci:scott/tiger/@"

Note:

Note:

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

Note: