How to Setup JSTL & JNDI
for MySQL 4.x JDBC Connection Pooling
with Tomcat 5.0 and 5.5

Preface

Nomenclature:

Note: All examples are given in Unix path notation, use Windows notation where applicable.
 

Section Index

Configuration

Pre-requisite setup:

Note: You only need naming-common.jar with 5.0 implementations, do not include it with 5.5 (or Tomcat will not start) because it is already bundled in the other 5.5 version of the factory Jar.

/usr/local/tomcat/5.5.7/common/lib

commons-collections-3.1.jar             
commons-dbcp-1.2.1.jar
commons-el.jar
commons-pool-1.2.jar
jasper-compiler-jdt.jar
jasper-compiler.jar
jasper-runtime.jar
jsp-api.jar
jstl.jar
mysql-connector-java-3.1.8-bin.jar
naming-java.jar
naming-factory.jar
naming-factory-dbcp.jar
naming-resources.jar
servlet-api.jar

naming-common.jar

If you don't have some of these, you can get the entire 5.5.7 bundle here (less the naming-common.jar file).
Unix/Linux/Mac OS X
Windows (Zip)

Database Setup:

glagnese:/ $ mysql -h mysql.microdeveloper.com -u scott -p

Enter password: 

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31 to server version: 4.1.9-nt-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> connect test
     Connection id: 32
     Current database: test

mysql> create table messages (text_message varchar(50));
Query OK, 0 rows affected (0.31 sec)
    

mysql> insert into messages values ('hello world');
Query OK, 1 row affected (0.05 sec)

mysql> insert into messages values ('Hello USA');
Query OK, 1 row affected (0.01 sec)

mysql> insert into messages values ('Hello Next Door');
     Query OK, 1 row affected (0.01 sec)

mysql> insert into messages values ('Hello Me');
     Query OK, 1 row affected (0.00 sec)

mysql> commit;
     Query OK, 0 rows affected (0.00 sec)

mysql> select * from messages;
+-----------------+
| text_message    |
+-----------------+
| hello world     |
| Hello USA       |
| Hello Next Door |
| Hello Me        |
+-----------------+
4 rows in set (0.00 sec)

mysql> exit
Bye

    

Steps to Implement:

<Context path="/dvdlib" docBase="dvdlib"       debug="5" reloadable="true" crossContext="true">
       
    <Resource name="jdbc/<alias>" 
       auth="Container"
       type="javax.sql.DataSource"
       maxActive="100" 
       maxIdle="30" 
       maxWait="10000"
       username="dvdlib"
       driverClassName="com.mysql.jdbc.Driver"
       url="jdbc:mysql://<server>:<port>/<database>?autoReconnect=true"/>
         
 </Context>

Example

<Engine defaultHost="localhost" name="Catalina">
   <Realm className="org.apache.catalina.realm.UserDatabaseRealm"/>
   <Host
       appBase="webapps"
       name="localhost">
   
      <Context path="/mydb" docBase="/mydb" debug="5" reloadable="true" crossContext="true">
         <Resource name="jdbc/test" 
             auth="Container"
             type="javax.sql.DataSource"
             maxActive="100" 
             maxIdle="30" 
             maxWait="10000"
             username="dvdlib"
             driverClassName="com.mysql.jdbc.Driver"
             url="jdbc:mysql://mysql.microdeveloper.com:3306/test?autoReconnect=true"/>
      </Context>
 
   </Host>
</Engine>

<web-app xmlns="http://java.sun.com/xml/ns/j2ee"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
   version="2.4">

   <description>Your Description</description>
   <resource-ref>
     <description>Connection Description</description>
     <res-ref-name>jdbc/<alias></res-ref-name>
     <res-type>javax.sql.DataSource</res-type>
     <res-auth>Container</res-auth>
   </resource-ref>

Example

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://java.sun.com/xml/ns/j2ee"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
   version="2.4">

   <description>This is a simple JSTL-JNDI test application</description>
   <resource-ref>
     <description>MySQL Database Connection</description>
     <res-ref-name>jdbc/test</res-ref-name>
     <res-type>javax.sql.DataSource</res-type>
     <res-auth>Container</res-auth>
   </resource-ref>
</web-app>

Testing the Changes

<%@ page contentType="text/html" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
  <head>
    <title>JSP is Easy</title>
  </head>
  <body bgcolor="white">
    <h1>JSP is as easy as ...</h1>
    <%-- Calculate the sum of 1 + 2 + 3 dynamically --%>
    1 + 2 + 3 = <c:out value="${1 + 2 + 3}" />
  </body>
</html>

Result

JSP is as easy as ...

<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>

<sql:query var="rs" dataSource="jdbc/mydb">
  select text_message from messages
</sql:query>

<html>
  <head>
    <title>DB Test</title>
  </head>
  <body>
    <h2>Results</h2>
    <c:forEach var="i" items="${rs.rows}">
      Text Message: <c:out value="${i.text_message}"/><br>
    </c:forEach>
  </body>
</html>

Results

Troubleshooting

Driver errors usually look like (Cannot create JDBC driver of class '' for connect URL 'null'):
Place the mysql-connector-java-3.1.6-bin.jar file in the <CATALINA_HOME>\common\lib directory
Do NOT place the JAR in your <CONTEXT>/WEB-INF/lib directory (this can cause problems)
If used with an IDE that auto-deploys, exclude the JAR from the deployment

Error messages that 'jdbc' is an unknown context:
Verify that the alias and the database names are correct and that you are using the alias in your calls. Check your alias and database configurations in the web.xml and server.xml files.

Make sure JSTL is working:
Verify that JSTL is working properly by using the test page given above. Make sure the web.xml uses the 2.4 syntax.