Hello All,
I have a curious problem with SQLData interface. I had posted my question a week ago... But i didnt get any reply for that
Here i am posting it all over again.
I need to insert objects (nested tables) into a table in Oracle9i. I need a
java interface for it. The DML Statements as well as my java code (which isn't working) is given below. I hinks ive missed something small here, if someone was kind enough to point out what i am missing. Someone please help me figure out the problem.
DML
--------
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,
) 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;
The following table creation is my best guess ...
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
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);
}
}
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 String getName()
{
System.out.println("JWell--getName");
return sName;
}
}
JWell_List.java
---------------
//import oracle.sql.ARRAY;
import java.sql.*;
import java.math.*;
import java.util.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
public class JWell_List implements SQLData
{
private ARRAY WelQ_list;
public String sql_type;
public String getSQLTypeName()
{
System.out.println("JWell_List--getSQLTypeName");
return sql_type;
}
public void readSQL (SQLInput data, String type)
throws SQLException
{
try
{
System.out.println("JWell_List--readSQL");
sql_type = type;
Well_list = (ARRAY)data.readArray();
}
catch(Exception e)
{
e.printStackTrace();
}
}
public void writeSQL (SQLOutput data)
throws SQLException
{
System.out.println("JWell_List--writeSQL");
data.writeArray(Well_list);
}
public JWell_List (String type, ARRAY well_list)
{
this.sql_type = type;
this.Well_list = well_list;
}
}
JContainer.java
---------------
import java.sql.*;
import java.math.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
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);
//data.writeArray(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;
}
}
JDemo_Container_Test1.java
-----------------------------
import java.sql.*;
import java.math.*;
import oracle.sql.*;
import oracle.jdbc.driver.*;
import java.util.*;
public class JDemo_Container_Test1
{
public static void main(String args[])
{
JDemo_Container_Test1 jd = new JDemo_Container_Test1();
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();
Map map = con.getTypeMap();
//getResultSet
map.put("Material_t", Class.forName("JMaterial"));
map.put("Well_t", Class.forName("JWell"));
map.put("Well_list_t", Class.forName("JWell_List_3"));
map.put("Container_t", Class.forName("JContainer"));
con.setTypeMap(map);
/*ResultSet res = stmt.executeQuery("select VALUE (s) from Container s");
while (res.next())
{
JWell jc = (JWell) res.getObject(1);
}
*/
//OracleResultSet ors = (OracleResultSet)stmt.executeQuery("SELECT WELL_LIST FROM Container");
OracleResultSet ors = (OracleResultSet)stmt.executeQuery("SELECT WELL_LIST FROM Container");
// rs = stmt.executeQuery("SELECT Employees FROM employee_table WHERE DeptName = 'SALES'");
ors.next();
Object Obj1 = ((OracleResultSet)ors).getObject(1);
if (Obj1 instanceof JContainer)
System.out.println("JContainer");
if (Obj1 instanceof JMaterial)
System.out.println("JContainer");
if (Obj1 instanceof JWell)
System.out.println("JContainer");
if (Obj1 instanceof JWell_List_3)
System.out.println("JContainer");
//OracleResultSet)ors).getObject(1);
ARRAY wellArray = ((OracleResultSet)ors).getARRAY(1);
Object[] wells = (Object[]) wellArray.getArray();
}
catch (Exception ex)
{
ex.printStackTrace();
}
}
}
DBConnector.java
-----------------
import java.util.*;
import java.sql.*;
class DBConnector
{
DBConnector()
{
try
{
Class.forName("oracle.jdbc.driver.OracleDriver"); //forcing the registeration of driver.
ResourceBundle obj = ResourceBundle.getBundle("DataBaseProperties");
String UserName= (String)obj.getObject("UserName");
String Password= (String)obj.getObject("Password");
String Host= (String)obj.getObject("Host");
String Port= (String)obj.getObject("Port");
if (Port == null)
Port = "1521";
String Database= (String)obj.getObject("Database");
String sConnectURL = "jdbc
racle:thin:"+UserName+"/"+Password+"@"+Host+":"+Port+":"+Database ;
//System.out.println(sConnectURL);
conn = DriverManager.getConnection (sConnectURL);
} catch (Exception e)
{
System.out.println("*****err*******" +e);
return;
}
}
public Statement getStatement()
throws Exception
{
return conn.createStatement ();
}
public Connection getConnection()
throws Exception
{
return conn;
}
public void closeDB()
throws Exception
{
conn.close();
}
private Connection conn=null;
private Statement stmt =null;
}
DatabaseProperties.properties
----------------------------
UserName=saj
Password=hello
Host=Gautam
Port=1521
Database=testdb1
