FileDocCategorySizeDatePackage
TestSQLData.javaAPI DocExample10895Wed Jul 25 20:44:38 BST 2001None

TestSQLData

public class TestSQLData extends Object

Fields Summary
Connection
conn
Constructors Summary
public TestSQLData()

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

    BigDecimal        locationId        = null;
    BigDecimal        personId          = null;
    int               index             = 0;
    Location          location          = null;
    PreparedStatement pstmt             = null;
    ResultSet         rslt              = null;
    ResultSet         rslt2             = null;
    Statement         stmt              = null;
    Person            person            = null;
    PersonIdentifier  personIdentifier  = null;
    PersonLocation    personLocation    = null;
    java.sql.Array    personIdentifiers = null;

    // Update the connection's type map
    try {
      conn.setAutoCommit(false);
    
      java.util.Map map = conn.getTypeMap();
      map.put("SCOTT.LOCATION_TYP",          
       Class.forName("Location"));
      map.put("SCOTT.PERSON_IDENTIFIER_TYP", 
       Class.forName("PersonIdentifier"));
      map.put("SCOTT.PERSON_TYP",            
       Class.forName("Person"));
      map.put("SCOTT.PERSON_LOCATION_TYP",   
       Class.forName("PersonLocation"));
      conn.setTypeMap(map);
    }
    catch (ClassNotFoundException e) {
      System.err.println("Class Not Found Error: " + e.getMessage());
    }
    catch (SQLException e) {
      System.err.println("SQL Error: " + e.getMessage());
    }
    
    // Clean up a prior execution
    try {
      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 object
    try {
      // use special constructor to initialize connection
      person = new Person(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!");

      Object[] ids = new Object[2];
      personIdentifier = new PersonIdentifier();
      personIdentifier.setId("EID");
      personIdentifier.setIdType("000000001");
      ids[0] = personIdentifier;
      personIdentifier = new PersonIdentifier();
      personIdentifier.setId("SDL");
      personIdentifier.setIdType("CA9999999999");
      ids[1] = personIdentifier;

      oracle.sql.ArrayDescriptor idArrayDescriptor = 
       oracle.sql.ArrayDescriptor.createDescriptor(
        "PERSON_IDENTIFIER_TAB", conn ); 

      personIdentifiers = new oracle.sql.ARRAY( 
       idArrayDescriptor, conn, ids ); 
      person.setIdentifiers(personIdentifiers);

      pstmt = conn.prepareStatement(
       "insert into person_ot values ( ? )");
      pstmt.setObject(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 Location(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 ( ? )");
      pstmt.setObject(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 {
      personLocation = new PersonLocation();

      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 ( ? )");
      pstmt.setObject(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 an object using standard JDBC
    try {
      Ref 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 = (Ref)rslt.getObject(1);
      person = (Person)rslt.getObject(2);
      rslt.close();
      rslt = null;
      stmt.close();
      stmt = null;
  
      person.setMothersMaidenName(null);

      pstmt = conn.prepareStatement(
       "update person_ot p set value(p) = ? " + 
       "where  ref(p) = ?");
      pstmt.setObject(1, person);
      pstmt.setRef(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 an object using REF get/setValue()
    try {
      Ref 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 = rslt.getRef(1);

      rslt.close();
      rslt = null;
      stmt.close();
      stmt = null;

      person = (Person)((oracle.sql.REF)personRef).getValue();
 
      person.setMothersMaidenName("unknown");

      ((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) { }
    }

    try {
      // Create and execute the object sql statement
      stmt = conn.createStatement();
      rslt = stmt.executeQuery("select value(p) from person_ot p");
      while (rslt.next()) {
        // Cast the object
        person = (Person)rslt.getObject(1);
        System.out.println(person._SQL_NAME);
        System.out.println("person_id           = " + 
         person.getPersonId().longValue());
        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());

         person.setConnection(conn);

        System.out.println("age                 = " + 
         person.getAge());
        System.out.println("age on 1/1/1980     = " + 
         person.getAgeOn(Timestamp.valueOf("1980-01-11 00:00:00.0")));
        System.out.println("mothers_maiden_name = " + 
         person.getMothersMaidenName());

        // Get the SQL Array
        personIdentifiers = person.getIdentifiers();
        // now use a result set
        rslt2 = personIdentifiers.getResultSet();
        while (rslt2.next()) {
          index = rslt2.getInt(1);
          personIdentifier = (PersonIdentifier)rslt2.getObject(2);
          System.out.println(personIdentifier._SQL_NAME);
          System.out.println("index               = " + 
           index);
          System.out.println("id                  = " + 
           personIdentifier.getId());
          System.out.println("id_type             = " + 
           personIdentifier.getIdType());
        }
      } 
      rslt.close();
      rslt = null;
      if (rslt2 != null) {
        rslt2.close();
        rslt2 = 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 (rslt2 != null)  
        try { rslt2.close(); } catch (SQLException ignore) { }
      if (stmt != null)  
        try { stmt.close();  } catch (SQLException ignore) { }
    }