email article printer friendly more resources

Using Data Sources the Right Way
Data sources let app servers manage pools of database connections. Learn to set up a version 5.0 data source through IBM WebSphere Application Server
by Kulvir Singh Bhogal and Kwang Sik Kang

Posted February 3, 2004

Let's take a look at setting up a data source as prescribed by the Java 2 Platform, Enterprise Edition (J2EE) 1.3 specification. For demonstration purposes, we'll make use of the IBM WebSphere Studio Application Developer version 5.0 (Application Developer) and its embedded WebSphere Test Environment. To begin, take a look at an example of a frequent J2EE programming malpractice—the JDBC 1.0 methodology of obtaining database connections manually:

Class.forName(
  "COM.ibm.db2.jdbc.app.DB2Driver");
Connection db2Conn = 
  DriverManager.getConnection(
  "jdbc:db2:someDB",
  "db2username","dbpassword"); 

This approach does not take advantage of the advanced database connection pooling mechanisms offered by most J2EE application servers. Before getting started, let's address the benefit of implementing a data source. Data sources, introduced in JDBC 2.0, allow for resource pooling of connections.

Creating and maintaining database connections is an expensive operation in terms of performance. By allowing the J2EE application server to manage a data source's connection pool, we drastically alleviate the overhead cost of creating brand new connections and get the robustness provided by the application server's built-in JDBC service.

For our exercise here, we'll interact with a simple database. We assume you are using IBM DB2 Universal Database. It is important to note that WebSphere Application Server has built-in support for a number of other database flavors. First, create a new database named bnkdb from the DB2 Command Line Processor (CLP). Connect to the newly created database using the db2admin ID:

create database bnkdb
connect to bnkdb user db2admin 
  using db2admin

The database will consist of a simple table comprised of three fields: ssn, lastname, and firstname. Create the table from the CLP:

create table accttable(ssn 
  varchar(11) not null primary 
  key, lastname varchar(75) not 
  null, firstname varchar(75) not 
  null)

Next, populate the table with sample data. Later, we will query for this data:

insert into accttable values(
  '111-11-1111', 'Bhogal','Kulvir')
insert into accttable values(
  '222-22-2222', 'Kang','Kwang')

In Application Developer 5, we assume that a WebSphere version 5.0 Test Environment server is already set up. Now, open up the server configuration to configure a new data source. From the server configuration, navigate to the Security tab, and click Add to create a new Java Authentication and Authorization Service (JAAS) authentication entry (see Figure 1).