Skip Headers

Oracle® Database JDBC Developer's Guide and Reference
10g Release 1 (10.1)

Part Number B10979-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

3 Datasources and URLs

This chapter discusses connecting applications to databases using JDBC datasources, as well as the URLs that describe databases. It is divided into the following sections:

3.1 Datasources

The JDBC 2.0 extension API introduced the concept of datasources, which are standard, general-use objects for specifying databases or other resources to use. Datasources 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 "Opening a Connection to a Database". The datasource facility provides a complete replacement for the previous JDBC DriverManager facility.

You can use both facilities in the same application, but ultimately we encourage you to transition your application to datasources. Eventually, Sun Microsystems will probably deprecate DriverManager and related classes and functionality.

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

3.1.1 A Brief Overview of Oracle Datasource 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 datasources 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.jar to be in the CLASSPATH. This file is included with the Java products on the installation CD, but is not included in the classes12.jar file. 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.)

3.1.2 Datasource Features and Properties

With datasource 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.

3.1.2.1 DataSource Interface and Oracle Implementation

A JDBC datasource 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 physical connection to the database.

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.

3.1.2.2 DataSource 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 3-1 and Table 3-2 document OracleDataSource properties. The properties in Table 3-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 3-2 are Oracle extensions.

Table 3-1 Standard Datasource 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 datasource class (for connection pooling, this is an underlying pooled connection datasource class; for distributed transactions, this is an underlying XA datasource class)
description String description of the datasource
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 Oracle Net Services 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 3-2 Oracle Extended Datasource Properties

Name Type Description
connectionCacheName String Name of cache; cannot be changed after cache has been created.
connection­Cache­Properties java.util.Properties Properties for Implicit Connection Cache; see "Connection Cache Properties" .
connectionCachingEnabled Boolean Specifies whether Implicit Connection Cache is in use.
connectionProperties java.util.Properties Connection properties. See the Javadoc for a complete list.
driverType String Designates the Oracle JDBC driver type —one of oci, thin, or kprb (server-side internal).
fastConnectionFailoverEnabled Boolean Whether Fast Connection Failover is in use; see Chapter 8, "Fast Connection Failover".
implicitCachingEnabled Boolean Whether the implicit connection cache is enabled.
loginTimeout int The maximum time in seconds that this data source will wait while attempting to connect to a database.
logWriter java.io.PrintWriter Log writer for this datasource.
maxStatements int The maximum number of statements in the application cache.
serviceName String Database service name for this datasource.
tnsEntry String (OracleXADatasource only) The TNS entry name, relevant only for the OCI driver. The TNS entry name corresponds to the TNS entry specified in the tnsnames.ora configuration file.

Enable this OracleXADataSource property when using the HeteroRM feature with the OCI driver, to access Oracle pre-8.1.6 databases and higher. The HeteroRM XA feature is described in "OCI HeteroRM XA". If the tnsEntry property is not set when using the HeteroRM XA feature, an SQLException with error code ORA-17207 is thrown.

url String The URL of the database connect string. Provided as a convenience, it can help you migrate from an older Oracle database. You can use this property in place of the Oracle tnsEntry and driverType properties and the standard portNumber, networkProtocol, serverName, and databaseName properties.
nativeXA Boolean (OracleXADatasource only) Allows an OracleXADataSource using the HeteroRM feature with the OCI driver, to access Oracle pre-8.1.6 databases and higher. The HeteroRM XA feature is described in "OCI HeteroRM XA". If the nativeXA property is enabled, be sure to set the tnsEntry property as well.

This DataSource property defaults to false.


Notes:

  • This table omits properties that supported the deprecated connection cache based on OracleConnectionCache.

  • Because nativeXA performs better than JavaXA, use nativeXA whenever possible.

The OracleDataSource class implements the following setXXX() and getXXX() 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)

  • boolean getConnectionCachingEnabled()

  • void setImplicitCachingEnabled()

  • void setDriverType(String dt)

  • String getDriverType()

  • void setURL(String url)

  • String getURL()

  • void setTNSEntryName(String tns)

  • String getTNSEntryName()

  • void setNativeXA(boolean nativeXA)

  • boolean getNativeXA()

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 the Thin or OCI drivers, 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 oci) and the networkProtocol is set to ipc, then any other property settings are ignored.

3.1.3 Creating a Datasource Instance and Connecting (without JNDI)

This section shows an example of the most basic use of a datasource 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("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");
...

3.1.4 Creating a Datasource Instance, Registering with JNDI, and Connecting

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


Note:

Creating and registering datasources is typically handled by a JNDI administrator, not in a JDBC application.

3.1.4.1 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("oci");
ods.setServerName("dlsun999");
ods.setNetworkProtocol("tcp");
ods.setDatabaseName("816");
ods.setPortNumber(1521);
ods.setUser("scott");
ods.setPassword("tiger");
...

3.1.4.2 Register the Datasource

Once you have initialized the connection properties of the OracleDataSource instance ods, as shown in the preceding example, you can register this datasource 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 datasources be registered in the jdbc naming subcontext of a JNDI namespace or in a child subcontext of the jdbc subcontext.

3.1.4.3 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();
...

3.1.5 Logging and Tracing

The datasource 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 datasource instance. If you want all datasource instances to use the same character stream, then you must register the stream with each datasource instance individually.

The OracleDataSource class implements the following standard datasource 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 datasource instance is created, logging is disabled by default (the log stream name is initially null).

  • Messages written to a log stream registered to a datasource instance are not written to the same log stream used 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 datasource instance was first bound to this JNDI name.

3.2 Database URLs and Database Specifiers

Database URLs are strings. The complete URL syntax is:

jdbc:oracle:driver_type:[username/password]@database_specifier


Notes:

  • The brackets indicate that the username/passwordpair is optional.

  • kprb, the internal server-side driver, uses an implicit connection; database URLs for the server-side driver end after the driver_type. See "Connecting to the Database with the Server-Side Internal Driver".

  • The Thin driver does not support OS authentication in making the connection, and therefore does not support special logins.

The first part of the URL specifies which JDBC driver is to be used. The supported driver_type values are thin, oci, and kprb.

The remainder of the URL contains an optional username and password separated by a slash, an @, and the database specifier, which uniquely identifies the database to which the application is connected. Some database specifiers are valid only for the Thin driver, some only for the OCI driver, and some for both.

3.2.1 Database Specifiers

Table 3-2, "Oracle Extended Datasource Properties", shows the possible database specifiers, listing which JDBC drivers support each specifier.


Notes:

  • Oracle Service IDs are no longer supported at 10g Release 1 (10.1).

  • The Thin driver does not support Oracle Names.

Table 3-3 Supported Database Specifiers

Specifier Supported Drivers Example
Oracle Net connection descriptor Thin, OCI Thin, using an address list:
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)))"

OCI, using a cluster:

"jdbc:oracle:oci:@(DESCRIPTION=
  (ADDRESS=(PROTOCOL=TCP)(HOST=cluster_alias)
    (PORT=1521))
    (CONNECT_DATA=(SERVICE_NAME=service_name)))" 
Thin-style service name Thin See "Thin-style Service Name Syntax" for details.
"jdbc:oracle:thin:scott/tiger@//myhost:1521/myservicename"
LDAP syntax Thin
"jdbc:oracle:thin:@ldap://ldap.acme.com:7777/sales,cn=OracleContext,dc=com"
or, when using SSL (see Note):
"jdbc:oracle:thin:@ldaps://ldap.acme.com:7777/sales,cn=OracleContext,dc=com"
Bequeath connection OCI Empty -- nothing after database name
"jdbc:oracle:oci:scott/tiger"
TNSNames alias OCI See "TNSNames Alias Syntax" for details.

Notes:

  • For complete information on how to specify an Oracle Net connection descriptor, LDAP directory naming, or a TNS connection string, see the Oracle Net Services Administrator's Guide.
  • The Thin driver can use LDAP over SSL to communicate with Oracle Internet Directory if you substitute ldaps: for ldap: in the database specifier. The LDAP server must be configured to use SSL; if it is not, the connection attempt will hang.

3.2.1.1 Thin-style Service Name Syntax

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

@//host_name:port_number/service_name


Notes:

host_name can be the name of a single host or a cluster_alias .

The JDBC Thin driver supports only the TCP/IP protocol.

3.2.1.2 TNSNames Alias Syntax

You can find the available TNSNAMES entries listed in the file tnsnames.ora 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 scott with password tiger that has a TNSNAMES entry of MyHostString, enter:

OracleDataSource ods = new OracleDataSource();
ods.setTNSEntryName("MyTNSAlias");
ods.setUser("scott");
ods.setPassword("tiger");
ods.setDriverType("oci8");
Connection conn = ods.getConnection(); 

Note:

Because the JDBC Thin driver can be used in applets that do not depend on an Oracle client installation, you cannot use a TNSNAMES entry to set up a Thin driver connection.