The Java Database Connectivity (JDBC) API is used to access a SQL database from a Java application. JDBC also supports tabular data sources, such as a spreadsheet.

Oracle JDeveloper is a free Integrated Development Environment (IDE) for modeling, developing, debugging, optimizing, and deploying Java applications. JDeveloper 10g is used to develop J2EE applications comprising the JSPs, EJBs, struts, servlets, and Java classes that may require accessing a database table in an Oracle 10g Database or a third-party database. In this extract from the book JDBC 4.0 and Oracle JDeveloper for J2EE Development (Packt Publishing), we will see how to configure JDBC in the JDeveloper IDE.

Unlike the Eclipse IDE, which requires a plug-in, JDeveloper has a built-in provision to establish a JDBC connection with a database. JDeveloper is the only Java IDE with an embedded application server, Oracle Containers for J2EE (OC4J) or WebLogic Server in JDeveloper 11g. Thus a database-based Web application may run in JDeveloper without a third-party application server. However, JDeveloper also supports third-party application servers. Starting with JDeveloper 11, application developers can point the IDE to an application server instance, including third-party application servers, that you want to use for testing during development. JDeveloper provides connection pooling for the efficient use of database connections. A database connection may be used in an ADF BC (Business Components) application or in a Java EE application.

A database connection in JDeveloper may be configured in the Connections Navigator. A Connections Navigator connection is available as a DataSource registered with a JNDI naming service. The database connection in JDeveloper is a reusable named connection that developers configure once and then use in as many of their projects as they want. Depending on the nature of the project and the database connection, the connection is configured in the bc4j.xcfg file or a Java EE data source. Here, it is necessary to distinguish between data source and DataSource. A data source is a source of data; for example, an RDBMS database is a data source. A DataSource is an interface that represents a factory for JDBC Connection objects.

JDeveloper uses the term Data Source or data source to refer to a factory for connections. We will also use the term Data Source or data source to refer to a factory for connections, which in the javax.sql package is represented by the DataSource interface. A DataSource object may be created from a data source registered with the JNDI (Java Naming and Directory) naming service using JNDI lookup. A JDBC Connection object may be obtained from a DataSource object using the getConnection method. As an alternative to configuring a connection in the Connections Navigator, a data source may also be specified directly in the data source configuration file data-sources.xml.

In this article we will discuss the procedure to configure a JDBC connection and a JDBC data source in JDeveloper 10g IDE. We will use the MySQL 5.0 database server and MySQL Connector/J 5.1 JDBC driver, which support the JDBC 4.0 specification. In this article you will learn the following:

• Creating a database connection in JDeveloper Connections Navigator.
• Configuring the Data Source and Connection Pool associated with the connection configured in the Connections Navigator.
• The common JDBC Connection Errors.

Before we create a JDBC connection and a data source we will discuss connection pooling and DataSource.

Connection Pooling and DataSource
The javax.sql package provides the API for server-side database access. The main interfaces in the javax.sql package are DataSource, ConnectionPoolDataSource, and PooledConnection. The DataSource interface represents a factory for connections to a database. DataSource is a preferred method of obtaining a JDBC connection. An object that implements the DataSource interface is typically registered with a Java Naming and Directory API-based naming service. DataSource interface implementation is driver-vendor specific. The DataSource interface has three types of implementations:

• Basic implementation: In a basic implementation there is a 1:1 correspondence between a client's Connection object and the connection with the database. This implies that for every Connection object, there is a connection with the database. With the basic implementation, the overhead of opening, initiating, and closing a connection is incurred for each client session.
• Connection pooling implementation: A pool of Connection objects is available from which connections are assigned to the different client sessions. A connection pooling manager implements the connection pooling. When a client session does not require a connection, the connection is returned to the connection pool and becomes available to other clients. Thus, the overheads of opening, initiating, and closing connections are reduced.
• Distributed transaction implementation: Distributed transaction implementation produces a Connection object that is mostly used for distributed transactions and is always connection-pooled. A transaction manager implements the distributed transactions.

An advantage to using a data source is that code accessing a data source does not have to be modified when an application is migrated to a different application server. Only the data source properties need to be modified. A JDBC driver that is accessed with a DataSource does not register itself with a DriverManager. A DataSource object is created using a JNDI lookup and subsequently a Connection object is created from the DataSource object. For example, if a data source JNDI name is jdbc/OracleDS, a DataSource object may be created using JNDI lookup. First, create an InitialContext object and subsequently create a DataSource object using the InitialContext lookup method. From the DataSource object create a Connection object using the getConnection() method:

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

The JNDI naming service, which we used to create a DataSource object, is provided by J2EE application servers such as the Oracle Application Server Containers for J2EE (OC4J) embedded in the JDeveloper IDE.

A connection in a pool of connections is represented by the PooledConnection interface, not the Connection interface. The connection pool manager, typically the application server, maintains a pool of PooledConnection objects. When an application requests a connection using the DataSource.getConnection() method, as we did using the jdbc/OracleDS data source example, the connection pool manager returns a Connection object, which is actually a handle to an object that implements the PooledConnection interface.

A ConnectionPoolDataSource object, which is typically registered with a JNDI naming service, represents a collection of PooledConnection objects. The JDBC driver provides an implementation of the ConnectionPoolDataSource, which is used by the application server to build and manage a connection pool. When an application requests a connection, if a suitable PooledConnection object is available in the connection pool, the connection pool manager returns a handle to the PooledConnection object as a Connection object. If a suitable PooledConnection object is not available, the connection pool manager invokes the getPooledConnection() method of the ConnectionPoolDataSource to create a new PooledConnection object. For example, if connectionPoolDataSource is a ConnectionPoolDataSource object, a new PooledConnection gets created as follows:

PooledConnection
pooledConnection=connectionPoolDataSource.getPooledConnection();

The application does not have to invoke the getPooledConnection() method though; the connection pool manager invokes the getPooledConnection() method and the JDBC driver implementing the ConnectionPoolDataSource creates a new PooledConnection and returns a handle to it. The connection pool manager returns a Connection object, which is a handle to a PooledConnection object, to the application requesting a connection. When an application closes a Connection object using the close() method, as follows, the connection does not actually get closed.

conn.close();

The connection handle gets deactivated when an application closes a Connection object with the close() method. The connection pool manager does the deactivation. When an application closes a Connection object with the close() method, any client info properties that were set using the setClientInfo method are cleared.

The connection pool manager is registered with a PooledConnection object using the addConnectionEventListener() method. When a connection is closed, the connection pool manager is notified and the connection pool manager deactivates the handle to the PooledConnection object and returns the PooledConnection object to the connection pool to be used by another application.

The connection pool manager is also notified if a connection has an error. A PooledConnection object is not closed until the connection pool is being reinitialized, the server is shutdown, or a connection becomes unusable.

In addition to connections being pooled, PreparedStatement objects are also pooled by default if the database supports statement pooling. It can be discovered if a database supports statement pooling using the supportsStatementPooling() method of the DatabaseMetaData interface. The PreparedStatement pooling is also managed by the connection pool manager. To be notified of PreparedStatement events such as a PreparedStatement getting closed or a PreparedStatement becoming unusable, a connection pool manager is registered with a PooledConnection manager using the addStatementEventListener() method. A connection pool manager deregisters a PooledConnection object using the removeStatementEventListener() method. Methods addStatementEventListener and removeStatementEventListener are new methods in the PooledConnection interface in JDBC 4.0. Pooling of Statement objects is another new feature in JDBC 4.0. The Statement interface has two new methods in JDBC 4.0 for Statement pooling: isPoolable() and setPoolable().

The isPoolable method checks if a Statement object is poolable and the setPoolable method sets the Statement object to poolable. When an application closes a PreparedStatement object using the close() method, the PreparedStatement object is not actually closed. The PreparedStatement object is returned to the pool of PreparedStatements. When the connection pool manager closes a PooledConnection object by invoking the close() method of PooledConnection, all the associated statements also get closed. Pooling of PreparedStatements provides significant optimization, but if a large number of statements are left open, it may not be an optimal use of resources. Thus, the following procedure is followed to obtain a connection in an application server using a data source:

1. Create a data source with a JNDI name binding to the JNDI naming service.
2. Create an InitialContext object and look up the JNDI name of the data source using the lookup method to create a DataSource object. If the JDBC driver implements the DataSource as a connection pool, a connection pool becomes available.
3. Request a connection from the connection pool. The connection pool manager checks if a suitable PooledConnection object is available. If a suitable PooledConnection object is available, the connection pool manager returns a handle to the PooledConnection object as a Connection object to the application requesting a connection.
4. If a PooledConnection object is not available, the connection pool manager invokes the getPooledConnection() method of the ConnectionPoolDataSource, which is implemented by the JDBC driver.
5. The JDBC driver implementing the ConnectionPoolDataSource creates a PooledConnection object and returns a handle to it.
6. The connection pool manager returns a handle to the PooledConnection object as a Connection object to the application requesting a connection.
7. When an application closes a connection, the connection pool manager deactivates the handle to the PooledConnection object and returns the PooledConnection object to the connection pool.

ConnectionPoolDataSource provides some configuration properties to configure a connection pool. The configuration pool properties are not set by the JDBC client, but are implemented or augmented by the connection pool. The properties can be set in a data source configuration. Therefore, it is not for the application itself to change the settings, but for the administrator of the pool, who also sometimes happens to be the developer, to do so. Connection pool properties supported by ConnectionPoolDataSource are discussed in Table 1.

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; 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 (see Figure 1).

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 a MySQL database, which is a third-party database for Oracle JDeveloper and click on Next. If a connection is to be configured with an Oracle database select Oracle (JDBC) as the Connection Type and click on Next (see Figure 2).

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-rcmysql-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-rcmysql-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 (see Figure 3).

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 (see Figure 4).

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 can 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 (see Figure 5).

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 (see Figure 6).

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

Select Property Set B to specify additional connection pool properties (see Figure 7).

The connection pool properties in Property Set B are discussed in Table 3.

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 (see Figure 8).

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 (see Figure 9).

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

JDBC Configuration Errors
You might get errors while configuring a JDBC connection. If you are using MySQL, and the connection URL is incorrect, or the MySQL database is not running, the following error message is generated:

Communications link failure

If you are using an Oracle database, some possible connection configuration errors are listed below:

IO exception: The Network Adapter could not establish the connection
IO exception: Connection refused

The Network Adapter could not establish the connection exception is caused by one or more of the following configuration errors:

1. The database host name, port number, or database instance name is wrong.
2. The database TNSListener has not been started. The TNSListener may be started with the lsnrctl utility.

C:>lsnrctl start

The Connection refused exception is caused by one or more of the following configuration errors:

1. The database SID specified is incorrect.
2. The database instance has not been started. To start the database instance connect to SQL*Plus as SYSDBA.

C:>sqlplus SYS/<pwd> AS SYSDBA

At the SQL prompt, start the database instance with the startup command:

SQL>startup

Summary
JDeveloper IDE provides a built-in Connections navigator to configure a connection with any relational database for which a JDBC driver is available. A connection configured in the Connections navigator is also available as a data source. In this article, we have configured a JDBC connection in JDeveloper with the MySQL database using the MySQL Connector/J 5.1 JDBC 4.0 driver.