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