Creating a MySQL data source using WebSphere Application Server

DATABASE CONNECTIVITY

  

Recently, Senior Technical Editor Dan Velasco wrote an article on how to connect to the popular open source database MySQL from WebSphere Studio Application Developer (see http://www.webspherepower.com/issues/issue200310/00001111001.html). Once you've created your application using MySQL, though, you'll want to deploy it to an application server, presumably WebSphere Application Server. To use MySQL on the application server, you will need to create a JDBC (Java Data Base Connectivity) data source if you want to take advantage of the connection pooling capabilities inherent in the WebSphere platform.

 

 

 

Creating a JDBC MySQL data source involves three independent steps, all accomplished with the WebSphere Application Server's Administrative Console facility. In order, these three steps are:

 

 

 

 

• Create a MySQL JDBC provider.
• Create the basic MySQL data source configuration.
• Add six custom properties to the MySQL data source configuration.

 

 

 

We'll walk through each of these steps, one step at a time.

 

 

 

Open the WebSphere Application Server Administrative Console
JDBC data sources are created in the WebSphere Application Server using the built-in Administrative Console, a Struts 1.0 application that comes bundled with the product. To activate the Administrative Console, open a Web browser and enter the following URL:

 

 

 

https://<;server-name>:9043/admin

 

 

 

Figure A illustrates the screen that will come up if all is working correctly with your application server and the Administrative Console has been enabled.

 

 

 

FIGURE A


Log into your Administrative Console. Roll over picture for a larger image.

 

 

 

 

If you have security turned on for the Administrative Console, the screen will also contain a password field and you will have to enter a valid user name and password to authenticate to the server. If you do not have security turned on, you can enter any username that you wish. Once you authenticate to the server, you will see the home page of the Administrative Console, depicted in Figure B.

 

 

 

FIGURE B


Welcome home. Roll over picture for a larger image.

 

 

 

 

Create the JDBC provider
The first step in creating the Data Source is to create the MySQL JDBC provider. Open up the Resources menu by clicking on the little plus sign (+) in front of the word Resources and then click on JDBC Providers. This should bring you to the screen found in Figure C.

 

Creating a MySQL data source using WebSphere Application Server (continued)

 

FIGURE C


Click New to create a providers. Roll over picture for a larger image.

 

 

 

 

Click on the New button, which should bring you to the JDBC Provider Configuration entry screen, shown in Figure D.

 

 

 

FIGURE D


Configure your new provider. Roll over picture for a larger image.

 

 

 

 

Enter the JDBC Provider configuration parameters using the following information:

 

 

 

 

• Name: MySQL
• Description: MySQL JDBC 2.0-compliant Provider configuration
• Classpath: ${User-defined_JDBC_DRIVER_PATH}/mysql-connector-java-3.0.9-stable-bin.jar (or the appropriate name for the version of MySQL Connector/J that you are using)
• Native Library Path: <leave blank>
• Implementation Classname: com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource

 

 

 

When you have finished entering the data, click on OK. If there are no data entry errors, this should take you back to the list of JDBC Providers, where you should now see your new MySQL JDBC Provider displayed as shown in Figure E.

 

 

 

FIGURE E


You're now the proud owner of a new provider. Roll over picture for a larger image.

 

 

 

 

You should also see a message at the top of the screen informing you that changes have been made and indicating that you should save these changes to the Master Configuration. While you can take that step at this point, we will be making further changes and you might as well wait until everything has been completed and then save everything at once.

 

 

 

 

"The secret trick to making this work is the Datasource Helper Classname"

 

 

 

Create the base JDBC data source entry
Click on the name of your new MySQL JDBC Provider entry to return to the JDBC Provider Configuration screen. Scroll to the bottom of the screen and click on the Data Sources link in the Additional Properties section, as shown in Figure F.

 

 

 

FIGURE F


Scroll down for the Additional Properties section. Roll over picture for a larger image.

 

 

 

 

This should take you to the empty Data Sources list shown Figure G, where you need to click on the New button.

 

 

 

FIGURE G


Click new to create a new data source. Roll over picture for a larger image.

 

 

 

 

This will bring you to the Data Source Configuration screen, shown in Figure H.

 

 

 

FIGURE H


Enter the Data Source configuration parameters. Roll over picture for a larger image.

 

 

 

 

Enter the Data Source configuration parameters using the following information:

 

 

 

 

• Name: <data source name>
• JNDI (Java Naming and Directory Interface) Name: jdbc/<data source name>
• Description: <data source description>
• Statement Cache Size: 10 (the default)
• Category: <leave blank>
• Datasource Helper Classname: com.ibm.websphere.rsadapter.ConnectJDBCDataStoreHelper
• Component-managed Authentication Alias: <leave blank>
• Container-managed Authentication Alias: <leave blank>

 

 

 

The secret (and not very well publicized) trick to making this work is the Datasource Helper Classname. Without the proper datasource helper, you will run into all kinds of interesting challenges trying to get this to work.

 

 

 

When you have finished entering the data, click on OK. If there are no data entry errors, this should take you back to the list of JDBC data sources, where you should now see your new MySQL JDBC data source displayed, shown in Figure I.

 

Creating a MySQL data source using WebSphere Application Server (continued)

 

FIGURE I


Here's your new data source. Roll over picture for a larger image.

 

 

 

 

Add MySQL-specific custom properties to your data source entry
Click on the name of your new MySQL JDBC data source entry to return to the JDBC data source configuration screen. Scroll to the bottom of the screen as shown in Figure J and click on the Custom Properties link in the Additional Properties section.

 

 

 

FIGURE J


Get ready to configure some custom properties. Roll over picture for a larger image.

 

 

 

 

This should take you to the empty Custom Properties list, shown in Figure K, where you can begin entering the six custom properties necessary to complete your MySQL Data Source entry.

 

 

 

FIGURE K


Now you can configure some hidden, but very important options. Roll over picture for a larger image.

 

 

 

 

The six custom properties, which are another difficult to locate tidbit of critical information, and their respective values are:

 

 

 

 

Name	Type	Value
databaseName	java.lang.String	<database-name>?autoReconnect=true
factory	java.lang.String	com.mysql.jdbc.jdbc2.optional.MysqlDataSourceFactory
password	java.lang.String	<password>
port	java.lang.String	3306 (or your MySQL port, if you have changed it)
serverName	java.lang.String	<server=name>
user	java.lang.String	<user-name>

 

 

 

Custom properties are entered one property at a time using the Custom Property Configuration screen. To enter your first property, click on the New button above the empty property list, which should take you to the Custom Property Configuration screen illustrated in Figure L.

 

 

 

FIGURE L


Enter the property name, Java type, and property value, then click OK Roll over picture for a larger image.

 

 

 

 

Enter the property name, Java type, and property value, then click OK. Repeat this process for all six of the properties listed above.

 

 

 

When you have completed entering all of the custom properties, the last step is to save all of the changes that you have made to the master configuration. After successfully entering the data for the last custom property, you should still be on the list of custom properties for your new data source, as shown in Figure M.

 

 

 

FIGURE M


Here you can see all your custom properties Roll over picture for a larger image.

 

 

 

 

At the top of the screen, you should still see the message about saving your changes to Master Configuration. Clicking on the word Save in that message will take you to the Save to the Master Configuration screen shown in Figure N.

 

 

 

FIGURE N


Don't forget to save your configuration. Roll over picture for a larger image.

 

 

 

 

Click on the Save button to save your newly completed MySQL JDBC data source configuration and you are ready to start using your new data source.

 

 

 

Wrap-up
By following these three simple steps, you can create your first MySQL JDBC data source. If you need additional MySQL data sources in the same server instance, creating the rest is even easier. Step one, creating the MySQL JDBC Provider only has to be done once per server; additional data sources can be added using the same provider by just completing steps two and three. Just make sure that your .jar file for the MySQL JDBC driver is accessible to the server and your new data source should be ready to go.