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