FileDocCategorySizeDatePackage
TestStruct.javaAPI DocExample14591Wed Jul 18 22:17:08 BST 2001None

TestStruct

public class TestStruct extends Object

Fields Summary
Connection
conn
Constructors Summary
public TestStruct()

    try {
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
      conn = DriverManager.getConnection(
       "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 TestStruct().process();
  
public voidprocess()

    // PERSON_TYP attributes
    final int PT_PERSON_ID           = 0;
    final int PT_LAST_NAME           = 1;
    final int PT_FIRST_NAME          = 2;
    final int PT_MIDDLE_NAME         = 3;
    final int PT_BIRTH_DATE          = 4;
    final int PT_MOTHERS_MAIDEN_NAME = 5;
    final int PT_IDENTIFIERS         = 6;
    // PERSON_IDENTITIFERS_TYP attributes
    final int PIT_ID                 = 0;
    final int PIT_ID_TYPE            = 1;
    // LOCATION_TYP attributes
    final int LT_LOCATION_ID         = 0;
    final int LT_PARENT_LOCATION_ID  = 1;
    final int LT_CODE                = 2;
    final int LT_NAME                = 3;    
    final int LT_START_DATE          = 4;    
    final int LT_END_DATE            = 5;
    // PERSON_LOCATION_TYP attributes
    final int PLT_PERSON_ID          = 0;
    final int PLT_LOCATION_ID        = 1;
    final int PLT_START_DATE         = 2;
    final int PLT_END_DATE           = 3;
    
    Array             identifiers    = null;
    CallableStatement cstmt          = null;
    long              location_id    = 0;
    long              person_id      = 0;
    PreparedStatement pstmt          = null;
    Ref               personRef      = null;
    ResultSet         rslt           = null;
    Statement         stmt           = null;
    Struct            location       = null;
    Struct            person         = null;
    Struct            personLocation = null;
    
    // Clean up a prior execution
    try {
      conn.setAutoCommit(false);

      stmt = conn.createStatement();
      stmt.executeUpdate(
       "delete person_location_ot where person_id = " + 
       "( select person_id from person_ot " + 
       "where last_name = 'O''Reilly' and first_name = 'Tim' )");
      stmt.executeUpdate(
       "delete location_ot " + 
       "where code = 'SEBASTOPOL'");
      stmt.executeUpdate(
       "delete person_ot " + 
       "where last_name = 'O''Reilly' and first_name = 'Tim'");
      stmt.close();
      stmt = null;
      conn.commit();
    }
    catch (SQLException e) {
      System.err.println("SQL Error: " + e.getMessage());
    }
    finally {
      if (stmt != null)
        try { stmt.close(); } catch (SQLException ignore) { }
    }

    // insert an person
    try {
      // create an array and the struct descriptors
      // the person and person identifier type, 
      // not the table name!
      oracle.sql.ArrayDescriptor identifiersArrayDescriptor = 
       oracle.sql.ArrayDescriptor.createDescriptor(
        "PERSON_IDENTIFIER_TAB", conn ); 
      oracle.sql.StructDescriptor identifiersStructDescriptor = 
       oracle.sql.StructDescriptor.createDescriptor(
        "PERSON_IDENTIFIER_TYP", conn );
      oracle.sql.StructDescriptor personStructDescriptor = 
       oracle.sql.StructDescriptor.createDescriptor(
        "PERSON_TYP", conn );

      Object[] personAttributes = new Object[7];

      cstmt = conn.prepareCall("{ ? = call PERSON_TYP.get_id() }");
      cstmt.registerOutParameter(1, Types.NUMERIC);
      cstmt.execute();
      person_id = cstmt.getLong(1);
      cstmt.close();
      cstmt = null;

      personAttributes[PT_PERSON_ID] = new BigDecimal(person_id);
      personAttributes[PT_LAST_NAME] = "O'Reilly";
      personAttributes[PT_FIRST_NAME] = "Tim";
      personAttributes[PT_MIDDLE_NAME] = null;
      personAttributes[PT_BIRTH_DATE] = 
       Timestamp.valueOf("1972-03-17 00:00:00.0");
      personAttributes[PT_MOTHERS_MAIDEN_NAME] = "Oh! I don't know!";

      Object[] identifiersStructs = new Object[2];

      Object[] identifiersAttributes = new Object[2];
 
      identifiersAttributes[PIT_ID] = "000000001";
      identifiersAttributes[PIT_ID_TYPE] = "EID";
      identifiersStructs[0] = new oracle.sql.STRUCT(
       identifiersStructDescriptor, conn, identifiersAttributes );

      identifiersAttributes[PIT_ID] = "CA9999999999";
      identifiersAttributes[PIT_ID_TYPE] = "SDL";
      identifiersStructs[1] = new oracle.sql.STRUCT(
       identifiersStructDescriptor, conn, identifiersAttributes );

      identifiers = new oracle.sql.ARRAY( 
       identifiersArrayDescriptor, conn, identifiersStructs ); 

      personAttributes[PT_IDENTIFIERS] = identifiers;
      person = new oracle.sql.STRUCT(
       personStructDescriptor, conn, personAttributes );
      pstmt = conn.prepareStatement(
       "insert into person_ot values ( ? )");
      pstmt.setObject(1, person, Types.STRUCT);
      int rows = pstmt.executeUpdate();
      pstmt.close();
      pstmt = null;
      System.out.println(rows + " rows inserted");
      conn.commit();
    }
    catch (SQLException e) {
      System.err.println("SQL Error: " + e.getMessage());
    }
    finally {
      if (cstmt  != null)
        try { cstmt.close(); } catch (SQLException ignore) { }
      if (pstmt != null)
        try { pstmt.close(); } catch (SQLException ignore) { }
    }

    // insert a location
    try {
      // create struct descriptor for location type
      // not the table name!
      oracle.sql.StructDescriptor locationStructDescriptor = 
       oracle.sql.StructDescriptor.createDescriptor(
        "LOCATION_TYP", conn );

      Object[] locationAttributes = new Object[6];

      stmt = conn.createStatement();
      rslt = stmt.executeQuery(
       "select location_id.nextval from sys.dual");
      rslt.next();
      location_id = rslt.getLong(1);
      rslt.close();
      rslt = null;
      stmt.close();
      stmt = null;
      locationAttributes[LT_LOCATION_ID] = new BigDecimal(location_id);
      locationAttributes[LT_PARENT_LOCATION_ID] = null;
      locationAttributes[LT_CODE] = "SEBASTOPOL";
      locationAttributes[LT_NAME] = "Sebastopol, CA, USA";
      locationAttributes[LT_START_DATE] =
       Timestamp.valueOf("1988-01-01 00:00:00.0");
      locationAttributes[LT_END_DATE] = null;

      location = new oracle.sql.STRUCT(
       locationStructDescriptor, conn, locationAttributes );
  
      pstmt = conn.prepareStatement(
       "insert into location_ot values ( ? )");
      pstmt.setObject(1, location, Types.STRUCT);
      int rows = pstmt.executeUpdate();
      pstmt.close();
      pstmt = null;
      System.out.println(rows + " rows inserted");
      conn.commit();
    }
    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) { }
    }

    // insert a person's location
    try {
      // create struct descriptor for person location type
      // not the table name!
      oracle.sql.StructDescriptor personLocationStructDescriptor = 
       oracle.sql.StructDescriptor.createDescriptor(
        "PERSON_LOCATION_TYP", conn );

      Object[] personLocationAttributes = new Object[4];

      personLocationAttributes[PLT_PERSON_ID] = 
       new BigDecimal(person_id);
      personLocationAttributes[PLT_LOCATION_ID] = 
       new BigDecimal(location_id);
      personLocationAttributes[PLT_START_DATE] =
       Timestamp.valueOf("1988-01-01 00:00:00.0");
      personLocationAttributes[PLT_END_DATE] = null;

      personLocation = new oracle.sql.STRUCT(
       personLocationStructDescriptor, 
       conn, 
       personLocationAttributes );

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

    // Update the object using setValue()
    try {
      stmt = conn.createStatement();
      rslt = stmt.executeQuery(
       "select ref(p) from person_ot p " + 
       "where last_name = 'O''Reilly' and first_name = 'Tim' ");
      rslt.next();
      personRef = rslt.getRef(1);
      rslt.close();
      rslt = null;
      stmt.close();
      stmt = null;

      person = (Struct)((oracle.sql.REF)personRef).getValue();

      Object[] personAttributes = person.getAttributes();
 
      personAttributes[PT_MOTHERS_MAIDEN_NAME] = null;

      person = new oracle.sql.STRUCT(
       ((oracle.sql.REF)personRef).getDescriptor(), 
       conn, 
       personAttributes);

      ((oracle.sql.REF)personRef).setValue(person);
      System.out.println("1 rows updated");
      conn.commit();
    }
    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 a PreparedStatement
    try {
      stmt = conn.createStatement();
      rslt = stmt.executeQuery(
       "select ref(p) from person_ot p " + 
       "where last_name = 'O''Reilly' and first_name = 'Tim'");
      rslt.next();
      personRef = rslt.getRef(1);
      rslt.close();
      rslt = null;
      stmt.close();
      stmt = null;

      person = (Struct)((oracle.sql.REF)personRef).getValue();

      Object[] personAttributes = person.getAttributes();

      personAttributes[PT_MOTHERS_MAIDEN_NAME] = "unknown";

      person = new oracle.sql.STRUCT(
       ((oracle.sql.REF)personRef).getDescriptor(), 
       conn, 
       personAttributes);

      pstmt = conn.prepareStatement(
       "update person_ot p set value(p) = ? " + 
       "where  ref(p) = ?");
      pstmt.setObject(1, person, Types.STRUCT);
      pstmt.setObject(2, personRef, Types.REF);
      int rows = pstmt.executeUpdate();
      pstmt.close();
      pstmt = null;
      System.out.println(rows + " rows updated");
      conn.commit();
    }
    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) { }
    }

    // Retrieve the object and display its attribute values
    try {
      stmt = conn.createStatement();
      rslt = stmt.executeQuery("select value(p) from person_ot p");
      while (rslt.next()) {
        person = (Struct)rslt.getObject(1);
        System.out.println(person.getSQLTypeName());
        Object[] attributes = person.getAttributes();
        System.out.println("person_id           = " +
         attributes[PT_PERSON_ID]);
        System.out.println("last_name           = " +
         attributes[PT_LAST_NAME]);
        System.out.println("first_name          = " +
         attributes[PT_FIRST_NAME]);
        System.out.println("middle_name         = " +
         attributes[PT_MIDDLE_NAME]);
        System.out.println("birth_date          = " +
         attributes[PT_BIRTH_DATE]);

        cstmt = conn.prepareCall(
         "{ ? = call PERSON_TYP.get_age( ? ) }");

        cstmt.registerOutParameter(1, Types.NUMERIC);
        // Pass the Struct person as the member SELF variable
        cstmt.setObject(2, person); 
        cstmt.execute();
        System.out.println("age                 = " +
         new Long(cstmt.getLong(1)).toString());
        cstmt.close();
        cstmt = null;

        cstmt = conn.prepareCall(
         "{ ? = call PERSON_TYP.get_age_on( ?, ? ) }");

        cstmt.registerOutParameter(1, Types.NUMERIC);
        // Pass the Struct person as the member SELF variable
        cstmt.setObject(2, person); 
        cstmt.setObject(3, Timestamp.valueOf("1980-01-01 00:00:00.0")); 
        cstmt.execute();
        System.out.println("age on 1/1/1980     = " +
         new Long(cstmt.getLong(1)).toString());
        cstmt.close();
        cstmt = null;

        System.out.println("mothers_maiden_name = " +
         attributes[PT_MOTHERS_MAIDEN_NAME]);
   
        identifiers = (Array)attributes[PT_IDENTIFIERS];
        if (identifiers != null) {
          Object[] personIdentifiers = 
           (Object[])identifiers.getArray();
          for (int i=0;i < personIdentifiers.length;i++) {
            System.out.println(
             ((Struct)personIdentifiers[i]).getSQLTypeName());
            Object[] idAttributes = 
             ((Struct)personIdentifiers[i]).getAttributes();
            System.out.println("id                  = " +
             idAttributes[PIT_ID]);
            System.out.println("id_type             = " +
             idAttributes[PIT_ID_TYPE]);
          }
        }
      } 
      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) { }
      if (cstmt != null)  
        try { cstmt.close(); } catch (SQLException ignore) { }
    }