Oracle Object Types - Java Implementation

Please find the code which has the implementation of inserting values in Oracle Objects and table.

CREATE TYPE product_detail_info AS object (product_detail_id VARCHAR(30), product_name VARCHAR(30),product_rate NUMBER)
CREATE TYPE product_type AS object (product_type_id VARCHAR(30), product_type_Name VARCHAR(30),obj_product_detail_info product_detail_info )
CREATE TABLE Electronics_Products(Electronics_Products_id VARCHAR(30), Electronics_Products_name VARCHAR(30),obj_product_type product_type)

Note :
While running this program Please give args[0] value as below
I- I - Insert
II- C - create Object and Table
III- S - Select

Java Code :


package com.oracleObjects.AcessOracleObjects;


import java.io.IOException;
import java.math.BigDecimal;
import java.sql.*;
import java.util.Properties;
/**
* @author tzg136
*
* TODO To change the template for this generated type comment go to
* Window - Preferences - Java - Code Style - Code Templates
*/
public class AcessOracleObjects_ElectronicsItem {
Connection conn = null;
String driverName ="";
String serverName = "";
String username ="";
String password = "";

String portNumber ="";
String sid = "";
String databaseName = "";

public AcessOracleObjects_ElectronicsItem()
{
Properties configFile = new Properties();
try {
configFile.load(AcessOracleObjects_ElectronicsItem.class.getClassLoader().getResourceAsStream("config.properties"));
driverName =configFile.getProperty("driverName");
serverName = configFile.getProperty("serverName");
username =configFile.getProperty("username");
password = configFile.getProperty("password");
portNumber =configFile.getProperty("portNumber");
sid = configFile.getProperty("sid");
databaseName = configFile.getProperty("databaseName");

} catch (IOException e) {
e.printStackTrace();
}


}
public Connection getConnection()
{
try
{
Class.forName(driverName);
String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
conn = DriverManager.getConnection(url, username, password);
System.out.println("Connected Sucessfully");

}
catch (ClassNotFoundException e) {
System.out.println("ClassNotFoundException "+e.getMessage());
} catch (SQLException e) {
System.out.println("SQl Syntaxt Error "+e.getMessage());
}
catch (Exception e) {
System.out.println("Exception "+e.getMessage());
}
return conn;
}
public void insertTableObjectValue()
{
try {
// Create an oracle.sql.ARRAY object to hold the values
Statement stmt = conn.createStatement();
//CREATE TYPE product_detail_info AS object (product_detail_id VARCHAR(30), product_name VARCHAR(30),product_rate NUMBER)
//product_detail_info('TV_FLAT21_001' , '21 Inch Flat TV ' ,2000 )
String product_detail_info_sql = "product_detail_info('D.Info TV_FLAT21_001' , '21 Inch Flat TV ' ,2000 )";

// CREATE TYPE product_type AS object (product_type_id VARCHAR(30), product_type_Name VARCHAR(30),obj_product_detail_info product_detail_info )
// product_type (product_type_id , product_type_Name,product_detail_info_sql )
String product_type_sql= "product_type( 'TV', 'This contains TV',("+product_detail_info_sql+"))";

//CREATE TABLE Electronics_Products(Electronics_Products_id VARCHAR(30), Electronics_Products_name VARCHAR(30),obj_product_type product_type)
//Electronics_Products(Electronics_Products_id, Electronics_Products_name ,product_type_sql)
String Electronics_Products_sql = "INSERT INTO Electronics_Products VALUES('ELE_item','this Elec items',"+product_type_sql+")";

// Insert a row with values for both the object1 and object2 types
System.out.println("SQL is = \n"+Electronics_Products_sql);
int row = stmt.executeUpdate(Electronics_Products_sql);
System.out.println("Row Count"+row);
//stmt.execute("INSERT INTO object1_table VALUES(1, object1('str1', object2('obj2str1', 123)))");

} catch (SQLException e) {
System.out.println("SQLException"+ e.getMessage());
}
catch (Exception e) {
System.out.println("Exception"+ e.getMessage());
}
}
public void createTableWithObject()
{
try {
// Create an oracle.sql.ARRAY object to hold the values
// Create a statement
Statement stmt = conn.createStatement();
String prod_de_in= "CREATE TYPE product_detail_info AS object (product_detail_id VARCHAR(30), product_name VARCHAR(30),product_rate NUMBER)";
// Create the object2 type
stmt.execute(prod_de_in);
String prod_type= "CREATE TYPE product_type AS object (product_type_id VARCHAR(30), product_type_Name VARCHAR(30),obj_product_detail_info product_detail_info )";
stmt.execute(prod_type);
String elect_Prod= "CREATE TABLE Electronics_Products(Electronics_Products_id VARCHAR(30), Electronics_Products_name VARCHAR(30),obj_product_type product_type)";
stmt.execute(elect_Prod);
// Create a table with a column to hold a number and the new object1 type
} catch (SQLException e) {
System.out.println("SQLException"+ e.getMessage());
}
catch (Exception e) {
System.out.println("Exception"+ e.getMessage());
}
}

public void selectTableObjectValue()
{
try {



Statement stmt = conn.createStatement();

// Select rows from object1_table
ResultSet resultSet = stmt.executeQuery("SELECT * FROM Electronics_Products");

// Get the OBJECT values from each row
while (resultSet.next()) {
// Get the integer from the first column col_integer of the row
String ele_prod_id = resultSet.getString("ELECTRONICS_PRODUCTS_ID");
String ele_prod_name = resultSet.getString("ELECTRONICS_PRODUCTS_NAME");

// Get the object1 value from the second column col_object1
oracle.sql.STRUCT obj_PRODUCT_TYPE = (oracle.sql.STRUCT)resultSet.getObject("OBJ_PRODUCT_TYPE");

// Get the object1 values from each row
Object[] obj_PRODUCT_TYPEValues = obj_PRODUCT_TYPE.getAttributes();

System.out.println("obj_PRODUCT_TYPEValues"+obj_PRODUCT_TYPEValues.toString());

int obj_PRODUCT_length = obj_PRODUCT_TYPEValues.length;

for(int i=0;i<obj_PRODUCT_length;i++)
{
System.out.println(i+" Product Type "+obj_PRODUCT_TYPEValues[0]);
System.out.println(i+"Product Type"+obj_PRODUCT_TYPEValues[1]);

// Product Detail Info
oracle.sql.STRUCT Obj_product_detail_info = (oracle.sql.STRUCT)obj_PRODUCT_TYPEValues[2];
Object[] obj_product_detail_info_Values = Obj_product_detail_info.getAttributes();
int obj_product_detail_info_length = obj_product_detail_info_Values.length;
for(int j=0;j<obj_product_detail_info_length;j++)
{
System.out.println(j+" Product Detail Info "+obj_product_detail_info_Values[0]);
System.out.println(j+" Product Detail Info "+obj_product_detail_info_Values[1]);
System.out.println(j+" Product Detail Info "+obj_product_detail_info_Values[2]);

}
}

// Get the first value of object1, which is a string
/* String str = (String)object1Values[0];

System.out.println(""+str);

// Get the second value of object1, which is of the type object2
oracle.sql.STRUCT object2 = (oracle.sql.STRUCT)object1Values[1];

// Get the values of object2
Object object2Values[] = object2.getAttributes();
str = (String)object2Values[0];
BigDecimal num = (BigDecimal)object2Values[1];
System.out.println(""+str);*/
}


}
catch (SQLException e) {
System.out.println("SQLException"+ e.getMessage());
}
catch (Exception e) {
System.out.println("Exception"+ e.getMessage());
}
}
public void closeConnection()
{
try
{
conn.close();
}
catch (Exception e) {
System.out.println("Exception"+ e.getMessage());
}

}



public static void main(String[] args) {
AcessOracleObjects_ElectronicsItem v= new AcessOracleObjects_ElectronicsItem();
Connection conn= v.getConnection();
if(args[0].equalsIgnoreCase("C"))
{
v.createTableWithObject();
}
else if(args[0].equalsIgnoreCase("I"))
{
v.insertTableObjectValue();
}
else if(args[0].equalsIgnoreCase("S"))
{
v.selectTableObjectValue();
}
else
{
System.out.println("No vlaida option is selected");
}
v.closeConnection();
}
}


Database Configuration :
driverName=oracle.jdbc.driver.OracleDriver
serverName=139.73.41.135
portNumber=1521
username=scott
password=tiger
databaseName=facisdev
sid=facisdev