/* JDBC program with ResultSet objects.  A ResultSet is
   the JDBC analog of a PL/SQL cursor. */
import java.sql.*;
import java.io.*;
import java.util.*;
public class ThreeLevel
{ public static void main(String[]args) throws
    SQLException, ClassNotFoundException
  {
    /* load jdbc drivers and
       connect to Oracle with the thin JDBC driver*/
    Class.forName("oracle.jdbc.OracleDriver");
    Connection conn =
      DriverManager.getConnection
        ("jdbc:oracle:thin:@db11.eng.fau.edu:1521:R11g",
         "acuartin", "oracle");

    /* create a Statement object for the connection and
       execute the indicated query from it, placing the
       output from the query in a ResultSet object */
    Statement s = conn.createStatement();
    ResultSet r1 = s.executeQuery("SELECT * FROM S ORDER BY S#");

    /* we separately prepare the select statement representing
       the set of part records that are supplied by the current
       supplier for a loop iteration so that we can compile that
       select statement once, instead of compiling it for each
       loop iteration */
    ResultSet r2;
    PreparedStatement s2 =
      conn.prepareStatement(
        "SELECT DISTINCT P.* FROM P,SPJ" +
           " WHERE SPJ.S# = ? AND SPJ.P# = P.P#" +
             " ORDER BY P.P#");

/*new stuff*/
    ResultSet r3;
    PreparedStatement j3 =
      conn.prepareStatement(
        "SELECT DISTINCT J.* FROM J,SPJ" +
           " WHERE SPJ.P# = ? AND SPJ.J# = J.J#" +
             " ORDER BY J.J#");
/*til here*/


    /* for each supplier record */
    while(r1.next())
    {
       /* print the supplier record */
       System.out.println(r1.getString(1)+"  "+ r1.getString(2)+"  "+
                          r1.getInt(3)+"  "+r1.getString(4));

       /* substitute the current S# for the parameter in the
          PreparedStatement object, and execute the select
          statement from that PreparedStatement object */
       s2.setString(1,r1.getString(1));
       r2 = s2.executeQuery();

       /* for each part record supplied by the current supplier */
       while(r2.next())
       {
        /* print the part record */
        System.out.println("    "+ r2.getString(1)+"  " +
                           r2.getString(2)+"  "+ r2.getString(3)+
                           "  " +r2.getInt(4)+"  "+r2.getString(5));
/*new stuff*/
            j3.setString(1,r2.getString(1));
            r3 = j3.executeQuery();


            while(r3.next())
            {
             System.out.println("        "+ r3.getString(1)+"  " +
                                r3.getString(2)+"  "+ r3.getString(3));
            }
/*until here*/
        }

    }


    /* close the Statement object, PreparedStatement object, and
       connection (note that the result sets are automatically closed
       when their statements are closed) */
    s.close();
    s2.close();

/*new stuff*/
    j3.close();
/*til here*/

    conn.close();
  }
}