How to insert java Object in oracle database

[Thilkumar82]

Hi,
I want to insert java Object into oracle database..
I am using BLOB column and code as follows ..

Data1 data1=new Data1();
data1.a=500;
data1.b=600;
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:oci:@mydb ", "username", "pwd");
PreparedStatement pst = con.prepareStatement("insert into obj_temp(user_id,data_object) values (?,?)");
pst.setInt(1,10);
pst.setObject(2,data1);

when executing this .. i am getting "Invalid column type" SQLException..
I am doing this for later use of this object from database..
here i want to get A's value 500 and B's value 600 when i retrieving the object from datadase..

Thnks in Advance,
Thilkumar

[baskar.nitt]

Hi ,
To store java objects in oracle db you have to convert the object to byte array. Check the following sample...
It will help you.. (insert objects to oracle db and retrieve objects from oracle db)

package com.samples;

import java.io.*;
import java.sql.*;
import oracle.jdbc.*;


public class Check1
{
	public static void main(String [] args) throws Exception
	{

		BufferedReader reader=null;
		try
		{
			reader = new BufferedReader(new InputStreamReader(System.in));

			System.out.println("1. Insert rows");
			System.out.println("2. Select rows");
			System.out.println("Enter your option : ");

			int option = Integer.parseInt(reader.readLine());

			switch(option)
			{
				case 1:
					insertRows();
					break;

				case 2:
					selectRows();
					break;
				default:
					System.out.println("Pls enter your option between 1 to 2");
			}


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

	public static void insertRows()
	{
		Connection connection =null;

		try
		{
				Class.forName("oracle.jdbc.driver.OracleDriver");

				connection = DriverManager.getConnection("jdbc:oracle:thin:@servername:1521:orcl", "username", "password");

				Data1 data1=new Data1(300,500);

				ByteArrayOutputStream baos = new ByteArrayOutputStream();
				ObjectOutputStream objOstream = new ObjectOutputStream(baos);
				objOstream.writeObject(data1);
				byte[] bArray = baos.toByteArray();

				System.out.println("*** bArray = " + bArray);

				PreparedStatement objStatement = connection.prepareStatement("insert into sample(user_id,data_object) values (?,?)");

				objStatement.setInt(1,10);
				objStatement.setBytes(2, bArray);
				objStatement.execute();

		}
		catch(SQLException sqlEx)
		{
			sqlEx.printStackTrace();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			connection=null;
		}

	}

	public static void selectRows()
	{
		Statement stmt = null;
		ResultSet rs = null;
		String sQuery ="";

		Connection connection =null;

		try
		{
			Class.forName("oracle.jdbc.driver.OracleDriver");

			connection = DriverManager.getConnection("jdbc:oracle:thin:@servername:1521:orcl", "username", "password");

			sQuery = "select * from sample";
			byte [] newArray = null;
			stmt = connection.createStatement();
			stmt.executeQuery(sQuery);
			rs = stmt.getResultSet();

			while(rs.next())
			{
				newArray = rs.getBytes("data_object");

				System.out.println("newArray = " + newArray);

				ObjectInputStream ois = new ObjectInputStream(new ByteArrayInputStream(newArray));

				Data1 objData1 = (Data1) ois.readObject();

				ois.close();

				if(objData1 == null)
				{
					System.out.println("Data1 Object is null");
				}
				else
				{
					System.out.println("a = " + objData1.geta() + " b = " + objData1.getb());
				}

			}
			rs.close();

		}
		catch(SQLException sqlEx)
		{
			sqlEx.printStackTrace();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			connection=null;
		}
	}
}


Data1.java:
============

package com.samples;

import java.io.*;

public class Data1 implements Serializable
{
	private int a;
	private int b;

	public Data1()
	{ }

	public Data1(int a, int b)
	{
		this.a=a;
		this.b=b;
	}

	public int geta()
	{
		return this.a;
	}
	public void seta(int a)
	{
		this.a=a;
	}


	public int getb()
	{
		return this.b;
	}

	public void setb()
	{
		this.b=b;
	}

}

[Thilkumar82]

Thank you so much basker,
It works for inserting into db.. but gives following error for selecting
"
java.io.StreamCorruptedException: invalid stream header
at java.io.ObjectInputStream.readStreamHeader(Unknown Source)
at java.io.ObjectInputStream.<init>(Unknown Source)
at Check1.selectRows(Check1.java:110)
at Check1.main(Check1.java:29)
"

Regards,
Thilkumar

[baskar.nitt]

whats your java version?

[baskar.nitt]

Replace the insertRows() method with the following code and check it.
The code which i have posted first time is works fine in jdk 1.5... so pls check with jdk 1.5..

public static void insertRows()
	{
		Connection connection =null;

		try
		{
				Class.forName("oracle.jdbc.driver.OracleDriver");

				connection = DriverManager.getConnection("jdbc:oracle:thin:@ramcobl104:1521:orcl", "wfuser", "wfuser");

				Data1 data1=new Data1(300,500);

				ByteArrayOutputStream baos = new ByteArrayOutputStream();
				ObjectOutputStream objOstream = new ObjectOutputStream(baos);
				objOstream.writeObject(data1);

				objOstream.flush();
				objOstream.close();

				byte[] bArray = baos.toByteArray();

				System.out.println("*** bArray = " + bArray);

				PreparedStatement objStatement = connection.prepareStatement("insert into sample(user_id,data_object) values (?,?)");

				objStatement.setInt(1,10);
				objStatement.setBytes(2, bArray);
				objStatement.execute();

		}
		catch(SQLException sqlEx)
		{
			sqlEx.printStackTrace();
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			connection=null;
		}

	}

[Thilkumar82]

Hi baskar,
I got solution for select from db using the foll,

InputStream is = rs.getBlob("data_object").getBinaryStream();
ObjectInputStream oip = new ObjectInputStream(is);
Object object = oip.readObject();

It is working correctly..
Thanks a lot..
One more doubt .. My java version is j2sdk1.4.2_03
but when get from DOS promt command (java -version)
it gives

java version "1.5.0_06"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_06-b05)
Java HotSpot(TM) Client VM (build 1.5.0_06-b05, mixed mode)

why?

Regards,
Thilkumar

[baskar.nitt]

The path is set to jdk 1.5 in your comp.

Go to My Computer icon -> right click --> properties -->Advanced Tab --> Click Environment Variables--> Path

Check the value of Path variable. It may set to JDK 1.5.

[jack239]

Yep I think the environmental PATH variable is referring to the 1.5 version.

[Thilkumar82]

Everything is refering to jdk1.4 but .. in my system we installed jre 1.5...

i think this is the reason for that..

Thanks..
thilkumar

[baskar.nitt]

yes.... jre 1.5 installation is the exact reason..