Database Connection Pooling with Tomcat

Software object pooling is not a new concept. There are many scenarios where some type of object pooling technique is employed to improve application performance, concurrency, and scalability. After all, having your database code create a new Connection object on every client request is an expensive process. Moreover, with today's demanding applications, creating new connections for data access from scratch, maintaining them, and tearing down the open connection can lead to massive load on the server.

We can configure a maximum number of DB connections in the pool. Make sure you choose a maximum connection count large enough to handle all of your database connections--alternatively, you can set 0 for no limit. Further, we can set the maximum number of idle database connections to be retained in the pool. Set this value to -1 for no limit. The most optimal performance is attained when the pool in its steady state contains just enough connections to service all concurrent connection requests, without having to create new physical database connections at runtime. We can also specify the maximum time (in milliseconds) to wait for a database connection to become available, which in this example is 10 seconds. An exception is thrown if this timeout is exceeded. You can set this value to -1 to wait indefinitely. Please make sure your connector driver, such as mysql.jar, is placed inside the /common/lib directory of your Tomcat installation.

To achieve performance and high throughput, we also need to fine-tune the container to work under heavy traffic. Here's how we'll configure the Connector element for the maxProcessors and acceptCount parameters in the server.xml file:

<!--  Configuring the request and response endpoints -->
<Connector port="80" maxHttpHeaderSize="8192" maxProcessors="150"
   maxThreads="150" minSpareThreads="25" maxSpareThreads="75"
   enableLookups="false" redirectPort="8443" acceptCount="150"
   connectionTimeout="20000" disableUploadTimeout="true" />

Configuring JNDI Reference

In order for JNDI to resolve the reference, we have to insert a <resource-ref> tag into the web.xml deployment descriptor file. We first begin by setting a <listener> tag for registering a ServletContextListener as shown below:

<listener>
        <listener-class> com.onjava.dbcp.DBCPoolingListener</listener-class>
</listener>

<!-- This component has a dependency on an external resource-->
 <resource-ref>
      <description> DB Connection Pooling</description>
      <res-ref-name> jdbc/TestDB</res-ref-name>
      <res-type> javax.sql.DataSource</res-type>
      <res-auth> Container</res-auth>
  </resource-ref>

<servlet>
        <servlet-name> EnrolledStudents</servlet-name>
        <servlet-class> com.onjava.dbcp.CourseEnrollmentServlet</servlet-class>
        <load-on-startup> 1</load-on-startup>
</servlet>

<servlet-mapping>
        <servlet-name> EnrolledStudents</servlet-name>
        <url-pattern> /enrollment.do</url-pattern>
</servlet-mapping>

This binding is vendor-specific, and every container has its own mechanism for setting data sources. Please note that this is just a declaration for dependency on an external resource, and doesn't create the actual resource. Comprehending the tags is pretty straightforward: this indicates to the container that the local reference name jdbc/TestDB should be set by the app deployer, and this should match with the resource name, as declared in server.xml file.

打算改天把这篇文章给翻译一下，留个记号先