Database Connection Pooling with Tomcatby Kunal Jaggi
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.
Connection pooling eliminates JDBC overhead. Further, object pooling also helps to reduce the garbage collection load. In this article, we'll look at an elegant way of creating a pool of open database-connection objects in Tomcat, so that they are handy whenever an application needs to access a DB resource.
With Database Connection Pooling (DBCP), we can scale our applications to handle increased load and deliver high performance benefits. Using recycled database connection objects cuts the time taken to re-instantiate and load frequently used objects, thus reducing unnecessary overheads. Configuring a DB pool can be a daunting task, because there has to be a way for different components within an application to know about the available pooled objects, and a mechanism to locate them. This is exactly where JNDI steps in, tying these dependencies together.
JNDI to the Rescue
The Java Naming and Directory Interface (JNDI) has been at the core of Java EE since its inception. JNDI offers a generic mechanism for Java EE components to find other components, resources, or services indirectly at runtime. The primary role of JNDI in a Java EE application is to provide an indirection layer, so that components can find required resources without being particularly aware of the indirection. This indirection is almost transparent. JNDI helps in holding applications together, but this coupling is very flexible, so that apps can be easily reassembled. JNDI spares you from providing direct references to the data source, JDBC driver class names, user names and passwords, or any vendor-specific quirks of setting up pooling. We just look up all of these dependencies at runtime through a JNDI call. The developer, on the other hand, is ignorant of the external resources.
Our approach to DBCP uses the Jakarta-Commons database connection pool. But first, we need to configure the JNDI
DataSource in Tomcat by adding a declaration for the resource to server.xml file, which resides inside the /conf directory of your Tomcat installation (indicated by the environment variable
CATALINA_HOME). The JNDI
DataSource is used as a factory for connections. One of the major advantages of using a configuration like this is that the characteristics of the pool can be changed without affecting the application code. Our application's use of connection pooling is almost transparent. The following code snippet shows us how to configure the container to enable connection pooling.
<Context path="/dbcp" docBase="dbcp" debug="5" reloadable="true" crossContext="true"> <Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource" removeAbandoned="true" removeAbandonedTimeout="30" maxActive="100" maxIdle="30" maxWait="10000" username="kunal" password="java_facier" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost/dbcptest"/> </Context>
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
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" />
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.