/*
 * Populate both the suppliers and coffees tables.
 */

import java.sql.*;
import java.io.*;     
public class InsertCoffeesAndSuppliers {

  public static void main(String args[]) throws SQLException, IOException {
		  
    // Load the Oracle JDBC driver
    try {
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

      // Get UserName/Password
      BufferedReader inp   = new BufferedReader(
                                                new InputStreamReader(System.in));
      System.out.print("Enter User Name:");
      String user = inp.readLine();
      System.out.print("Enter Password:");
      String password = inp.readLine();

      // Connect to the database.
      // "ora" is the machine with the oracle server.
      // SQL Listener is listening on port 1521.
      // The instance of the database is "csdb".
      Connection con =
        DriverManager.getConnection ("jdbc:oracle:thin:" +
                                     user.trim()+"/"+password.trim()+
                                     "@ora.oswego.edu:1521:csdb");
      Statement stmt;
      stmt = con.createStatement();							

      //Insert into suppliers
      stmt.executeUpdate("insert into SUPPLIERS " +
	                 "values(49, 'Superior Coffee', '1 Party Place', " +
                         "'Mendocino', 'CA', '95460')");
      
      stmt.executeUpdate("insert into SUPPLIERS " +
                         "values(101, 'Acme, Inc.', '99 Market Street', " +
                         "'Groundsville', 'CA', '95199')");
      
      stmt.executeUpdate("insert into SUPPLIERS " +
	                 "values(150, 'The High Ground', '100 Coffee Lane', " +
                         "'Meadows', 'CA', '93966')");
      
      //Insert into coffees
      stmt.executeUpdate("insert into COFFEES " +
                         "values('Colombian', 00101, 7.99, 0, 0)");

      stmt.executeUpdate("insert into COFFEES " +
                         "values('French_Roast', 00049, 8.99, 0, 0)");

      stmt.executeUpdate("insert into COFFEES " +
                         "values('Espresso', 00150, 9.99, 0, 0)");

      stmt.executeUpdate("insert into COFFEES " +
                         "values('Colombian_Decaf', 00101, 8.99, 0, 0)");

      stmt.executeUpdate("insert into COFFEES " +
                         "values('French_Roast_Decaf', 00049, 9.99, 0, 0)");

      //Display Supplier records.
      String query = "select SUP_NAME, SUP_ID from SUPPLIERS";
      ResultSet rs = stmt.executeQuery(query);
      
      System.out.println("\nSuppliers and their ID Numbers:");
      while (rs.next()) {
        String s = rs.getString("SUP_NAME");
        int n = rs.getInt("SUP_ID");
        System.out.println(s + "   " + n);
      }
      query = "select COF_NAME, PRICE from COFFEES";	
      rs = stmt.executeQuery(query);
	
      System.out.println("\n\nCoffee Break Coffees and Prices:");
      while (rs.next()) {
        String s = rs.getString("COF_NAME");
        float f = rs.getFloat("PRICE");
        System.out.println(s + "   " + f);
      }
	
      stmt.close();
      con.close();
      
    } catch(SQLException ex) {
      System.err.println("SQLException: " + ex.getMessage());
    }
  }
}