Win a copy of Learning OpenStack Networking: Build a solid foundation in virtual networking technologies for OpenStack-based clouds this week in the Cloud/Virtualization forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Liutauras Vilda
  • Campbell Ritchie
  • Tim Cooke
  • Bear Bibeault
  • Devaka Cooray
Sheriffs:
  • Jeanne Boyarsky
  • Knute Snortum
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Ganesh Patekar
  • Stephan van Hulst
  • Pete Letkeman
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Ron McLeod
  • Vijitha Kumara

Storing Objects using SQLData Interface  RSS feed

 
Ranch Hand
Posts: 200
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!