import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.*;

public class LabWeek12

{
	static Connection conn	=null;
	static String dbURL		="jdbc:oracle:thin:@127.0.0.1:1521:XE";
	static String user 		="projects";
	static String password	="projects";
	static Statement stmt = null;
	/* Creates a new instance of JdbcEx1 */

	public LabWeek12(){ }

	public static void main (String args[]) throws SQLException, IOException
		{
			/*
			 * Register the driver with the Class manager
			 */
			try
			{
				Class.forName("oracle.jdbc.driver.OracleDriver");
			}
			catch (ClassNotFoundException e)
			{
				System.err.println(e.getMessage());
			}

			/* Open the connection */
			try
			{
				conn = DriverManager.getConnection(dbURL, user, password);
				conn.clearWarnings();
				System.out.println("Connection opened! for driver ==> Oracle 11XE)");
			}
			catch(SQLException e)
			{
				System.err.println(e.getMessage());
			}

			BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
			String option="1";

			while(!option.equalsIgnoreCase("X"))
			{
				System.out.print("Want to do Q1 (1) or Q2 (2) or (X) exit: ");
				option = br.readLine();
				System.out.println("");
				if (option.equalsIgnoreCase("1"))
				{
					doQ1(conn);
				}
				else if(option.equalsIgnoreCase("2"))
				{
					doQ2(conn);
				}
				else if(option.equalsIgnoreCase("X"))
				{
					conn.close();
					System.out.println("Connection closed!");
				}
			}


		}


	public static void doQ1(Connection conn)
	{
		try
		{
		conn = DriverManager.getConnection(dbURL, user, password);
		conn.clearWarnings();
		stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT * FROM VQ1");

			while (rs.next())
			{
				System.out.println(rs.getString("PRODUCTID")
						+ "\t"+ rs.getString("ProductName") + "  "
						+rs.getString("UNITPRICE"));
			}
		}
		catch(SQLException e)
		{
			System.err.println(e.getMessage());
		}
	}

	public static void doQ2(Connection conn)
	{
		{
			try
			{
			conn = DriverManager.getConnection(dbURL, user, password);
			conn.clearWarnings();
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM VQ2");

				while (rs.next())
				{
					System.out.println(rs.getString("OrderID")
							+ "\t"+ rs.getDate("OrderDate") + "  "
							+rs.getDate("ShippedDate")+ " "+rs.getString("CompanyName"));
				}
			}
			catch(SQLException e)
			{
				System.err.println(e.getMessage());
			}
		}
	}
}
//end of class