3 Datasources and URLs

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.

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.

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.

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

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

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