// File: StateDBAccess.java
// Test Oracle Database with States file 

import java.io.*;
import java.sql.*;
import java.util.*;
import java.net.URL;
import sun.jdbc.odbc.*;
import java.text.DecimalFormat;
import oracle.jdbc.pool.OracleDataSource;
import oracle.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;

public class StateDBAccess extends HttpServlet {

  private static final String HOST = "acad.kutztown.edu";
  private static final String DATABASE_NAME = "orcl";
  private static final int DATABASE_PORT = 1521;
  private static final String USERNAME = "spiegel";
  private static final String PASSWORD = "legeips";

  private static final String popQuery =
       "SELECT sName, sPop, sCap FROM States WHERE sPop BETWEEN ? AND ? "+
							" ORDER BY sName";
  private static Statement stmnt;
  private static Connection con;

  // Need to open DB connection here. In doPost, it would close at end of fn
  public void init(ServletConfig config)  throws ServletException
  {     super.init(config);
	// Set up JDBC stuff
	try { // Get a connection
	    con = new OracleConnection().
            getConnection(HOST,DATABASE_NAME,DATABASE_PORT,USERNAME,PASSWORD);
	}
  	catch (Exception e) {
	    e.printStackTrace();
  	}
  }


 // Output name, cap, & pop of states returned from a query
 public static void outputQueryResults(ResultSet data,PrintWriter out)
                                              throws java.sql.SQLException
 // Won't use try..catch here
 {out.println("<TABLE Caption=\"States Meeting Query Specifications\" "+
      "Align=Center bgColor=green cellspacing=5><TR><TH>Name</TH><TH>"+
	"Capital<TH>Population</TH></TR>");
  while (data.next()) {// once for each line in the database
      // ResultSet has functions to obtain elements; we need for String & int
      // Note we could use field # insteasd of "Name"; also null terminate. Why?
      out.println("<TR><TD>"+data.getString("sName")+"</TD><TD>"+
	data.getString("sCap")+"</TD><TD>"+data.getInt("sPop")+
		"</TD></TR>");
  }
  out.println("</TABLE>");
 }

 // Print the States database
 public static void printData(Statement stmnt,PrintWriter out)
 {try {
  // Issue a query so we can output a subset of the database contents
     // The query will obtain all states' info as listed, and
     //  also asks that the data be ordered
     ResultSet data=stmnt.executeQuery("SELECT sName, sPop, sCap FROM States"+
                                                            " ORDER BY sName");
     outputQueryResults(data,out);
     data.close();
   }
   catch (Exception e) {
     e.printStackTrace();
   }
 }

 //  Find all states in a designated population range
 //  Use to demonstrate a PreparedStatement with variables
 public static void findByPop(PreparedStatement qStmnt,PrintWriter out,
							int low, int high)
 {// Must set up a query in a String, since we are using variable values
  // Let's use a PreparedStatement
  try {
    // Set the values in the PreparedStatement
    qStmnt.setInt(1,low);
    qStmnt.setInt(2,high);
    // Set up for query
    ResultSet res = qStmnt.executeQuery();
    out.println("<Center>States with Population Between " + low + " & " +
                         high+"</Center>");
    outputQueryResults(res,out);
    res.close(); // Must close before we can change query parameters
  }
  catch (Exception e) {
    out.println("Error with pop query "+e);
  }
 }

 // User can update one state's capital
 public static void updateCap(PrintWriter out,String StateName,String NewCap)
 {int res;
  String capUpdate=
        "UPDATE States SET sCap='"+NewCap+"' WHERE sName='"+ StateName +"' ";

  try {
    // Execute an *UPDATE*, not a query
    res = stmnt.executeUpdate(capUpdate);
    if (res>0)
	out.println("Success: Capital of "+StateName+" Now "+NewCap);
    else
	out.println("Failure: "+StateName+" Not Found ");
  }
  catch (Exception e) {
    out.println(e);
  }
 }

 public void createStatesTable(PrintWriter out)
 {  int ctr=0;
    State[] sList=new State[51]; 

    URL TheFile=null;
    try {	// Set up a URL to the file
       TheFile=new URL("http://acad.kutztown.edu/~spiegel/cis521/Examples/StatesDB/states.txt");
    }
    catch (Exception e) {
       out.println("URL Setup failed...");
    }
    InputStream s=null;
    try { // Hook up to the file on the server
       s=TheFile.openStream();
    }
    catch (Exception e)  {
       out.println("!! Stream open failed !!");
    }
    try {

      BufferedReader DataInput=new BufferedReader(new InputStreamReader(s));
      // Read the file and print the States that were read
/*
      int ctr=0;
      System.out.println("The List in File Order...");
      State.printHeader();
*/
      while ((sList[ctr++]=State.readStateFromFile(DataInput,out))!=null);
    }
    catch (Exception e) {
      out.println("Error Reading States File:"+e+"<BR>");
    }

    // Set up JDBC stuff
    Statement stmnt;
    try {
      Connection con = new OracleConnection().getConnection
                (HOST, DATABASE_NAME, DATABASE_PORT, USERNAME, PASSWORD);
      stmnt=con.createStatement();
      // Need to use unique names (Area messed things up!)
      String createString="CREATE TABLE States (sName VARCHAR2(14), "+
                "sPop NUMBER, sArea NUMBER, sYear NUMBER, "+
                                        "sOrder NUMBER, sCap VARCHAR2(14))";
      stmnt.executeUpdate(createString);
      for (int idx=0;idx<ctr;idx++) {
        // Form the string representing the insertion statement for this state
        // Note use of ' to delimit strings in the SQL statement
        String StateSQLString = ("INSERT INTO States VALUES('" +sList[idx].name
          + "'," + sList[idx].population + "," + sList[idx].area + "," +
                         sList[idx].year + "," + sList[idx].order + ",'" +
                                                    sList[idx].capital + "')");
	out.println(sList[idx].name);
        stmnt.executeUpdate(StateSQLString);
      } // for
      // We can remove the table (use when table needs to go)
      // stmnt.executeUpdate("DROP TABLE States");
      stmnt.close();
    }
    catch (Exception e) {
      out.println("Database table create error:"+e+"<BR>");
    }
    
 }

 public void dropStatesTable(PrintWriter out)
 {Statement stmnt;
  try {
   stmnt=con.createStatement();
   // We can remove the table (use when table needs to go)
   stmnt.executeUpdate("DROP TABLE States");
   stmnt.close();
   out.println("States Table Dropped");
  }
  catch (Exception e) {
    out.println("Error removing table:"+e+"<BR>");
  }
 }
 
    public void doGet(HttpServletRequest req, HttpServletResponse res)
					throws ServletException, IOException
    {doPost(req,res);
    }

    public void doPost(HttpServletRequest req, HttpServletResponse res)
					throws ServletException, IOException
    {    //Get the response's PrintWriter to return text to the client.
            PrintWriter toClient = res.getWriter();
	    // No matter what, set the "content type" header of the response
	    res.setContentType("text/html");
        try {
	    Connection con = new OracleConnection().
            getConnection(HOST,DATABASE_NAME,DATABASE_PORT,USERNAME,PASSWORD);
	    // Create a statement to access database
	    stmnt=con.createStatement();


            // test for the table exists
            try {
              PreparedStatement qStmnt=con.prepareStatement(popQuery);
              // Set the values in the PreparedStatement
              qStmnt.setInt(1,1);
              qStmnt.setInt(2,1000000);
              // Set up for test query
              ResultSet r = qStmnt.executeQuery();
            }
            catch(SQLException e) {  // no table (likely)
              toClient.println("-SQL error:"+e+"- <BR>");
              try {   // make the table
                createStatesTable(toClient);
              }
              catch (Exception ee) {     
                toClient.println("Unspecified error creating table:"+ee);
              }    
            }
	    String qChoice=(String)req.getParameterValues("buttonGroup")[0];
	    if (qChoice.equals("Pop")) {  // Population query
                PreparedStatement qStmnt=con.prepareStatement(popQuery);
		// Get parameters
		int low=Integer.parseInt(req.getParameterValues("low")[0]);
		int high=Integer.parseInt(req.getParameterValues("hi")[0]);
		// Call DB Access Function
		findByPop(qStmnt,toClient,low,high);
	    }
	    else if (qChoice.equals("Cap")) {
		String Name=((String)req.
			getParameterValues("state")[0]).toUpperCase();
		Name=Name.toUpperCase();
		String Cap=((String)req.
			getParameterValues("newcap")[0]).toUpperCase();
		updateCap(toClient,Name,Cap);
	    }
	    else if (qChoice.equals("Drop")) {
	        dropStatesTable(toClient);
	    }    
	    // Close the statement
	    stmnt.close();
        }
        catch (Exception e) {
            toClient.println(stmnt+"Unspecified error:"+e+" 2 <BR>");
        }
    }



}  // class