Recent Posts

Thursday, 15 November 2018

Working with Large Objects - BLOB

 
      Sometimes as the part of programming requirement,we have to insert and retrieve large files like images,video files,audio files,resume etc wrt database.
E.g: upload image in socialweb sites, upload resume in job related web sites.
     To store and retrieve large information we should go for Large Objects(LOBs). There are 2 types of Large Objects.
1. Binary Large Object (BLOB)
2. Character Large Object (CLOB)
1. Binary Large Object (BLOB)
☀ A BLOB is a collection of binary data stored as a single entity in the database.
☀ BLOB type objects can be images,video files,audio files etc..
☀ BLOB data type can store maximum of "4GB" binary data.

2. Character Large Objects (CLOB)
☀ A CLOB is a collection of Character data stored as a single entity in the database.
☀ CLOB can be used to store large text documents(may plain text or xml documents)
☀ CLOB Type can store maximum of 4GB data.

Steps to insert BLOB type into database
1. create a table in the database which can accept BLOB type data.
   create table persons(name varchar2(10),image BLOB);
  
2. Represent image file in the form of Java File object.
   File f = new File("ashok.jpg");
  
3. Create FileInputStream to read binary data represented by image file.
   FileInputStream fis = new FileInputStream(f);

4. Create PreparedStatement with insert query.
   PreparedStatement pst = con.prepareStatement("insert into persons values(?,?)");
  
5. Set values to positional parameters.
   pst.setString(1,"ashok");
  
   To set values to BLOB datatype, we can use the following method: setBinaryStream()
☀ public void setBinaryStream(int index,InputStream is)
☀ public void setBinaryStream(int index,InputStream is,int length)
☀ public void setBinaryStream(int index,InputStream is,long length)
E.g:
pst.setBinaryStream(2,fis); ➔ Oracle 11g
pst.setBinaryStream(2,fis,(int)f.length()); ➔ Oracle 10g

6. execute sql query
pst.executeUpdate();
import java.sql.*;
import java.util.*;
import java.text.*;
**
 * 
 * @author ashok.mariyala
 *
 */
public class BlobInsertTest { 
   public static void main(String[] args) throws Exception { 
      Class.forName("oracle.jdbc.OracleDriver");
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott","tiger"); 
      String sqlQuery = "insert into persons values(?,?)"; 
      PreparedStatement pst = con.prepareStatement(sqlQuery); 
      pst.setString(1,"ashok"); 
      File f = new File("ashok.jpg");
      FileInputStream fis = new FileInputStream(f);
      ps.setBinaryStream(2,fis);
      System.out.println("inserting image from :"+f.getAbsolutePath());
      int rowCount = pst.executeUpdate(); 
      if(rowCount == 0) 
         System.out.println("Record Not inserted"); 
      else
         System.out.println("Record inserted"); 
      con.close();
   }
}
Retrieving BLOB Type from Database
     For retrieving BLOB Type from Database, we can use either simple Statement or PreparedStatement.


     Because of Buffer we have to perform only 10 Read Operations & 10 Write Operations.

Steps to Retrieve BLOB type from Database

1. Prepare ResultSet object with BLOB type
   RS rs = st.executeQuery("select * from persons");

2. Read Normal data from ResultSet
   String name=rs.getString(1);
  
3. Get InputStream to read binary data from ResultSet.
   InputStream is = rs.getBinaryStream(2);
  
4. Prepare target resource to hold BLOB data by using FileOutputStream
   FileOutputStream fos = new FileOutputStream("ashok_db.jpg");
  
5. Read Binary Data from InputStream and write that Binary data to output Stream.


import java.sql.*;
import java.util.*;
import java.text.*;
**
 * 
 * @author ashok.mariyala
 *
 */
public class BlobRetriveTest { 
   public static void main(String[] args) throws Exception { 
      Class.forName("oracle.jdbc.OracleDriver");
      Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","scott","tiger"); 
      String sqlQuery = "select * from persons"; 
      PreparedStatement pst = con.prepareStatement(sqlQuery); 
      ResultSet rs = pst.executeQuery(); 
      FileOutputStream os = new FileOutputStream("ashok_db.jpeg");
      while(rs.next()) { 
         String name = rs.getString(1); 
         InputStream is = rs.getBinaryStream(2); 
         byte[] buffer = new byte[2048];
         while(is.read(buffer)>0) {
            os.write(buffer);
         }
         os.flush(); 
         System.out.println("Image is available in :ashok_db.jpeg");
      }
      con.close();
   }
}

Next Tutorial : Working with Large Objects - CLOB

Previous Tutorial : Retrieving Date values from the database

No comments:

Post a Comment