I needed to set up a connection pool for a JSP application using Tomcat 5.5 and the Informix 3.0 JDBC driver recently and had a hard time finding any Informix specific instructions, so thought I might aswell start with that..
Since connection is an expensive part of a JDBC application it makes sense to maintain a connection pool, particularly for simple applications which connect/disconnect each time the page is viewed. Tomcat 5.5 comes with the Jakarta Commons Database Connection Pool implementation built-in so configuring a connection pool to work with any JDBC driver is fairly straightforward, and can be divided into 3 steps. In the following notes $CATALINA_HOME is assumed to be the path where you installed Tomcat, the application under webapps is called jspdemo, the INFORMIXSERVER is ol_myserver on mymachine:
1. Add a Context entry to conf/server.xml.
The Context entry goes in server.xml before the end of the Host definition </Host>. Add a Context entry for each connection URL required.
Edit $CATALINA_HOME/conf/server.xml and add:
<Context path="/jspdemo" docBase="jspdemo" debug="0" reloadable="true" crossContext="true">
<Resource name="jdbc/jspdemo" auth="Container" type="javax.sql.DataSource" maxActive="20"
maxIdle="10" maxWait="1000" username="informix" password="mypasswd"
Read the Tomcat documentation for more information on the resource parameters such as maxActive and maxIdle.
2. Add a Resource reference to WEB-INF/web.xml.
If the application directory is jspdemo, the web.xml file will be:
$CATALINA_HOME/webapps/jspdemo/WEB-INF/web.xml - If you don't have one a Tomcat tutorial should guide you through creating a basic one. Before the </web-app> tag add the following:
<description>Informix Datasource example</description>
Note the res-ref-name is the same as the Resource name defined in server.xml.
3. Modify the JSP application to get the Connection from the connection pool.
Add javax.naming.*,javax.sql.* to your JSP page import tag.
Replace the old
Connection conn = ...with:
Context initContext = new InitialContext();
Context envContext = (Context) initContext.lookup("java:/comp/env");
DataSource ds = (DataSource) envContext.lookup("jdbc/jspdemo");
Connection conn = ds.getConnection();
Restart Tomcat to re-read the server.xml settings and the connection pool should be operational. After a few connections run onstat -u at the database server to see the pooled sessions.