BLOB

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 social web 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

ResultSet 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();
   }
}
BLOB


Scroll to top