package exercises;

import java.io.*;
import java.sql.*;

/*
 * @author Jason Goldenberg
 * Created March 29th, 2017
 * Purpose: connect to database using Oracle
 */

public class jdbcEx1 {

    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      = "Password";
    public jdbcEx1() { }

    public static void doQ1(Connection conn) throws SQLException
    {
        try {
            conn = DriverManager.getConnection(dbURL, user, password);
            System.out.println("Connection opened! For driver ==> Oracle 11XE");
            stmt = conn.createStatement();
            ResultSet qw = stmt.executeQuery("select productid, productname, unitprice" + " from VQ1");
            System.out.println("Lab 10 ~ Jason Goldenberg, 20XX");
            System.out.printf("%10s %10s %10s\n", "ID", "Product", "Price");
            System.out.printf("%10s %9s %10s\n", "===", "===", "===");
            while (qw.next()) {
                System.out.println(qw.getString(1) + "\t" + qw.getString(2) + "\t\tqqqqqqqqq\t\t$" + qw.getString(3));
            }


            qw.close();


        } catch (SQLException m) {
            System.err.println(m.getMessage());
        } finally {
            if (!conn.isClosed()) {
                conn.close();
                System.out.println("Connection closed");
            }
        }
    }








    public static void doQ2(Connection conn) throws SQLException
    {

        try {
            conn = DriverManager.getConnection(dbURL, user, password);

            conn.clearWarnings();


            System.out.println("Connection opened! For driver ==> Oracle 11XE");
            stmt = conn.createStatement();
            ResultSet variable = stmt.executeQuery("alter session set nls_date_format = 'DD-MM-YYYY'");
            ResultSet rs = stmt.executeQuery("select * from VQ2");
            System.out.println("");
            System.out.println("Lab 10 ~ Jason Goldenberg, 20XX");
            System.out.printf("%10s %15s %25s %27s\n", "SHIPPED DATE", "ORDER DATE", "ORDERID", "COMPANY NAME");
            System.out.printf("%10s %15s %25s %27s\n", "=====", "======", "========", "========");
            while (rs.next()) {
                System.out.println("  " + rs.getString("ORDERID") + "\t\t" + rs.getString(2) + "\t" + rs.getString(3) + "\t" + rs.getString("COMPANYNAME"));
            }


            rs.close();


            variable.close();
        } catch (SQLException m) {
            System.err.println(m.getMessage());

        } finally {

      	  if (!conn.isClosed()) {
                conn.close();
                System.out.println("Connection Closed! Oracle");
            }
        }
    }



    public static void main (String args[]) throws SQLException, IOException {
        BufferedReader br = new BufferedReader(new InputStreamReader(System.in));
        String option = "";


        		while (!option.equalsIgnoreCase("X")) {
        			System.out.print("Please choose 'Q1' or 'Q2?'  or 'X' to end: ");
        			option = br.readLine();
        			System.out.println("");

        				if (option.equalsIgnoreCase("1")){
        						doQ1(conn);
        					}
        				if (option.equalsIgnoreCase("2")){
        						doQ2(conn);

        								System.out.println("");
        }
    }
    }
}