import java.io.*;
import java.math.*;
import java.sql.*;
import java.text.*;
public class TestStructWithE {
Connection conn;
public TestStructWithE() {
try {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
conn = DriverManager.getConnection(
// "jdbc:oracle:oci8:@dssw2k01", "scott", "tiger");
"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 TestStructWithE().process();
}
public void process() throws SQLException {
int columnA = 0;
int columnB = 1;
int columnC = 2;
int columnAbc = 0;
int columnD = 1;
oracle.sql.ArrayDescriptor dAD = null;
oracle.sql.StructDescriptor abcSD = null;
oracle.sql.StructDescriptor eSD = null;
Object[] abcAttributes = null;
Object[] dElements = null;
Object[] eAttributes = null;
Array dA = null;
PreparedStatement pstmt = null;
Ref ref = null;
ResultSet rslt = null;
Statement stmt = null;
Struct abcS = null;
Struct eS = null;
// Clean up a prior execution
try {
conn.setAutoCommit(false);
stmt = conn.createStatement();
stmt.executeUpdate(
"delete e");
stmt.close();
stmt = null;
}
catch (SQLException e) {
System.err.println("SQL Error: " + e.getMessage());
}
finally {
if (stmt != null) try { stmt.close(); } catch (SQLException ignore) { }
}
// insert
try {
// create an array and the struct descriptors
// using the type not the table name!
dAD = oracle.sql.ArrayDescriptor.createDescriptor("D_TAB", conn);
abcSD = oracle.sql.StructDescriptor.createDescriptor("ABC_TYP", conn);
eSD = oracle.sql.StructDescriptor.createDescriptor("E_TYP", conn);
abcAttributes = new Object[3];
abcAttributes[columnA] = new String("X");
abcAttributes[columnB] = new Integer(1);
abcAttributes[columnC] = new Timestamp(System.currentTimeMillis());
abcS = new oracle.sql.STRUCT(abcSD, conn, abcAttributes );
dElements = new Object[10];
dElements[0] = new String("one");
dElements[1] = new String("two");
dElements[2] = new String("three");
dElements[3] = new String("four");
dElements[4] = new String("five");
dElements[5] = new String("six");
dElements[6] = new String("seven");
dElements[7] = new String("eight");
dElements[8] = new String("nine");
dElements[9] = new String("ten");
dA = new oracle.sql.ARRAY(dAD, conn, dElements );
eAttributes = new Object[2];
eAttributes[columnAbc] = abcS;
eAttributes[columnD] = dA;
eS = new oracle.sql.STRUCT(eSD, conn, eAttributes );
pstmt = conn.prepareStatement(
"insert into E values ( ? )");
pstmt.setObject(1, eS, Types.STRUCT);
int rows = pstmt.executeUpdate();
pstmt.close();
pstmt = null;
System.out.println(rows + " rows inserted");
}
catch (SQLException e) {
System.err.println("SQL Error: " + e.getMessage());
}
finally {
if (pstmt != null) try { pstmt.close(); } catch (SQLException ignore) { }
}
// Update the object using a PreparedStatement
try {
stmt = conn.createStatement();
rslt = stmt.executeQuery(
"select value(e) from E e where e.abc.a = 'X'");
rslt.next();
eS = (Struct)rslt.getObject(1);
rslt.close();
rslt = null;
stmt.close();
stmt = null;
eAttributes = eS.getAttributes();
dElements = new Object[10];
dElements[0] = new String("a");
dElements[1] = new String("b");
dElements[2] = new String("c");
dElements[3] = new String("d");
dElements[4] = new String("e");
dElements[5] = new String("f");
dElements[6] = new String("g");
dElements[7] = new String("h");
dElements[8] = new String("i");
dElements[9] = new String("j");
dA = new oracle.sql.ARRAY(dAD, conn, dElements );
eAttributes[columnD] = dA;
eS = new oracle.sql.STRUCT(eSD, conn, eAttributes );
pstmt = conn.prepareStatement(
"update E e set value(e) = ? where e.abc.a = 'X'");
pstmt.setObject(1, eS);
int rows = pstmt.executeUpdate();
pstmt.close();
pstmt = null;
System.out.println(rows + " rows updated");
}
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 the object using setValue()
try {
stmt = conn.createStatement();
rslt = stmt.executeQuery(
"select ref(e) from E e where e.abc.a = 'X'");
rslt.next();
ref = rslt.getRef(1);
rslt.close();
rslt = null;
stmt.close();
stmt = null;
eS = (Struct)((oracle.sql.REF)ref).getValue();
eAttributes = eS.getAttributes();
dElements = new Object[10];
dElements[0] = new String("1");
dElements[1] = new String("2");
dElements[2] = new String("3");
dElements[3] = new String("4");
dElements[4] = new String("5");
dElements[5] = new String("6");
dElements[6] = new String("7");
dElements[7] = new String("8");
dElements[8] = new String("9");
dElements[9] = new String("10");
dA = new oracle.sql.ARRAY(dAD, conn, dElements );
eAttributes[columnD] = dA;
eS = new oracle.sql.STRUCT(eSD, conn, eAttributes );
((oracle.sql.REF)ref).setValue(eS);
System.out.println("1 rows updated");
}
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 setValue()
try {
stmt = conn.createStatement();
rslt = stmt.executeQuery(
"select ref(e) from E e where e.abc.a = 'X'");
rslt.next();
ref = rslt.getRef(1);
rslt.close();
rslt = null;
stmt.close();
stmt = null;
CallableStatement cstmt = null;
int rows = 0;
cstmt = conn.prepareCall("{ ? = call E_TYP.getStaticValue( ) }");
cstmt.registerOutParameter(1, Types.DOUBLE, 2);
rows = cstmt.executeUpdate();
System.out.println("rows = " + rows);
Double staticValue = new Double(cstmt.getDouble(1));
System.out.println(staticValue);
eS = (Struct)((oracle.sql.REF)ref).getValue();
cstmt = conn.prepareCall("{ ? = call E_TYP.getMemberValue( ? ) }");
cstmt.registerOutParameter(1, Types.DOUBLE, 2);
cstmt.setObject(2, eS);
rows = cstmt.executeUpdate();
System.out.println("rows = " + rows);
Double memberValue = new Double(cstmt.getDouble(1));
System.out.println(memberValue);
cstmt.close();
cstmt = null;
eAttributes = eS.getAttributes();
String[] dStrings = new String[10];
dStrings[0] = new String("One");
dStrings[1] = new String("TwoTwo");
dStrings[2] = new String("ThreeThreeThree");
dStrings[3] = new String("FourFourFourFour");
dStrings[4] = new String("FiveFiveFiveFiveFive");
dStrings[5] = new String("SixSixSixSixSixSix");
dStrings[6] = new String("SevenSevenSevenSevenSevenSevenSeven");
dStrings[7] = new String("EightEightEightEightEightEightEightEight");
dStrings[8] = new String("NineNineNineNineNineNineNineNineNine");
dStrings[9] = new String("TenTenTenTenTenTenTenTenTenTen");
dA = new oracle.sql.ARRAY(dAD, conn, dStrings );
eAttributes[columnD] = dA;
eS = new oracle.sql.STRUCT(eSD, conn, eAttributes );
((oracle.sql.REF)ref).setValue(eS);
System.out.println("1 rows updated");
}
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 {
conn.commit();
stmt = conn.createStatement();
rslt = stmt.executeQuery("select value(e) from E e");
while (rslt.next()) {
eS = (Struct)rslt.getObject(1);
System.out.println(eS.getSQLTypeName());
eAttributes = eS.getAttributes();
System.out.println("ABC = " + eAttributes[columnAbc]);
System.out.println("D = " + eAttributes[columnD]);
abcS = (Struct)eAttributes[columnAbc];
System.out.println(abcS.getSQLTypeName());
abcAttributes = abcS.getAttributes();
System.out.println("A = " + abcAttributes[columnA]);
System.out.println("B = " + abcAttributes[columnB]);
System.out.println("C = " + abcAttributes[columnC]);
dA = (Array)eAttributes[columnD];
if (dA != null) {
System.out.println(dA.getBaseTypeName());
dElements = (Object[])dA.getArray();
for (int i=0;i < dElements.length;i++) {
System.out.println("D[" + i + "] = " + dElements[i]);
}
}
}
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();
}
} |
