Setting the Environment

Before getting started, we have to install the JDeveloper 10.1.3 IDE and the MySQL 5.0 database. Download JDeveloper from: http://www.oracle.com/technology/software/products/jdev/index.html. Download the MySQL Connector/J 5.1, the MySQL JDBC driver that supports JDBC 4.0 specification. To install JDeveloper extract the JDeveloper ZIP file to a directory. Log in to the MySQL database and set the database to test. Create a database table, Catalog, which we will use in a web application. The SQL script to create the database table is listed below:

CREATE TABLE Catalog(CatalogId VARCHAR(25)
PRIMARY KEY, Journal VARCHAR(25), Publisher VARCHAR(25),
Edition VARCHAR(25), Title Varchar(45), Author Varchar(25));
INSERT INTO Catalog VALUES('catalog1', 'Oracle Magazine',
'Oracle Publishing', 'Nov-Dec 2004', 'Database Resource Manager', 'Kimberly Floss');
INSERT INTO Catalog VALUES('catalog2', 'Oracle Magazine', 'Oracle Publishing', 'Nov-Dec 2004', 'From ADF UIX to JSF', 'Jonas Jacobi');

MySQL does not support ROWID, for which support has been added in JDBC 4.0. Having installed the JDeveloper IDE, next we will configure a JDBC connection in the Connections Navigator. Select the Connections tab and right-click on the Database node to select New Database Connection.

Click on Next in Create Database Connection Wizard. In the Create Database Connection Type window, specify a Connection Name — MySQLConnection for example — and set Connection Type to Third Party JDBC Driver, because we will be using MySQL database, which is a third-party database for Oracle JDeveloper and click on Next. If a connection is to be configured with Oracle database select Oracle (JDBC) as the Connection Type and click on Next.

In the Authentication window specify Username as root (Password is not required to be specified for a root user by default), and click on Next. In the Connection window, we will specify the connection parameters, such as the driver name and connection URL; click on New to specify a Driver Class. In the Register JDBC Driver window, specify Driver Class as com.mysql.jdbc.Driver and click on Browse to select a Library for the Driver Class. In the Select Library window, click on New to create a new library for the MySQL Connector/J 5.1 JAR file. In the Create Library window, specify Library Name as MySQL and click on Add Entry to add a JAR file entry for the MySQL library. In the Select Path Entry window select mysql-connector-java-5.1.3-rc\mysql-connector-java-5.1.3-rc-bin.jar and click on Select. In the Create Library window, after a Class Path entry gets added to the MySQL library, click on OK. In the Select Library window, select the MySQL library and click on OK. In the Register JDBC Driver window, the MySQL library gets specified in the Library field and the mysql-connector-java-5.1.3-rc\mysql-connector-java-5.1.3-rc-bin.jar gets specified in the Classpath field. Now, click on OK. The Driver Class, Library, and Classpath fields get specified in the Connection window. Specify URL as jdbc: mysql://localhost:3306/test, and click on Next.

In the Test window click on Test Connection to test the connection that we have configured. A connection is established and a success message gets output in the Status text area. Click on Finish in the Test window. A connection configuration, MySQLConnection, gets added to the Connections navigator.

The connection parameters are displayed in the structure view. To modify any of the connection settings, double-click on the Connection node. The Edit Database Connection window gets displayed. The connection Username, Password, Driver Class, and URL may be modified in the Edit window.

A database connection configured in the Connections navigator has a JNDI name binding in the JNDI naming service provided by OC4J. Using the JNDI name binding, a DataSource object may be created in a J2EE application. To view, or modify the configuration settings of the JDBC connection select Tools | Embedded OC4J Server Preferences in JDeveloper. In the window displayed, select Global | Data Sources node, and to update the data-sources.xml file with the connection defined in the Connections navigator, click on the Refresh Now button. Checkboxes may be selected to Create data-source elements where not defined, and to Update existing data-source elements.

The connection pool and data source associated with the connection configured in the Connections navigator get listed. Select the jdev-connection-pool-MySQLConnection node to list the connection pool properties as Property Set A and Property Set B.

The tuning properties of the JDBC connection pool may be set in the Connection Pool window. The different tuning attributes are listed in following table:

Select Property Set B to specify additional connection pool properties.

The connection pool properties in Property Set B are discussed in the following table:

The Connection Factory node specifies the Factory Class, User name, Password, Login Timeout, and connection URL. The factory class must implement one of the following interfaces: java.sql.Driver, javax.sql.DataSource, javax.sql.ConnectionPoolDataSource, javax.sql.XADataSource.

The Managed DataSource node specifies the managed data sources associated with the connection, and which are data sources managed by the OC4J. A managed data source is an OC4J implementation of the javax.sql.DataSource interface that wraps a JDBC driver class, or data source class. Even if the factory class does not implement the javax.sql.DataSource interface, the OC4J implementation of the factory class implements the javax.sql.DataSource interface. A managed data source supports connection caching, global transaction management, and error handling, all provided by the OC4J. A managed data source is associated with a connection pool, and thus has the advantage of being able to specify the tuning parameters. The JNDI Name of the data source is specified in the managed data source window. The JNDI Name is in the jdbc/MySQLConnectionDS format, with MySQLConnection being the connection name configured in the Connections navigator.

A connection MySQLConnection in the Connections navigator is available as a data source with the JNDI Name binding jdbc/MySQLConnectionDS. To obtain a connection from the data source, add a resource-ref element to the web application in which a connection is to be obtained. In a Servlet or JSP application, a connection may be obtained with the data source JNDI Name.

InitialContext initialContext = new InitialContext();
javax.sql.DataSource ds = (javax.sql.DataSource)
initialContext.lookup("java:comp/env/jdbc/MySQLConnectionDS");
java.sql.Connection conn = ds.getConnection();

JavaEE 5 defines annotations to support resource injection. Resource injection is the injection of external resources, such as a data source in a JEE 5 application using the javax.annotation.Resource annotation. JDeveloper 11 will support resource injection with annotations to obtain a handle of a data source. For example, define a catalogDS resource of the javax.sql.DataSource type, as shown below:

private @Resource DataSource catalogDS;

The catalogDS field of type javax.sql.DataSource is annotated with the @Resource annotation. JNDI lookup is not required with resource injection, and the DataSource resource is also not defined in the web.xml deployment descriptor.