***UPDATE***
Please see our FAQ on Poolman installation instructions. PoolMan is the current recommended pooling solution since it can share database pools across both JSP documents and servlets.

Some customers that are using JDK1.2 have been experiencing trouble with the sample code provided here. It turns out that this was being caused because the twz1 drivers needed to be recompiled using Java 2. Since Servlets.Net no longer recommends using the twz1 drivers, we have changed the sample to use the MM.MySQL drivers. If you experience the following error message, please make sure to use the MM.MySQL driver:

java.lang.NoClassDefFoundError: twz1/jdbc/mysql/jdbcMysqlDriver

***NOTICE***
The code supplied here was developed using an older version of the JavaExchange JDBCGlobalBrokerKit. It uses the Singleton approach to creating global connection pools. The JavaExchange site now recommends using the Inheritance approach. Servlets.Net advises that you examine both approaches when developing your pooling solution. We plan to add additional examples to our site illustrating how to do this.

It is important to properly use database connection pooling when you are developing servlets that communicate with your MySQL database. There are only a certain number of database connections available to you, so this example will help you use them more efficiently. More information on database connection pooling can be found in the Database section of the FAQ.

This example has been adapted from the GlobalBrokerExample1.java file available from www.javaexchange.com. It has been modified to work specifically with MySQL and Servlets.Net web hosting services.

This example shows how to set up a global Database Connection Broker in a Servlet using JDBCGlobalBroker and DbConnectionBroker. Once a JDBCGlobalBroker has been created, the settings that it was created with will be used for all additional instances of the class. This allows all of your servlets to use the same pool of database connections.

For example, if you had 10 servlets and 2 database connections to your database, you will want to set up a global broker with a max pool size of 2. That way, all 10 servlets would share the 2 connections in Round-Robin order.

You could alternatively have each servlet manage its own pool of database connections. This can be done by creating an instance of JDBCConnectionBroker rather than JDBCGlobalBroker. The drawback is that each servlet will then need its own set of database connections, potentially using more connections than your Servlets.Net account allows.

Servlets.Net encourages you to use the JDBCGlobablBroker and share a pool of all of your connections with all of your servlets. This sample demonstrates how to do this.

Whenever you change a servlet or a JSP page on your site, Live Software's JRun servlet runner will reload all of your servlets. When this happens, your broker will be destroyed and a new one created. This might cause problems. To avoid this, you should set up a "master broker servlet" that extends the PermanentServlet class:

com.livesoftware.jrun.PermanentServlet

Servlets implementing this interface will have their "init" and "destroy" methods called when first loaded, and when the servlet engine shuts down. Usually, these type of servlets should not be dynamically reloaded. This means that these servlets should be placed within your "classes" directory rather than within your "servlets" directory.

The JDBC driver and the Connection Broker classes that are used in this example are already installed and available to your JVM. The sample should work on your site once you modify the sample file to point to your database and query your tables.

Download the MySQL Broker Example: MySQLBrokerExample.txt (4.5KB)

Here is an example that demonstrates using Live Software's JSPDBConnection bean to pool database connections from JSP pages: JSPDBConnectionExample.txt (0.7KB)