Working with Large Objects Using JDBC in Java

Sometimes as part of programming requirements, we have to insert and retrieve large files like images, video files, audio files, resumes, etc with respect to the database.

Example:

• Uploading images on the matrimonial website
• Upload resume on job-related websites

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)

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 datatype can store a maximum of “4GB” binary data. eg: sachin.jpg

Steps to insert BLOB type into the Database:

1. Create a table in the database that can accept BLOB-type data.

create table persons(name varchar2(10), image BLOB);

2. Represent the image file in the form of a Java File object.

File f = new File("sachin.jpg");

3. Create FileInputStream to read binary data represented by an 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, "katrina");

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)

6. Execute SQL query

pst.executeUpdate();

Insert BLOB type into the Database

Output:

 

Steps to Retrieve BLOB Type from Database:

1. Prepare ResultSet to object with BLOB the

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 FOS("katrina_new.jpg");

5. Read Binary Data from InputStream and write that Binary data to output Stream.

int i=is.read();
while(i!=-1)
 {
   fos.write(i);
   is.read();
 }
 
(or)

// take suitable size of array
byte[] b= new byte[2048]; 
while(is.read(b) > 0){
  fos.write(b);
}

Retrieve BLOB type from Database

CLOB (Character Large Objects)

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 be plain text or XML documents). CLOB Type can store a maximum of 4GB of data. Example: resume.txt 

Steps to insert CLOB type file in the Database:

All steps are exactly the same as BLOB, except for the following differences

1. Instead of FileInputStream, we have to take FileReader.
2. Instead of setBinaryStream() method we have to use setCharacterStream() method.

public void setCharacterStream(int index, Reader r) throws SQLException
public void setCharacterStream(int index, Reader r, int length) throws SQLException
public void setCharacterStream(int index, Reader r, long length) throws SQLException

Output:

 

Steps to Retrieving CLOB Type from the Database:

All steps are exactly the same as BLOB, except for the following differences.

1. Instead of using FileOutputStream, we have to use FileWriter
2. Instead of using getBinaryStream() method we have to use getCharacterStream() method.

What is the Difference Between BLOB and CLOB?

We can use BLOB Type to represent binary information like images, video files, audio files, etc. Whereas we can use CLOB Type to represent Character data like text files, XML files, etc.