Connection Pooling and Caching

Data Sources

The JDBC 2.0 extension API introduces the concept of data sources, which are standard, general-use objects for specifying databases or other resources to use. Data sources can optionally be bound to Java Naming and Directory Interface (JNDI) entities so that you can access databases by logical names, for convenience and portability.

This functionality is a more standard and versatile alternative to the connection functionality described under "Open a Connection to a Database". The data source facility provides a complete replacement for the previous JDBC DriverManager facility.

You can use both facilities in the same application, but ultimately developers will be encouraged to use data sources for their connections, regardless of whether connection pooling or distributed transactions are required. Eventually, Sun Microsystems will probably deprecate DriverManager and related classes and functionality.

For further introductory and general information about data sources and JNDI, refer to the Sun Microsystems specification for the JDBC 2.0 Optional Package.

A Brief Overview of Oracle Data Source Support for JNDI

The standard Java Naming and Directory Interface, or JNDI, provides a way for applications to find and access remote services and resources. These services can be any enterprise services, but for a JDBC application 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.

Note:
Using JNDI functionality requires the file jndi.zip to be in the CLASSPATH. This file is included in the Oracle database plus JServer option product CD, but is not included in the classes12.zip and classes111.zip files. You must add it to the CLASSPATH separately. (You can also obtain it from the Sun Microsystems Web site, but it is advisable to use the version from Oracle, because that has been tested with the Oracle drivers.)

Data Source Features and Properties

"First Steps in JDBC" includes sections on how to use the JDBC DriverManager class to register driver classes and open database connections. The problem with this model is that it requires your code to include vendor-specific class names, database URLs, and possibly other properties, such as machine names and port numbers.

With JDBC 2.0 data source functionality, using 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 allows your application code for opening database connections to be portable to other environments.

Data Source 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 an OracleConnection instance, optionally taking a user name and password as input.

To use other values, you can set properties using appropriate setter methods discussed in the next section. For alternative user names and passwords, you can also use the getConnection() signature that takes these as input--this would take priority over the property settings.

Note:
The OracleDataSource class and all subclasses implement the java.io.Serializable and javax.naming.Referenceable interfaces.

Data Source Properties

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.

Table 15-1 and Table 15-2 document OracleDataSource properties. The properties in Table 15-1 are standard properties according to the Sun Microsystems specification. (Be aware, however, that Oracle does not implement the standard roleName property.) The properties in Table 15-2 are Oracle extensions.

Table 15-1 Standard Data Source Properties

Name	Type	Description
`databaseName`	`String`	name of the particular database on the server; also known as the "SID" in Oracle terminology
`dataSourceName`	`String`	name of the underlying data source class (for connection pooling, this is an underlying pooled connection data source class; for distributed transactions, this is an underlying XA data source class)
`description`	`String`	description of the data source
`networkProtocol`	`String`	network protocol for communicating with the server; for Oracle, this applies only to the OCI drivers and defaults to `tcp` (Other possible settings include `ipc`. See the Net8 Administrator's Guide for more information.)
`password`	`String`	login password for the user name
`portNumber`	`int`	number of the port where the server listens for requests
`serverName`	`String`	name of the database server
`user`	`String`	name for the login account

The OracleDataSource 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()

Note that there is no getPassword() method, for security reasons.

Table 15-2 Oracle Extended Data Source Properties

Name	Type	Description
`driverType`	`String`	category of the Oracle JDBC driver you are using--can be `oci8`, `thin`, or `kprb` (server-side internal)
`url`	`String`	URL of the database connect string; as a convenience for customers migrating from older versions of Oracle JDBC, you can use this in place of the Oracle `tnsEntry` and `driverType` properties and the standard `portNumber`, `networkProtocol`, `serverName`, and `databaseName` properties
`tnsEntry`	`String`	TNS entry name--relevant only for OCI drivers and assumes an Oracle client installation with `TNS_ADMIN` environment variable set appropriately

The OracleDataSource class implements the following setter and getter methods for the Oracle extended properties:

public synchronized void setDriverType(String dt)
public synchronized String getDriverType()
public synchronized void setURL(String url)
public synchronized String getURL()
public synchronized void setTNSEntryName(String tns)
public synchronized String getTNSEntryName()

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

If you are using a Thin or OCI driver, 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:scott/tiger@localhost:1521: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 oci8) and the networkProtocol is set to ipc, then any other property settings are ignored.

Creating a Data Source Instance and Connecting (without JNDI)

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("oci8");
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:

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

For a complete sample program, see "Data Source without JNDI--DataSource.java".

Creating a Data 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.

For a complete sample, see "Data Source with JNDI--DataSourceJNDI.java".

Note:
Creating and registering data sources is typically handled by a JNDI administrator, not in a JDBC application.

Initialize Connection Properties

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

...
OracleDataSource ods = new OracleDataSource();

ods.setDriverType("oci8");
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 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 that 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 name space 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.

Notes:
The JDBC 2.0 Specification requires that all JDBC data sources be registered in the jdbc naming subcontext of a JNDI namespace or in a child subcontext of the jdbc subcontext.

Register the Data Source for the Oracle8i JNDI

To bind the OracleDataSource, OracleConnectionPoolDataSource and OracleXADataSource instances into JNDI, use the bindds command of the Oracle8i sess_sh (session shell) tool. The sess_sh tool is furnished with Oracle8i as an interactive interface to the session namespace of a database instance. The sess_sh tool offers shell commands that give the session namespace much of the "look and feel" of a UNIX file system. (Once you start the sess_sh tool, a $ command-line prompt appears.) See the Oracle8i Java Tools Reference manual for a complete description on using the bindds command.

Use the following syntax to bind a data source in an Oracle8i JNDI namespace:

$ bindds <datasource_name> [options]

Table 15-3 describes the bindds command options.

Table 15-3 Possible Data Source Options Using the bindds Command

Option	Description
`-h, -help`	prints out information about `bindds` options
`-version`	prints out version information
`-describe`	prints out a description of what the command does
`-rebind`	forces a bind if the data source object is already bound
`-g, -G, -grant <schema1,schema2...>`	adds `read` or `execute` schemas to the new object and directory
`-rg, -rG, -recursiveGrant <schema1,schema2...>`	adds `read` or `execute` schemas to the new data source object and directory
`-dstype <pool/xa/jta>`	binds either `OracleDataSource`, `OraclePooled`, `OracleXA`, or `OracleJTADataSource` (default is `OracleDataSource`)
`-url <jdbc_url>`	specifies the URL (for example: `jdbc:oracle:oci8:@`)
`-host <host_name>`	specifies the fully qualified host name of the data source
`-port <port_number>`	specifies the port on which this data source listens
`-sid <database_name>`	specifies the database ID
`-driver <JDBC_driver>`	specifies a JDBC driver, such as KPRB, OCI8, or Thin
`-dblink <dblink>`	specifies the fully qualified database link
`-protocol <protocol>`	specifies the protocol used to connect to the datasource
`-tns <tnsEntry>`	specifies the Transparent Network Substrate (TNS) entry in the `tnsnames.ora` file (see the Net8 Administrator's Guide)
`-u <user>, -user <user>`	specifies the user name for connection to the data source
`-p <pwd>, -password <pwd>`	specifies the password for the user name

The following bindds example creates an Oracle data source object that is bound as jdbcdb (the logical name) into an Oracle8i JNDI namespace.

$ bindds jdbcdc -url jdbc:oracle:oci8:@ -u scott -p tiger

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 simply a Java Object) to a new OracleDataSource instance 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();
...

Logging and Tracing

The data source facility offers a way to register a character stream for JDBC to use as output for error logging and tracing information. This facility allows tracing specific to a particular data source instance. If you want all data source instances to use the same character stream, then you must register the stream with each data source instance individually.

The OracleDataSource class implements the following standard data source methods for logging and tracing:

public synchronized void setLogWriter(PrintWriter pw)
public synchronized PrintWriter getLogWriter()

The PrintWriter class is in the standard java.io package.

Notes:

When a data source instance is created, logging is disabled by default (the log stream name is initially null).

Messages written to a log stream registered to a data source instance are not written to the log stream normally maintained by DriverManager.

An OracleDataSource instance obtained from a JNDI name lookup will not have its PrinterWriter set, even if the PrintWriter was set when a data source instance was first bound to this JNDI name.