Setting Data Source in Tomcat

JNDI (Java Naming and Directory Interface) is like a directory in Java. We simply dump in it all the objects that we want to share. Anyone requiring the object need to do a JNDI lookup.

Till today, I thought that JNDI is available in application servers only. But JNDI is maintained by Tomcat as well.

I banged my head for sevral hours to get it working. After hair pulling for 4-5 hours, I figured out that I am using a wrong name to lookup my object :).

Requirement: Set up a MYSQL connection datasource in Tomcat.
Purpose: To use JDBC we have 2 ways.

1. Using Driver, obtain connection and proceed.



2. Let server create a data source (a pool of connection objects) thus freeing us from all the hassles of Connection creation, destruction and so on.

Better approach is the second one. Hence we shall setup a data source in Tomcat.

Artifacts required:

1. Tomcat server (Tomcat 6 in my case)



2. MYSQL



3. MYSQL driver jar (put in tomcat_home/lib)



4. Eclipse (Any IDE will do as long as you are comfortable with it.)

Steps
Dont ask me what these steps mean. These are the steps I followed to obtain my end goal.

1. Create a web project. A dynamic web project in case of eclipse.



2. Try to run this project in eclipse. This will create servers project and all the associated mumbo jumbo.



3. Edit tomcat_home/conf/server.xml to create a data source. If running the web project using the eclipse's run command, then go to project - 'servers', identify the tomcat server on which we have decided to run our project, and edit server.xml there.



4. Edit web.xml of the dynamic web project to refer to the data source.



5. In the code, lookup for the datasource and use it.

Step 3,4,5 are explained in details below.

Let us assume that we will create a datasource with JNDI name as ValkyrieDS.
   The details of server are
      Username - root
      Passowrd - NIL
      Schema - sample

   Project name - ValkyrieReporter v1.0

Step 3 - Edit server.xml editing

server.xml is a confusing thing. So dont pay heed to anything that looks lucrative.
Locate the tag hosts and edit it as mentioned below

<Host appBase="webapps" ... >
 ...
       <Context docBase="ValkyrieReporter v1.0" path="/ValkyrieReporter_v1.0" 
  reloadable="true" 
  source="org.eclipse.jst.j2ee.server:ValkyrieReporter v1.0">
         <Resource name="ValkyrieDS" 
          auth="Container"
          type="javax.sql.DataSource"
          maxActive="100" 
          maxIdle="30" 
          maxWait="10000"
          username="root"
          password=""
          driverClassName="com.mysql.jdbc.Driver"
          url="jdbc:mysql://localhost:3306/sample?autoReconnect=true"/>
       </Context>
</Host>

Step 4 - Edit web.xml

web.xml is the heart of a web application. So lets break that heart.

Simply add a resource reference as

<web-app>
 <resource-ref>
  <description>Connection Description</description>
  <res-ref-name>ValkyrieDS</res-ref-name>
  <res-type>javax.sql.DataSource</res-type>
  <res-auth>Container</res-auth>
 </resource-ref>
</web-app>

Step 5 - Lookup in code

// Define the JNDI String. This is where I mistook and was stuck for 5 hours :(
String DATASOURCE_JNDI = "java:comp/env/ValkyrieDS";
// Notice the string java:comp/env/ is prepended the JNDI name we use. I dont know why.

// Create a new InitialContext object.
Context initialContext = new InitialContext();
// Lookup the data source object.
DataSource datasource = (DataSource) initialContext.lookup(DATASOURCE_JNDI);
// Create the connection
Connection connection = datasource.getConnection();
// Make JDBC calls.
...

For this excercise, I hunted internet and got many solutions. But only this solution worked for me.
Following are the solutions that didnot work for me.

1. Create an XML file in the webapp folder - didnot work.



2. Add a resource tag in global JNDI section in server.xml

Please let me know if any other way exists.