ConnPoolTest.java sample

// *******************************************************************
// * ConnPoolTest.java - test connection pooling                     *
// *******************************************************************
package WebSphereSamples.ConnPool;

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.util.*;
// ********
// STEP 1 *
// ********
// Import JDBC packages and IBM implemented extensions, plus naming 
// service packages.
import java.sql.*;
import javax.sql.*;
import com.ibm.ejs.dbm.jdbcext.*;
import javax.naming.*;

public class ConnPoolTest extends HttpServlet
{
//    **************************************************************** 
//    * Variables                                                    *
//    ****************************************************************
   private static DataSource ds   = null;
   private static String user     = null; 
   private static String password = null;
   private static String owner    = null;
   private static String source   = null;
   private static String title    = "Connection Pool Test";
   private static String greeting = "Hello";
   private static String nobody   = "Nobody named ";

//    Name of property file used to complete the user, password,
//    and table owner information at runtime, plus other information.
//    The ".properties" extension is assumed.
   private static final String CONFIG_BUNDLE_NAME = 
   "WebSphereSamples.ConnPool.ConnPoolTestStrings";

//    ****************************************************************
//    * Initialize servlet when it is first loaded                   *
//    ****************************************************************
   public void init(ServletConfig config)
   throws ServletException
   {
      super.init(config);
      try
      {
//          Get information at runtime (from an external property file
//          identified by CONFIG_BUNDLE_NAME) about the user, password,  
//          and owner information.  This information could be provided 
//          in other ways - perhaps in a somewhat more secure compiled
//          resource bundle, or hardcoded within this application.
         PropertyResourceBundle configBundle = 
         (PropertyResourceBundle)PropertyResourceBundle.
         getBundle(CONFIG_BUNDLE_NAME);
         user     = configBundle.getString("poolServlet.user");
         password = configBundle.getString("poolServlet.password");
         owner    = configBundle.getString("poolServlet.owner");

//          Get context and logical name information to use below in a
//          naming service lookup to get a DataSource object.
         source   = configBundle.getString("poolServlet.source");
      }
      catch (Exception e)
      {
         System.out.println("Properties file exception: " + e.getMessage());
      }

      try
      {
//          **********
//          * STEP 2 *
//          **********
//          Create the initial naming context. 
         Hashtable parms = new Hashtable();
         parms.put(Context.INITIAL_CONTEXT_FACTORY, 
                   "com.ibm.ejs.ns.jndi.CNInitialContextFactory");
         Context ctx = new InitialContext(parms);

//          **********
//          * STEP 3 *
//          **********
//          Perform a naming service lookup to get a DataSource object.
//          The single DataSource object is a "factory" used by all
//          requests to get an individual connection for each request.
//          The Web administrator can provide the lookup parameters.
//          The code below uses a value read in from an external property
//          file.  The text string source is typically something like
//          "jdbc/sample" where "jdbc" is the context for the
//          lookup and "sample" is the logicalname of the DataSource
//          object to be retrieved.
         ds = (DataSource)ctx.lookup(source);
      }
      catch (Exception e)
      {
         System.out.println("Naming service exception: " + e.getMessage());
      }
   }

//    ****************************************************************
//    * Respond to user GET request                                  *
//    ****************************************************************
   public void doGet(HttpServletRequest req, HttpServletResponse res)
   {
      Connection conn  = null;
      Vector firstNameList = new Vector();
      String lastName = req.getParameter("radio");
      String searchName = null;
      if (lastName.equals("someone"))
      {
         searchName=req.getParameter("text");
      }
      else
      {
         searchName = lastName;
      }
      try
      {
//          **********
//          * STEP 4 *
//          **********
//          Get a Connection object conn using the DataSource factory.
         conn = ds.getConnection(user, password);

//          **********
//          * STEP 5 *
//          **********
//          Run DB query - create a Vector of first names of all people 
//          whose last name is 'PARKER'.
//          Standard JDBC coding follows.  Change the query for your
//          specific situation.
         Statement stmt = conn.createStatement();
         String query   = "Select FirstNme " + 
                          "from " + owner.toUpperCase() + ".Employee " +
                          "where LASTNAME = '" + searchName + "'";
         ResultSet rs   = stmt.executeQuery(query);
         while (rs.next())
         {
            firstNameList.addElement(rs.getString(1));
         }
//          Invoke close() on stmt, which also closes rs, freeing
//          resources and completing the interaction..
         stmt.close();

//          If satisfying a single request involves multiple uses of 
//          the conn object, extending over a period of time approaching
//          the maximum age parameter, then there is a chance that the
//          connection might be considered an orphan connection and be taken
//          away from the servlet.  If conn is taken away, then an attempt 
//          to use it (say, through the stmt or rs objects) will result 
//          in a com.ibm.ejs.dbm.jdbcext.ConnectionPreemptedException 
//          exception.  One possible response to such an exeption could be
//          to use the DataSource object once again to get another connection.
      }
      catch (Exception e)
      {
         System.out.println("Get connection, process, or close statement " +
                            "exception: " + e.getMessage());
      }

//       **********
//       * STEP 6 *
//       **********
//       Close the connection, which does not close and actual connection, 
//       but releases it back to the pool.
      finally
      {
         if (conn != null)
         {
            try
            {
               conn.close();
            }
            catch (Exception e)
            {
               System.out.println("Close connection exception: " + e.getMessage());
            }
         }
      }
//       **********
//       * STEP 7 *
//       **********
//       Prepare and return HTML response - say Hello to everyone 
//       whose last name is 'Parker' and address them with their first 
//       and last name.
      res.setContentType("text/html");
//       Next three lines prevent dynamic content from being cached
//       on browsers.
      res.setHeader("Pragma", "no-cache");
      res.setHeader("Cache-Control", "no-cache");
      res.setDateHeader("Expires", 0);
      try
      {
         ServletOutputStream out = res.getOutputStream();
         out.println("<HTML>");
         out.println("<HEAD><TITLE>" + title + "</TITLE></HEAD>");
         out.println("<BODY>");
         if (firstNameList.isEmpty())
         {
            out.println("<H1>" + nobody + searchName + "</H1>");
         }
         else
         {
            for (int i = 0; i < firstNameList.size(); i++)
            {
               out.println("<H1>" + greeting + " " + 
                           firstNameList.elementAt(i) + 
                           " " + searchName + "</H1>"); 
            }
         }
         out.println("</BODY></HTML>");
         out.close();
      }
      catch (IOException e)
      {
         System.out.println("HTML response: " + e.getMessage());
      }
   }
}