The sample code to
store the java object [serialised] in to the oracle database as Blob
and reterive the java object back from the blob
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.ObjectInput;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* This class demontrates the code
* how to put an serialised java object in to database as BLOB and
* how to get an serialised java object from database
*
*
* SQL to be executed to create table
CREATE TABLE "SAMPLE"
(
"ID" VARCHAR2(20 BYTE) NOT NULL ENABLE,
"CONTENT" BLOB,
CONSTRAINT "SAMPLE_PK" PRIMARY KEY ("ID") ENABLE
)
*
*/
public class InsertAndFetchJavaObjAsBLOB {
static String userid = "<USER_NAME>"; //user name to connect to database schema
static String password = "<PASSWORD>"; //password to connect to database schema
static String url = "jdbc:oracle:thin:@<IP_ADDRESS>:<PORT>:<SID>"; //connection string specifying IP, port and SID for database
static Connection con = null;
public static void main(String[] args) throws Exception {
boolean insert = true;
if(insert){
insertObjectToBlob(12,"Kevin", 12, 45);
insertObjectToBlob(11,"Kevin", 11, 48);
insertObjectToBlob(10,"Kevin", 10, 38);
insertObjectToBlob(9,"Kevin", 9, 33);
insertObjectToBlob(8,"Kevin", 8, 39);
insertObjectToBlob(7,"Kevin", 7, 29);
insertObjectToBlob(6,"Kevin", 6, 22);
insertObjectToBlob(5,"Kevin", 5, 27);
insertObjectToBlob(4,"Kevin", 4, 28);
insertObjectToBlob(3,"Kevin", 3, 26);
insertObjectToBlob(2,"Kevin", 2, 25);
insertObjectToBlob(1,"Kevin", 1, 26);
}
readObjectFromBlob(1);
readObjectFromBlob(5);
readObjectFromBlob(7);
readObjectFromBlob(10);
readObjectFromBlob(12);
}
/**
* This methos demontrates the code
* how to put an serialised java object in to database as BLOB and
* @param id
* @param name
* @param div
* @param rollNo
*/
public static void insertObjectToBlob(int id, String name, int div, int rollNo) {
System.out.println("Entering methos insertObjectToBlob with param id: "+id+" | name: "+name+" | div: "+div+" | rollNo: "+rollNo);
Connection con = getOracleJDBCConnection();
if (con != null) {
try{
System.out.println("Got Connection.");
DatabaseMetaData meta = con.getMetaData();
System.out.println("Driver Name : " + meta.getDriverName());
System.out.println("Driver Version : " + meta.getDriverVersion());
Statement stmt = con.createStatement();
//create a java object
Student s = new Student(name,div,rollNo);
ByteArrayOutputStream baos = new ByteArrayOutputStream();
ObjectOutputStream objOstream = new ObjectOutputStream(baos);
objOstream.writeObject(s);
objOstream.flush();
objOstream.close();
byte[] bArray = baos.toByteArray();
System.out.println("bArray = " + bArray);
PreparedStatement objStatement = con.prepareStatement("insert into sample(id,content) values (?,?)");
objStatement.setInt(1,id);
//objStatement.setBlob(2, blob );
objStatement.setBytes(2, bArray);
boolean a = objStatement.execute();
System.out.println("Result of Insert: "+a);
stmt.close();
}catch(Exception e){
e.printStackTrace();
}finally{
if(con != null){
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
} else {
System.out.println("Could not Get Connection");
}
System.out.println("Exiting methos insertObjectToBlob.");
}
/**
* This methos demontrates the code
* how to get an serialised java object from database
* @param id
*/
public static void readObjectFromBlob(int id) {
System.out.println("Entering methos readObjectFromBlob with param id: "+id);
Connection connection = null;
PreparedStatement pstmt = null;
System.out.println("Deriver name");
InputStream in = null;
try {
// Load the JDBC driver
/*String driverName = "oracle.jdbc.driver.OracleDriver";
Class.forName(driverName);*/
System.out.println("Hello");
// Create a connection to the database
connection = getOracleJDBCConnection();
System.out.println("Connectin before close :: " + connection);
ResultSet rs = null;
java.sql.Blob rs1 = null;
String queryStr = null;
// queryStr = new String("select * from CPOS_SALE_TRN_INVOICE_DTL");
queryStr = new String(
"select content from SAMPLE where id="+id);
pstmt = connection.prepareStatement(queryStr);
rs = pstmt.executeQuery();
if (null != rs) {
System.out.println("Result set found.");
while (rs.next()) {
System.out.println("Result set found. Inside while.");
rs1 = (Blob) rs.getBlob(1);
System.out.println("rs1: " + rs1);
ByteArrayOutputStream baos = new ByteArrayOutputStream();
byte[] buf = new byte[1024];
in = rs1.getBinaryStream();
System.out.println("in: " + in);
int n = 0;
while ((n = in.read(buf)) >= 0) {
baos.write(buf, 0, n);
}
System.out.println("baos: " + baos);
byte[] bytes = baos.toByteArray();
System.out.println("bytes: " + baos);
ByteArrayInputStream bis = new ByteArrayInputStream(bytes);
System.out.println("bis: " + bis);
ObjectInput in1 = new ObjectInputStream(bis);
System.out.println("in1: " + in1);
Object o = in1.readObject();
System.out.println("object : " + o);
if (o != null) {
System.out.println("Object Class: "
+ o.getClass().getName());
System.out.println(((Student)o).getName()+" | "+((Student)o).getStd()+" | "+((Student)o).getRollNo());
}
}
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if (in != null) {
try {
in.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
System.out.println("Exiting methos readObjectFromBlob.");
}
public static Connection getOracleJDBCConnection() {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException: ");
System.err.println(e.getMessage());
}
try {
con = DriverManager.getConnection(url, userid, password);
} catch (SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
return con;
}
}
