Creating a Managed Data Source

In the previous section, a JDBC connection was obtained in the Connections navigator. A corresponding JNDI managed data source becomes available for the Connections navigator connection. A data source object is configured with a JNDI Name binding in the OC4J server integrated with JDeveloper. A data source may also be configured in the Embedded OC4J Server Preferences window directly, or may be configured declaratively by modifying the data-sources.xml file. We will discuss each of these methods for creating a data source. A data source may be configured at the Global level or the Current Workspace level. A Global data source is available to all applications while a Current Workspace data source is available only in the current workspace. To create a new Global data source, select Tools | Embedded OC4J Preferences and select the Global | Data Sources node, and click on the New button.

Before we are able to configure a managed data source, we need to configure a connection pool. In the Create Data Source window select Transaction Level as Connection Pool, specify a connection pool name, and click on the OK button.

A new connection pool gets created and its tuning properties may be set, as required. In the Connection Factory window, specify a Factory Class, User name, Password, Login Timeout, and connection URL. The Factory Class is required to implement one of the following interfaces: java.sql.Driver, javax.sql.DataSource, javax.sql.ConnectionPoolDataSource, javax.sql.XADataSource. The Factory Class for a MySQL managed data source can be a MySQL class that implements the javax.sql.DataSource interface, for example the com.mysql.jdbc.jdbc2.optional.MysqlDataSource and com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource classes, or it can be a class that implements the java.sql.Driver interface, for example the com.mysql.jdbc.Driver class. Whether a class implementing the java.sql.Driver interface is used, or a class implementing the javax.sql.DataSource interface is used, the OC4J server wraps the class and provides an implementation of the javax.sql.DataSource interface. We will use the data source class com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource. A Password is not required for a root user, by default. Specify the connection URL as jdbc: mysql://localhost:3306/test. Click on OK to configure the connection pool.

Create a data source by selecting the Global | Data Sources node, and click on New, as we did for creating a Connection Pool. In the Create Data Source window, select Transaction Level as Managed Data Source, specify a data source Name and click on OK. A data source may also be configured as a Native Data Source. The difference between a Managed Data Source and a Native Data Source is that the OC4J server does not wrap a Native Data Source. The OC4J server does not provide connection caching for a Native Data Source. A Native Data Source does not support global/distributed transactions. A distributed transaction is a transaction that spans over multiple database servers. If a Native Data Source is to be configured, specify a data source class that implements the javax.sql.DataSource interface. A Connection Pool is not to be configured for a Native Data Source.

A new Managed Data Source gets added. Specify the Connection Pool Name with which the data source is to be associated as the Connection Pool that we configured earlier. Specify a JNDI Name, User name, Password, Login Timeout, Transaction Level, and click on OK.

A Managed Data Source gets configured and updates the data-sources.xml file using the Refresh button. A Managed Data Source may also be created declaratively by modifying the data-sources.xml configuration file directly in the directory: C:\JDeveloper\jdev\system\oracle.j2ee.10.1.3.nn.nn\embedded-oc4j\config.<JDeveloper10.1.3> is the directory in which JDeveloper is installed. Add a managed-data-source element to the data-sources.xml file specifying the configuration of the DataSource object. The managed-data-source element includes the data source class used to obtain a DataSource object. The managed-data-source element for configuring a data source, MySQLDataSource, with the MySQL database, using jndi-name jdbc/MySQLDataSource, is listed below:

<managed-data-source name='MySQLDataSource'
connection-pool-name='MySQL Connection Pool'
jndi-name='jdbc/MySQLDataSource'/>
<connection-pool name='MySQL Connection Pool'>
<connection-factory factory-class='com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource'
user='root'
password=''
url="jdbc:mysql://localhost:3306/test">
</connection-factory>
</connection-pool>

The elements in the data-sources.xml file are based on the XML Schema http://www.oracle.com/technology/oracleas/schema/data-sources-10_1.xsd. The root element is data-sources, and the data-sources element has one or more managed-data-source, connection-pool, and native-data-source elements. The managed-data-source element has the following attributes: name, user, password, login-timeout, tx-level, schema, connection-pool-name, and jndi-name. Each managed-data-source element is associated with a connection pool, which is specified in the connection-pool element. A connection-pool element has a connection-factory sub-element and attributes, which are discussed in following table:

If a native data source is used, connection caching is performed mainly programmatically. The OracleDataSource provides connection caching using the methods getConnectionCacheName(), setConnectionCacheName(String cacheName), getConnectionCacheProperties(), and setConnectionCacheProperties(java.util.Properties cp). Alternatively, the connection cache manager class OracleConnectionCacheManager may be used to specify connection caching. The OracleConnectionCacheManager class provides the getCacheManagerInstance(), createCache(String cacheName, javax.sql.DataSource ds, Properties cacheProperties), createCache(javax.sql.DataSource ds, Properties cacheProperties), removeCache(String cacheName, int mode), refreshCache(String cacheName, int mode), and getCacheProperties(String cacheName) methods, to configure the connection cache. With a native data source, declarative connection caching may also be specified in the native-data-source element in the data-sources.xml file with the property elements. The connection cache properties that may be specified in a native data source are discussed in the following table:

The Managed Data Source is the preferred data source as the connection pooling is implemented by the OC4J server, and the developer does not have to implement the connection pooling. In the next section, we will develop a web application to retrieve data from MySQL database. Delete the managed data source, MySQLDataSource, and the connection pool, MySQLConnectionPool configured in this section by selecting the managed data source/connection pool in the Tools | Embedded OC4J Server Preferences window and by selecting the Delete button. Update the data-sources.xml configuration file using the Refresh Now button