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

import HTML.*;
import ConnectionPool.*;

public class TitleListGlobalPooledServlet extends HttpServlet {

  public void init(ServletConfig config)
    throws ServletException {

    super.init(config);
  }

  //Process the HTTP Get request
  public void doGet(HttpServletRequest request,
    HttpServletResponse response)
      throws ServletException, IOException {

    // If we get a GET request, pass the request/response to
    // the doPost() method
    doPost(request, response);
  }

  //Process the HTTP Post request
  public void doPost(HttpServletRequest request,
    HttpServletResponse response)
    throws ServletException, IOException {

    response.setContentType("text/html");
    PrintWriter out = response.getWriter();

    Connection con = null;
    ConnectionPool pool = null;

    // Create the HTML Document
    HTMLDocument document =
      new HTMLDocument("Title List Pooled Servlet");

    try {


      // Get a reference to the ConnectionPool from the Global
      // ServletContext
      pool =(ConnectionPool)
        getServletContext().getAttribute("CONNECTION_POOL");

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

      if ( con != null ) {

        // Create the statement
        Statement statement = con.createStatement();

        // Use the created statement to SELECT the DATA
        // FROM the Titles Table.
        ResultSet rs = statement.executeQuery("SELECT * " +
         "FROM Titles");

        // Create the HTMLTable
        HTMLTable table = new HTMLTable();
        table.setBorder(1);

        HTMLTableRow row = null;
        HTMLTableCell cell = null;

        // Create the Cell Headings
        row = new HTMLTableRow();

        cell = new HTMLTableCell(HTMLTableCell.HEADING);
        cell.addObject(new HTMLText("ID"));
        row.addObject(cell);

        cell = new HTMLTableCell(HTMLTableCell.HEADING);
        cell.addObject(new HTMLText("Name"));
        row.addObject(cell);

        cell = new HTMLTableCell(HTMLTableCell.HEADING);
        cell.addObject(new HTMLText("Rating"));
        row.addObject(cell);

        cell = new HTMLTableCell(HTMLTableCell.HEADING);
        cell.addObject(new HTMLText("Price"));
        row.addObject(cell);

        cell = new HTMLTableCell(HTMLTableCell.HEADING);
        cell.addObject(new HTMLText("Quantity"));
        row.addObject(cell);

        cell = new HTMLTableCell(HTMLTableCell.HEADING);
        cell.addObject(new HTMLText("Type ID"));
        row.addObject(cell);

        cell = new HTMLTableCell(HTMLTableCell.HEADING);
        cell.addObject(new HTMLText("Category ID"));
        row.addObject(cell);

        table.addObject(row);

        // Iterate over the ResultSet
        while ( rs.next() ) {

          row = new HTMLTableRow();

          // get the id, which is an int
          cell = new HTMLTableCell(HTMLTableCell.DATA);
          cell.addObject(new HTMLText(
            new Integer(rs.getInt("title_id")).toString()));
          row.addObject(cell);

          // get the name, which is a String
          cell = new HTMLTableCell(HTMLTableCell.DATA);
          cell.addObject(
            new HTMLText(rs.getString("title_name")));
          row.addObject(cell);

          // get the rating, which is a String
          cell = new HTMLTableCell(HTMLTableCell.DATA);
          cell.addObject(
            new HTMLText(rs.getString("rating")));
          row.addObject(cell);

          // get the price, which is a Float
          cell = new HTMLTableCell(HTMLTableCell.DATA);
          cell.addObject(new HTMLText(
            new Float(rs.getFloat("price")).toString()));
          row.addObject(cell);

          // get the Quantity, which is a Integer
          cell = new HTMLTableCell(HTMLTableCell.DATA);
          cell.addObject(new HTMLText(
            new Integer(rs.getInt("quantity")).toString()));
          row.addObject(cell);

          // get the Type, which is a Integer
          cell = new HTMLTableCell(HTMLTableCell.DATA);
          cell.addObject(new HTMLText(
            new Integer(rs.getInt("type_id")).toString()));
          row.addObject(cell);

          // get the Category, which is a Integer
          cell = new HTMLTableCell(HTMLTableCell.DATA);
          cell.addObject(new HTMLText(
            new Integer(rs.getInt("category_id")).toString()));
          row.addObject(cell);
          table.addObject(row);
        }
        // Close the ResultSet
        rs.close();
        document.addObject(table);
      }
    }
    catch (SQLException sqle) {

      System.err.println(sqle.getMessage());
    }
    catch (Exception e) {

      System.err.println(e.getMessage());
    }
    finally {

      // Release the connection
      pool.releaseConnection(con);
    }
    out.println(document.toHTML());
    out.close();
  }

  //Get Servlet information
  public String getServletInfo() {

    return "TitleListGlobalPooledServlet Information";
  }
}