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.

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:

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');

Configuring a MySQL JDBC Provider
To configure a MySQL DataSource, you first have to define a MySQL JDBC Provider. Open the application server administrative console and from the left-hand panel, choose Resources>JDBC Providers (see Figure 1).

Figure 1. Choose the JDBC providers option under Resources to begin creating a new JDBC provider.

Figure 2. Choose New to specify a new JDBC provider.

From the subsequent JDBC providers screen, choose New to specify a new JDBC provider (see Figure 2).

In the next screen (see Figure 3), for “Step 1: Select the database type,” specify a database type of “User-defined.” For “Step 2: Select the provider type,” specify a provider type of “User-defined JDBC Provider.” For “Step 3: Select the implementation type,” specify “User-Defined” and click Next.

Figure 3. Set the properties for the new JDBC provider as shown.

Figure 4. Set the name, class path, and implementation class names for the JDBC Provider.

In the next General Properties screen (see Figure 4), specify a name of MySQL JDBC Provider. Specify a class path of: C:mysql-connector-java-3.1.12mysql-connector-java-3.1.12-bin.jar. Note that this path should change to reflect the location of your mysql-connector-java-3.1.12-bin.jar JAR file. For your implementation class name, specify com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource. When you have specified the name, class path, and implementation class name as shown in Figure 4, click Apply. You will be prompted to save your changes to the master configuration, which you should do before moving on.

Setting Up the J2C Authentication Data
Before you can move on to creating your DataSource, you need to define some Java 2 Connection (J2C) Authentication authentication data. In short, you’ll be telling WAS what user ID and password to use when connecting to MySQL. From the left-hand pane of the administrative console (see Figure 5), choose Security>Global Security.

Figure 5. Get started on your authentication definition by selecting Global security from the WAS security menu.

Figure 6. The Authentication section in the right column is where you can select your J2C data option.

In the Configuration screen, under the Authentication section in the right column, expand the JAAS Configuration option, and choose the J2C Authentication data option (see Figure 6).

In the J2C dialog, click the New button to create a new authentication data entry.

In the subsequent screen, you can define the data associated to your new authentication data entry. Specify an alias name for your MySQL proxy user. I specified the name MySQLAdmin. Also, specify the user ID that will connect to MySQL on WAS’s behalf. This should be an authorized user which you or your MySQL administrator has authorized for accessing MySQL. In my case, I am just using the root user. Specify the user’s password in the Password section (see Figure 7). After specifying an alias, user ID and password, click Apply.

Figure 7. Now that you’ve created an authentication data entry, populate the properties fields, according to your setup and click Apply.

You will be asked again to save your changes to the master configuration. Go ahead and do this before proceeding. After saving your changes, you should see a new entry in the Global security section corresponding to the alias you just designated to connect to MySQL on WAS’s behalf.

Creating a MySQL DataSource
After defining your MySQL JDBC provider and creating your J2C authentication data entry, you can move on to creating your MySQL DataSource. From the left-hand panel of your administrative console (see Figure 8), choose Resources>JDBC Providers. In the subsequent JDBC providers screen, choose the MySQL JDBC Provider (which is the provider you created earlier):

From the subsequent Configuration screen, choose Data Sources under the Additional Properties section (see Figure 9). In the screen that follows, click on the New button to specify that you want to create a new Data Source.

Figure 8. Choose the MySQL JDBC provider object from the JDBC Providers section in the right column.

Figure 9. Look for the link to “Data Sources” in the right column, under Additional Properties in order to move to the next screen.

Next, you will be presented with the Data Source Configuration screen (see Figure 10). Specify a name for your DataSource. I called mine MySQLDataSource. Also, specify a JNDI name for your DataSource. I specified jdbc/mysqlds.

Further down in the DataSource configuration screen, specify a component-managed authentication alias of MySQLAdmin by choosing it from the drop-down list. This alias is the one you created earlier in the section on “Setting Up the J2C Authentication Data.”

Click Apply for the changes to take effect. Before moving on, you need to specify some additional properties for WAS to connect to the MySQL database. Under the Additional Properties section, choose Custom Properties from the right-hand column (see Figure 11).

Figure 10. Fill out the properties fields for the configuration of your Data Source.

Figure 11. The Custom Properties section in the right column is your next destination. Use it to specify additional properties for connecting WAS to MySQL.

Using the New Button, create three custom properties. The data for the custom properties is shown in Table 1. The value of your server-name should be replaced to localhost if you are co-locating WAS and MySQL on a single machine. If not, it should be replaced with either the IP address or hostname of the remote MySQL machine. I am also assuming that MySQL is listening on port 3306 (the default installation port). If this is not the case, you should replace your value accordingly.

Table 1. Values for the three custom properties you need to connect WAS to MySQL.

Figure 12 shows the custom properties defined. During the definition process, you will need to save your changes to the master configuration after defining each property.

Figure 12. Each of the three custom properties has now been defined.

Figure 13. The message shown at the top of the screen shot lets you know that your connection was successful.

After creating your DataSource, go back to the Data Sources main window. Here, you’ll find a button to “Test connection.” Use the Test Connection facility to make sure WAS can talk to MySQL as expected.

You will receive a success message if your DataSource was properly configured (see Figure 13).

Using the MySQL DataSource in your J2EE Applications
Now that you have your MySQL DataSource set up, it is time to try it out in a J2EE sample application. For my testing I used a simple Java servlet (named MySQLTestServlet), which lists the contents of the MySQL table I created and seeded with sample data earlier in the article. The servlet is packaged in an EAR file for your convenience so you can deploy it to your application server and make sure you have in fact tied together WAS and MySQL via a DataSource.

The workhouse of the MySQLTestServlet is the doGet method, shown below:

protected void doGet(HttpServletRequest request,          HttpServletResponse response) throws ServletException, IOException {          response.setContentType("text/html");          PrintWriter out = response.getWriter();          out.println("");          out.println("
WAS + MySQL Example
");          try {               Context ctx = new InitialContext();               DataSource ds = (DataSource) ctx                         .lookup("java:comp/env/jdbc/mysqldsref");               Connection connection = ds.getConnection();               Statement stmt = connection.createStatement();               ResultSet resultSet = stmt.executeQuery("select * from testtbl");               while (resultSet.next()) {                    String ssn = resultSet.getString("SSN");                    String lastName = resultSet.getString("LASTNAME");                    String firstName = resultSet.getString("FIRSTNAME");                    out.print(ssn + ": ");                    out.print(firstName + " ");                    out.println(lastName + "
");               }          } catch (NamingException e) {               e.printStackTrace();          } catch (SQLException e) {               e.printStackTrace();          }          out.println("");          out.close();}

The doGet method simply looks up the MySQL DataSource from JNDI and obtains a java.sql.Connection object. Using the Connection object, you create a Statement and execute a query against the TESTTBL you created earlier in the article. The ResultSet is cycled through using a while loop, each time printing the contents of the current row. Figure 14 is the Servlet as invoked by my Internet browser.

Figure 14. Invoking the servlet through a browser allows you to see the execution of a query against the TESTTBL.

If you pay close attention to the code, you will notice the DataSource JNDI lookup:

DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/mysqldsref");

Notice how I look up a JNDI reference named mysqldsref. But if you recall, you defined the MySQL JNDI name to be jdbc/mysqlds. You might be asking yourself, “Why the different JNDI name?” The reason is because in your WAR file’s deployment descriptor web.xml, you use a level of indirection to refer to the actual JNDI name:

                              jdbc/mysqldsref          javax.sql.DataSource          Container          Shareable

This level of redirection comes in handy so if you decide to change your actual JNDI name for your DataSource, you don’t have to modify your code. In fact, during your application’s deployment, you can change the bindings specified in your bindings file. Also the resource reference allows you to look up your JDBC resource using the java:comp/env namespace. The resource reference you defined in your deployment descriptor refers to your ibm-web-bnd.xmi file, which if you notice points to the JNDI name you defined when creating your MySQL DataSource (i.e., jdbc/mysqlds):

xmlns:webappbnd="webappbnd.xmi" xmi:id="WebAppBinding_1147148645444" 
virtualHostName="default_host">    jdbc/mysqlds">      

All Ready for MySQL
The MySQL database has gained a massive following over the years. This article showed you how to use the WebSphere Application Server Version 6 administrative console to create a DataSource that can facilitate communication between your MySQL databases and your J2EE applications.