// ******************************************************************* // * 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()); } } }