FileDocCategorySizeDatePackage
TestCustomDatum.javaAPI DocExample10033Sat Jul 28 12:44:00 BST 2001None

TestCustomDatum

public class TestCustomDatum extends Object

Fields Summary
Connection
conn
Constructors Summary
public TestCustomDatum()

    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 TestCustomDatum().process();
  
public voidprocess()

    BigDecimal          personId          = null;
    BigDecimal          locationId        = null;
    LocationTyp         location          = null;
    PreparedStatement   pstmt             = null;
    PersonLocationTyp   personLocation    = null;
    PersonIdentifierTab personIdentifiers = null;
    PersonIdentifierTyp personIdentifier  = null;
    PersonTyp           person            = null;
    ResultSet           rslt              = null;
    Statement           stmt              = 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 a person
    try {
      person = new PersonTyp(conn);
      personId = person.getId();
      person.setPersonId(personId);
      person.setLastName("O'Reilly");
      person.setFirstName("Tim");
      person.setMiddleName(null);
      person.setBirthDate(Timestamp.valueOf("1972-03-17 00:00:00.0"));
      person.setMothersMaidenName("Oh! I don't know!");
      
      personIdentifiers = 
       new PersonIdentifierTab(new PersonIdentifierTyp[2]);
      
      personIdentifier = new PersonIdentifierTyp(conn);
      personIdentifier.setId("EID");
      personIdentifier.setIdType("000000001");
      personIdentifiers.setElement(personIdentifier, 0);
      
      personIdentifier = new PersonIdentifierTyp(conn);
      personIdentifier.setId("SDL");
      personIdentifier.setIdType("CA9999999999");
      personIdentifiers.setElement(personIdentifier, 1);
      
      person.setIdentifiers(personIdentifiers);
      
      pstmt = conn.prepareStatement(
       "insert into person_ot values ( ? )");
      ((OraclePreparedStatement)pstmt).setCustomDatum(1, person);
      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) { }
    }

    // insert a location
    try {
      location = new LocationTyp(conn);
      locationId = location.getId();
      location.setLocationId(locationId);
      location.setParentLocationId(null);
      location.setCode("SEBASTOPOL");
      location.setName("Sebastopol, CA, USA");
      location.setStartDate(
       Timestamp.valueOf("1988-01-01 00:00:00.0"));
      location.setEndDate(null);

      pstmt = conn.prepareStatement(
       "insert into location_ot values ( ? )");
      ((OraclePreparedStatement)pstmt).setCustomDatum(1, location);
      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) { }
    }

    // insert a person's location
    try {
      // CustomDatum always need a Connection
      personLocation = new PersonLocationTyp(conn); 
      personLocation.setPersonId(personId);
      personLocation.setLocationId(locationId);
      personLocation.setStartDate(
       Timestamp.valueOf("1988-01-01 00:00:00.0"));
      personLocation.setEndDate(null);

      pstmt = conn.prepareStatement(
       "insert into person_location_ot values ( ? )");
      ((OraclePreparedStatement)pstmt).setCustomDatum(1, personLocation);
      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 person using a PreparedStatement
    try {
      PersonTypRef personRef = null;
      stmt = conn.createStatement();
      rslt = stmt.executeQuery(
       "select ref(p), value(p) from person_ot p " + 
       "where last_name = 'O''Reilly' and first_name = 'Tim'");
      rslt.next();
      personRef = (PersonTypRef)
       ((OracleResultSet)rslt).getCustomDatum(
        1, PersonTypRef.getFactory());
      person = (PersonTyp)
       ((OracleResultSet)rslt).getCustomDatum(
        2, PersonTyp.getFactory());
      rslt.close();
      rslt = null;
      stmt.close();
      stmt = null;
      person.setMothersMaidenName(null);

      pstmt = conn.prepareStatement(
       "update person_ot p set value(p) = ? " + 
       "where  ref(p) = ?");
      ((OraclePreparedStatement)pstmt).setCustomDatum(1, person);
      ((OraclePreparedStatement)pstmt).setCustomDatum(2, personRef);
      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) { }
    }

    // Update person using REF get/setValue()
    try {
      PersonTypRef personRef = null;
      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 = (PersonTypRef)
       ((OracleResultSet)rslt).getCustomDatum(
        1, PersonTypRef.getFactory());
      rslt.close();
      rslt = null;
      stmt.close();
      stmt = null;
      person = personRef.getValue();
      person.setMothersMaidenName("unknown");
      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) { }
    }

    try {
      stmt = conn.createStatement();
      rslt = stmt.executeQuery("select value(p) from person_ot p");
      while (rslt.next()) {
        person = (PersonTyp)
        ((OracleResultSet)rslt).getCustomDatum(
         1, PersonTyp.getFactory());
        System.out.println(person._SQL_NAME);
        System.out.println("person_id           = " + 
         person.getPersonId());
        System.out.println("last_name           = " + 
         person.getLastName());
        System.out.println("first_name          = " + 
         person.getFirstName());
        System.out.println("middle_name         = " + 
         person.getMiddleName());
        System.out.println("birth_date          = " + 
         person.getBirthDate());
        System.out.println("age                 = " + 
         person.getAge());
        System.out.println("age on 1/1/1980     = " + 
         person.getAgeOn(
          Timestamp.valueOf("1980-01-01 00:00:00.0")));
        System.out.println("mothers_maiden_name = " + 
         person.getMothersMaidenName());
        personIdentifiers = person.getIdentifiers();
        if (personIdentifiers != null) {
          System.out.println(personIdentifiers._SQL_NAME);
          for (int i=0;i < personIdentifiers.length();i++) {
            System.out.println(personIdentifier._SQL_NAME);
            System.out.println("id                  = " + 
             personIdentifiers.getElement(i).getId());
            System.out.println("id_type             = " + 
             personIdentifiers.getElement(i).getIdType());
          }
        }
      } 
      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) { }
    }