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