// Integrated Case Study: Course 4 v2.2
// Product Lookup Servlet.java 

// This program is called by the ProductLookupPage.html file
// It retrieves the list of Products from the Book Nook database
// that match the uploaded title and author information and returns the
// results as XML.  See ProductList.dtd

// It is for illustration only and assumes:
//     - that an ODBC DSN called BookNook exists

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

public class ProductLookup extends HttpServlet {

    private static void loadJDBCDriver () {
        System.out.println ("Loading Database driver...");
        try {
//    	DriverManager.registerDriver ( new oracle.jdbc.driver.OracleDriver());
                 DriverManager.registerDriver ( new sun.jdbc.odbc.JdbcOdbcDriver()    );
                 System.out.println ("Database driver loaded...");
        }
        catch (SQLException e) {
            System.err.println (e.getMessage ());
            System.exit (1);  // Driver error
        }
    }

    private static Connection getConnected () {
         System.out.println ("Establishing database connection...");
         try {
//            String jdbc_url = "jdbc:oracle:thin:@localhost:1521:oracle";
//            Connection conn =  DriverManager.getConnection (jdbc_url, "scott", "tiger");
            String jdbc_url = "jdbc:odbc:BookNook";
            Connection conn =  DriverManager.getConnection (jdbc_url);
            System.out.println ("Database connection established ...");
            return conn;
        } 
        catch (SQLException e) {
            System.err.println (e.getMessage () ) ;
            System.exit (1);  // Driver failure
        }
        return null;	// never happen
    }

    private static Statement makeStatement (Connection conn) {
        try {
            System.out.println ("Making a Statement...");
            Statement stmt = conn.createStatement ();
            System.out.println ("Statement created...");
            return stmt;
        }
        catch (SQLException e) {
            System.err.println (e.getMessage () ) ;
            System.exit (2);  // Driver failure
        }
        return null;	// never happen
    }

    private static ResultSet openResultSet (Statement stmt, String query) {
        try {
            System.out.println ("Creating resultSet...");
            ResultSet rs = stmt.executeQuery (query);
            System.out.println ("Resultset created...");
            return rs;
        }
        catch (SQLException e) {
            System.err.println (e.getMessage () ) ;
            System.exit (3);  // ResultSet error
        }
        return null;	// never happen
    }

    private static void processResultSet (ServletOutputStream out, ResultSet rs) {
        try {
            System.out.println ("\n\n+++++ Processing ResultSet +++++\n");
            while (rs.next() ) {
                out.println ("<Product>");                
	out.println ("<Title>" + rs.getString(1) + "</Title>");
	out.println ("<Author>" + rs.getString(2) + "</Author>");
	out.println ("<ISBN>" + rs.getString(3) + "</ISBN>");
	out.println ("<Cost>" +rs.getString(4) + "</Cost>");
                if (rs.getInt (5) > 0)
	    out.println ("<InStock>Yes</InStock>");
                else
	    out.println ("<InStock>No</InStock>");
                out.println ("</Product>");                
            }
            System.out.println ("\n\n+++++ ResultSet Processed +++++\n");        
        }
        catch (IOException e) {
            System.err.println (e.getMessage () ) ;
            System.exit (4);  // Output error
        }
        catch (SQLException e) {
            System.err.println (e.getMessage () ) ;
            System.exit (5);  // Processing error
        }
    }

    private static void closeThingsDown (ResultSet rs, Statement stmt, Connection conn)    {
        try {
            System.out.println ("Closing Things Down...");
            rs.close();
            System.out.println ("ResultSet Closed...");
            stmt.close ();
            System.out.println ("Statement closed...");
            conn.close();
            System.out.println ("Connection closed...");
        }
        catch (SQLException e) {
            System.err.println (e.getMessage () ) ;
            System.exit (5);  // Closure failure
        }
    }

    public void doGet (HttpServletRequest request, HttpServletResponse response) 
    {
        try {
            loadJDBCDriver ();
            Connection conn = getConnected ();
            Statement stmt = makeStatement (conn);
			
            response.setContentType ("text/xml");
            ServletOutputStream out = response.getOutputStream();
            out.println ("<?xml version=\"1.0\" standalone=\"yes\"?>");	// xml header
            out.println ("<?xml-stylesheet type=\"text/xsl\" href=\"/JRProductList.xsl\"?>"); // xsl ss

            out.println ("<ProductList>");				// root element

            // Prepare query based on uploaded user search criteria
            String title = request.getParameter ("title");
            String author = request.getParameter ("author");
			String ISBN = request.getParameter ("ISBN");
            String query = "SELECT Title, Author, ISBN, Cost, QuantityOnHand FROM Products ";

            if ( title != null & ! title.equals ("") )
                query = query + "WHERE Title LIKE '" + title + "' ";

            if (author != null &  ! author.equals ("") )
               if (query.indexOf ("WHERE") == -1 )
                query = query + "WHERE Author LIKE '" + author + "' ";
              else
                query = query + "OR Author LIKE '" + author + "' ";

			  if (ISBN != null & ! title.equals ("") )
				query = query + "WHERE ISBN LIKE '" + ISBN + "' ";
			else
				query = query + "OR ISBN LIKE '" + ISBN + "' ";

            ResultSet rs = openResultSet (stmt, query);
            processResultSet (out, rs);				// print results as xml
            closeThingsDown (rs, stmt, conn);			// close db stuff

            out.println ("</ProductList>");				// close root
        }
        catch (IOException e) {
            System.err.println (e.getMessage () );
        }
    }    

    public void doPost (HttpServletRequest request, HttpServletResponse response)     {
        doGet (request, response);
    }
}