Connect MySQL to WebSphere Application Server Using a DataSource

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 . You’ll need fundamental familiarity with WAS.
?MySQL Server 5.0 (you can download the Community edition at 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');

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.

Name Type Value
databaseName java.lang.String testdb
Port java.lang.String 3306
serverName java.lang.String

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");          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 ( { 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.



Share the Post:
Global Layoffs

Tech Layoffs Are Getting Worse Globally

Since the start of 2023, the global technology sector has experienced a significant rise in layoffs, with over 236,000 workers being let go by 1,019

Cybersecurity Banking Revolution

Digital Banking Needs Cybersecurity

The banking, financial, and insurance (BFSI) sectors are pioneers in digital transformation, using web applications and application programming interfaces (APIs) to provide seamless services to

FinTech Leadership

Terry Clune’s Fintech Empire

Over the past 30 years, Terry Clune has built a remarkable business empire, with CluneTech at the helm. The CEO and Founder has successfully created

The Role Of AI Within A Web Design Agency?

In the digital age, the role of Artificial Intelligence (AI) in web design is rapidly evolving, transitioning from a futuristic concept to practical tools used

Global Layoffs

Tech Layoffs Are Getting Worse Globally

Since the start of 2023, the global technology sector has experienced a significant rise in layoffs, with over 236,000 workers being let go by 1,019 tech firms, as per data

Huawei Electric Dazzle

Huawei Dazzles with Electric Vehicles and Wireless Earbuds

During a prominent unveiling event, Huawei, the Chinese telecommunications powerhouse, kept quiet about its enigmatic new 5G phone and alleged cutting-edge chip development. Instead, Huawei astounded the audience by presenting

Cybersecurity Banking Revolution

Digital Banking Needs Cybersecurity

The banking, financial, and insurance (BFSI) sectors are pioneers in digital transformation, using web applications and application programming interfaces (APIs) to provide seamless services to customers around the world. Rising

FinTech Leadership

Terry Clune’s Fintech Empire

Over the past 30 years, Terry Clune has built a remarkable business empire, with CluneTech at the helm. The CEO and Founder has successfully created eight fintech firms, attracting renowned

The Role Of AI Within A Web Design Agency?

In the digital age, the role of Artificial Intelligence (AI) in web design is rapidly evolving, transitioning from a futuristic concept to practical tools used in design, coding, content writing

Generative AI Revolution

Is Generative AI the Next Internet?

The increasing demand for Generative AI models has led to a surge in its adoption across diverse sectors, with healthcare, automotive, and financial services being among the top beneficiaries. These

Microsoft Laptop

The New Surface Laptop Studio 2 Is Nuts

The Surface Laptop Studio 2 is a dynamic and robust all-in-one laptop designed for creators and professionals alike. It features a 14.4″ touchscreen and a cutting-edge design that is over

5G Innovations

GPU-Accelerated 5G in Japan

NTT DOCOMO, a global telecommunications giant, is set to break new ground in the industry as it prepares to launch a GPU-accelerated 5G network in Japan. This innovative approach will

AI Ethics

AI Journalism: Balancing Integrity and Innovation

An op-ed, produced using Microsoft’s Bing Chat AI software, recently appeared in the St. Louis Post-Dispatch, discussing the potential concerns surrounding the employment of artificial intelligence (AI) in journalism. These

Savings Extravaganza

Big Deal Days Extravaganza

The highly awaited Big Deal Days event for October 2023 is nearly here, scheduled for the 10th and 11th. Similar to the previous year, this autumn sale has already created

Cisco Splunk Deal

Cisco Splunk Deal Sparks Tech Acquisition Frenzy

Cisco’s recent massive purchase of Splunk, an AI-powered cybersecurity firm, for $28 billion signals a potential boost in tech deals after a year of subdued mergers and acquisitions in the

Iran Drone Expansion

Iran’s Jet-Propelled Drone Reshapes Power Balance

Iran has recently unveiled a jet-propelled variant of its Shahed series drone, marking a significant advancement in the nation’s drone technology. The new drone is poised to reshape the regional

Solar Geoengineering

Did the Overshoot Commission Shoot Down Geoengineering?

The Overshoot Commission has recently released a comprehensive report that discusses the controversial topic of Solar Geoengineering, also known as Solar Radiation Modification (SRM). The Commission’s primary objective is to

Remote Learning

Revolutionizing Remote Learning for Success

School districts are preparing to reveal a substantial technological upgrade designed to significantly improve remote learning experiences for both educators and students amid the ongoing pandemic. This major investment, which

Revolutionary SABERS Transforming

SABERS Batteries Transforming Industries

Scientists John Connell and Yi Lin from NASA’s Solid-state Architecture Batteries for Enhanced Rechargeability and Safety (SABERS) project are working on experimental solid-state battery packs that could dramatically change the

Build a Website

How Much Does It Cost to Build a Website?

Are you wondering how much it costs to build a website? The approximated cost is based on several factors, including which add-ons and platforms you choose. For example, a self-hosted

Battery Investments

Battery Startups Attract Billion-Dollar Investments

In recent times, battery startups have experienced a significant boost in investments, with three businesses obtaining over $1 billion in funding within the last month. French company Verkor amassed $2.1

Copilot Revolution

Microsoft Copilot: A Suit of AI Features

Microsoft’s latest offering, Microsoft Copilot, aims to revolutionize the way we interact with technology. By integrating various AI capabilities, this all-in-one tool provides users with an improved experience that not

AI Girlfriend Craze

AI Girlfriend Craze Threatens Relationships

The surge in virtual AI girlfriends’ popularity is playing a role in the escalating issue of loneliness among young males, and this could have serious repercussions for America’s future. A

AIOps Innovations

Senser is Changing AIOps

Senser, an AIOps platform based in Tel Aviv, has introduced its groundbreaking AI-powered observability solution to support developers and operations teams in promptly pinpointing the root causes of service disruptions

Bebop Charging Stations

Check Out The New Bebob Battery Charging Stations

Bebob has introduced new 4- and 8-channel battery charging stations primarily aimed at rental companies, providing a convenient solution for clients with a large quantity of batteries. These wall-mountable and

Malyasian Networks

Malaysia’s Dual 5G Network Growth

On Wednesday, Malaysia’s Prime Minister Anwar Ibrahim announced the country’s plan to implement a dual 5G network strategy. This move is designed to achieve a more equitable incorporation of both

Advanced Drones Race

Pentagon’s Bold Race for Advanced Drones

The Pentagon has recently unveiled its ambitious strategy to acquire thousands of sophisticated drones within the next two years. This decision comes in response to Russia’s rapid utilization of airborne

Important Updates

You Need to See the New Microsoft Updates

Microsoft has recently announced a series of new features and updates across their applications, including Outlook, Microsoft Teams, and SharePoint. These new developments are centered around improving user experience, streamlining