I have seen a lot of messages concerning the configuration of MySQL datasources in Tomcat. I just tried it out and got it to work and wanted to post what I've discovered.
I am using the following:
Tomcat 5.5.7
DB: MySQL 4.1
Connector: mysql-connector-java-3.1.7-bin.jar (which I placed in <TOMCAT_INSTALL_DIR>\common\lib)
db user: testuser
db user password: password
db: test (which contains a table called "testdata" with columns: id, foo, bar)
I have a web app called "myApp" and it is deployed in Tomcat 5.5.7 as <TOMCAT_INSTALL_DIR>\webapps\myApp.war.
myApp's web.xml is literally empty. It looks like this:
<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE web-app
PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.2//EN"
"
http://java.sun.com/j2ee/dtds/web-app_2_2.dtd">;
<web-app> </web-app>
I created a file callled myApp.xml and placed it under <TOMCAT_INSTALL_DIR>\conf\Catalina\localhost. Below is the content of myApp.xml:
<Context path="/myApp" docBase="myApp"
debug="5" reloadable="true" crossContext="true">
<Resource name="jdbc/myAppDB" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
username="testuser" password="password" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/test?autoReconnect=true" validationQuery="select count(*) from testdata"/>
</Context>
myApp.war includes a JSP to test out the connection. Below is the source code for the JSP:
<%@ page import="java.sql.Connection,
javax.sql.DataSource,
javax.naming.Context,
javax.naming.InitialContext,
java.sql.PreparedStatement,
java.sql.ResultSet,
java.sql.SQLException"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<body>
<%
PreparedStatement stmt = null;
ResultSet rs = null;
Connection conn = null;
try {
Context initContext = new InitialContext();
Context envContext = (Context)initContext.lookup("java:/comp/env");
DataSource ds = (DataSource)envContext.lookup("jdbc/myAppDB");
conn = ds.getConnection();
stmt = conn.prepareStatement("select id, foo, bar from testdata");
rs = stmt.executeQuery();
String id = null;
String foo = null;
String bar = null;
while (rs.next()) {
id = rs.getString("id");
foo = rs.getString("foo");
bar = rs.getString("bar");
%>
<BR><%=id%><BR><%=foo%><BR><%=bar%><BR>
<%
}
rs.close();
rs = null;
stmt.close();
stmt = null;
conn.close();
conn = null;
} catch (SQLException e) {
e.printStackTrace();
} finally {
// Always make sure result sets and statements are closed,
// and the connection is returned to the pool
if (rs != null) {
try { rs.close(); } catch (SQLException e) { ; }
rs = null;
}
if (stmt != null) {
try { stmt.close(); } catch (SQLException e) { ; }
stmt = null;
}
if (conn != null) {
try { conn.close(); } catch (SQLException e) { ; }
conn = null;
}
}
%>
</body>
</html>
I hope this helps somebody.