Jair Rillo Junior’s blog

When we are working on a JEE environment, it is common to use a DataSource to get the connection from the database. If you are using EJB in fact, you must setup a DataSource to be used by the Entities.

In this blog, I’ve already talked about DataSource in Tomcat. Now it is time to work around the DataSource in a Application Server, in our case, JBoss 4.2 and Glassfish.

Setting up a DataSource in JBoss 4.2

First of all, you must have JBoss installed, of course. If you want to learn how to install JBoss, here there is a simple but useful topic about it.

Basically to setup a DataSource in JBoss, you should create a XML file and add the JDBC driver in the classpath. Both the XML file and the JDBC driver depends on which database you are going to use. In this topic, we are going to use MySQL, however you are free to use others databases as well.

Before get started the configuration, make sure your database is already running and you have a database created. In MySQL for instance, you can create a database through the command: create database test;

Creating the XML file and copy the JDBC to the proper directory

JBoss brings us some XML examples. You can reach them into $JBOSS_HOME/docs/examples/jca. There you will find many XMLs. Copy the xml regarding your database (in my case, mysql-ds.xml) to the directory $JBOSS_HOME/server/default/deploy.

Open this file using your preferred text editor and edit the connection-url, user-name, password. Below following an example:

 
    MySqlDS
    jdbc:mysql://localhost:3306/test
    com.mysql.jdbc.Driver
    database_username
database_password
    org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter
 
       mySQL

After that, you must copy the JDBC driver from your database to the directory $JBOSS_HOME/server/default/lib. (Mysql uses the mysql-connector-java driver. It can be downloaded from MySQL official website. If you are not using mysql at all, check out your database documentation to figure out which files you must copy to the lib directory).

Done! Our DataSource in JBoss has been setup and it is ready to be used. Let’s learn how to use this DataSource within a Web Application.

Creating a Web Application that uses the DataSource

Create yourself a Web Application to test the DataSource. In my case, I am using Eclipse Europa, then I should open the JEE Perspective, go to Package Explorer, right click there and choose New -> Enterprise Application Project (you can create a simple Dynamic one if you prefer). Put the name of the EAR, in my case TestDS and click next twice. In the Modules, add only the Web Modules (by default, the web modules’s name is: TestDSWeb).

After these steps, both the EAR and the WEB project should appear on the Package explorer.

Now it is time to create the index.jsp page. Expand the TestDSWeb project, right click on WebContent -> New -> JSP. Put the name index.jsp. The content of the JSP file looks like:

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
 
Insert title here
<h3>Test</h3>
<a href="TestServlet">Click here for test</a>

The JSP has a hyperlink that calls a servlet called TestServlet. In fact, the TestServlet will call our DataSource previously created.

To create the servlet, right click on TestDSWeb -> New -> Servlet. Put the name TestServlet and the package servlets. Click on Next twice and uncheck the doGET and Constructor options. After that the Servlet will be created and its code looks like:

package servlets;
 
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
 
import javax.naming.InitialContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
 
public class TestServlet extends javax.servlet.http.HttpServlet {
    static final long serialVersionUID = 1L;
 
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        try {
            InitialContext ctx = new InitialContext();
            DataSource ds = (DataSource) ctx.lookup("java:/MySqlDS");
            Connection con = ds.getConnection();
            PreparedStatement query = con.prepareStatement("select * from test");
            ResultSet rs = query.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString(2));
            }
            rs.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Stop now, breath and let’s take a look deeper in the code:

• Line 19: we started a InitialContext object. Remember: it is used to get object from JNDI.
• Line 20: we created the DataSource object through JNDI. To get the DataSource, you must use java:/ plus yourDataSource name. But where is the DataSource name? In the mysql-ds.xml file there is a tag called jndi-name. It is the name used there.
• Line 21: we created the Connection object through DataSource.

The example ends here. You could see how is easy setup and use a DataSource. Next we are going to show up how to setup a DataSource in Glassfish.

Setting up a DataSource in Glassfish

If you are using Glassfish instead JBoss (btw, I prefer Glassfish when I am working with EJB 3), you can follow up this part of the topic to figure out how setup a DataSource on it.

The DataSource in Glassfish is set up through its Admin Console. As you will see, the process is made using a GUI (Web-based) and very simple. Before run the Admin Console, make sure you have the JDBC driver in your machine. Copy the file into $GLASSFISH/lib directory.

To access the Admin Console, start Glassfish and type the following URL: http://localhost:4848. By default, the Username is admin and Password is adminadmin, but of course you could change them in the installation process

The first thing to do is to create a Connection Pool. To do that, within Admin Console, go to Resources -> Connection Pools.

Now let’s add our connection pool. On the content, click on New button. On the new screen, put the Name choose Resource Type and Database Vendor. Look at my choice below:

Click on next after the fields are filled up. On the next screen, scroll down and you will find many options. Basically you have to change the following: DatabaseName, Password, ServerName, URL, User. After you change these fields, click on Finish button.

You will be redirected back to the Connection Pool screen. To test if your pool is working, click on it and on the next screen, click on Ping button. If a success message appears, means your pool was setup properly.

Now let’s setup the JDBC Resource itself. Click on it and then New button.

On the next screen, choose: JNDI Name and Poll Name (Description is not required). Remember: By convention, the JNDI Name must starts with jdbc/DataSourceName. In my case for instance, I’ve choosen jdbc/ConnectionTestDS.

Click OK and done, your DataSource is setup in Glassfish.

Creating a client

You can use the same example than used in JBoss to create a Client. However the JNDI call must be different. (Remember: the JNDI call in Glassfish is different than JBoss).

You can get your DataSource from Glassfish using the following line:

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

Look at the line above. You retrieves the DataSource in Glassfish using java:comp/env plus JNDI Name.

I hope the both examples, JBoss and Glassfish, be useful for anyone. It is useful when you are going to create a EJB3 Entity component (next topic).