/*
 * @(#)ConnectionPoolServlet.java
 *
 * Copyright (c) 1998-1999 Servertec. All Rights Reserved.
 *
 * This software is the proprietary and confidential property of Servertec.
 * Use only in accordance with the terms of the license agreement.
 *
 * SERVERTEC MAKES NO REPRESENTATIONS OR WARRANTIES ABOUT THE SUITABILITY
 * OF THE SOFTWARE, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED
 * TO THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR
 * PURPOSE, OR NON-INFRINGEMENT. SERVERTEC SHALL NOT BE LIABLE FOR ANY
 * DAMAGES SUFFERED BY LICENSEE AS A RESULT OF USING, MODIFYING OR
 * DISTRIBUTING THIS SOFTWARE OR ITS DERIVATIVES.
 *
 * THIS NOTICE MUST NOT BE ALTERED NOR REMOVED.
 *
 * CopyrightVersion 1.0
 */

import java.io.IOException;

import java.sql.Statement;
import java.sql.ResultSet;

import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.ServletOutputStream;
import javax.servlet.ServletException;
import javax.servlet.http.Cookie;
import javax.servlet.ServletConfig;

import stec.sql.ConnectionPoolManager;
import stec.sql.Connection;

public class ConnectionPoolServlet extends HttpServlet
{
    public void service(HttpServletRequest _request, HttpServletResponse _response) throws ServletException, IOException
    {
        _response.setContentType("text/html");

        ServletOutputStream out = _response.getOutputStream();

        out.println("<html>");
        out.println("<head><title>List Employees</title></head>");
        out.println("<body>");

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        
        try
        {
            StringBuffer table = new StringBuffer();

            table.append("<table cellpadding=5 cellspacing=0 border=1>\n");
            table.append("<tr>\n");
            table.append("<th>");
            table.append("First Name");
            table.append("</th>\n");
            table.append("<th>");
            table.append("Last Name");
            table.append("</th>\n");
            table.append("<th>");
            table.append("Title");
            table.append("</th>\n");
            table.append("<th>");
            table.append("Email Address");
            table.append("</th>\n");
            table.append("<th>");
            table.append("Phone Number");
            table.append("</th>\n");
            table.append("</tr>\n");
            
            int count = 0;

            connection = ConnectionPoolManager.getConnection("iob");
            if(connection == null)
            {
                throw new Exception("Unable to find advworks connection pool.");
            }
            
            statement = connection.createStatement();
            resultSet = statement.executeQuery("select * from employees");
            
            while(resultSet.next())
            {
                table.append("<tr>\n");
                table.append("<td>");
                table.append(resultSet.getString("FirstName"));
                table.append("</td>\n");
                table.append("<td>");
                table.append(resultSet.getString("LastName"));
                table.append("</td>\n");
                table.append("<td>");
                table.append(resultSet.getString("Title"));
                table.append("</td>\n");
                table.append("<td>");
                table.append(resultSet.getString("EmailAddress"));
                table.append("</td>\n");
                table.append("<td>");
                table.append(resultSet.getString("PhoneNumber"));
                table.append("</td>\n");
                table.append("</tr>\n");

                count++;
            }
            
            table.append("</table>\n");
            out.print(table.toString());

            if(count == 0)
            {
                out.println("no employees were displayed");
            }
            else if(count == 1)
            {
                out.println("1 employee was displayed");
            }
            else
            {
                out.println(count + " employees were displayed");
            }
        }
        catch(Exception ex)
        {
            throw new ServletException(ex.getMessage());
        }
        finally
        {
            try
            {
                if(resultSet != null)
                    resultSet.close();
            }
            catch(Exception ex)
            {
                ; // [mjg] ignore
            }

            try
            {
                if(statement != null)
                    statement.close();
            }
            catch(Exception ex)
            {
                ; // [mjg] ignore
            }

            try
            {
                if(connection != null)
                    connection.close();
            }
            catch(Exception ex)
            {
                ; // [mjg] ignore
            }
        }

        out.println("</body>");
        out.println("</html>");
        out.close();
    }

    public String getServletInfo()
    {
        return "ConnectionPoolServlet";
    }
}
