Skip Headers

Oracle9iAS Containers for J2EE Services Guide
Release 2 (9.0.2)

Part Number A95879-01
Go To Documentation Library
Home
Go To Product List
Solution Area
Go To Table Of Contents
Contents
Go To Index
Index

Go to previous page Go to next page

15
Data Sources

This chapter describes how to configure and use data sources in your Oracle9iAS Containers for J2EE (OC4J) application. A data source, which is the instantiation of an object that implements the javax.sql.DataSource interface, enables you to retrieve a connection to a database server.

This chapter covers the following topics:

Introduction

A data source is a Java object that has the properties and methods specified by the javax.sql.DataSource interface. Data sources offer a portable, vendor-independent method for creating JDBC connections. Data sources are factories that return JDBC connections to a database. J2EE applications use JNDI to look up DataSource objects. Each JDBC 2.0 driver provides its own implementation of a DataSource object, which can be bound into the JNDI namespace. Once bound, you can retrieve this data source object through a JNDI lookup.

Because they are vendor-independent, we recommend that J2EE applications retrieve connections to data servers using data sources.

Definition of Data Sources

You define OC4J data sources in an XML file known as data-sources.xml.

Defining Location of the DataSource XML Configuration File

Your application can know about the data sources defined in this file only if the application.xml file knows about it. The path attribute in the <data-sources> tag in the application.xml file must contain the name and path to your data-sources.xml file, as follows:

<data-sources
  path = "data-sources.xml"
/>

The path attribute of the <data-sources> tag contains both path and name of the data-sources.xml file. The path can be fixed, or it can be relative to where the application.xml is located. Both the application.xml and data-sources.xml files are located in $J2EE_HOME/config/application.xml. Thus, the path contains only the name of the data-sources.xml file.

Defining Data Sources

The $J2EE_HOME/config/data-sources.xml file is pre-installed with a default data source. For most uses, this default is all you will need. However, you can also add your own customized data source definitions.

The default data source is an emulated data source. That is, it is a wrapper around Oracle data source objects. You can use this data source for applications that access and update only a single data server. If you need to update more than one database, you must use a non-emulated data source. See "Non-Emulated Data Sources" for more information.

This data source is extremely fast and efficient, because it does not require any JTA or XA operations. These would be necessary if you were to manage more than a single database.

The following is the default data source definition that you can use for most applications:

<data-source
  class="com.evermind.sql.DriverManagerDataSource"
  name="OracleDS"
  location="jdbc/OracleCoreDS"
  xa-location="jdbc/xa/OracleXADS"
  ejb-location="jdbc/OracleDS"
  connection-driver="oracle.jdbc.driver.OracleDriver"
  username="scott"
  password="tiger"
  url="jdbc:oracle:thin:@localhost:5521:oracle"
  inactivity-timeout="30"
/>

"Using Data Sources" fully describes all attributes.

Retrieving a Connection From a Data Source

One way to modify data in your database is to retrieve a JDBC connection and use JDBC or SQLJ statements. We recommend that you use data source objects in your JDBC operations.

Do the following to modify data within your database:

  1. Retrieve the DataSource object through a JNDI lookup on the data source definition in the data-sources.xml file.

    The lookup is performed on the logical name of the default data source, which is an emulated data source that is defined in the ejb-location tag in the data-sources.xml file.

    You must always cast or narrow the object that JNDI returns to the DataSource, because the JNDI lookup() method returns a Java object.

  2. Create a connection to the database represented by the DataSource object.

Once you have the connection, you can construct and execute JDBC statements against this database specified by the data source.

The following code represents the preceding steps:

Context ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("jdbc/OracleDS");
Connection conn = ds.getConnection();

Use the following methods of the DataSource object in your application code to retrieve the connection to your database:

You can cast the connection object returned on the getConnection method to oracle.jdbc.OracleConnection and use all the Oracle extensions. This is shown below:

oracle.jdbc.OracleConnection conn = 
(oracle.jdbc.OracleConnection) ds.getConnection();

Once retrieved, you can execute SQL statements against the database either through SQLJ or JDBC.

Emulated and Non-Emulated Data Sources

There are several types of data sources. The data sources that are used the most are emulated and non-emulated.

Emulated Data Sources

Emulated data sources are wrappers around Oracle data sources. If you use these data sources, your connections are extremely fast, because they do not provide full XA or JTA global transactional support. We recommend that you use these data sources for local transactions or when your application requires access or update to a single database. You can use emulated data sources for Oracle or non-Oracle databases. For efficiency, any JNDI retrieved connection to the same emulated data sources share the same connection with the first identified username within the same transaction.

You can use the emulated data source to obtain connections to different databases by changing the values of url and connection-driver. The following is a definition of an emulated data source:

<data-source
  class="com.evermind.sql.DriverManagerDataSource"
  name="OracleDS"
  location="jdbc/OracleCoreDS"
  xa-location="jdbc/xa/OracleXADS"
  ejb-location="jdbc/OracleDS"
  connection-driver="oracle.jdbc.driver.OracleDriver"
  username="scott"
  password="tiger"
  url="jdbc:oracle:thin:@localhost:5521:oracle"
  inactivity-timeout="30"
/>

When creating the DataSource object, use the ejb-location logical name, as follows:

Context ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("jdbc/OracleDS");
Connection con = ds.getConnection();

This creates a DataSource session for SCOTT/TIGER.


Note:

In the past, you could use the location and xa-location attributes for retrieving data source objects. Currently, we recommend that you only use the ejb-location JNDI name in emulated data source definitions for retrieving the data source.


You can use local transactions only with a connection retrieved from an emulated data source. The XAResource that you enlist with the transaction manager is an emulated XAResource, so the Oracle database is unaware of global transactions. It provides only local transactional support. If you want to use global transactions, you must use a non-emulated data source.

Connections retrieved within a single transaction from a data source using the same username and password causes the logical connections to share a single physical connection. The following code shows two connections--conn1 and conn2--that share a single physical connection. They are both retrieved off the same data source object. They also authenticate with the same username and password.

Context ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("jdbc/OracleCMTDS1");
Connection conn1 = ds.getConnection("scott", "tiger");
Connection conn2 = ds.getConnection("scott", "tiger");

If you provide different a different username and password for the second connection from this data source, an error condition occurs. You can avoid this problem by using the "dedicated.connection" JNDI property. This is described in "Using Different Usernames for Two Connections to DataSource".

Non-Emulated Data Sources

Non-emulated data sources are pure Oracle data sources. Non-emulated data sources provide XA and JTA global transactional support. Thus, if you want to coordinate modifications in a global transaction, you should use this data source. Thus, you can use only these data sources for global two-phase commit transactions.

We recommend that you use these data sources for distributed database communications, recovery, and reliability. Non-emulated data sources share physical connections for several logical connections to the same database for the same user.

The following is an example of a non-emulated data source definition.

<data-source
  class="com.evermind.sql.OrionCMTDataSource"
  name="OracleDS"
  location="jdbc/OracleCMTDS1"
  connection-driver="oracle.jdbc.driver.OracleDriver"
  username="scott"
  password="tiger"
  url="jdbc:oracle:thin:@<hostname>:<TTC port number>:<DB SID>"
  inactivity-timeout="30"
/>

The following are the expected attribute definitions:

Other Non-Emulated DataSource Classes

However, you can also define other non-emulated data sources. You can use any of the Oracle DataSource objects listed in the Oracle9i JDBC Developer's Guide. The following shows an example of one of these DataSources:

To define a non-emulated data source with the OracleXADataSource class, configure the following in the data-sources.xml file:

<data-source
  class="oracle.xa.client.OracleXADataSource"
  name="OracleXADS"
  location="jdbc/OracleXADS"
  connection-driver="oracle.jdbc.driver.OracleDriver"
  username="scott"
  password="tiger"
  url="jdbc:oracle:thin:@<hostname>:<TTC port number>:<DB SID>"
  inactivity-timeout="30"
/>

Using Data Sources

The following sections describe the data sources that your application can use and how to access them:

Configuring Data Source Objects

For most purposes, you can use the data sources that are already defined in the server data-sources.xml configuration file.

To define a new data source object, there are three ways that you can do this.

  1. using the -installDataSource option in the admin.jar administrative command-line tool

  2. using Oracle Enterprise Manager

  3. directly editing the configuration files

To find out how to use the Administrative tools, see the Oracle9iAS Containers for J2EE User's Guide. For Oracle Enterprise Manager information, see Oracle Enterprise Manager Administrator's Guide. This chapter explains how to set up and manage data sources by editing the configuration files directly.

Configuration Files

One main configuration file establishes data sources at the OC4J server level: $J2EE_HOME/config/data-sources.xml. You can add new data sources to that file by editing it, following the guidelines in the next sections.

OC4J parses the data-sources.xml file when it starts, instantiates data source objects, and binds them into the server JNDI namespace. So if you add a new data source specification to this file, you must restart the OC4J server to make the new data source available for lookup.

Each application also has a separate JNDI namespace. The files web.xml, ejb-jar.xml, orion-ejb-jar.xml, and the orion-web.xml contain entries that you can use to map application JNDI names to data sources, as the next section describes.

Data Source Attributes

A data source can take many attributes. Some are mandatory, but most are optional. The attributes are specified in a <data-source> tag. Table 15-1 lists the attributes and their meaning.

Table 15-1 Data Source Attributes  
Attribute Name Meaning of Value Default Value

class

Names the class that implements the data source. This is a mandatory attribute. For non-emulated, the class attribute can be "com.evermind.sql.OrionCMTDataSource". For emulated, the class attribute should be "com.evermind.sql.DriverManagerDataSource".

N/A

location

The JNDI logical name for the data source object. OC4J binds the class instance into the application JNDI namespace with this name. This is a mandatory attribute. This JNDI lookup name is used for non-emulated data sources.

N/A

name

The optional name of the data source. Must be unique within the application.

If this name is not supplied, the location is used as the name.

connection-driver

The JDBC-driver classname for this data source, which is needed by some data sources that deal with java.sql.Connection. For most data sources, the driver should be "oracle.jdbc.driver.OracleDriver".

None.

username

The optional name of the schema to connect to.

None.

password

The optional password for the schema.

None.

URL

The URL for database connections. Must be supplied for Oracle database connections.

None.

xa-location

The logical name of an XA data source. Use this attribute only for emulated data sources. However, we recommend that you use ejb-location for your JNDI lookup.

None.

ejb-location

A logical name of an EJB data source. Use this attribute if you are using JTA for single-phase commit transactions or if you are looking up emulated data sources. If you use it to retrieve the data source, you can map the returned connection to oracle.jdbc.OracleConnection.

None.

inactivity-timeout

Time (in seconds) to cache unused connections before closing them.

60 seconds

connection-retry-
interval

The interval to wait (in seconds) before retrying a failed connection attempt.

1 second

max-connections

The maximum number of open connections for a pooled data source.

Depends on the data source type.

min-connections

The minimum number of open connections for a pooled data source. The first time min-connections is initiated is after the first DataSource.getConnection method is invoked.

0

wait-timeout

The number of seconds to wait for a free connection if the pool is used up (that is, has reached max-connections used).

60

max-connect-attempts

The number of times to retry making a connection. This is useful when the network is not stable or the environment is unstable for any other reason that will sometimes make connection attempts fail.

3

property

This element is used to specify either a database link for two-phase commit transactions (dblink) or a database caching scheme (cache_scheme).

None

Data Source Methods

You can call the following methods on a DataSource object:

getConnection();

Attempt to establish a database connection.

getConnection(String uid, String password);

Attempt to retrieve a database connection, specifying the username and password.

getLoginTimeout();

Retrieve the maximum time in seconds that this data source can wait while attempting to connect to a database

setLoginTimeout(int seconds);

Set the maximum time in seconds that this data source will wait while attempting to connect to a database.

getLogWriter();

Retrieve the log writer for this data source. Returns a java.io.Printwriter object.

setLogWriter(PrintWriter out);

Set the log writer for this data source.

Portable Data Source Lookup

When the OC4J server starts, the data sources in the data-sources.xml file in the j2ee/home/config directory are added to the OC4J JNDI tree. When you lookup a data source using JNDI, you specify the JNDI lookup as follows:

DataSource ds = ic.lookup("jdbc/OracleCMTDS1");

The OC4J server looks in its own internal JNDI tree for this data source.

However, it is recommended--and much more portable--for an application to look up a data source in the application JNDI tree, using the portable java:comp/env mechanism. Place an entry pointing to the data source in the application web.xml or ejb-jar.xml files, using the <resource-ref> tag. For example:

<resource-ref>
     <res-ref-name>jdbc/OracleDS</res-ref-name>
     <res-type>javax.sql.DataSource</res-type>
     <res-auth>Container</res-auth>
</resource-ref>

where <res-ref-name> can be one of the following:

  1. The actual JNDI name--such as "jdbc/OracleDS"--that is defined in the data-sources.xml. In this situation, no mapping is necessary. This is demonstrated by the above code example. The <res-ref-name> is the same as the JNDI name bound in the data-sources.xml file. You would retrieve this data source without using "java:comp/env" as shown by the following JNDI lookup:

    InitialContext ic = new InitialContext();
    DataSource ds = ic.lookup("jdbc/OracleDS");
    
    
  2. A logical name that is mapped to the actual JNDI name in the OC4J-specific files, orion-web.xml or orion-ejb-jar.xml. The OC4J-specific XML files then define a mapping from the logical name in the web.xml or ejb-jar.xml file to the actual JNDI name defined in the data-sources.xml file.

Example 15-1 Mapping Of Logical JNDI Name To Actual JNDI Name

The following demonstrates option #2 above. If you want to choose a logical name of "jdbc/OracleMappedDS" to be used within your code for the JNDI retrieval. Then you would have the following in your web.xml or ejb-jar.xml files:

<resource-ref>
     <res-ref-name>jdbc/OracleMappedDS</res-ref-name>
     <res-type>javax.sql.DataSource</res-type>
     <res-auth>Container</res-auth>
</resource-ref>

In order for the actual JNDI name to be found, you must have a <resource-ref-mapping> element that maps the "jdbc/OracleMappedDS" to the actual JNDI name in the data-sources.xml file. If we are using the default emulated data source, then the ejb-location would be defined with "jdbc/OracleDS" as the actual JNDI name. Thus, the following line would be contained in the OC4J-specific XML file:

<resource-ref-mapping name="jdbc/OracleMappedDS" location="jdbc/OracleDS" />

You can then look up the data source in the application JNDI namespace using the Java statements:

InitialContext ic = new InitialContext();
DataSource ds = ic.lookup("java:comp/env/jdbc/OracleMappedDS");

Using Oracle JDBC Extensions

To use Oracle JDBC extensions, cast the returned connection to oracle.jdbc.OracleConnection, as follows:

Context ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("jdbc/OracleCMTDS1");
oracle.jdbc.OracleConnection conn = 
(oracle.jdbc.OracleConnection) ds.getConnection();

You can use any of the Oracle extensions on the returned connection, "conn".

// you can create oracle.jdbc.* objects using this connection
oracle.jdbc.Statement orclStmt =
(oracle.jdbc.OracleStatement)conn.createStatement();
// assume table is varray_table
oracle.jdbc.OracleResultSet rs =
orclStmt.executeQuery("SELECT * FROM " + tableName);
while (rs.next())
{
oracle.sql.ARRAY array = rs.getARRAY(1);
...
}

Behavior of a Non-Emulated Data Source Object

The physical behavior of a non-emulated data source object changes depending on whether you retrieve a connection off the data source within a global transaction or not. The following discusses these differences:

Retrieving a Connection Outside a Global Transaction

If you retrieve a connection from a non-emulated data source and you are not involved in a global transaction, every getConnection method returns a logical handle. When the connection is used for work, a physical connection is created for each connection created. Thus, if you create two connections outside of a global transaction, both connections use a separate physical connection. When you close each connection, it is returned to a pool to be used by the next connection retrieval.

Retrieving a Connection Within a Global Transaction

If you retrieve a connection from the non-emulated data source and you are involved in a global JTA transaction, all physical connections retrieved off of the same DataSource object by the same user within the transaction share the same physical connection.

For example, if you start a transaction and retrieve two connections off of the "jdbc/OracleCMTDS1" DataSource with the "scott" user, both connections share the physical connection. In the following example, both conn1 and conn2 share the same physical connection.

Context ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("jdbc/OracleCMTDS1");
//start txn
txn.start();
Connection conn1 = ds.getConnection("scott", "tiger");
Connection conn2 = ds.getConnection("scott", "tiger");

However, separate physical connections are retrieved for connections retrieved from separate DataSource objects. The following example shows both conn1 and conn2 retrieved from different DataSource objects--"jdbc/OracleCMTDS1" and "jdbc/OracleCMTDS2". Both conn1 and conn2 will exist upon a separate physical connection.

Context ic = new InitialContext();
DataSource ds1 = (DataSource) ic.lookup("jdbc/OracleCMTDS1");
DataSource ds2 = (DataSource) ic.lookup("jdbc/OracleCMTDS2");
//start txn
txn.start();
Connection conn1 = ds1.getConnection();
Connection conn2 = ds2.getConnection();

Using Database Caching Schemes

You can define the database caching scheme to use within the data source definition. There are three types of caching schemes: DYNAMIC_SCHEME, FIXED_WAIT_SCHEME, and FIXED_RETURN_NULL_SCHEME. To define one of these schemes, provide a <property> element, as follows:

<data-source
  class="com.evermind.sql.OrionCMTDataSource"
  name="OracleDS"
  location="jdbc/OracleCMTDS1"
  connection-driver="oracle.jdbc.driver.OracleDriver"
  username="scott"
  password="tiger"
  url="jdbc:oracle:thin:@<hostname>:<TTC port number>:<DB SID>"
  inactivity-timeout="30"
  <property name="cacheScheme"

   value="DYNAMIC_SCHEME"/>

/>

Connection Retrieval Error Conditions

The following creates an error condition:

Using Different Usernames for Two Connections to DataSource

When you retrieve a connection from the a DataSource object with a username and password, this username and password is used on all subsequent connection retrievals within the same transaction. This error condition is valid for all data source types. For example, you retrieve the "jdbc/OracleCMTDS1" data source with the "scott" user. In retrieving a second connection off of the same data source with a different username, such as "adams", the username provided is ignored. Instead, the "scott" user is used.

Context ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("jdbc/OracleCMTDS1");
//start txn
txn.start();
Connection conn1 = ds.getConnection("scott", "tiger"); //uses scott/tiger 
Connection conn2 = ds.getConnection("adams", "wood"); //uses scott/tiger also

Thus, you cannot authenticate using two different users to the same data source. If you try to access the tables as "adams/wood", you enter into an error condition.

You can bypass this behavior by specifying the dedicated.connection JNDI property as true before retrieving the InitialContext. This property states that every connection retrieval uses a separate connection, even if to the same resource. Thus, you can specify different users to the same data source.

env.put("dedicated.connection", "true");

Mixing Local and Global Transactions

You cannot mix local and global transactions. You must use either one or the other. This error condition applies only to non-emulated data sources. The following code shows an invalid mixture of local and global transactions:

Context ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("jdbc/OracleCMTDS1");
Connection conn1 = ds.getConnection("scott", "tiger"); 
conn1.work(); //perform work on conn1 in a local transaction
//start global transaction
txn.start();
conn1.morework(); //perform work on conn1 within a global transaction ERROR!

Another mode of mixing transactional types is as follows:

Context ic = new InitialContext();
DataSource ds = (DataSource) ic.lookup("jdbc/OracleCMTDS1");
Connection conn1 = ds.getConnection("scott", "tiger"); 
//start global transaction
txn.start();
conn1.work(); //perform work on conn1 in a global transaction
txn.commit();
conn1.morework(); //perform work on conn1 within a local transaction ERROR!

Even though you have committed the global transaction, you are still mixing global and local transactional work within the same bean.

Using the OCI JDBC Drivers

The examples of Oracle data source definitions in this chapter use the Oracle JDBC thin driver. However, you can use the Oracle JDBC OCI (thick) driver as well. Set the following before you start the OC4J server:

The URL to use in the url attribute of the <data-source> element definition can have any of these forms:

Using Merant Drivers

When your application must connect to heterogeneous databases, use Merant JDBC drivers. Merant JDBC drivers are not meant to be used with an Oracle database but for connecting to non-Oracle databases, such as Microsoft, SQLServer, Sybase and DB2. If you want to use Merant drivers with OC4J, add corresponding entries for each database in the data-sources.xml file.

Please see the Merant documentation for information on installing the Merant JDBC drivers.

The following is an example of a data source entry for SQLServer. For more detailed information, see the Merant DataDirect Connect JDBC User's Guide and Reference.

<data-source 
   class="com.evermind.sql.DriverManagerDataSource"
   name="MerantDS" 
   location="jdbc/MerantCoreSSDS" 
   xa-location="jdbc/xa/MerantSSXADS" 
   ejb-location="jdbc/MerantSSDS" 
   connection-driver="com.merant.datadirect.jdbc.sqlserver.SQLServerDriver"
   username="test" 
   password="secret" 
   url="jdbc:sqlserver//hostname:port;User=test;Password=secret"
   inactivity-timeout="30" 
 />

For a DB2 database, here is a data source configuration sample:

<data-source 
  class="com.evermind.sql.DriverManagerDataSource"
  name="MerantDS" 
  location="jdbc/MerantCoreDB2DS" 
  xa-location="jdbc/xa/MerantDB2XADS" 
  ejb-location="jdbc/MerantDB2DS" 
  connection-driver="com.merant.datadirect.jdbc.db2.DB2Driver"
  username="test" 
  password="secret" 
  url="jdbc:sqlserver//hostname:port;LocationName=jdbc;CollectionId=default;
  inactivity-timeout="30" 
/>

For a Sybase database, here is a data source configuration sample:

<data-source 
  class="com.evermind.sql.DriverManagerDataSource"
  name="MerantDS" 
  location="jdbc/MerantCoreSybaseDS" 
  xa-location="jdbc/xa/MerantSybaseXADS" 
  ejb-location="jdbc/MerantSybaseDS" 
  connection-driver="com.merant.datadirect.jdbc.sybase.SybaseDriver"
  username="test" 
  password="secret" 
  url="jdbc:sqlserver//hostname:port;User=test;Password=secret"
  inactivity-timeout="30" 
/>

You can also use vendor-specific data sources in the class attribute directly. That is, you do not need to use an OC4J-specific data source in the class attribute.



Go to previous page Go to next page
Oracle
Copyright © 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Solution Area
Go To Table Of Contents
Contents
Go To Index
Index