What is a good tutorial on how to set up tomcat with mysql and database pooling?

Question

I have been looking for some good instructions on how to utilize connection pooling in Tomcat 6 connecting to Mysql 5, but there does not seem to be any complete references. I am having trouble piecing together different tutorials to get a clear picture.

I must admit, I am very much a beginner in dealing with Tomcat and it's configurations.

Answer 1

Place the MySQL driver jar file in $TOMCAT_HOME/lib/

Make a META_INF/context.xml file in your webapp (The META_INF/ folder is at the same level as your WEB_INF/ folder) that looks e.g. like:

<?xml version="1.0" encoding="UTF-8"?>
<Context path="/tomcattest" docBase="tomcattest" reloadable="true" crossContext="true">
    <Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"
       maxActive="10" maxIdle="5" 
       username="mysqluser" password="mysqlpasswd"
       driverClassName="com.mysql.jdbc.Driver"  
       url="jdbc:mysql://192.168.1.30:3306/databasename?autoReconnect=true" />
 </Context>

You probably have to replace path="/tomcattest" docBase="tomcattest" to match your webapp too.

You need to add a bit to your WEB_INF/web.xml too - the resource-ref section shown below. It might e.g. look like:

<?xml version="1.0" encoding="UTF-8"?>
<web-app id="WebApp_ID" version="2.4"
    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"&gt;
    <display-name>tomcattest</display-name>
    <servlet>
     <description>
     </description>
     <display-name>MyServlet</display-name>
     <servlet-name>MyServlet</servlet-name>
     <servlet-class>MyServlet</servlet-class>
    </servlet>

    <servlet-mapping>
     <servlet-name>MyServlet</servlet-name>
     <url-pattern>/MyServlet/*</url-pattern>
    </servlet-mapping>

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

</web-app>

Now your servlets can fetch Connections from the pool by doing:

Context initContext = new InitialContext();
DataSource ds   = (DataSource)initContext.lookup("java:/comp/env/jdbc/TestDB");
Connection conn = ds.getConnection();
//use conn
conn.close();

More info here