import java.sql.*;    // for JDBC
import java.util.*;   // for the Scanner class
import java.io.*;     // for the File class (I think)

/**
    to try to query the current employee last names and salaries
    from a table empl currently stored in the Oracle 
    student database <br /><br />

    this assumes there is a file in the local directory
    named pwd-file.txt containing the password to the
    Oracle account with the username java<br /><br />

    (This is using a Java-8-friendly approach, suitable
    for use on nrs-projects...)

    @version 2021-11-15
    @author Sharon Tuttle
*/

public class EmpSal
{
    /**
        tries to query for current employee last names and salaries
        from the empl table stored in the Oracle student
        database and print them to the screen

        @param args not used here
    */

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

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

        // some of the JDBC methods MAY throw exceptions...

        try
        {
            // load jdbc driver for Oracle

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

            // get password for java account from pwd-file.txt

            Scanner inFile = null;

            try
            {
                inFile = new Scanner(new File("pwd-file.txt"));
            }
            catch (FileNotFoundException exc)
            {
                System.err.println("GetDate: could not open "
                                   + "pwd-file.txt; Goodbye!");
                System.exit(1);
            }

            // but if I reach here, now my Scanner methods
            //     let me read from this file!

            String password = inFile.nextLine();
            inFile.close();

            // I know the account is java

            String username = "java";

            // make a Connection object!

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

            // make a Statement object!

            Statement stmt = con.createStatement();

            // for convenience/readability, I am storing the
            //     SQL command string in a variable

            // DO NOT PUT ; in QUERY STRING

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

            ResultSet rS = stmt.executeQuery(emplQuery);

            // NOTE: this is a more typical query, I hope this
            //    ResultSet DOES have multiple rows --
            // I WILL loop through this!

            String emplName = null;
            int emplSal = 0;

            while(rS.next())
            {
                // print the ResultSet's current row's two columns
        
                emplName = rS.getString(1);

                // hey, the ResultSet get methods often also include
                //    a version in which you specify the desired
                //    column name you want to grab from

                emplSal = rS.getInt("salary");

                System.out.println(emplName + " $" + emplSal);       
            }

            // CLASS STYLE STANDARD: CLOSE your Statement and
            //    ESPECIALLY your Connection when done!

            stmt.close();
            con.close();
        }
        catch (Exception exc)
        {
            System.out.println(exc);
        }
    }
}