Storing BLOB,CLOB and Array type object:

Blob(binary large object)- to store binary objects like image, audio, video etc
Clob(character large object)- To store text information like files.
We can manipulate these type of data without storing all of their data in the database server to the client machine. This is because any instance of these types is actually a locator(logical pointer) to the object in the data base represented by their instances.Performance increases due to this feature.
But we can store SQL datatype BLOB,CLOB and Array on client machine with help of methods in the Blob,Clob and Array type interfaces.
Inserting image to database:
Example:
Create table in the database
Create table image(id number(10),pic blob);
Inserting demo.jpg into the image table in the database.
File file=new File("demo.jpg");
InputStream i=new FileInputStream(file);
stmt=con.prepareStatement("update image set pic=? where id=1");
stmt.setBinaryStream(1,i,(int)(file.length()));
stmt.executeUpdate();
System.out.println("image inserted successfully");
Getting the image from database:
while(rs.next()){
InputStream is=rs.getBinaryStream(2);
FileOutputStream fos=new FileOutputStream("new.jpg");
while((i=is.read())!= -1){
fos.write(i);}
fos.close();
The above program retrives the image from the data base and store as new.jpg
Array type:
In SQL
Create type pnos as varray(3) of number(10); => creating array type
create table Customer(cid number(2),cname varchar2(10),phonenos pnos); => creating table with array values.
program to insert data:
import oracle.sql.*;//for ArrayDescriptor
PreparedStatement ps=con.prepareStatement("insert into Customer values(?,?,?)");
ps.setInt(1,11);
ps.setString(2,"DEF");
Integer v[]={1111,5555,8888};
ArrayDescriptor ad=ArrayDescriptor.createDescriptor("PNOS",con);
ARRAY arr=new ARRAY(ad,con,v);//for Oracle
//java.sql.Array arr=con.createArrayOf("NUMBER",v);//Other than oracle
ps.setArray(3,arr);
ps.executeUpdate();
System.out.println("Row Inserted Successfully");
Retrive data:
import oracle.sql.*;
con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","advjava","advjava");
Statement st=con.createStatement();
ResultSet rs=st.executeQuery("select cid,cname,pnos from customer where cid=11");
while(rs.next()){
System.out.print(rs.getInt(1)+"\t"+rs.getString(2)+"\t");
Array a=rs.getArray(3);
ResultSet rs1=a.getResultSet();
while(rs1.next()){
System.out.print(rs1.getString(2)+",");
}