package exercise;
import java.sql.*;
import java.util.*;

/**
* Program Name: databaseLab10.java
* Purpose: databaseLab10.java - connect to a database using Oracle Thin Driver
* Coder: Mike Pasara Sec03 0741815
* Date: Mar 31, 2016
*/

public class databaseLab10
{
    static Connection conn     = null;
    static Statement stmt     = null;
    static String dbURL        = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
    static String user        = "projects";
    static String password    = "projects";
    static String option = "1";
    static Scanner input = new Scanner(System.in);
    static BufferedReader br = new BufferedReader(new InputStreamReader(System.in));


    //creates a new instance of databaseLab
    public databaseLab10()
    {

    }

    public static void main (String args[]) throws SQLException
    {
        //Register the driver with the Class manager

        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");
        }
        catch (ClassNotFoundException e)
        {
            System.err.println(e.getMessage());
        }

        //Open and close the connection
        try
        {
            conn = DriverManager.getConnection(dbURL, user, password);
            conn.clearWarnings();
            System.out.println("Connection opened! for driver ==> Oracle 11E");
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("select * from customers");

            //Dump out the data 2 ways - 1) by ordinal position, and 2) by name
            while (rs.next())
            {
                System.out.println(rs.getString(2) + " " + rs.getString("CUSTLASTNAME"));
            }
            rs.close();
        }
        catch (SQLException e)
        {
            System.err.println(e.getMessage());
        }

    finally
    {
        if (!conn.isClosed())
        {
            conn.close();
            System.out.println("Connection closed! Oracle");
        }// end if

    }//end finally

        String option = null;

        while ( !option.equalsIgnoreCase("X") )
        {
        	System.out.print("Mike - Want to do Q1 (1) or Q2 (2) or (X) exit: ");
        	option = input.nextLine();
        	System.out.println("");

        	if( option.equalsIgnoreCase("1") )
        	{
        		doQ1(conn);
        	}
        	else if ( option.equalsIgnoreCase("2") )
        	{
        		doQ2(conn);
        	}
        	else if ( option.equalsIgnoreCase("X") )
        	{
        		if( !conn.isClosed() )
        		{
        			conn.close();
        			System.out.println("Connection closed!");
        		}
        }
        else
        System.out.println("Invalid Input Please Try again! [ 1, 2, or X] ");
        }

    }//end main

	public static void doQ1(Connection c){
		try
		{
			q1 = c.createStatement();
			ResultSet rs = q1.executeQuery("SELECT * FROM VQ1");

			System.out.println("Id      Product Name                           UnitPrice");
			System.out.println("--      ------------                           ---------");
			while(rs.next()){
				System.out.printf("%-7s %-35s%10s\n",rs.getString(1), rs.getString(2), rs.getString(3));
			}//end while
			rs.close();

		} catch (SQLException ex){
			System.err.println(ex.getMessage());
		}// end try catch

	}//end method

	/**
	 * Method Name: doQ2
	 * purpose: executes a sql statement
	 * @param: Connection c; the connection
	 * @return: nothing
	 */

	public static void doQ2(Connection c){
		try
		{
			q2 = c.createStatement();
			ResultSet rs = q2.executeQuery("SELECT * FROM VQ2");

			System.out.println("Id        Order Date                Shipped Date            Company Name");
			System.out.println("--        ----------                ------------            ------------");
			while(rs.next()){

				System.out.printf("%-9s %-25s %9s %23s\n",rs.getString(1), rs.getString(2),  rs.getString(3), rs.getString(4));

			}//end while

			rs.close();

		}	catch (SQLException ex){
			System.err.println(ex.getMessage());
		}//end try catch
	}//end method
}// end class