Prentice Hall and Sun Microsystems. Personal use only; do not redistribute.

18.7 Connection Pooling

501

Listing 18.18 PreparedStatements.java (continued)

  private static void printUsage() {

    System.out.println("Usage: PreparedStatements host " +

                       "dbName username password " +

                       "oracle|sybase [print].");

  }

}

18.7 Connection Pooling

Opening a connection to a database is a time consuming process. For short

queries, it can take much longer to open the connection than to perform the

actual database retrieval. Consequently, it makes sense to reuse 

Connection

objects in applications that connect repeatedly to the same database. This

section presents a class for connection pooling: preallocating database con 

nections and recycling them as clients connect. Servlets and JSP pages can

benefit significantly from this class since the database to which any given

servlet or JSP page connects is typically known in advance (e.g., specified in

the 

init

 method). For example, the servlet shown in Section 18.8 shows a

sevenfold performance gain by making use of this connection pool class.

A connection pool class should be able to perform the following tasks:

1. Preallocate the connections.

2. Manage available connections. 

3. Allocate new connections. 

4. Wait for a connection to become available. 

5. Close connections when required.

I'll sketch out the approach to each of these steps here. The full code for

the 

ConnectionPool

 class is shown in Listing 18.19. As with all classes in the

book, you can download the source code from 

http://www.coreserv 

lets.com/

.

1. Preallocate the connections. 

Perform this task in the class constructor. Allocating more con 

nections in advance speeds things up if there will be many con 

current requests later but causes an initial delay. As a result, a 

servlet that preallocates very many connections should build the 

Second edition of this book: www.coreservlets.com; Sequel: www.moreservlets.com.

Servlet and JSP training courses by book's author: courses.coreservlets.com.