ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.


AddThis Social Bookmark Button

Database Connection Pooling with Tomcat
Pages: 1, 2, 3

Putting DBCP into Action

As our application first starts asking the pool for Connection objects, they will be newly created, but when the application has finished with an object, it's returned to the pool rather than destroyed. This has huge performance benefits. Now, as the application needs more Connection objects, the pool will be able to issue recycled objects that have previously been returned by the application.

As an example, let's create a listener class to work with the pool. Our listener class implements the ServletContextListener interface; thus, it'll be initialized when the container starts and creates a ServletContext for this web app. Remember, there's only one ServletContext per web app. Any class implementing the ServletContextListener interface is initialized when the container starts. This early initialization cuts unnecessary overhead later, since it's ideal to have a cached set of open database connection objects available when the container starts rather than waiting for a client request. Inside the listener class, we'll do the necessary JNDI lookup and then set the DataSource as a ServletContext attribute so that it's available to the entire web app. The following code snippet shows us how to extract DataSource through a JNDI call:

public class DBCPoolingListener implements
 public void contextInitialized
  (ServletContextEvent sce){

  try {
    // Obtain our environment naming context
    Context envCtx = (Context) new InitialContext().

    // Look up our data source
    DataSource  ds = (DataSource) envCtx.lookup

      ("DBCPool", ds);
   } catch(NamingException e){ e.printStackTrace();
 public void contextDestroyed(ServletContextEvent

The component naming context is indicated by the prefix java:comp/env/.

For the sake of simplicity, we'll create a simple servlet, hard-coding the business logic and presentation. We'll use the JDBC 2.0 Standard Extension API, which specifies that a database service provider can implement a pooling technique that can allow multiple Connection objects to be shared among several requesting clients. Here's how we'll extract DataSource from the ServletContext attribute and then establish a Connection to pooled DB connection objects.

public void init() throws ServletException {
    try {
 //Create a datasource for pooled connections.
 datasource = (DataSource) getServletContext().

  //Register the driver for non-pooled connections.
    catch (Exception e) {
      throw new ServletException(e.getMessage());

The servlet is written to use either pooled or non-pooled database connections, depending on the query string passed in its URL. The servlet fetches a pooled connection object using Tomcat DBCP, and non-pooled connections directly from MySQL connector.

Here's an example of obtaining a Connection object. If the pooledConnection flag is set, it simply calls getConnection() on the DataSource. If not, it manually creates a new Connection object:

private synchronized Connection getConnection
    (boolean pooledConnection)
    throws SQLException {
  if (pooledConnection) {

    // Allocate and use a connection from the pool
    return datasource.getConnection();
  else {

    Connection con = DriverManager.getConnection(
    return con;   //return a newly created object

Having acquired a Connection, the servlet executes a simple join between the course and enrollment tables, and then formats and outputs the results as HTML. The example uses PreparedStatement to pre-compile SQL and run it repeatedly. This eliminates the tedious task of parsing and compiling the SQL query on every client request. Pre-compilation improves performance and offers enhanced security by preventing SQL injection attacks. For thread safety, we'll keep Connection, PreparedStatement, and ResultSet as local variables inside of the doGet() method.

Pages: 1, 2, 3

Next Pagearrow