Database Programming with JDBC & Java, Second Edition

Connection Pooling

Up to this point, you have created a connection, done your database business, and closed the connection. This process clearly works fine for the examples I have presented to this point in the book. Unfortunately, it does not work in real-world server applications. It does not work because the act of creating a database connection is a very expensive operation for most database engines. If you have a server application, such as a Java servlet or a middle-tier application server, that application is likely going back and forth between the database many times per minute. Suddenly, the “open connection, talk to the database, close connection” model of JDBC programming becomes a huge bottleneck.

The JDBC Optional Package provides a standardized solution to the problem—connection pooling.^[13] Connection pooling is a mechanism through which open database connections are held in a cache for use and reuse by different parts of an application. In a Java servlet, for example, each user initiates the execution of the servlet’s doGet() method, which grabs a Connection instance from the connection pool. When it is done serving that user, it returns the Connection instance to the pool. The Connection is never closed until the web server shuts down.

Unlike the parts of JDBC you have encountered so far, connection pooling is not necessarily implemented by driver vendors. While a connection pool can be implemented by driver vendors (the mSQL-JDBC driver comes with a JDBC 2.0 Optional ...