import java.sql.*;      // for JDBC
import java.io.*;       // for file i/o

/**
 * grabs employee names and their salaries from an Oracle database
 *    but uses 2 static methods, one to grab the java account password
 *    from a local file and another to make formatting nicer
 * (3rd JDBC example - a Java application)
 *
 * assumes that the Oracle student database is on 
 *     cedar.humboldt.edu, and that there is an account
 *     on that database with username java and password  
 *     that you know and have stored in a file pwd-file.txt 
 *     in the same directory as this class' .class file 
 *
 * @author Ann Burroughs
 * @author (modified by) Sharon Tuttle
 * @version 3-13-13
 */

public class EmpSal2
{
    /**
     * tries to query for empl table names and salaries
     *     from the java account on the 
     *     Oracle student database and print them to
     *     the screen
     *
     * @param args not used
     */

    public static void main(String args[])
    {
        // turn off looking at IPv6 stack to avoid 2 minute wait for V6 timeout

        System.setProperty("java.net.preferIPv4Stack", "true");

        try
	{
	    // load driver

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

	    String username = "java";
	    String password = getPassword();

	    // create Connection object con

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

	    // create a Statement object for Connection con

	    Statement stmt = con.createStatement();

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

            // execute query and store result into ResultSet rS

            ResultSet rS = stmt.executeQuery(query);

            System.out.println("");
            System.out.println("Employees and their salaries");
            System.out.println("----------------------------");

            // for each row in ResultSet rS, print employee and 
            //     salary
    
            String emplName;
            int emplSal;
            String prettyRow;  // to hold "prettier" output

            while (rS.next())
	    {
		// for current row in ResultSet rS, get first column
		//    value and treat it as a String

		emplName = rS.getString(1);

		// for current row in ResultSet rS, get second column
		//    value and treat it as an int

		emplSal = rS.getInt(2);

		prettyRow = formatNameAndSal(emplName, emplSal);
		System.out.println(prettyRow);
	    }

            System.out.println("");

            // close Statement and Connection objects

            stmt.close();
            con.close();

	} // end of try block

        // any exception thrown within the try-block above will be
        //    caught and reported

        catch (Exception e)
	{
	    System.out.println(e);
	}

    }  // end main

    /**
     * reads the java account password from the file pwd-file.txt,
     *   assumed to be in the same directory as this class
     *
     *   @return     the password read
     */

    private static String getPassword()
    {
	BufferedReader fromStream;  
	String password = "";
        try
	{
	    fromStream = new BufferedReader(
	        new FileReader("pwd-file.txt"));
	    password = fromStream.readLine();
	    fromStream.close();
	}
        catch (FileNotFoundException exc)
	{
	    System.out.println(
                "EmpSal2: Could not open: "
                + "pwd-file.txt");
	}
        catch (IOException exc)
	{
	    System.out.println(    
               "EmpSal2: IOError: " +
               exc.getMessage());
	}
        return password;
    }

    /**
     * formats its name and salary parameters into a String
     *    more suitable for printing to the screen
     *
     *   @param name the last name of an employee
     *   @param sal  the salary of that employee
     *   @return     a nicely-formatted string containing the
     *               employee's name and salary
     */
    
    private static String formatNameAndSal(String name, int sal)
    {
        // based on the declarations for columns empl_last_name 
        //     and salary in table empl

        final int MAX_NAME_LENGTH = 15;

        String paddedName = "";
        String paddedSal = "";
        int    numBlanksNeeded;

        // pad the last name with additional blanks, if needed,
        //    so names and salaries will line up

        if (name.length() < MAX_NAME_LENGTH)
	{
	    numBlanksNeeded = MAX_NAME_LENGTH - name.length();
	    paddedName = name;
 
	    for (int i=0; i < numBlanksNeeded; i++)
	    {
	        paddedName = paddedName + " ";
	    }
	}

        // right-justify the salary, if needed

        if (sal < 1000)
	{
	    // assuming that no salary is less than $100...

	    paddedSal = " " + sal;
	}
        else
	{
	    paddedSal = "" + sal;
	}        

        return paddedName + " $" + paddedSal;

    } // end formatNameAndSal

}  // end EmpSal2 class