FileDocCategorySizeDatePackage
TestStructWithE.javaAPI DocExample10389Thu Feb 22 12:58:04 GMT 2001None

TestStructWithE

public class TestStructWithE extends Object

Fields Summary
Connection
conn
Constructors Summary
public TestStructWithE()

  try {
   DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
   conn = DriverManager.getConnection(
//    "jdbc:oracle:oci8:@dssw2k01", "scott", "tiger");
    "jdbc:oracle:thin:@dssw2k01:1521:orcl", "scott", "tiger");
  }
  catch (SQLException e) {
   System.err.println(e.getMessage());
   e.printStackTrace();
  }
 
Methods Summary
protected voidfinalize()

  if (conn != null) { try { conn.close(); } catch (SQLException ignore) { } }
  super.finalize();
 
public static voidmain(java.lang.String[] args)

  new TestStructWithE().process();
 
public voidprocess()

  int columnA   = 0;
  int columnB   = 1;
  int columnC   = 2;
  int columnAbc = 0;
  int columnD   = 1;

  oracle.sql.ArrayDescriptor  dAD   = null;
  oracle.sql.StructDescriptor abcSD = null;
  oracle.sql.StructDescriptor eSD   = null;

  Object[] abcAttributes            = null;
  Object[] dElements                = null;
  Object[] eAttributes              = null;

  Array             dA              = null;
  PreparedStatement pstmt           = null;
  Ref               ref             = null;
  ResultSet         rslt            = null;
  Statement         stmt            = null;
  Struct            abcS            = null;
  Struct            eS              = null;

  // Clean up a prior execution
  try {
   conn.setAutoCommit(false);
   stmt = conn.createStatement();
   stmt.executeUpdate(
    "delete e");
   stmt.close();
   stmt = null;
  }
  catch (SQLException e) {
   System.err.println("SQL Error: " + e.getMessage());
  }
  finally {
   if (stmt != null) try { stmt.close(); } catch (SQLException ignore) { }
  }

  // insert
  try {
   // create an array and the struct descriptors
   // using the type not the table name!
   dAD   = oracle.sql.ArrayDescriptor.createDescriptor("D_TAB", conn); 
   abcSD = oracle.sql.StructDescriptor.createDescriptor("ABC_TYP", conn);
   eSD   = oracle.sql.StructDescriptor.createDescriptor("E_TYP", conn);

   abcAttributes          = new Object[3];
   abcAttributes[columnA] = new String("X");
   abcAttributes[columnB] = new Integer(1);
   abcAttributes[columnC] = new Timestamp(System.currentTimeMillis());
   abcS = new oracle.sql.STRUCT(abcSD, conn, abcAttributes );

   dElements              = new Object[10];
   dElements[0]           = new String("one");
   dElements[1]           = new String("two");
   dElements[2]           = new String("three");
   dElements[3]           = new String("four");
   dElements[4]           = new String("five");
   dElements[5]           = new String("six");
   dElements[6]           = new String("seven");
   dElements[7]           = new String("eight");
   dElements[8]           = new String("nine");
   dElements[9]           = new String("ten");
   dA = new oracle.sql.ARRAY(dAD, conn, dElements ); 
   
   eAttributes            = new Object[2];
   eAttributes[columnAbc] = abcS;
   eAttributes[columnD]   = dA;

   eS = new oracle.sql.STRUCT(eSD, conn, eAttributes );

   pstmt = conn.prepareStatement(
    "insert into E values ( ? )");
   pstmt.setObject(1, eS, Types.STRUCT);
   int rows = pstmt.executeUpdate();
   pstmt.close();
   pstmt = null;
   System.out.println(rows + " rows inserted");
  }
  catch (SQLException e) {
   System.err.println("SQL Error: " + e.getMessage());
  }
  finally {
   if (pstmt != null) try { pstmt.close(); } catch (SQLException ignore) { }
  }

  // Update the object using a PreparedStatement
  try {
   stmt = conn.createStatement();
   rslt = stmt.executeQuery(
    "select value(e) from E e where e.abc.a = 'X'");
   rslt.next();
   eS = (Struct)rslt.getObject(1);
   rslt.close();
   rslt = null;
   stmt.close();
   stmt = null;

   eAttributes = eS.getAttributes();

   dElements              = new Object[10];
   dElements[0]           = new String("a");
   dElements[1]           = new String("b");
   dElements[2]           = new String("c");
   dElements[3]           = new String("d");
   dElements[4]           = new String("e");
   dElements[5]           = new String("f");
   dElements[6]           = new String("g");
   dElements[7]           = new String("h");
   dElements[8]           = new String("i");
   dElements[9]           = new String("j");

   dA = new oracle.sql.ARRAY(dAD, conn, dElements ); 
   
   eAttributes[columnD]   = dA;

   eS = new oracle.sql.STRUCT(eSD, conn, eAttributes );

   pstmt = conn.prepareStatement(
    "update E e set value(e) = ? where e.abc.a = 'X'");
   pstmt.setObject(1, eS);
   int rows = pstmt.executeUpdate();
   pstmt.close();
   pstmt = null;
   System.out.println(rows + " rows updated");
  }
  catch (SQLException e) {
   System.err.println("SQL Error: " + e.getMessage());
  }
  finally {
   if (rslt  != null) try { rslt.close();  } catch (SQLException ignore) { }
   if (stmt  != null) try { stmt.close();  } catch (SQLException ignore) { }
   if (pstmt != null) try { pstmt.close(); } catch (SQLException ignore) { }
  }

  // Update the object using setValue()
  try {
   stmt = conn.createStatement();
   rslt = stmt.executeQuery(
    "select ref(e) from E e where e.abc.a = 'X'");
   rslt.next();
   ref = rslt.getRef(1);
   rslt.close();
   rslt = null;
   stmt.close();
   stmt = null;

   eS = (Struct)((oracle.sql.REF)ref).getValue();

   eAttributes = eS.getAttributes();

   dElements              = new Object[10];
   dElements[0]           = new String("1");
   dElements[1]           = new String("2");
   dElements[2]           = new String("3");
   dElements[3]           = new String("4");
   dElements[4]           = new String("5");
   dElements[5]           = new String("6");
   dElements[6]           = new String("7");
   dElements[7]           = new String("8");
   dElements[8]           = new String("9");
   dElements[9]           = new String("10");

   dA = new oracle.sql.ARRAY(dAD, conn, dElements ); 
   
   eAttributes[columnD]   = dA;

   eS = new oracle.sql.STRUCT(eSD, conn, eAttributes );

   ((oracle.sql.REF)ref).setValue(eS);
   System.out.println("1 rows updated");
  }
  catch (SQLException e) {
   System.err.println("SQL Error: " + e.getMessage());
  }
  finally {
   if (rslt  != null) try { rslt.close();  } catch (SQLException ignore) { }
   if (stmt  != null) try { stmt.close();  } catch (SQLException ignore) { }
  }

  // Update the object using setValue()
  try {
   stmt = conn.createStatement();
   rslt = stmt.executeQuery(
    "select ref(e) from E e where e.abc.a = 'X'");
   rslt.next();
   ref = rslt.getRef(1);
   rslt.close();
   rslt = null;
   stmt.close();
   stmt = null;

   CallableStatement cstmt = null;
   int rows = 0;
   cstmt = conn.prepareCall("{ ? = call E_TYP.getStaticValue( ) }");
   cstmt.registerOutParameter(1, Types.DOUBLE, 2);
   rows = cstmt.executeUpdate();
   System.out.println("rows = " + rows);
   Double staticValue = new Double(cstmt.getDouble(1));
   System.out.println(staticValue);

   eS = (Struct)((oracle.sql.REF)ref).getValue();

   cstmt = conn.prepareCall("{ ? = call E_TYP.getMemberValue( ? ) }");
   cstmt.registerOutParameter(1, Types.DOUBLE, 2);
   cstmt.setObject(2, eS);
   rows = cstmt.executeUpdate();
   System.out.println("rows = " + rows);
   Double memberValue = new Double(cstmt.getDouble(1));
   System.out.println(memberValue);

   cstmt.close();
   cstmt = null;

   eAttributes = eS.getAttributes();

   String[] dStrings     = new String[10];
   dStrings[0]           = new String("One");
   dStrings[1]           = new String("TwoTwo");
   dStrings[2]           = new String("ThreeThreeThree");
   dStrings[3]           = new String("FourFourFourFour");
   dStrings[4]           = new String("FiveFiveFiveFiveFive");
   dStrings[5]           = new String("SixSixSixSixSixSix");
   dStrings[6]           = new String("SevenSevenSevenSevenSevenSevenSeven");
   dStrings[7]           = new String("EightEightEightEightEightEightEightEight");
   dStrings[8]           = new String("NineNineNineNineNineNineNineNineNine");
   dStrings[9]           = new String("TenTenTenTenTenTenTenTenTenTen");

   dA = new oracle.sql.ARRAY(dAD, conn, dStrings ); 
   
   eAttributes[columnD]   = dA;

   eS = new oracle.sql.STRUCT(eSD, conn, eAttributes );

   ((oracle.sql.REF)ref).setValue(eS);
   System.out.println("1 rows updated");
  }
  catch (SQLException e) {
   System.err.println("SQL Error: " + e.getMessage());
  }
  finally {
   if (rslt  != null) try { rslt.close();  } catch (SQLException ignore) { }
   if (stmt  != null) try { stmt.close();  } catch (SQLException ignore) { }
  }

  try {
   conn.commit();
   stmt = conn.createStatement();
   rslt = stmt.executeQuery("select value(e) from E e");
   while (rslt.next()) {
    eS = (Struct)rslt.getObject(1);
    System.out.println(eS.getSQLTypeName());
    eAttributes = eS.getAttributes();
    System.out.println("ABC           = " + eAttributes[columnAbc]);
    System.out.println("D             = " + eAttributes[columnD]);

    abcS = (Struct)eAttributes[columnAbc];
    System.out.println(abcS.getSQLTypeName());
    abcAttributes = abcS.getAttributes();
    System.out.println("A             = " + abcAttributes[columnA]);
    System.out.println("B             = " + abcAttributes[columnB]);
    System.out.println("C             = " + abcAttributes[columnC]);

    dA = (Array)eAttributes[columnD];
    if (dA != null) {
     System.out.println(dA.getBaseTypeName());
     dElements = (Object[])dA.getArray();
     for (int i=0;i < dElements.length;i++) {
      System.out.println("D[" + i + "]          = " + dElements[i]);
     }
    }
   } 
   rslt.close();
   rslt = null;
   stmt.close();
   stmt = null;
  }
  catch (SQLException e) {
   System.err.println("SQL Error: " + e.getMessage());
  }
  finally {
   if (rslt != null)  
    try { rslt.close();  } catch (SQLException ignore) { }
   if (stmt != null)  
    try { stmt.close();  } catch (SQLException ignore) { }
  }