BLOB

In this tutorial, we are going to discuss about BLOB (Binary Large Object). 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.

Binary Large Object (BLOB)
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();
   }
}

That’s all about the BLOB in JDBC. If you have any queries or feedback, please write us email at contact@waytoeasylearn.com. Enjoy learning, Enjoy Java.!!

BLOB