I spent some time working through just this issue and now have a tomcat-5 connection pool working against mysql.
Here are the applicable software versions:
tomcat: v5.0.27
mysql: Ver 12.22 Distrib 4.0.20
0. Shutdown tomcat.
1. If not already present, copy the following (recent) versions of the mysql connector and commons-* jars
to $TOMCAT_HOME/common/lib. Delete any old ones.
mysql-connector-java-3.0.14-production-bin.jar
commons-collections-2.1.1.jar
commons-dbcp-1.2.1.jar
2. Ignore the advice in the tomcat-5 docs on jakarta.apache.org telling you to modify server.xml. Tomcat 5 uses a minimalist server.xml and now does application-specific configuration in separate files. If you have mucked up your server.xml either by hand editing or using the tomcat manager app, start fresh. Shutdown tomcat and copy a new minimalist server.xml in place.
If your webapp is called foo, create a file $TOMCAT_HOME/conf/Catalina/localhost/foo.xml. Save a copy of foo.xml in another location - the working copy is deleted if you undeploy foo using the tomcat manager.
If your mysql database is called sampdb, the host serving mysql is mysqlhost.mydomain.com, and your mysql user/password for schema sampdb is sampadm/xxx add the following to foo.xml:
<?xml version='1.0' encoding='utf-8'?>
<Context crossContext="true" debug="5" docBase="foo" path="/foo" reloadable="true">
<Logger className="org.apache.catalina.logger.FileLogger" prefix="localhost_foo_log." suffix=".txt" timestamp="true"/>
<Resource name="jdbc/sampdb" type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/sampdb">
<parameter>
<name>url</name>
<value>jdbc:mysql://mysqlhost.mydomain.com:3306/sampdb?autoReconnect=true</value>
</parameter>
<parameter>
<name>validationQuery</name>
<value>select now()</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>30</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>10</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>com.mysql.jdbc.Driver</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>10000</value>
</parameter>
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>username</name>
<value>sampadm</value>
</parameter>
<parameter>
<name>password</name>
<value>xxx</value>
</parameter>
</ResourceParams>
</Context>
Note that the above also declares a separate file for logging output from foo (localhost_foo_log.txt).
4. In your application you can obtain a connection from the pool as advertised in the tomcat docs.
Here is some template code to open a connection using the pool and then print the contents of the member table from the mysql sample database:
package mypackage;
import javax.naming.*;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.*;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
public class simpleDBCP extends HttpServlet {
// ***** Servlet access to data base
public void doGet (HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException
{
String query = "SELECT * FROM member";
try{
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
if(envCtx == null )
throw new Exception("Boom - No Environment Context");
// the following matches the resource name defined in foo.xml
DataSource ds =
(DataSource) envCtx.lookup("jdbc/sampdb");
if (ds != null) {
Connection conn = ds.getConnection();
if(conn != null) {
Statement stmt = conn.createStatement ();
ResultSet rs = stmt.executeQuery (query);
printResultSet ( resp, rs );
// important to cleanup
rs.close();
stmt.close();
conn.close();
} else {
throw new Exception("No Connection");
}
} else {
throw new Exception("No Datasource");
}
} catch(SQLException ex) {
PrintWriter out = resp.getWriter();
resp.setContentType("text/html");
while (ex != null) {
out.println ("SQL Exception: " + ex.getMessage ());
ex = ex.getNextException ();
} // end while
} // end catch SQLException
catch(NamingException ex) {
ex.printStackTrace();
}
catch(Exception ex) {
ex.printStackTrace();
}
} // end doGet
private void printResultSet ( HttpServletResponse resp, ResultSet rs )
throws SQLException {
try {
PrintWriter out = resp.getWriter();
out.println("<html>");
out.println("<head><title>rnm simpleDBCP</title></head>");
out.println("<body>");
out.println("<center><font color=AA0000>");
out.println("<h3>rnm Sample Database Connection Pool Servlet</h3>");
out.println("<h3>Data Retrieved:</h3>");
out.println("<table border='1'>");
int numCols = rs.getMetaData().getColumnCount ();
while ( rs.next() ) {
out.println("<tr>");
for (int i=1; i<=numCols; i++) {
out.print("<td>" + rs.getString(i) + "</td>" );
} // end for
out.println("</tr>");
} // end while
out.println("</table>");
out.println("</font></center>");
out.println("</body>");
out.println("</html>");
out.close();
} // end try
catch ( IOException ex) {
ex.printStackTrace();
} // end catch
} // end returnHTML
} // end simpleDBCP
5. Deploy foo.war and start tomcat. Be on the lookout for complaints from tomcat (maybe unable to login using the provided user/password, etc.).
** Additional info (unrelated to DBCP) **
If you wish to create a resource link, say to a globally defined mail resource, you can add something like the following after the closing ResourceParams tag:
<ResourceLink global="mail/Session" name="mail/Session" type="javax.mail.Session"/>
The above expects that a global resource mail/Session has been defined in server.xml as follows (note that this can be done via the tomcat manager application as well):
<Resource name="mail/Session" type="javax.mail.Session"/>
<ResourceParams name="mail/Session">
<parameter>
<name>mail.smtp.host</name>
<value>smtp_relay</value>
</parameter>
</ResourceParams>
Hope this helps.
- Rick
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
