Creating & Referencing a JDBC JNDI Data Source

Here is how to create & reference a MySQL data source in WAS - one that can be retrieved using JNDI and features connection pooling. An example of how to call the data source, from a simple .war application running in the local container is also shown.

NOTE:

• The instructions below apply to WebSphere Application Server 6.0.2.35 on Ubuntu 9.04, however I've also tested them successfully on in i5 WAS V6.1.0.11 Express instance - but bear in mind that some of the steps are slightly different (easier) due to the more modern version of WAS (best used via IE6 as the client IMO!)
  

Create The Data Source
1. Resources > JDBC Providers @ Server level (the default for all of these instructions) > New...

• For General properties I used "User-defined"

After clicking Next I provided the following details

• Name = MySQL JDBC Provider
• Class path = ${USER_INSTALL_ROOT}/mysql/mysql-connector-java-5.1.8-bin.jar - this equated to /home/jsears/IBM/RAD-6.0/runtimes/base_v6/profiles/AppSrv01/mysql
• Implementation class name = com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource
  

2. Security > Global security > JAAS Configuration > J2C Authentication data > New

• Alias = MySQLUID - WAS renamed this to jsears-desktopNode01/MySQLUID
• User ID = root
  
• Password = mysql
  

3. Resources > JDBC providers > MySQL JDBC Provider > Data sources > New

• Name = MySQL
• JNDI name = jdbc/MySQL
• Component-managed authentication alias =jsears-desktopNode01/MySQLUID
  

4. Resources > JDBC providers > MySQL JDBC Provider > Data sources > MySQL > Custom Properties

• databaseName = bugs - I referenced a BugZilla install on a separate server
• port = 3306
• serverName = 192.168.192.110
  

5. Resources > JDBC providers > MySQL JDBC Provider > Data sources > MySQL > WebSphere Application Server data source properties

• Statement cache size = 10
• Pretest connections = ticked
  

6. Resources > JDBC providers > MySQL JDBC Provider > Data sources > MySQL > Test connection

• NOTE: first restart WAS!
  

Reference The Data Source
1. Create a simple Servlet, with a doGet method, and flesh it out - the important thing will be the JNDI reference line:

• DataSource ds = (DataSource) ctx.lookup("java:comp/env/MySQLResourceReference");

2. Against the web.xml deployment descriptor, using RAD, I added the Resource Reference to a data source:

Thus, after writing a very simple doGet I ended up with the following container based servlet that called the JNDI reference via indirection in the web.xml deployment descriptor:

I accepted all the defaults when uploading the .war - including the "Map resource references to resources" options, as I had already defined the details previously:

The URL I used, once the application was started, to invoke this servlet was: http://127.0.0.1:9081/ReferenceJNDIdataSource/ReferenceJNDIdataSource