Datasources, Tomcat and Netbeans

I am currently developing a JSF application using NetBeans 6.0 and Tomcat 6.0.14. When I tried to deploy the application on our production server (by just dumping the .war file in Tomcat's webapps directory, it didn't work properly. I could access the app's start page, but it failed to render the following pages.

A look in tomcat's log files revealed that there was a problem with the mysql driver.

Caused by: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver

I tried to make sure that the mysql-connector jar is included in the project's imported libraries and that it can be found in META-INF/lib, but this didn't help. I later found out that in Netbeans, under Tools→Servers→Deployment there is a "Enable JDBC driver deployment option". Disabling this resulted in a replication of the problem on my development machine, even when using NetBeans. So I had to find out what it was that NetBeans was doing when this option was enabled and repeat it on the production server.

I started making a research on the net, based on the error message I was getting. The oslution proposed by most people was to place the mysql driver jar in tomcat's common/lib directory and nowhere else, but this just wouldn't do the trick for me. I also checked that the datasource's declarations in the web.xml and context.xml files were correct.

A final attempt to go through the steps to create and declare a datasource as described in tomcat's website revealed a small detail in step 1: the driver jar file needs to be placed in the $CATALINA_HOME/lib directory rather than $CATALINA_HOME/common/lib.

Still remains to check whether there is a difference in the directory structure in versions 5.5 and 6.0 of tomcat, since no common directory appears to exist in installations of tomcat 6.0.

How to create a Datasource and a Connection Pool on Tomcat.

This post describes how you can setup a Connection Pool for a web application under Tomcat. I have been using NetBeans 5.5.1, with bundled Tomcat (version 5.5.17). NetBeans does offer some visual tools to create datasources and connection pools, but as far as I understand, these only work with the Sun Application Server.

Before doing anything else, the following jars are required:

• mysql-connector-java-5.0.7-bin.jar
• commons-dbcp-1.2.2.jar
• commons-collections-3.2.jar
• commons-pool-1.3.jar

The first one is the official mysql driver which can be downloaded from the mysql site. The other three are part of the Apache Commons collection.
They need to be placed in the WEB-INF/lib subdirectory of the webapp. (Of course this can also be done through the NetBeans IDE.) Actually, although I read on various websites about the last 3 jars, I am not absolutely sure if they are definitely required. Even after I removed them, my application continued to work fine.
Important: The MySQL Connector/J jar needs to be placed under the Tomcat common/lib/ subdirectory. (For the bundled with the NetBeans IDE version of Tomcat, this directory is $NETBEANS_HOME/enterprise3/apache-tomcat-5.5.17/common/lib.

1. Add this to the context.xml file (which is located in the META-INF directory of the web-app.

<Context path="/ePmashup">
  <Resource 
      name="jdbc/dataSource"
      auth="Container"
      type="javax.sql.DataSource"
      factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory"
      username="username"
      password="password"
      driverClassName="com.mysql.jdbc.Driver"
      url="jdbc:mysql://localhost:3306/databaseName"
      maxWait="1000"
      removeAbandoned="true"
      maxActive="30"
      maxIdle="10"
      removeAbandonedTimeout="60"
      logAbandoned="true"/>    
</Context>

The options in italics have to be substituted with the appropriate values.

2. Create a DataSource object in the application code.

Context initContext = new InitialContext();
Context envContext  = (Context)initContext.lookup("java:/comp/env");
datasource = (DataSource)envContext.lookup("jdbc/dataSource");

This snippet has to be wrapped in a try-catch block, because it might throw a NamingEsception. In my code, I placed this piece of code in the init() method of the Servlet that contacts the database and returns the results to the client.

 public void init() throws ServletException {
   super.init();
   try {
       Context initContext = new InitialContext();
       Context envContext  = (Context)initContext.lookup("java:/comp/env");
       datasource = (DataSource)envContext.lookup("jdbc/dataSource");
          
   } catch( NamingException ne ) {
     throw new RuntimeException( "Unable to aquire data source", ne );
  }
 }

Obviously, the string passed in the lookup method, needs to be the Resource name (in context.xml).

3. Now, a connection from the pool can be retrieved and used like this:

Connection conn = dataSource.getConnection();

Deploying web applications that use different Java versions.

If you have a Tomcat installation that uses, say, Java 1.4.2, you can only deploy web application that are built with Java <= 1.4.2. If you need to run a Java 5.0 web application, you need to set tomcat to use JRE 5.0 (by setting the JAVA_HOME or JRE_HOME enviroment variables), but this might possibly break older applications that use Java 1.4.2.

Most probably, you'll need to have a separate installation of tomcat, that will use Java 5.0. It is possible to have two or more instances of tomcat running, and these can be either the same version or different versions. However, the two instances need to listen to different ports. The server port is specified in file $CATALINA_HOME/conf/server.xml. I noticed however that in order to get it to work, we need to change all the ports and not just the server port.

This needs cleanup/rewritting