Configuring JNDI datasource in Tomcat 6

I was starting on a new project yesterday and I needed the MySQL connection to be configured as a JNDI datasource on Tomcat 6.  Tomcat 6 has a separate Web page detailing the steps required to configure JNDI datasource.

So following that advice, I copied the MySQL connection java driver (mysql-connector-java-3.0.17-ga-bin.jar) to TOMCAT_HOME/lib and then added the required entries in server.xml and web.xml as given below. Here xpc is the MySQL database instance I want to connect.

server.xml entry (under Context section)

<resource name="jdbc/xpc" 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/xpc?autoReconnect=true"/>

web.xml entry

<resource-ref>
	<description>DB Connection</description>
	<res-ref-name>jdbc/xpc</res-ref-name>
	<res-type>javax.sql.DataSource</res-type>
	<res-auth>Container</res-auth>
</resource-ref>

Then I wrote sample code to connect to MySQL and select a value from a test table. The code is given below. In this sample, JSP calls the Test class to print the value of temp_val from test table. This code also demonstrates how to connect to a MySQL database using JNDI lookup.

Test.java

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
 
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
 
public class Test {
	public static String t() {
		try {
			Context ctx = new InitialContext();
			DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/xpc");
			Connection connection = ds.getConnection();
			Statement stmt = connection.createStatement();
			ResultSet resultSet = stmt.executeQuery("select * from test");
			while (resultSet.next()) {
				String temp_val = resultSet.getString("temp_val");
				return temp_val;
			}
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
 
}

Test.jsp

<html>
<body>
<%=Test.t()%>
</body>

But when I ran the above code, I got a strange exception give below,

org.apache.commons.dbcp.SQLNestedException: Cannot create JDBC driver of class ” for connect URL ‘null’, cause: java.sql.SQLException: No suitable driver
 

This came as a surprise since everything was configured as in the documentation. But after a couple of google queries, I came across the cause of this problem. The problem was that I was modifying server.xml instead of context.xml which is present in the same folder! One I copied the “Resource” configuration under Context element of context.xml DB connectivity was working fine.

If you are working on Eclipse, you can change context.xml directly from the “Servers” entry in the project explorer. In case of netbeans, context.xml is created inside META-INF folder of the project which can be changed to include the JNDI configuration.

How to connect to MySQL using JNDI from Tomcat6
1. Copy mysql-connector-java-3.0.17-ga-bin.jar to $TOMCAT_HOME/lib.
2. Add “Resource” entry in context.xml (under config folder of tomcat or under META-INF folder of project).
3. Add “resource-ref” entry in web.xml of the project.

June 16, 2008 | Posted in Programming

One Comment to “Configuring JNDI datasource in Tomcat 6”

  1. Emmanuel Says:
    July 2nd, 2008 at 12:12 am

    Hi,
    I was having the same problem and thought that your solution will help me but I am still having the same problem.
    Here is what I am getting:

    org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create JDBC driver of class ” for connect URL ‘null’
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1150)
    at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)

    I am using Netbeans 6.1 and tomcat 6
    I will very much appreciate if you can help me out.
    Here is my context:

    and in my web.xml:

    jdbc:mysql://localhost:3306/webapps
    jdbc/DataSource_Test
    javax.sql.DataSource
    Container

Leave a Comment