Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Connect MySQL to WebSphere Application Server Using a DataSource : Page 4

Making WebSphere Application Server cooperate with MySQL isn't hard. Follow along to create a DataSource that marries WAS and MySQL.


WEBINAR: On-Demand

Unleash Your DevOps Strategy by Synchronizing Application and Database Changes REGISTER >

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("<HTML><HEAD><TITLE>"); out.println("WAS + MySQL Example"); out.println("</TITLE></HEAD><BODY>"); out.println("<H1>WAS + MySQL Example</H1>"); 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 + "<BR>"); } } catch (NamingException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } out.println("</BODY></HTML>"); 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:

<resource-ref id="MySQLResourceRef"> <description> </description> <res-ref-name>jdbc/mysqldsref</res-ref-name> <res-type>javax.sql.DataSource</res-type> <res-auth>Container</res-auth> <res-sharing-scope>Shareable</res-sharing-scope> </resource-ref>

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):

<?xml version="1.0" encoding="UTF-8"?> <webappbnd:WebAppBinding xmi:version="2.0" xmlns:xmi="http://www.omg.org/XMI"
xmlns:webappbnd="webappbnd.xmi" xmi:id="WebAppBinding_1147148645444"
virtualHostName="default_host"> <webapp href="WEB-INF/web.xml#WebApp_ID"/> <resRefBindings xmi:id="ResourceRefBinding_MySQLResourceRef" jndiName=" jdbc/mysqlds"> <bindingResourceRef href="WEB-INF/web.xml#ResourceRef_MySQLResourceRef"/> </resRefBindings> </webappbnd:WebAppBinding>

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.

Kulvir Singh Bhogal works as an IBM consultant, devising and implementing J2EE-centric solutions at customer sites across the nation.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date