Connect MySQL to WebSphere Application Server Using a DataSource

n the J2EE realm, data sources are Java objects that represent physical data storage systems such as relational databases. It is via javax.sql.DataSource objects that a J2EE application can retrieve underlying connections to the databases being represented by the DataSource object. The layer of abstraction represented by a DataSource object allows for a J2EE container to pool connections to the database on behalf of J2EE applications running in the J2EE container. Furthermore, DataSource objects allow a J2EE application to abstract itself from the database it is interacting with. Accordingly, one can more easily migrate from one database flavor to another.

The popularity of the MySQL database cannot be denied. Recognizing this, as well as the popularity of IBM WebSphere Application Server Version 6, in this article I'll show you how to configure a MySQL DataSource so that you can use WebSphere Application Server (WAS) with a MySQL database running on Windows.

In order to define a MySQL data source on WAS 6, you need to get your hands on the JDBC driver for MySQL. You can download it from the MySQL developer zone. At the time of this article's writing, the JDBC driver, known as MySQL Connector/J, was in version 3.1. Extract the ZIP file onto your hard drive (in my case C:\). A directory called mysql-connector-java-3.1.12 is created. In this directory you should see a file named mysql-connector-java-3.1.12.jar. This is the file that houses the JDBC driver.

What You Need

—IBM WebSphere Application Server 6 (you can download a trial of WAS 6 at http://www-128.ibm.com/developerworks/websphere/downloads/ . You'll need fundamental familiarity with WAS. —MySQL Server 5.0 (you can download the Community edition at http://dev.mysql.com/downloads/). You can co-locate WAS and MySQL server on the same machine or install them on separate machines.

Preparing a MySQL SandBox
I'll start by creating a MySQL database. Open up the MySQL command line client. Create a database named testdb:

mysql> create database testdb;

Next, switch to the testdb database:

mysql> use testdb;

Create a table named testtbl taking on the following form:

Column Name	Type	Nullable	Primary Key
ssn	varchar(30)	NO	YES
lastname	varchar(30)	NO
firstname	varchar(30)	NO

Translating that to SQL, issue the following statement:

mysql> create table testtbl(ssn varchar(30) not null primary key, lastname varchar(30) not null, firstname varchar(30) not null)

After issuing the CREATE TABLE statement above, you should be able to see the following output from the DESCRIBE command:

mysql> describe testtbl;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| ssn       | varchar(30) | NO   | PRI | NULL    |       |
| lastname  | varchar(30) | NO   |     | NULL    |       |
| firstname | varchar(30) | NO   |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.06 sec)

Finally, seed the database with a couple of rows of sample data:

mysql> insert into testtbl values('111-11-1111','Bhogal','Kulvir');

mysql> insert into testtbl values('222-22-2222','Feona','Meet');