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.
<Context path="/edi-web" docBase="edi-web" debug="5"
<Resource name="EDI-DS" auth="Container"
maxIdle="30" maxWait="10000" username="user"
In this example we have configured the DataSource EDI-DS which references an Oracle connection.
Use of the JDBC Data Sources requires that you make an appropriate JDBC driver available to both Tomcat internal classes and to your web application. This is most easily accomplished by installing the driver's JAR file(s) into the $CATALINA_HOME/common/lib directory, which makes the driver available both to the resource factory and to your application
(Notice: Tomcat 6 library repository is $CATALINA_HOME/lib )
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.
We can also specify the maximum time (in milliseconds) to wait for a database connection to become available, which in this example is 30 seconds.
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
Retrieving the Connection
Getting a connection form the DataSource is just a matter of looking up the Datasource from the InitialContext and then retrieve the associated connection:
Context ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("java:/comp/env/EDI-DS");
Connection conn = ds.getConnection();