//
// example of a JDBC PreparedStatement object
//
// remember: preferable/more efficient if a query is to be
// 	     done more than once
//
// written by: Sharon M. Tuttle
// last modified: 11-10-00

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

public class TryPrepStmt
{
	public static void main(String[] args)
	{
	    PreparedStatement pStmt;
	    Connection        con;		
	    BufferedReader    in;
	    String            goOn, itemName, itemFlavor, itemQStr;
	    int		      itemQuantity, numInserted;

	    try
	    {
	            // load driver, set up connection 
		    Class.forName("oracle.jdbc.driver.OracleDriver");
                    con = DriverManager.getConnection
                                ("jdbc:oracle:thin:@redwood:1521:student", 
		                 "java", "java");     
				
		    // create PreparedStatement --- note that this
		    // constructor, unlike createStatement(), expects
		    // String SQL command, possibly containing ?'s
		    // (IN parameters) that can be replaced before
		    // execution with values.
		    //
		    // (note the use of a sequence to set the
		    // item_num)
		    pStmt = con.prepareStatement("insert into table480 " +
				                 "values " +
						 "(table480_seq.nextval, " +
						 "?, ?, ?)");

		    // Now, we want to loop, asking if user wants
		    // to enter another row:
		    System.out.print("Would you like to enter " +
				     "a row into table480? (y/n): ");
		    System.out.flush();

		    in = new BufferedReader(new InputStreamReader(
				System.in));
		    goOn = in.readLine();
		    goOn = goOn.trim();
		    while (goOn.equals("y") || goOn.equals("Y"))
		    {
			// get desired item name (column item)
			System.out.print("Enter item name: ");
			System.out.flush();

			itemName = in.readLine();
			itemName = itemName.trim();

			// replace first ? in pStmt with itemName
			pStmt.setString(1, itemName);

			// get desired item flavor (column flavor)
			System.out.print("Enter item flavor: ");
			System.out.flush();

			itemFlavor = in.readLine();
			itemFlavor = itemFlavor.trim();

			// replace second ? in pStmt with itemFlavor
			pStmt.setString(2, itemFlavor);

			// get desired quantity
			System.out.print("Enter item quantity: ");
			System.out.flush();

			itemQStr = in.readLine();
			itemQStr = itemQStr.trim();
			try
			{
				itemQuantity = Integer.parseInt(itemQStr);

				// replace 3rd ? in pStmt with
				// item quantity (column quantity)
				pStmt.setInt(3, itemQuantity);
			}
			catch (NumberFormatException exc)
			{
				System.out.println("TryPrepStmt: column " +
					"quantity MUST be an integer; you " +
					"entered: " + itemQStr);
				System.out.println("This row was not " +
					"inserted.");
				// go to top of loop again?
			        System.out.print("Would you like to enter " +
				   "another row into table480? (y/n): ");
			        System.out.flush();

			        goOn = in.readLine();
			        goOn = goOn.trim();
				continue;
			}
			
			// safe to insert row now?
			numInserted = pStmt.executeUpdate();
			System.out.println("TryPrepStmt: " +
				numInserted + " row(s) inserted.");
		
			// do they want to insert more?
			System.out.print("Would you like to enter " +
				"another row into table480? (y/n): ");
			System.out.flush();

			goOn = in.readLine();
			goOn = goOn.trim();
		    } // end of while-loop inserting rows

		// close prepared statement and connection!
		pStmt.close();
		con.close();
	    }
	    catch (Exception e)
	    {
		System.out.println(e);
	    }
	}
}