10 March 2011

DB2 Connection Pooling in tomcat 6

The Context element represents a web application, which is run within a particular virtual host. Each web application is based on a Web Application Archive (WAR) file, or a corresponding directory containing the corresponding unpacked contents.

The web application used to process each HTTP request is selected by Catalina based on matching the longest possible prefix of the Request URI against the context path of each defined Context. Once selected, that Context will select an appropriate servlet to process the incoming request, according to the servlet mappings defined in the web application deployment descriptor file (which MUST be located at /WEB-INF/web.xml within the web app's directory hierarchy).

You may define as many Context elements as you wish. Each such Context MUST have a unique context path. In addition, a Context must be present with a context path equal to a zero-length string. This Context becomes the default web application for this virtual host, and is used to process all requests that do not match any other Context's context path.

Sample context.xml ($CONTEXT_PATH/MET-INF/context.xml):

<Context path="/CONTEXT_PATH" docBase="DOC_BASE" crossContext="true" reloadable="true" debug="1">
   <Resource name="jdbc/RESOURCE_NAME" auth="Container" type="javax.sql.DataSource" driverClassName="com.ibm.db2.jcc.DB2Driver" url="jdbc:db2://HOST_NAME:PORT_NUMBER/DATABASE_NAME" u
sername="USER_NAME" password="PASSWORD" maxActive="10" maxIdle="5" maxWait="100000" removeAbandoned="true">


CONTEXT_PATH - The context path of this web application, which is matched against the beginning of each request URI to select the appropriate web application for processing. All of the context paths within a particular Host must be unique. If you specify a context path of an empty string (""), you are defining the default web application for this Host, which will process all requests not assigned to other Contexts. The value of this field must not be set except when statically defining a Context in server.xml, as it will be inferred from the filenames used for either the .xml context file or the docBase.

DOC_BASE - The Document Base (also known as the Context Root) directory for this web application, or the pathname to the web application archive file (if this web application is being executed directly from the WAR file). You may specify an absolute pathname for this directory or WAR file, or a pathname that is relative to the appBase directory of the owning Host. The value of this field must not be set when the Context is configured using a META-INF/context.xml file as it will be inferred by the automatic deployment process. If a symbolic link is used for docBase then changes to the symbolic link will only be effective after a Tomcat restart or by undeploying and redeploying the context. A context reload is not sufficient.

RESOUCE_NAME - Name of the resource (JNDI name)

HOST_NAME - Host name where the DB2 database is running

PORT_NUMBER - Post number on which DB2 instance is running

DATABASE_NAME - Name of database where the table is located

USER_NAME - Dabatabase user name

PASSWORD - Database user password

Other Parameter Specification:
maxActive - Maximum number of dB connections in pool. Make sure you configure your mysqld max_connections large enough to handle all of your db connections. Set to 0 for no limit.

maxIdle - Maximum number of idle dB connections to retain in pool. Set to -1 for no limit. See also the DBCP documentation on this and the minEvictableIdleTimeMillis configuration parameter.

maxWait - Maximum time to wait for a dB connection to become available in ms, in this example 10 seconds. An Exception is thrown if this timeout is exceeded. Set to -1 to wait indefinitely.

driverClassName - JDBC Class name for Connector/J.

url - The JDBC connection url for connecting to your dB. The autoReconnect=true argument to the url makes sure that the JDBC Driver will automatically reconnect
if db closed the connection.

Put dc2jcc.jar in $CATALINA_HOME/lib directory

Getting Database connection in HttpServlet sample code:

import java.io.*;
import java.sql.*;
import javax.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.naming.*;

public class TestDBCP extends HttpServlet
     public void doPost(HttpServletRequest request, HttpServletResponse response)
     throws ServletException,IOException     {

          PrintWriter out = response.getWriter();
          Connection con = null;
     PreparedStatement pstmt = null;
     ResultSet rs = null;
          String username = "TestUSER";
     String password = "TESTPWD";

          try {
              InitialContext context = new InitialContext();

              // Look up the data source
              DataSource dataSource =

              // Get a connection from the pool
              con = dataSource.getConnection();

              String query = "select fullname from user where username = ? and password = ? ";
              // ...Use the connection...
         pstmt = con.prepareStatement(query);
              pstmt.setString(1, userName);
         pstmt.setString(2, password);
              rs = pstmt.executeQuery();
         String result = null;

         if (rs.next()) {
                  String result = rs.getString("fullname");              }

              if (result != null) {
                  out.println("Welcome : " + result);

              } else {

                  out.println("Not a valid user, please try again");

      } catch (Exception e) {
              out.println("ERROR : " + e.getMessage());

      } finally {
              try {
             if (rs != null) rs.close();
             if (pstmt != null) pstmt.close();

          } catch (Exception e) {

          } finally {
             try {

              if (con != null) con.close();

             } catch (e) { }


No comments:

Post a Comment