Chapter 4. Database Connectivity and Messaging

Configure data sources

As defined in the Sun JDBC 2.0 Standard Extension specification, data sources allow you to manage a pool of connections to a database. Using connection pools provides you with the following advantages:

  • It improves performance. Creating connections is expensive; a data source creates a connection as soon as it is instantiated.

  • It simplifies resource allocation. Resources are only allocated from the data sources, and not at arbitrary places in the code.

  • It simplifies connection calls. To get a connection in JDBC 1.0, you would need to call Class.forName() on the class name of the database driver, before making DriverManager calls.

Data sources work as follows:

  1. When a servlet, or other client, wants to use a connection, it looks up a data source by name from a JNDI server.

  2. The data source then returns a connection to the client.

  3. If the data source has no more connections, it may ask the database manager for more connections (as long as it has not exceeded the maximum number of connections).

  4. When the client has finished with the connection, it closes the connection.

  5. The data source then returns the connection to the available pool.

You can configure data sources for WebSphere Application Server v6.0 by using the Deployment page in the Application Deployment Descriptor editor or using the Administrative Console.

Configure the data source using the WebSphere Enhanced EAR capabilities.

Access the deployment descriptor

To access the deployment descriptor where the enhanced EAR settings are defined, do the following:

  1. Open the Web perspective Project Explorer view.

  2. Expand Enterprise Applications > Chapter4EAR.

  3. Double-click Deployment Descriptor : Chapter4EAR to open the file in the Deployment Descriptor Editor.

  4. Click the Deployment tab.

    Deployment tab

Note: For JAAS Authentication, when using Cloudscape, the configuration of the user ID and password for the JAAS Authentication is not needed. When using DB2 Universal Database or other database types that require a user ID and password, you will need to configure the JAAS authentication.

Configure a new JDBC provider

The following procedure is only needed if you wish to add a new JDBC provider using the enhanced EAR capability in the deployment descriptor:

  1. From the Deployment tab of the Application Deployment Descriptor, click Add under the JDBC provider list:

  2. When the Create a JDBC Provider dialog appears, select IBM DB2 as the Database type, select DB2 Legacy CLI-based Type 2 JDBC Driver as the JDBC provider type, and then click Next.

    Add JDBC Provider

  3. Enter Development DB2 JDBC Driver in the Name field (mandatory) and then click Finish.

    Add JDBC Provider - Step 2

Configure the data source

To configure a new data source using the enhanced EAR capability in the deployment descriptor, do the following:

  1. From the Deployment tab of the Application Deployment Descriptor, select the JDBC provider (Development DB2 JDBC Driver).

  2. Click Add next to data source.

  3. When the Create a Data Source dialog appears, select DB2 Legacy CLI-based Type 2 JDBC Driver under the JDBC provider, select Version 5.0 data source, and then click Next.

    Add data source

  4. When the Create a Data Source dialog appears, enter the following and then click Finish:

    Name (NOTE: mandatory field): Enhanced Development Data Source

    JNDI name (NOTE: mandatory field): jdbc/EnhancedDS

    Add data source - Step 2

Configure the databaseName property

To configure the databaseName in the new data source using the enhanced EAR capability in the deployment descriptor to define the location of the database for your environment, do the following:

  1. Select the data source created in the previous section (Enhanced Development Data Source).

  2. Select the databaseName property under the Resource properties.

  3. Click Edit next to Resource properties to change the value for the databaseName.

  4. When the Edit a resource property dialog appears, enter SAMPLE in the Value field and then click OK.

    Edit a resource property

  5. Save the Application Deployment Descriptor. The changes will not take effect until the server is restarted.

Note: If your enterprise application is already deployed, it may be necessary to remove the application from the server and then republish it.

Test enhanced data source

Create new enhanced-ds-test.jsp JSP file in Web Project:


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<HTML>
<HEAD>
<%@ page language="java" contentType="text/html; charset=UTF-8"
	pageEncoding="UTF-8"
	import="javax.sql.*, java.sql.*, javax.naming.*"%>
<META http-equiv="Content-Type" content="text/html; charset=UTF-8">
<META name="GENERATOR" content="IBM Software Development Platform">
<META http-equiv="Content-Style-Type" content="text/css">
<LINK href="theme/Master.css" rel="stylesheet"
	type="text/css">
<TITLE>Enhanced Data Source Test</TITLE>
</HEAD>
<BODY>
<H1>Sample Employees</H1>

<%!
private DataSource ds = null; 
final String dbDatasource = "java:comp/env/jdbc/SampleDS";

private Connection getConnection() throws SQLException {
	if (ds == null) {
		try {
			InitialContext ctx = new InitialContext();
			ds = (DataSource) ctx.lookup(dbDatasource);
		} catch (NamingException e) {
			throw new SQLException(e.getMessage());
		}
	}
	return ds.getConnection();
}
%>

<table border="1" cellspacing="0" cellpadding="5">
	<thead>
		<tr>
			<th>First Name</th>
			<th>Last Name</th>
		</tr>
	</thead>	
<%
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
	con = getConnection();
	stmt = con.createStatement();
	rs = stmt.executeQuery("SELECT * FROM DB2ADMIN.EMPLOYEE ORDER BY LASTNAME FETCH FIRST 5 ROWS ONLY");
	while (rs.next()) {
	%>
	<tr>
		<td>
			<%= rs.getString("FIRSTNME") %>
		</td>
		<td>
			<%= rs.getString("LASTNAME") %>
		</td>		
	</tr>	
	<%	
	}
} catch (Exception e) {
	out.println("Exception: " + e.getMessage());
} finally {
	try {
		if (rs != null) rs.close();
		if (stmt != null) stmt.close();
		if (con != null) con.close();
	} catch (SQLException e) {}
}
%>
</table>

</BODY>
</HTML>
					
					

Add a data source reference to the Web Project:

Add a data source reference to the Web Project

Configure JAAS alias and add it to data source (See here: Configure JAAS aliases for resource authentication ).

Run the enhanced-ds-test.jsp JSP page on server:

Run JSP on server

BOOT.BY - Tech Industry News         Free SCBCD 1.3 Study Guide     Free SCDJWS 1.4 Study Guide     SCDJWS 1.4 Quiz     Free IBM Certified Associate Developer Study Guide     Free SCJP 5.0 (Tiger) Study Guide     Free Mock Exam Engine     Free SCWCD 1.4 Study Guide     IBM Test 000-287. Enterprise Application Development with IBM WebSphere Studio, V5.0 Study Guide     Free SCBCD 5.0 Study Guide