import java.io.*;
import java.math.*;
import java.sql.*;
import java.text.*;
import oracle.jdbc.driver.*;
public class TestCustomDatum {
Connection conn;
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();
}
}
public static void main(String[] args)
throws Exception {
new TestCustomDatum().process();
}
public void process() throws SQLException {
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) { }
}
}
protected void finalize()
throws Throwable {
if (conn != null)
try { conn.close(); } catch (SQLException ignore) { }
super.finalize();
}
}
|
