package exercise;

/**
 * @author Mike Pasara Sec 03 0741815
 */

import java.sql.*;
import java.util.*;

public class databaseLab10
{
	static Connection conn = null;
	static String bdURL = "jdbc:oracle:thin:@127.0.0.1:1521:XE";
	static String user = "projects";
	static String password = "projects";
	static Statement q1 = null;
	static Statement q2 = null;

	public databaseLab10() {};

	public static void main(String[] args) throws SQLException
	{
		try{
			Class.forName("oracle.jdbc.driver.OracleDriver");
		}	catch (ClassNotFoundException ex){
			System.err.println(ex.getMessage());
		}//end try catch

		try{

			conn = DriverManager.getConnection(bdURL,user,password);
			conn.clearWarnings();
			System.out.println("Connection opened for Driver ==> Oracle 11EX");

			Scanner keyboard = new Scanner(System.in);
			String choice = "";

			while(!choice.equalsIgnoreCase("x")){
				System.out.print("Mike - Which Question would you like to see? Q1 (1) or Q2 (2), press x to exit: ");
				choice = keyboard.next();

				if(choice.equalsIgnoreCase("1"))
					doQ1(conn);

				else if(choice.equalsIgnoreCase("2"))
					doQ2(conn);

				else
					System.out.println("~Invalid Input~");

					System.out.println("\n");
			}//end while

			keyboard.close();
		}	catch(SQLException ex){
			System.err.println(ex.getMessage());
		}	finally{
				if(!conn.isClosed()){
					conn.close();

					System.out.println("Connection closed");
				}//end if
		}//end try catch finally
	}// 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