In this article I will show up how to setup a DataSource on Websphere 6.1 for MySQL step by step.
After you read this article, you will be capable to create a DataSource on Websphere 6.1 using MySQL database. Also, this article can be useful if you want to use another database, such as DB2, Oracle and so on.
Setting up a DataSource
Basically, there are 3 steps to create a DataSource on Websphere:
- Create a JDBC Provider
- Create a J2C
- Create the DataSource
Creating a JDBC Provider
First of all, you must start the Websphere server and open its admin console: http://localhost:9060/ibm/console/. On Admin Console, expand the Resources -> JDBC section.
Click on JDBC Provider link and a list of current JDBC providers will appears for you. By default, WAS 6.1 brings only Derby JDBC Provider already setup. We are going to create a new one for MySQL. Select the Scope and then click on New.
On the new screen, select User-Defined in Database type field (because WAS doesn’t have MySQL pre-defined), com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource in Implementation Class Name field and MySQL JDBC Provider in Name field.
After that, click on Next button. On next screen, you must inform the ClassPath of the JARs files. In my case, I copied the file mysql-connector-java-5.1.6-bin.jar to the <WAS-HOME>/lib directory.
Click on Next and on the next screen click on Finish. The first screen will come up and then you must click on Save link to commit the information to Websphere.
Creating a J2C
J2C is a way to create an authentication for our DataSource. The easiest way to create it is go to Data Sources section, click on New and then click on the link create a new J2C authentication alias.
On the new screen, click on New button and fill up the fields:
Click on OK and then Save link.
Creating the DataSource
Go to Data Sources section and click on New. On the new screen, insert the Data source name, JNDI name and select the J2C.
Note: The JNDI name default is jdbc/ plus JNDI name.
On the next screen, select the JDBC Provider previously created.
On the next two screens, just click on Next and then Finish. To commit the changes, click on Save link.
We are almost done. We need now to setup a couple of parameters. To do that, click on your Data Source and onto the new screen, click on Custom properties link.
On Custom properties, you must change the value to databaseName and serverName. (there are also other properties that you are free to change them if you want).
After the changes are done, it is time to test the DataSource. Back to the Data sources section, check the datasource you want and click on Test connection button. A message should appears.
We’re done! The DataSource has been created, tested and now it is ready to be used within Websphere Application Server v6.1
Creating a Web Application to access the DataSource
If you are using Websphere 6.1, probably you are using RAD 7 (Rational Application Developer) as well.
First of all, you must create a Dynamic Project. Then go to Web perspective and select the menu New -> Dynamic Web Project. Insert a project name and click finish (for this example, you do not need to create an EAR project).
The second step is to link the DataSource in our Web Project. To do that, simply open the Deployment Descriptor (double click on it).
On the new screen, go to References section. There, click on Add button and then Resource reference. Fill up the fields like below:
Back to the References section, click on the Reference just created and in the Websphere bindings, insert the JNDI name of the datasouce created into Websphere.
After that, both files (web.xml and ibm-web-bnd.xmi) will be configured. Your web application can call the DataSource through the alias: jdbc/ApplicationDS.
Below following a piece of code which uses the DataSource. This code can be used in a Servlet, JSP, ManagedBean and so on.
- try {
- Context ctx = new InitialContext();
- DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/ApplicationDS");
- Connection con = ds.getConnection();
- PreparedStatement query = con.prepareStatement("select * from customers");
- } catch (Exception e) {
- e.printStackTrace();
- }
try {
Context ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/ApplicationDS");
Connection con = ds.getConnection();
PreparedStatement query = con.prepareStatement("select * from customers");
} catch (Exception e) {
e.printStackTrace();
}
Look at the line number 3, in this line we are calling the DataSource from Websphere.
Conclusion
I really hope this topic be useful for anyone else. It is a simple and fast way to create a DataSource (and use it) in Websphere 6.1 environment.
Tooling/support for WS 5.1, 6.x No vendor lockin, only $149/year
www.myeclipseide.com
Pure Java database. Improve data access and increase profits. Try It
www.progress.com/objectstore
MS SQL Server Database Admin Tools Download Now! Windows, Linux, OSX
www.aquafold.com
Manage performance of servers, EJBs Servlets, JVM, Web Application
manageengine.adventnet.com/Download