Im am entirely new to World of Oracle Database
I need a
Java interface to be created which can talk to the OODBMS objects stored on Oracle 9i (Please find the DDL's attached with this).
Using the Java interface one should be able to insert, delete and update data directly as OBJECT's (OODBMS not RDBMS) (Using SQLData interface).
Please give me the Java code to insert / delete / update the Container table.
Please give me the entire code coz my dead line has crossed.. and this is driving me Nutzzzz. However i have attempetd to do this but i get a nasty 'java.sql.SQLException: invalid name
pattern:' exception. The code that i have written is given at the end. Atleast someone could pointout the error in my code
DML Statements
---------------
CREATE OR REPLACE TYPE Material_t AS OBJECT (
materialId NUMBER,
name VARCHAR2(80),
sequence VARCHAR2(1000)
) NOT FINAL;
CREATE OR REPLACE TYPE Well_t AS OBJECT (
wellId NUMBER,
name VARCHAR2(80),
material Material_t,
wellComment VARCHAR2(80)
) NOT FINAL;
CREATE TYPE Well_list_t AS TABLE OF Well_t;
CREATE OR REPLACE TYPE Container_t AS OBJECT (
containerId NUMBER,
name VARCHAR2(80),
well_list Well_list_t
) NOT FINAL;
CREATE TABLE Container of Container_t (containerId PRIMARY KEY)
OBJECT ID PRIMARY KEY NESTED TABLE well_list STORE AS Wells;
CREATE TABLE Container of Container_t (
PRIMARY KEY (containerId))
OBJECT ID PRIMARY KEY
NESTED TABLE well_list STORE AS Wells (
(PRIMARY KEY (NESTED_TABLE_ID, WellId))
ORGANIZATION INDEX COMPRESS)
RETURN AS LOCATOR;
My Code
---------
//JMaterial.java
import java.sql.*;
import java.math.*;
public class JMaterial implements SQLData
{
private BigDecimal MaterialId;
private
String sName;
private String sSeq;
private String sql_type;
public String getSQLTypeName()
{
System.out.println("JMaterial--getSQLTypeName");
return sql_type;
}
public void readSQL (SQLInput data, String type)
throws SQLException
{
System.out.println("readSQL");
sql_type = type;
MaterialId = data.readBigDecimal();
sName = data.readString();
sSeq = data.readString();
}
public void writeSQL (SQLOutput data)
throws SQLException
{
System.out.println("writeSQL");
data.writeBigDecimal(MaterialId);
data.writeString(sName);
data.writeString(sSeq);
}
public JMaterial (String type, BigDecimal mat_id, String name, String seq)
{
this.sql_type = type;
this.MaterialId = mat_id;
this.sName = name;
this.sSeq = seq;
}
}
// JWell.java
import java.sql.*;
import java.math.*;
public class JWell implements SQLData
{
private BigDecimal WellId;
private String sName;
private JMaterial obj_mat;
private String sql_type;
public String getSQLTypeName()
{
System.out.println("JWell--getSQLTypeName");
return sql_type;
}
public void readSQL (SQLInput data, String type)
throws SQLException
{
System.out.println("JWell--readSQL");
sql_type = type;
WellId = data.readBigDecimal();
sName = data.readString();
obj_mat = (JMaterial)data.readObject();
}
public void writeSQL (SQLOutput data)
throws SQLException
{
System.out.println("Well--writeSQL");
data.writeBigDecimal(WellId);
data.writeString(sName);
data.writeObject(obj_mat);
}
public JWell (String type, BigDecimal well_id, String name, JMaterial mat)
{
this.sql_type = type;
this.WellId = well_id;
this.sName = name;
this.obj_mat = mat;
}
}
// JWell.java
//import oracle.sql.ARRAY;
import java.sql.*;
import java.math.*;
import java.util.*;
public class JWell_List implements SQLData
{
private java.sql.Array Well_list;
private String sql_type;
public String getSQLTypeName()
{
System.out.println("JWell_List--getSQLTypeName");
return sql_type;
}
public void readSQL (SQLInput data, String type)
throws SQLException
{
System.out.println("JWell_List--readSQL");
sql_type = type;
Well_list = data.readArray();
}
public void writeSQL (SQLOutput data)
throws SQLException
{
System.out.println("JWell_List--writeSQL");
data.writeArray(Well_list);
}
public JWell_List (String type, java.sql.Array well_list)
{
this.sql_type = type;
this.Well_list = well_list;
}
}
// JContainer.java
import java.sql.*;
import java.math.*;
public class JContainer implements SQLData
{
private BigDecimal containerId;
private String sName;
private JWell_List obj_well_list;
private String sql_type;
public String getSQLTypeName()
{
System.out.println("JContainer--getSQLTypeName");
return sql_type;
}
public void readSQL (SQLInput data, String type)
throws SQLException
{
System.out.println("JContainer--readSQL");
sql_type = type;
containerId = data.readBigDecimal();
sName = data.readString();
obj_well_list = (JWell_List)data.readObject();
}
public void writeSQL (SQLOutput data)
throws SQLException
{
System.out.println("JContainer--writeSQL");
data.writeBigDecimal(containerId);
data.writeString(sName);
data.writeObject(obj_well_list);
}
public JContainer (String type, BigDecimal cont_id, String name, JWell_List well_list)
{
this.sql_type = type;
this.containerId = cont_id;
this.sName = name;
this.obj_well_list = well_list;
}
}
//JContainer_test1.java
import java.sql.*;
import java.math.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
public class JDemo_Container1
{
public static void main(String args[])
{
JDemo_Container1 jd = new JDemo_Container1();
jd.demo();
}
public void demo ()
{
// setup mappings for the connection
try {
DBConnector dbconnect = new DBConnector();
Connection con = dbconnect.getConnection();
PreparedStatement pstmt;
Statement stmt;
ResultSet rs;
stmt = dbconnect.getStatement();
System.out.println("1");
JMaterial jmat1 = new JMaterial("Material_t",new BigDecimal(11), "c1", "c1");
System.out.println("2");
JMaterial jmat2 = new JMaterial("Material_t_2",new BigDecimal(12), "c2", "c2");
System.out.println("3");
JWell jwell1 = new JWell("Well_t",new BigDecimal(11), "c1", jmat1);
System.out.println("4");
JWell jwell2 = new JWell("Well_t_2",new BigDecimal(12), "c2", jmat2);
System.out.println("5");
StructDescriptor duhdesc = StructDescriptor.createDescriptor("Well_t", con);
// I get Error here
System.out.println("5xxx");
Object[] myduh0 = new Object[3];
myduh0[0] = new BigDecimal(10);
myduh0[1] = "woof";
myduh0[1] = jmat1;
Object[] myduh1 = new Object[3];
myduh1[0] = new BigDecimal(10);
myduh1[1] = "bhow";
myduh1[1] = jmat2;
// descriptors for the objects
STRUCT duh0 = new STRUCT (duhdesc, con, myduh0);
STRUCT duh1 = new STRUCT (duhdesc, con, myduh1);
System.out.println("5.1");
STRUCT[] myduhtable = new STRUCT[2];
myduhtable[0] = duh0;
myduhtable[1] = duh1;
System.out.println("6");
ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("Container", con);
System.out.println("6.1");
System.out.println("7");
ARRAY ary = new ARRAY(descriptor, con, myduhtable);
System.out.println("8");
pstmt = con.prepareStatement ("INSERT INTO Container VALUES (?)");
System.out.println("9");
((oracle.jdbc.driver.OraclePreparedStatement)pstmt).setARRAY(1, ary);
System.out.println("10");
pstmt.execute();
System.out.println("11");
System.out.println("insert done");
pstmt.close();
}
catch (Exception ex)
{
ex.printStackTrace();
}
}
}
