Inserting Oracle Struct Object Using JDBC

JDBC 3.0 specification includes support for User-Defined-Type (UDT) by providing various methods in the PreparedStatement, CallableStatement and ResultSet interface. Most of the database now enable you to create structured types, used todefine complex data types. This is required in case you want to create a user-defined-type (UDT) in a database. For example, you migth need to create a user-defined-type to represent the address of an employee in a single column.

Now lets understand the Struct data type by creating an application. In this application first you need to create a type in database. Following code snippet shows the example of creating a user-defined-type in Oracle.

CREATE TYPE empaddress AS OBJECT (
	flatno NUMBER,
	street varchar2(20),
	city varchar2(15),
	state varchar2(10),
	pincode NUMBER
);
CREATE TABLE personal_details (
	empno NUMBER,
	photo BLOB,
	permanent_address empaddress,
	present_address empaddress
);

After creating the empaddress type,create a java file which represent the UDT in java. To create a this file we need to follow the following rules.

• They should be a public non-abstract class .
• They should be a sub-type of java.sql.SQLData interface. SQLData interface contains getSQLTypeName(), readSQL(), and writeSQL() methods.
• They should have a no argument constructer.

Following is the java code for this java file.

import java.sql.*;
 
public class EmployeeAddress implements SQLData {
 
	public EmployeeAddress(){}
	public void writeSQL(SQLOutput so) 
			throws SQLException {
		so.writeInt(fno);
		so.writeString(street);
		so.writeString(city);
		so.writeString(state);
		so.writeInt(pin);
	}//writeSQL
 
	public void readSQL(SQLInput si, String name) 
			throws SQLException{
		fno=si.readInt();
		street=si.readString();
		city=si.readString();
		state=si.readString();
		pin=si.readInt();
		typename=name;
	}//readSQL
 
	public String getSQLTypeName()
	{return typename;}
 
	public void setFlatno(int i){fno=i;}
	public void setStreet(String s){street=s;}
	public void setCity(String s){city=s;}
	public void setState(String s){state=s;}
	public void setPin(int i){pin=i;}
	public void setTypeName(String s){typename=s;}
 
	public int getFlatno(){return fno;}
	public String getStreet(){return street;}
	public String getCity(){return city;}
	public String getState(){return state;}
	public int getPin(){return pin;}
 
	String street,city,state, typename;
	int fno,pin;
}//class

Now Observe the following java code to insert this UDT using PreparedStatement. We use setObject() method of PreparedStatement to insert this EmployeeAddress java object into Oracle column.

import java.sql.*;
import java.util.*;
import java.io.*;
 
public class InsertPersonalDetails {
 
public static void main(String s[]) throws Exception {
	Driver d= (Driver) ( Class.forName( 
	"oracle.jdbc.driver.OracleDriver").newInstance());
	Properties p=new Properties ();
	p.put("user","scott");
	p.put("password","tiger");
	Connection con=d.connect(
		"jdbc:oracle:thin:@mysys:1521:khan",p);
	PreparedStatement ps= con.prepareStatement(
		"insert into personaldetails "+
		" (empno,photo,permanent_address) values(?,?,?)");
	ps.setInt(1,7002);
	File f=new File("MyImage.gif");
	FileInputStream fis= new FileInputStream(f);
	ps.setBinaryStream(2,fis, (int)f.length());
	EmployeeAddress addr=new EmployeeAddress();
	addr.setFlatno(106);
	addr.setCity("Jalgaon");
	addr.setStreet("MG Road");
	addr.setPin(5000049);
	addr.setState("Maharashtra");
	addr.setTypeName("EMPADDRESS");
	ps.setObject(3,addr);
	int i=ps.executeUpdate();
	System.out.println("Personal Details inserted successfully");
	con.close();
	}//main
}//class