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

/**
 * inserts 6 rows into table table_from_java, giving
 *    an example of a PreparedStatement 
 *
 * 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 PrepStmtEx
{
    /**
     * inserts 6 rows into table table_from_java, giving
     *    an example of a PreparedStatement 
     *
     * @param args expected to contain the desired table
     *    name as the first element in this array
     */

    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);

            // here are some arrays with desired row values

            String[] colors = {"dove grey", "sky blue", "crimson",
                               "fuchsia", "puce", "slate"};
            int[] codes = {333, 2100, 6565, 334, 7000, 801};
            double[] deposits = {32.99, 210.00, 45.22, 33.01,
                                 200.00, .12};

            // I'd like an appropriate primary key value
            
            Statement stmt = con.createStatement();

            String pkQuery = "select nvl(max(pk_tbl_java), 0) " +
		             "from table_from_java";

            ResultSet rS = stmt.executeQuery(pkQuery);
            rS.next();
            int nextPK = rS.getInt(1) + 1;
            stmt.close();

            // set up the desired PreparedStatement to be
	    //     repeated

            PreparedStatement pStmt = 
                con.prepareStatement(
                    "insert into table_from_java " +
                    "values " +
                    "(?, sysdate, ?, ?, ?)");

            // by default, JDBC changes are each committed
	    //     as they are made -- UNLESS you turn OFF
            //     that default auto-commit feature:

            con.setAutoCommit(false);

            // now, insert the number of rows based on
	    //    my array values

            int numRows = colors.length;

            for (int i=0; i<numRows; i++)
	    {
                pStmt.setInt(1, nextPK);
                nextPK++;
                pStmt.setString(2, colors[i]);
                pStmt.setInt(3, codes[i]);
                pStmt.setDouble(4, deposits[i]);

                pStmt.executeUpdate();
            }

            // and now commit these insertions

            con.commit();

            pStmt.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(
                "Could not open: "
                + "pwd-file.txt");
	}
        catch (IOException exc)
	{
	    System.out.println(    
               "IOError: " +
               exc.getMessage());
	}
        return password;
    }
     
}  // end PrepStmtEx class