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

/** 
 * our second servlet! Now using JDBC!
 *
 * @author Ann Burroughs
 * @author (modified) Sharon Tuttle
 * @version 3-25-13
 */

public class St10EmpSal extends HttpServlet
{
    // data fields
    
    private String username;
    private String password;

    /**
     *   craft a dynamic response to a browser POST
     *   request, here returning employe names and
     *   salaries
     *
     * @param request a request with username and password
     *    info
     * @param response a response with current employee
     *    username and passwords
     */

    public void doPost(HttpServletRequest request,
                       HttpServletResponse response)
                      throws ServletException, IOException
    {
        response.setContentType("text/html");

        ServletOutputStream out = response.getOutputStream();

        // grab the username and password from the
	//     requesting form

        username = request.getParameter("username");
        password = request.getParameter("password");

        Html318Helpers.beginHtml(out, "EmpSal Servlet");

        grabEmpSalFromDatabase(out);

        Html318Helpers.endHtml(out);
    }

    /**
     * private method to handle the database connection
     *
     * @param outStream the ServletOutputStream that the desired
     *                  HTML and database information should be
     *                  written to (employee names and salaries)
     */

    private void grabEmpSalFromDatabase(ServletOutputStream outStream)
                 throws IOException
    {
        String eName;
        int    eSal;

        try
        {
           // turn off looking at IPv6 stack to avoid 2 minute wait for 
           //     V6 timeout

	    System.setProperty("java.net.preferIPv4Stack", "true");  

            // load driver

            Class.forName("oracle.jdbc.driver.OracleDriver");

            // create Connection object con

            Connection con = DriverManager.getConnection(
                "jdbc:oracle:thin:@cedar:1521:student",
                username, password);

            // create a Statement object for Connection con

            Statement stmt = con.createStatement();

            String query = "select empl_last_name, salary " +
                           "from empl";

            // execute query and store result into resultset

            ResultSet rS = stmt.executeQuery(query);

            outStream.println("");
            outStream.println("<h1>Employees and their salaries"
                               + "</h1>");

            outStream.println("<table>");
            outStream.println("<tr> <th> Employee Last Name </th>");
            outStream.println("     <th> Employee Salary </th> </tr>");

            // for each row in ResultSet rS, print employee and
            //     salary

            while (rS.next())
            {
                // for current row in ResultSet rS, get first column
                //    value and treat it as a String

                eName = rS.getString(1);

                // for current row in ResultSet rS, get second column
                //    value and treat it as an int

                eSal = rS.getInt(2);

                outStream.println("<tr> <td> " + eName + " </td>");
                outStream.println("     <td> $" + eSal + " </td> </tr>");
            }

            outStream.println("</table>");

            // close Statement and Connection objects

            stmt.close();
            con.close();

        } // end of try block

        // any exception thrown within the try-block above will be
        //    caught and reported

        catch (Exception e)
        {
            outStream.println("<p> " + e + "</p>");
        }

    }  // end grabEmpSalFromDatabase

}