Try to make a Connection Pool with Tomcat 6

I really am having a nightmare configuring Tomcat to set up a connection pool. I have done a lot of reading of various forums and the documents from Tomcat but am having to ask here as a last resort. This is the first time I have tried to get connections from the container so it's all new to me.

I have been having NameNotFoundException's which only seem to be fixed when I put the context.xml file back from MyApp/META-INF/context.开发者_StackOverflow中文版xml to Tomcat 6.0/conf/context.xml, so for some reason it's not seeing the context.xml file in MyApp's META-INF directory. Any ideas?

Now I am getting an SQLNestedException: Cannot create PoolableConnectionFactory (''@'localhost' (using password:YES))

First of all it suprises me that the user is blank because I have specified 'root' in the context.xml. As for not being able to create a PoolableConnectionFactory, I have seen a couple of example context.xml files that had a factory attribute. Do I need this? If so what class should I specify there?

My context.xml is:

<?xml version='1.0' encoding='utf-8'?>
<Context>

<!-- Configure a JDBC DataSource for the user database -->
<Resource name="jdbc/searchdb" 
          type="javax.sql.DataSource" 
          auth="Container" 
          user="root" 
          password="mypassword" 
          driverClassName="com.mysql.jdbc.Driver" 
          url="jdbc:mysql://localhost:3306/search" 
          maxActive="8" 
          maxIdle="4"/>

<!-- Default set of monitored resources -->
<WatchedResource>WEB-INF/web.xml</WatchedResource>
<!--<WatchedResource>META-INF/context.xml</WatchedResource>-->

</Context>

I have seen a context.xml with a WatchedResource elemnt for the META-INF/context.xml. I tried it but it didn't seem to make a difference and it seems strange to me so I have commented it out. Should I actually be including it?

My test servlet:

package search.web;
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.util.*;
import java.sql.*;
import javax.sql.*;
import javax.naming.*;
import search.model.*;
public class ConPoolTest extends HttpServlet {

    public void doGet(HttpServletRequest request,
                  HttpServletResponse response) 
                  throws IOException, ServletException {
        Context ctx = null;
        DataSource ds = null;
        Connection conn = null;
        try {
            ctx = new InitialContext();
            ds = (DataSource)ctx.lookup("java:comp/env/jdbc/searchdb");
            conn = ds.getConnection();
            if(conn != null) {
                System.out.println("have a connection from the pool");
            }
        } catch(SQLException e) {
            e.printStackTrace();
        } catch(NamingException e) {
            e.printStackTrace();
        } finally {
            try {
                if(conn!=null) {
                    conn.close();
                }
            } catch(SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

I look forward to your suggestions.

Many thanks

Joe

PS I would have also listed the stack trace, but for some reason it is showing up in the console, but not in the logs.

Update:

Now that I look at the error message again I'm wondering what it is that is denying me access. I assumed that it was the database, but is itactually the container? Do I need to set up some sort of authentication in the tomcatusers.xml file?

The Tomcat docs for JNDI Datasources contain complete examples how to setup JDBC data sources in the context.xml

Some comments to your question:

1. Tomcat should copy the context.xml from your app's WAR to conf/Catalina/localhost/app.xml during deployment (when it unpacks your app). The file should not go to conf/. Check whether you have an old copy lying around in these places and clean that up.

2. The error that it's using the wrong user also suggests that there is more than a single context.xml and you're looking at the wrong one.

3. You don't need PoolableConnectionFactory with Tomcat 6. This might be cruft left from an update from Tomcat 5 or something broke and they tried several things and forgot to clean up the config file.

4. Tomcat automatically watches web.xml; there is no need to make it a WatchedResource a second time.