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

/**
 * grab empl names and their salaries, and display them
 *     to the screen
 * 
 * assumes:
 * *   that the Oracle student database
 *     is on cedar.humboldt.edu
 *
 * *   that there is an account on that database
 *     with the username java
 *     whose password is stored in a (read-protected)
 *     file named pwd-file.txt in the same directory
 *     as this class' .class file
 * 
 * @author Ann Burroughs
 * @author modified by Sharon Tuttle
 * @version 2015-11-02
 **/

public class EmpSal
{
    /**
     * tries to query for empl names and salaries
     *     and prints the results to the screen
     *
     * @param args not used here
     */

    public static void main(String[] args)
    {
        System.setProperty("java.net.preferIPv4Stack", 
                           "true");

        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver");

            // get the password from a local file

            BufferedReader fromStream;
            String username = "java";
            String password = "";

            try
            {
                fromStream = new BufferedReader(
                                 new FileReader("pwd-file.txt"));
                password = fromStream.readLine();
                fromStream.close();
            }

            catch (FileNotFoundException exc)
            {
                System.out.println("EmpSal: Could not open " +
                                   "pwd-file.txt");
                System.exit(1);
            }

            catch (IOException exc)
            {
                System.out.println("EmpSal: IOError: " +
                                   exc.getMessage());
            }              

            // connect to database on cedar

            Connection con = DriverManager.getConnection(
                "jdbc:oracle:thin:@cedar:1521:student",
                username, password);

            Statement emplQueryStmt = con.createStatement();

            String emplQueryString =   
                  "select empl_last_name, salary "
                + "from empl";

            ResultSet rS = 
                emplQueryStmt.executeQuery(emplQueryString);

            // "walk" through my results, printing
            //    employee names and salaries to the screen
 
            String emplName = "";
            int emplSal = 0;

            while (rS.next())
            {
                // this says: for the "current" row,
                //    get the FIRST column's value,
                //    and treat it like a String:
 
                emplName = rS.getString(1);

                // this says: for the "current" row,
                //    get the value of the column whose
	        //    label is "salary", and treat it
                //    like an int

                emplSal = rS.getInt("salary");

                System.out.println(emplName + " - $" + emplSal);
            }
 
            emplQueryStmt.close();
            con.close();

        } // end of try-block
   
        // any exception thrown from my "outer" try
        //    will be caught and reported

        catch (Exception exc)
        {
            System.out.println("EmpSal: unexpected exception: " +
                               exc.getMessage());
        }

    } // end main method

} // end class EmpSal