Employee List

/*
 * @(#)FastEmployeeList_21.java
 *
 * Copyright (c) 1998 Karl Moss. All Rights Reserved.
 *
 * You may study, use, modify, and distribute this software for any
 * purpose provided that this copyright notice appears in all copies.
 *
 * This software is provided WITHOUT WARRANTY either expressed or
 * implied.
 *
 * @author  Karl Moss
 * @version 1.0
 * @date    20Mar99
 *
 */

package javaservlets.db;

import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;

/**
 * <p>This is a simple servlet that will use JDBC to gather all
 * of the employee information from a database and format it
 * into an HTML table. This servlet uses a global connection
 * pool accessed by an attribute in the servlet context (new
 * for JSDK version 2.1)
 */

public class FastEmployeeList_21 extends HttpServlet
{
  /**
    * <p>Performs the HTTP GET operation
    *
    * @param req The request from the client
    * @param resp The response from the servlet
    */
  public void doGet(HttpServletRequest req,
                    HttpServletResponse resp)
    throws ServletException, java.io.IOException
    {
      // Set the content type of the response
      resp.setContentType("text/html");

      // Create a PrintWriter to write the response
      java.io.PrintWriter out =
        new java.io.PrintWriter(resp.getOutputStream());

      // Print the HTML header
      out.println("<html>");
      out.println("<head>");
      out.println("<title>Employee List</title>");
      out.println("</head>");
      out.println("<h2><center>");
      out.println("Employees for Nezzer's Chocolate Factory");
      out.println("</center></h2>");
      out.println("<br>");
      out.println("<center>");
      out.println("(with global servlet connection pool)");
      out.println("</center><br>");

      query("SELECT Empno, Name, Position FROM Employee",
            out);
      
      // Wrap up
      out.println("</html>");
      out.flush();
      out.close();
    }

  /**
    * <p>Initialize the servlet. This is called once when the
    * servlet is loaded. It is guaranteed to complete before any
    * requests are made to the servlet
    *
    * @param cfg Servlet configuration information
    */
  public void init(ServletConfig cfg)
    throws ServletException
    {
      super.init(cfg);
    }

  /**
    * <p>Destroy the servlet. This is called once when the servlet
    * is unloaded.
    */
  public void destroy()
    {
      super.destroy();
    }

  /**
    * <p>Given the SQL query string, execute the query and
    * format the results into an HTML table
    *
    * @param query SQL query to execute
    * @param out PrintWriter to use to output the query results
    * @return true if the query was successful
    */
  private boolean query(String query,
                        java.io.PrintWriter out)
    throws ServletException
    {
      boolean rc = true;
      
      // The JDBC Connection object
      Connection con = null;
      
      // The JDBC Statement object
      Statement stmt = null;

      // The JDBC ResultSet object
      ResultSet rs = null;

      // Keep stats for how long it takes to execute
      // the query
      long startMS = System.currentTimeMillis();

      // Keep the number of rows in the ResultSet
      int rowCount = 0;

      // Get the ConnectionServlet that holds the
      // connection pool
      ServletConfig config = getServletConfig();
      ServletContext context = config.getServletContext();
      Object o = context.getAttribute(ConnectionServlet_21.KEY);
      if (o == null) {
        throw new ServletException("ConnectionServlet not started");
      }
      ConnectionServlet_21 conServlet = (ConnectionServlet_21) o;
      
      try {

        // Get an available connection from our connection pool
        con = conServlet.getConnection();
        
        // Create a statement object that we can execute queries
        // with
        stmt = con.createStatement();

        // Execute the query
        rs = stmt.executeQuery(query);

        // Format the results into an HTML table
        rowCount = formatTable(rs, out);

      }
      catch (Exception ex) {
        // Send the error back to the client
        out.println("Exception!");
        ex.printStackTrace(out);
        rc = false;
      }
      finally {
        try {
          // Always close properly
          if (rs != null) {
            rs.close();
          }
          if (stmt != null) {
            stmt.close();
          }
          if (con != null) {
            // Put the connection back into the pool
            conServlet.close(con);
          }
        }
        catch (Exception ex) {
          // Ignore any errors here
        }
      }

      // If we queried the table successfully, output some
      // statistics
      if (rc) {
        long elapsed = System.currentTimeMillis() - startMS;
        out.println("<br><i>" + rowCount + " rows in " +
                    elapsed + "ms</i>");
      }
      
      return rc;
    }

  /**
    * <p>Given a JDBC ResultSet, format the results into
    * an HTML table
    *
    * @param rs JDBC ResultSet
    * @param out PrintWriter to use to output the table
    * @return The number of rows in the ResultSet
    */
  private int formatTable(java.sql.ResultSet rs,
                          java.io.PrintWriter out)
    throws Exception
    {
      int rowCount = 0;
      
      // Create the table
      out.println("<center><table border>");
      
      // Process the results. First dump out the column
      // headers as found in the ResultSetMetaData
      ResultSetMetaData rsmd = rs.getMetaData();
      
      int columnCount = rsmd.getColumnCount();

      // Start the table row
      out.println("<tr>");
      
      for (int i = 0; i < columnCount; i++) {

        // Create each table header. Note that the column index
        // is 1-based
        out.println("<th>" +
                    rsmd.getColumnLabel(i + 1) +
                    "</th>");
      }

      // End the table row
      out.println("</tr>");
        
      // Now walk through the entire ResultSet and get each
      // row
      while (rs.next()) {
        rowCount++;

        // Start a table row
        out.println("<tr>");
        
        // Dump out the values of each row
        for (int i = 0; i < columnCount; i++) {
          
          // Create the table data. Note that the column index
          // is 1-based
          out.println("<td>" +
                      rs.getString(i + 1) +
                      "</td>");
        }

        // End the table row
        out.println("</tr>");
      }
      
      // End the table
      out.println("</table></center>");

      return rowCount;
    }
  
}