by Steven J. Owens (unless otherwise attributed)
This is just a simple, straight example of how to configure connection pooling in Tomcat 4.1.30, using the Jakarta-Commons DBCP (Database Connection Pooling) that comes bundled with Tomcat. This example assumes you're using MySQL.
There are three things you should take care of first, to use this example:
Setting up the database and setting your database username/password are database-specific, and there are a gazillion web pages out there on how to do that in mysql. However, just because I hate having to go dig up information, here's a quickie example of how I usually do it using mysql:
$ mysql -u root -p Password: yourmysqlrootpasswordhere mysql> create database mygloriouswebapp; mysql> grant all on mygloriouswebapp.* to glorious identified by "foobar" ; mysql> grant all on mygloriouswebapp.* to glorious@localhost identified by "foobar" ; mysql> exit ;
I have been told that there are performance-related downsides to granting all permissions, so I would recommend that you make sure to come back and tweak the permissions for this user before going into production.
If you're using something besides mysql (like, say, postgres) then be an adult and google for how to set it up.
The JDBC driver is more java/tomcat-specific. MySQL's JDBC driver is available at the MySQL website (mysql.com, duh). For example, in my personal installation of tomcat for development work, my jar is:
/common/lib/mysql-connector-java-2.0.14-bin.jar
Now, the exact location of where your jar file needs to go can actually get to be a bit of a hairy topic, due to the details of how classloading works in a servlet engine. In a nutshell, there are several different lib directories in your java and tomcat installation, and the result will be an entire hierarchy of classloaders, with some classloaders having some jars. Which lib directory you put the jar in will determine which code can load it. This can cause frustrating and tricky behavior, which I'm not really going to go into here, but you should be aware of if and can read more about it at:
http://jakarta.apache.org/tomcat/tomcat-4.1-doc/class-loader-howto.html
http://jakarta.apache.org/tomcat/tomcat-5.0-doc/class-loader-howto.html
There are essentially three facts you have to know or decide:
...and three places you have to enter those facts:
Okay, so here we go. The parts you have to edit are in bold red. If you're color blind and also unable to see the bold for some reason, do a case-insensitive search for "foo".
In the <context> tag set (in either server.xml, or in tomcat/webapps/webappname.xml):
<context>
....
<Resource name="jdbc/FooDB"
auth="Container"
type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/FooDB">
<!-- Ask tomcat to watch out for unclosed connections and close them -->
<parameter>
<name>removeAbandoned</name>
<value>true</value>
</parameter>
<parameter>
<name>removeAbandonedTimeout</name>
<value>300</value>
</parameter>
<parameter>
<name>logAbandoned</name>
<value>true</value>
</parameter>
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<!-- Maximum number of dB connections in pool. Make sure you
configure your mysqld max_connections large enough to handle
all of your db connections. Set to 0 for no limit.
-->
<parameter>
<name>maxActive</name>
<value>100</value>
</parameter>
<!-- Maximum number of idle dB connections to retain in pool.
Set to 0 for no limit.
-->
<parameter>
<name>maxIdle</name>
<value>30</value>
</parameter>
<!-- Maximum time to wait for a dB connection to become available
in ms, in this example 10 seconds. An Exception is thrown if
this timeout is exceeded. Set to -1 to wait indefinitely.
-->
<parameter>
<name>maxWait</name>
<value>10000</value>
</parameter>
<!-- MySQL dB username and password for dB connections -->
<parameter>
<name>username</name>
<value>foo</value>
</parameter>
<parameter>
<name>password</name>
<value>bar</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>com.mysql.jdbc.Driver</value>
</parameter>
<!-- The JDBC connection url for connecting to your MySQL dB.
The autoReconnect=true argument to the url makes sure that the
mm.mysql JDBC Driver will automatically reconnect if mysqld closed the
connection. mysqld by default closes idle connections after 8 hours.
-->
<parameter>
<name>url</name>
<value>jdbc:mysql://localhost:3306/foo?autoReconnect=true</value>
</parameter>
</ResourceParams>
</context>
Note that the number of connections and other details are the default values that come with tomcat. This will work for a lot of stuff, but you will eventually have to tune your numbers to fit your application.
In tomcat/webappname/WEB-INF/web.xml:
<web-app>
...
<resource-ref>
<description>DB Connection for Foo project</description>
<res-ref-name>jdbc/FooDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
In the application code:
public Connection getConnection() {
try {
Context context = new InitialContext();
if (context == null ) { throw new Exception("Boom - No Context"); }
DataSource datasource = (DataSource)context.lookup("java:comp/env/jdbc/FooDB");
if (datasource == null ) { throw new Exception("Boom - No DataSource"); }
return datasource.getConnection();
} catch (Exception e) {
e.printStackTrace() ;
throw new RuntimeException("Database Not Available.") ;
}
}
Fire up tomcat and give it a try. Keep an eye on the logs (typically in tomcat/logs) and look for error messages. I've found that typically there's a webapp log as well as the general tomcat log (catalina.out on tomcat 4.x), and sometimes errors will show up the webapp log and not in the tomcat log. It took me a while to get in the habit of checking in both logs.